XIRR with dynamic range based on date input (OFFSET)

Does this get what you want?

Please Login or Register  to view this content.

Glad it basically works. Thanks for the rep too.

Originally Posted by TripleG-CO

The only limitation I found when converting my example into the spreadsheet I am working with is that it can't calculate a negative XIRR. It seems to default to 0%

FYI, Excel XIRR does not default to 0%, and it is not limited to non-negative results. It should be limited to more than -100%. But unfortunately, Excel XIRR sometimes returns results less than -100%.

However, Excel XNPV does not work with negative discount rates. (Excel NPV does.)

And beware that Excel XIRR sometimes returns bogus results, as well as incorrect #NUM and #DIV/0 errors, especially with unrealistic cash flows. So it is prudent to check that the Excel XIRR results does truly cause the NPV to be close to zero.

(Use SUMPRODUCT instead of Excel XNPV, since Excel XNPV does not accept negative discount rates.)

Originally Posted by TripleG-CO

I have a monthly construction period and then semiannual operating periods for the cash flows. If I do a running XIRR using one construction outflow, then starting with operating inflows/outflows after that then I get the negatives I would expect.

My issue then becomes not only how to tell excel to pick an end date for the IRR, but to also look for a dynamic start date.

It is easy to specify both dynamic start and end dates. But I'm not sure the calculated IRR is truly the result that you need.

First, I avoid using OFFSET because it is a "volatile" function. That means the formula as well as any dependent formulas are recalculated every time Excel (re)calculates any cell in any worksheet in the workbook. So I would have written the original formula as:

Please Login or Register  to view this content.

So if the start date is in B2, we simply replace C5 and B5 as follows:

Please Login or Register  to view this content.

However, that seems impractical since a cash flow on any particular start date is probably not the initial cash flow for the remaining series of cash flows.

For example, if you just want the IRR for the semiannual operating periods, simply setting B2 to 5/31/2020 or 6/30/2020 will not calculate the intended IRR.

Instead, I suspect you want the initial cash flow to be the sum (or NPV or NFV) of the construction cash flows ("using one construction outflow").

Although it is not difficult to construct a dynamic sum using INDEX/MATCH again, you would need to alter the table of operating cash flows so that the initial construction sum is properly signed and in the cell before the first operating cash flow.

I would need to presume too much in order to speculate such a change to your design.

-----

PS.... Of course, if you are enamored to OFFSET, I could have simply changed the nigelbloomy's formula as follows:

Please Login or Register  to view this content.

@Joeu I like your construction. I forget that index can be used after a colon to create a range. Thank you for the reminder.

Originally Posted by TripleG-CO

That new formula using INDEX for the start and end dates worked in the model I am working on. [....] Any idea how to apply the same concept to the XNPV?

Since Excel XNPV does not accept negative discount rates (for no good reason), I suggest using SUMPRODUCT. The mathematical NPV formula can be found in the Excel XIRR (and XNPV) help page. For example:

Please Login or Register  to view this content.

where B1 is the end date, B2 is the start date, B3 is the annual discount rate, C5:C81 is the range of cash flows, and B5:B81 is the range of corresponding dates.

PS.... It would be more efficient to put the two MATCH expressions in separate cells, and reference the cells in the SUMPRODUCT and XIRR formulas.

FYI, with your example data, the SUMPRODUCT formula returns about -0.5089. You might not think that is "close to zero". But it is, relative to the magnitude of the cash flow values (-301M to 154M !!). My own XIRR implementation does produce a discount rate that causes the NPV to be much closer to zero (about -1.69E-8). But the difference between the two IRRs is only about 0.0000008029%.