NPV of Cash Flow Payments

[AdmiralAJ]

I have been trying to find a simple answer to my NPV problem without
creating all the monthly cash flows if I have only the following
information. I want to NPV a stream of payments that changes every
year. For example this is what I know

Months Monthly Pmt Amt
1-12 $3.00
13-24 $3.12
25-36 $3.26
37-48 $3.40
49-60 $3.50

Using a 6% discount rate how do I generate the NPV without creating
cells to hold each months payment? Is there a way or am I just
wishfully thinking here? Any help would be appreciated.

AJ

 

[joeu2004]

Months Monthly Pmt Amt
1-12 $3.00
13-24 $3.12
25-36 $3.26
37-48 $3.40
49-60 $3.50
Using a 6% discount rate how do I generate the NPV without
creating cells to hold each months payment?

In this particular case, where the payment amount changes on a regular
basis (every 12 months), the most direct (but least flexible) solution
might be the following array formula (commit with ctrl-shift-Enter):

=SUM( PV(6%/12,12,-{3;3.12;3.26;3.4;3.5},0,0) / (1+6%/
12)^(12*(ROW($1:$5)-1)) )

Note that the PV "type" argument is 0, which means that payments are
presumed to be at the end of each period. That gives the same result
as the Excel NPV() function. But I suspect you want "type" to be 1,
which means that payments are at the beginning of each period.

Of course, the formula can be modified in many ways in order to make
it more general. For example, instead of -{3;...}, you might use -
A1:A5, a range that contains the payment amounts. And instead of
12*(row(...)-1), you might use (B1:B5-1), a range that contains the
payment number each time the repeated amount changes (i.e. corresponds
to A1:A5). Lastly, instead of 6%/12, you might use C1, a cell that
contains the monthly rate.

Finally, when you wrote "a 6% discount rate", I assumed that is the
nominal annual rate; ergo, 6%/12 is the monthly rate. If 6% is the
monthly rate, obviously you would use that instead of 6%/12 (in two
places). But if 6% is the APY (compounded rate), the monthly rate
would be RATE(12,0,-1,1+6%) or (1+6%)^(1/12)-1. Thus, (1+m)^12-1 is
6%, where "m" is the monthly rate. This comment applies to the use of
NPV as well.

HTH.

 

[AdmiralAJ]

In this particular case, where the payment amount changes on a regular
basis (every 12 months), the most direct (but least flexible) solution
might be the following array formula (commit with ctrl-shift-Enter):

=SUM( PV(6%/12,12,-{3;3.12;3.26;3.4;3.5},0,0) / (1+6%/
12)^(12*(ROW($1:$5)-1)) )

Note that the PV "type" argument is 0, which means that payments are
presumed to be at the end of each period. That gives the same result
as the Excel NPV() function. But I suspect you want "type" to be 1,
which means that payments are at the beginning of each period.

Of course, the formula can be modified in many ways in order to make
it more general. For example, instead of -{3;...}, you might use -
A1:A5, a range that contains the payment amounts. And instead of
12*(row(...)-1), you might use (B1:B5-1), a range that contains the
payment number each time the repeated amount changes (i.e. corresponds
to A1:A5). Lastly, instead of 6%/12, you might use C1, a cell that
contains the monthly rate.

Finally, when you wrote "a 6% discount rate", I assumed that is the
nominal annual rate; ergo, 6%/12 is the monthly rate. If 6% is the
monthly rate, obviously you would use that instead of 6%/12 (in two
places). But if 6% is the APY (compounded rate), the monthly rate
would be RATE(12,0,-1,1+6%) or (1+6%)^(1/12)-1. Thus, (1+m)^12-1 is
6%, where "m" is the monthly rate. This comment applies to the use of
NPV as well.

HTH.

Thanks Joe! Sorry for the delayed response but I had gone away for
vacation. I have one other question. Suppose instead of having
the monthly amount I was given the sum of the monthly payments. For
example, in year 1 the monthly payment is $3.00, but the sum of the
monthly payments in year 1 would be $36. How could I modify your
formula to deal with this? Would I divide the range by 12?

Thanks again for a great formula!

AJ