Calculate IRR with a dynamic range?

[Capt. Awesome]

Hi there,
I'm a newb, please take it easy on me. :-)
I have an income stream that could vary between 15 and 60 years which I want to calculate IRR from. My current formula is as follows : =ROUND(IRR(Stream!F3:F362,0),4), this works great for the fixed 360 interval period (months for 30 years). But what if the investment be only for 10 years? I do not want to calc an IRR for 360 months. I have tried using the OFFSET function with F3 as a reference but must be doing something wrong. I made a new cell for 'IRR term' and tried to input the number of cells to be included in the IRR range but it failed. Do I use this number as 'row', 'height, or both in the OFFSET function?
Any help is greatly appreciated!
Capt.

[Jacc]

Something like this, perhaps?

Please Login or Register  to view this content.

[Capt. Awesome]

Well sort of, but what if the 359 need only be 240, that is where I'm having issues.

[Jacc]

Like this then? Put any value you want in cell A1.

Please Login or Register  to view this content.

[Capt. Awesome]

Thanks so much for your prompt responses Jacc, but this is still not working for me, #NUM error. This is what I'm trying =ROUND(IRR(OFFSET(Stream!F3,F10,0),0),4) Obviously the F10 cell is my variable input. What could I be doing wrong?

[Capt. Awesome]

I figured it out, using 'Stream!F10' for the 'row' input doesn't work, rather, the place for height needs to be 'Stream!F10' and rows needed to be left at nil. Works like a charm now! Here's the new (working) formula =ROUND(IRR(OFFSET(Stream!F3,0,0,Model!F10*12),0),4).
Szep napot mindenki!

[Jacc]

Ah, I see now that I made a mistake, I should have tested it properly.
Good that you found a solution and that you shared it here.