XIRR function with relative references

Hello,

i am seeking some helping building a formula using XIRR to calculate the IRR over a certain range that could be a subset of the entire cash flow strip. my thought was to use relative references to offset from a starting point and use the CELL address formula to build the cell references. I am not married to this approach if anyone has a better way i would really appreciate it. please see attached example and please let me know if i can further explain anything, thank you!

Using an array formula (you may have to enter with Ctrl+Shift+Enter if normal enter does not work), E13=XIRR(OFFSET(J10,0,$C$14,1,$C$15-$C$14+1),OFFSET(J6,0,$C$14,1,$C$15-$C$14+1))

Why not avoid OFFSET?

Formula:

Please Login or Register  to view this content.

My personal mania: eliminate all unnecessary volatile function calls.

hrlngrv, correcting your formula: E13=XIRR(INDEX(K10:CL10,C14):INDEX(K10:CL10,C15),INDEX(K6:CL6,C14):INDEX(K6:CL6,C15))

OFFSET is much more convenient to use because you don't need to specify the range and makes no significant difference unless you use OFFSET extensively throughout your workbook.

@exceltrainee314.... GIGO? I question the correctness of varying the subrange of cash flows arbitrarily. If you share my doubts, explain the cash flows.

Usually, a valid cash flow model for calculating an IRR has an initial balance (or investment) and final balance, with external cash flows in between.

So, for each subrange, the initial balance and corresponding date might be the same. And we need to know the final balance for the date of each possible subrange.

In models of business operations, where the "external" cash flows are net profit, the "initial investment" would be the initial net working capital (NWC); and the "final balance" would be the net profit minus the original NWC.

Thank you! this worked great, nice and simple

thank you!