Calculating IRRs

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.

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

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;
}

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;
}

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.

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;
}

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

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?

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

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

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

I posted a short script awhile back for it

do you have experience with their extension? any thoughts?

@Hannah_Wiginton knows all about it! :slightly_smiling_face:

Hey @JonahM

The IRR automation is in our On2Air Actions app. You can start a free trial to test it out.

We can also do a demo and free setup for you if you prefer that route.

Thank you @ScottWorld !

thanks @Hannah_Wiginton @ScottWorld @ATG ! will take a look at the extension as well as the script
thanks!

This helped me a lot!
Thanks, I thought you should've had some recognition