Most of us, when computing NPV, will list out the periods and the respective cash flows.
Even though Excel contains a lot of nifty functions that can make the calculation in a flash, personally, I would stick to the above method for 2 reasons.
- Most of the time, the Cash Flows of each period is not fixed and may be a composite of a few components, and
- It is easier to illustrate to bosses and other people how the NPV is derived when they can see individual periods.
Still, it is good to know how to use the other functions. Just this week, I was presented with this question:
“What would the fixed annuity be if I changed my investment horizon from 15 to 30 years?”
Instead of listing 15 and 30 columns and using “Goal Seek” to find the difference in annuity (by sticking to the NPV formula), I took a quick refresher of the PMT formula from the website below.
PMT is the formula that derives the annuity amount given the rate of return, time horizon and present value. Give it a try!