How to calculate IRR for regular intervals but varying dates

[jonaust]

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): IRR Example Calculation.xlsx

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

https://techcommunity.microsoft.com/t5/image/serverpage/image-id/447537i4C691A8B680FA5F0/image-size/medium?v=v2&px=400

https://techcommunity.microsoft.com/t5/image/serverpage/image-id/447538i7FA8A229D70F4A91/image-size/medium?v=v2&px=400



I want the IRR to calculate from today's date, but also not starting 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?

 

[GBeavis]

=IRR(Range of Cash Flows, Range of Dates)

Sure! In Excel, you can use the built-in IRR function to calculate the Internal Rate of Return for multiple properties with different start and end dates. The IRR function takes a series of cash flows (investments and returns) and their corresponding dates as input and returns the IRR as the result.

Here's an example of a formula that you can use to calculate the IRR for multiple properties with different start and end dates:

excelCopy code
=IRR(Range of Cash Flows, Range of Dates)

In this formula:

• "Range of Cash Flows" refers to the range of cash flows (investments and returns) for a particular property. This range should include all the cash flows for that property, including the initial investment (negative value) and subsequent returns (positive values).
• "Range of Dates" refers to the range of dates corresponding to the cash flows in the "Range of Cash Flows". This range should include the dates for each cash flow, and the dates should be entered in chronological order.

You can use this formula for each property separately, inputting the respective ranges of cash flows and dates for each property. The formula will then return the IRR for each property based on its individual cash flows and dates.

For example, if you have three properties with cash flows and dates in ranges A1:B5, A6:B10, and A11:B15, respectively, you can use the following formulas to calculate the IRR for each property:

• For Property 1: =IRR(A1:A5, B1:B5)
• For Property 2: =IRR(A6:A10, B6:B10)
• For Property 3: =IRR(A11:A15, B11:B15)

Note that the IRR function may require an initial guess value for the IRR, which you can provide as an optional argument after the ranges of cash flows and dates. If you don't provide a guess value, Excel will use a default guess of 0.1 (10%). You can specify a different guess value if needed, depending on the characteristics of your cash flows.