How to calculate IRR for regular intervals but varying dates

Copper Contributor

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 represents a different month and each row represents a different property. The cash flow for each property is reflected under the corresponding month.

 

I'm including a link to an example spreadsheet (the actual spreadsheet is a lot larger than this with a lot more properties and dates, but this should get me started): https://www.dropbox.com/s/od9tkrfb4tpe8ti/IRR%20Example%20Calculation.xlsx?dl=0 

 

If easier, here are a couple of screenshots of the example:

JonAust_0-1677862393307.png

 

JonAust_1-1677862417855.png

 

I want the IRR to calculate from today's date, but also not start earlier than the start date. In the actual spreadsheet, in most cases there will be future cash flows that show up beyond the "end date" that I don't want to include in the IRR calculation (i.e. when the lease moves into an option period), but I only want the IRR calculation to look at the dates between the start / today's date and the ending date.


Finally, time period 0 should include the amount shown in "Initial Investment" and "Date End" should include both the cash flow of that month PLUS the initial investment.

 

Does anyone know how to create a formula for this?

3 Replies
Sorry I don't know what IRR is (to me it is inter-rater reliability) but read that it is some rate of return and presume you basically need a sum of value within that date range something like:
=SUMIFS($F2:$AC2, $F$1:$AC$1, ">" & $B2, $F$1:$AC$1, "<" & $C2, $F$1:$AC$1, "<" & TODAY() )

@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!