PV of Annuities using Excel

This is a guest post from Joe Helstrom, CPA and is an excerpt from his Ebook Excel Time Values of Money Function for the CPA

Present Value of Annuities


An annuity is a periodic payment of a fixed amount. The most prevalent examples are car loans and mortgages. They add the payment (Pmt) variable. The present value of an annuity deals with the value today of a future stream of fixed payments at a specific earnings rate.

Example.
Your client has just won the lottery. He must choose between $1 million paid to him immediately or $150,000 paid to him at the end of the next 10 years. He can earn 5% annually on his investments. Which option should he choose?

We have two scenarios to evaluate. The first involves $1 million today (the PV). The $1 million involves no computation. It’s already a present value. The second involves a series of periodic payments of $150,000 (Pmt) over the next 10 years (Nper) to be evaluated at an annual rate of 5%. We want to know the present value (Pv) of the annuity in the second scenario to compare to the first scenario. First, we prepare our input spreadsheet.

Click on the Fx button on the left side of the Formulas tab.  It will ask you to select a category.  Select “Financial”.  On the bottom, scroll down and choose PV.  Click OK.  The function arguments wizard will appear.  Complete the function arguments wizard as shown below.

Click OK. 

Once again, ignoring the sign convention, the present value of $150,000 per year over the next 10 years at a rate of 5% is $1,158,260.  Which is better – $1 million today or $1,158,260?  In this case, the better deal is to take the $150,000 per year periodic payments.

If you want to make the answer a positive number, place a minus sign in front of the payment (Pmt) input.  The answer is negative as Excel assumes that you would need to pay (cash outflow) this amount to obtain (cash inflow) $150,000 per year payments at a 5% rate.