ExcelBanter - How to calculate PV of payments where pmts. change every 5 yrs.

GlennB wrote:
Need to calculate PV for payment stream covering 60 years, but the payment
amount changes every 5 years.

Use NPV(). You can specify the 60 cash flows in two ways: (1) put
them all into a column and specify that range in the NPV() function,
e.g. NPV(5%, A1:A60); or (2) specify constant arrays in the NPV()
function, e.g.
NPV(5%,{1000,1000,1000,1000,1000},{2000,2000,2000, 2000,2000},...).
With only 12 such arrays, that would fit within the limitation of 29
"values".

Caveat: Excel's NPV() assumes that the first value should be
discounted. So typically, you would add CF0 (the initial investment, a
negative number) outside the NPV() function.

Alternatively, you can sum the PV of the individual sets of similar
cash flows. For example:

=CF0 + pv(5%, 5, -1000, 0) + pv(5%, 5, 0, -pv(5%, 5, -2000, 0)) +
pv(5%, 10, 0, -pv(5%, 5, -3000, 0)) + ....

Note: For both the NPV() and PV() functions, choose the signs of
payments and principal properly.