Formula for the NPV of a future interim period

[kgimber]

We're able to accurately calculate cumulatively the NPV of a certain number of periods (in this example, seven) however, using the NPV function, we are unable to ascertain an NPV for one specific interim period within the range.

Is there a built in function in Excel that will do this for us or will we need to write something in VBA?

Thanks in advance for your assistance.

(Workbook attached for reference)NPV Example.xlsx

[Ace_XL]

What is your expected result?

[kgimber]

$3.50 in my example is the correct value, but we're hoping to be able to use one formula in one step as opposed to a two step process.

[Ace_XL]

You do have that in Row 9..

maybe
=+H2/(1+$C$3)^SUBSTITUTE(H1,"Yr","")*1

[kgimber]

Thanks. That is what one of my colleagues came up with. Seems to me that there must be some more "graceful" way to do it, but I won't argue it if works!

[MoneyMaker]

Thanks. That is what one of my colleagues came up with. Seems to me that there must be some more "graceful" way to do it, but I won't argue it if works!

If you are attempting to find present value of each of the cash flows separately then you can make use of the PV function which is used as follows

=PV( rate, nper, pmt, fv, type )

So for your data, you may select any cells from the cash flows to find the present value of such cash flow

For this particular cell, you can type in the following formula

=PV(C3, 4, 0, -H2)

where C3 is the discount rate
4 is the time period of the cash flow
0 for pmt since the payment in not an annuity but a lump sum
-H2 where H2 contains the cash flows of $6.80 whose present value you are seeking

The answer is $3.50