Forum Discussion

JonAust's avatar
JonAust
Copper Contributor
Mar 03, 2023

How to calculate IRR for regular intervals but varying dates

Hi everyone, I am trying to create a formula to calculate the IRR for multiple properties each with a different start and end date. The properties are combined into one spreadsheet, each column repre...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 03, 2023

JonAust 

I have had a stab at this using Excel 365.

WorksheetFormula
= MAP(dateStart, dateEnd, initialInvestment, SEQUENCE(ROWS(amounts)), IRRλ)

IRRλ(s, e, inv, p)
= LET(
    currentAmt, TAKE(DROP(amounts, p - 1), 1),
    firstAmt,   XLOOKUP(MAX(s, Today), date, currentAmt, , 1),
    finalAmt,   XLOOKUP(e, date, currentAmt, , -1),
    irr,        IRR(HSTACK(inv, firstAmt:finalAmt)),
    irr
  )

I wouldn't know whether the values returned are right or wrong; at least they are numbers!

Resources