Microsoft Excel Time Value Function Tutorial – Annuities | TVMCalcs.com

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.

Microsoft Excel Time Value Function Tutorial – Annuities | TVMCalcs.com.

PMT is the formula that derives the annuity amount given the rate of return, time horizon and present value.  Give it a try!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: