Calculating IRRs

[ATG]

Hi - I have a table with a list of cash flows, dates. These are linked to another table which contains individual deals. I want to calculate, for each deal, the IRR - in Excel this is using the IRR function which takes a list of dates and a list of cash flows and returns a single IRR number.

Can this be done in Airtable? Rollups seem to only allow calculations involving one linked field only - whereas I need two: both date and cashflow. Also, there doesn’t seem to be an IRR function. The calculation is relatively straight forward, can I code my own function to support this?

Secondary question - right now I have to manually for each date/cash flow record link it to a given deal. Is there a way to link all cash flows where dealID equals x?

[kuovonne]

Airtable does not have an IRR function. You can see the complete list of functions in the formula field reference.

Do you want to solve the same formula as in this post?

If you want to rollup information based on two fields in the linked record, create a formula field in the original table, then roll up the formula field.

If you want to calculate information across multiple rows and columns, that is more difficult and cannot be done easily in Airtable with just rollups and formulas. It can be done by writing a custom script in the Scripting block.

[ATG]

No - this is a different calculation than APR but similar in concept. For example, I invest $100 on day 1, and get $100 in 1 year, and another $200 in another year. The IRR is the discount rate where the present value of the cash flows after day 1 is equal to the value of the investment on day 1. (The answer for this example is 100%). The calculation involves two columns of data (date and cash flow) and multiple rows (more than two but potentially lots) and is solved by finding roots of a polynomial numerically.
Sounds like it has to be a custom scripting block.

This is a pretty function in Excel - hard to convert any financial users to Airtable if this (and other similar) functions are not offered as part of a standard Airtable package. Just a friendly suggestion.

Thanks

[ATG]

In case anyone cares, sample code calculating IRRs using the Newton Raphson method. Took way too long - this really should be part of the standard package of functions.

filtered is a globally defined RecordQueryResult (filtered for the relevant cashflows).

function calcIRR()
//Calculate IRR via Newton/Raphson method:
{
let estimate = Math.pow(1, 1/365) - 1; //Convert to daily rates
var adj=100;
for(adj = 100; Math.abs(adj) > 0.000001; )
{
adj = fX(estimate)/dfX(estimate);
estimate += adj;
}
return IRR = Math.pow(1+estimate, 365)-1; //Convert back to annual
}

function DateDiff(a, b)
//Calculate number of days between two dates
{
return ((b - a)/(10006060*24));
}

function fX(guess)
//Calculate f(X) aka present value of cashflows with discount rate “guess”
{
let total = 0;
let date0 = new Date(filtered[0].getCellValue(BaseSpecificNames.cashFlowDates));
let t = 0;
let datet = new Date;
for (let record of filtered)
{
datet = new Date(record.getCellValue(BaseSpecificNames.cashFlowDates));
t = DateDiff(date0, datet);
total += ((record.getCellValue(BaseSpecificNames.cashFlows)) / Math.pow(1+guess,t));
}
return total;
}

function dfX(guess)
//Calculate derivative of f(X)
{
let total = 0;
let date0 = new Date(filtered[0].getCellValue(BaseSpecificNames.cashFlowDates));
let t = 0;
let datet = new Date;
let value = 0;
for (let record of filtered)
{
datet = new Date(record.getCellValue(BaseSpecificNames.cashFlowDates));
t = DateDiff(date0, datet);
value = record.getCellValue(BaseSpecificNames.cashFlows);
total += ((t*value) / Math.pow(1+guess,t+1));
}
return total;
}

[Bill_French]

ATG:

sample code calculating IRRs using the Newton Raphson method. Took way too long - this really should be part of the standard package of functions.

Agree. I’ve run into similar issues with complex machine learning computations. To get the job done I had to use a script block that called into a Python app which performed the computations and returned the result. Very ugly. Like turning a light switch off with a wrecking ball.

But, in this case you may be hitting the known floating point bug in Airtable. This function might avert the bug while speeding it up. I’m not an expert in matters of IRR, I have this function in our libraries from another Firebase project I did a while back.

function IRRCalc(CArray) {

  min = 0.0;
  max = 1.0;
  do {
    guest = (min + max) / 2;
    NPV = 0;
    for (var j=0; j<CArray.length; j++) {
          NPV += CArray[j]/Math.pow((1+guest),j);
    }
    if (NPV > 0) {
      min = guest;
    }
    else {
      max = guest;
    }
  } while(Math.abs(NPV) > 0.000001);
  return guest * 100;
}

ATG:

Is there a way to link all cash flows where dealID equals x?

Yes, this can be done in a script block.

One last idea - you could use a script block to call out to a service in Google Sheets, let it handle the IRR computation and return the results.

[ATG]

I might have hit the bug also - had some small discrepancies a few decimal points in on most of the sums. I assumed it was due to conversion between Airtable float and Javascript float or something like that.

The solutions to the root are usually in a pretty tight range though, I did some tests and it seems it wasn’t sufficient to cause problems here.

Thanks for the code snippet. I wanted to use Newton Raphson with repeated Airtable database calls mostly as a test. It’s a little more complicated, converges as an algorithm much more quickly, but incurs much bigger overhead of Airtables database calls - and most importantly, might be similar to how a math savvy business user might end up coding things.

Using Python or Google sheets is too clunky.

This is the sort of thing that ought to be addressed immediately in an enterprise product

[Jimmy_Driver]

Hi - I am an absolute beginner on the scripting block but I have a need to create and XIRR function in my table. Ideally the formula would generate and XIRR based on all cash flows linked to the record. Would one of you all who commented direct me as to what code to copy into my base?

[Mehul_Bhandari]

There needs to be separate Block/Roll Up Formula for Finance formulas esp. calculation of emi ,irr, xirr, npv . simple interest (SI) etc.

[JonahM]

Hi there - is there any update regarding performing IRRs calculations on Airtable?

[ScottWorld]

On2Air: Actions offers an IRR (internal rate of return) function for use in Airtable, along with several other financial calculations as well:

Function - Finance

Function - Finance

Perform Financial Calculations on Records like determining Present Value of investments, Future Value, Interest Payments, Payments, Principal Payments, Rate, Net Present Value, and Internal Rate of Return