NPV without repetitive cash flows

[lukihnio]

Hi,
Is there any way get NPV of uneven cash flows, without listing all cash flows? I'm hoping to replicate the Financial Calculators CF function, for instance BA II plus has CF function. You don't have to list all cash flows, you just point out how many time each cash flow should be paid out.
Thanks in advance

 

[joeu2004]

One way: array-enter (press ctrl+shift+Enter instead of just Enter) the following:

=NPV(1%, 123, 456*(ROW(CF1:CF12)>0), 789, 1234*(ROW(CF1:CF5)>0), 5678)

That calculates the NPV for the following series of cash flows: 123, 456 for 12 periods, 789, 1234 for 5 periods, 5678.

The reference to column CF can be any column. Of course, ROW(cellref)>0 is always true (1).

If you want 12 and 5 to be variables (e.g. their values are in B3 and B5), I would use CF1:INDEX(CF:CF,B3,1), for example. I would not use OFFSET or INDIRECT if you repeat the NPV formula many hundreds (thousands) of times.

 

[lukihnio]

Thank you so so much I'll give it a try and let you know.

 

[lukihnio]

It works perfectly. Thank you so so much

 

[joeu2004]

You're welcome.