Forum Discussion
JonAust
Mar 03, 2023Copper Contributor
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...
PeterBartholomew1
Mar 03, 2023Silver Contributor
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!
PeterBartholomew1
Mar 03, 2023Silver Contributor