Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate PV of payments where pmts. change every 5 yrs.
Need to calculate PV for payment stream covering 60 years, but the payment
amount changes every 5 years. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XCEL 2 calculate week or bi-weekly accel. mortgage payment | Excel Discussion (Misc queries) | |||
How do I calculate loan balances when payments are missed? | Excel Worksheet Functions | |||
looking for real estate open house fliers that calculate payments | Excel Discussion (Misc queries) | |||
Change value in cell | Excel Discussion (Misc queries) | |||
Change over time fornula | Excel Worksheet Functions |