Essentially, this tutorial gives answers to three “what” questions. What is present value of annuity? What is the present value formula? What discount or interest rate to use for present value calculation?
If offered a choice to receive a certain sum of money right now or defer the payment into the future, which would you choose? For most people, taking money now is a natural instinct. In the financial world, this is explained by the time value of money concept.
There are two main terms that measure how much the value of money changes over time: future value (FV) and present value (PV). If you are curious to know the worth of your investment after a certain period, calculate its future value as explained in the FV function tutorial. If you wish to find the current worth of money, then you need to calculate present value, and this tutorial shows how to quickly do this in Excel.
Annuities can seem complicated at first, but learning how to calculate their present value in Excel is easier than you think! In this comprehensive guide, I’ll walk you through everything you need to know to find the present value of any annuity using Excel’s builtin financial functions
What is An Annuity?
First things first – what exactly is an annuity?
An annuity is simply a series of fixed payments made at set intervals over a defined period of time. Some common examples include:
 Pension payments
 Mortgage payments
 Lease payments
 Insurance premiums
Annuities provide a way to spread out a lump sum amount into smaller, regular installments. The party making the payments is known as the annuitant. The interval at which payments are made can be annual, semiannual, quarterly, monthly, etc.
Now, on to the tricky part – determining an annuity’s present value.
Why Calculate an Annuity’s Present Value?
The present value of an annuity represents its worth in today’s dollars. Knowing this allows you to analyze annuities and make informed financial decisions.
Here are some examples of when you may need to calculate an annuity’s present value:
 Deciding whether to accept a settlement offer with annuity payouts
 Evaluating a new insurance policy or pension plan
 Determining how much to save to reach a certain annuity value
 Comparing different annuity offers to choose the best one
The present value reflects the time value of money – the concept that money available now is worth more than the same amount in the future. $100 today is worth more than $100 a year from now, since that $100 could be invested and earn interest over the next year.
By discounting an annuity’s future payments back to their value today, you can analyze them on an applestoapples basis.
How to Calculate Present Value of An Annuity in Excel
Calculating an annuity’s present value in Excel is simple thanks to the builtin PV
function.
The PV
function uses this formula:
PV = [Payment Amount] x [1  (1 + Interest Rate)^Number of Periods] / Interest Rate
Where:
 Payment Amount = The fixed periodic payment
 Interest Rate = The interest rate per period
 Number of Periods = The total number of payment periods
It may look complex, but Excel does all the work for you. Let’s walk through an example:

Enter the annuity details – the annual payment amount, number of years, and annual interest rate.

Click on an empty cell and type
=PV(
to start the function. 
Select the annual interest rate cell, type a comma.

Select the number of years cell, type a comma.

Select the payment amount cell, type
)
to close the function.
And that’s it! Excel automatically calculates and displays the present value.
Let’s see it in action. Say you’re offered an annuity that pays $5,000 annually for 18 years at a 4% interest rate. Here are the steps to calculate its present value in Excel:
![Excel annuity example][]
The present value is $60,036.65 – that’s what this annuity stream is worth in today’s dollars.
Easy stuff! You can use this same process to calculate the present value of any annuity in Excel.
Present Value of an Annuity Due
A subtle variation on the normal annuity is an “annuity due”, where payments are made at the beginning of each period rather than the end.
To calculate the present value of an annuity due in Excel, you once again use the PV
function. The only difference is you must specify 1
for the optional Type argument:=PV(Interest Rate, Number of Years, Payment, 0, 1)
Specifying a Type of 1 tells Excel you want to compute the present value for an annuity due rather than a regular annuity.
Let’s revisit the previous example, but now structure it as an annuity due with the $5,000 annual payments being made at the beginning of each year.
Here is the adjusted PV
function: =PV(B3,B2,B1,0,1)
And the present value increases to $63,778.95 when the payments are made at the start of the year.
Limitations of Excel’s PV Function
Excel’s PV
function works great for basic annuity calculations, but does have some limitations:
 It can only handle annuities with constant, evenlyspaced payments. For variable or irregular payments, you need to calculate each PV individually.
 It assumes fixed interest rates over the annuity term. For annuities with varying rates, you again need to break it down into segments.
 It can’t directly handle annuities with a principal payment at the end. To model this, you need to calculate it as the present value of two separate annuities – one for the periodic payments, one for the balloon payment.
So in more advanced cases, you may need to build up a more customized present value calculation using basic time value of money formulas. But for standard fixed annuities, the PV
function is quick and convenient.
Present Value Annuity Formula Examples
Let’s walk through a few more examples of using Excel’s PV
function to calculate an annuity’s present value in different scenarios:
Example 1 – Lump Sum vs Monthly Annuity
You win the lottery and are presented two payout options:

A lump sum payment of $500,000 today

Monthly annuity payments of $5,000 for the next 10 years (120 payments) at a 4% annual effective interest rate
Which option should you take? Calculating the present value of the annuity will tell us.![Annuity example 1][]
The present value of the monthly annuity is $568,623 – greater than the lump sum amount! So the annuity is the better financial choice (assuming the payments are secure).
Example 2 – Retirement Annuity Choices
You’re comparing two retirement annuity options:

$750 paid monthly for 30 years at an 8% annual interest rate

$1,000 paid monthly for 20 years at a 10% annual interest rate
Which provides greater value today?![Annuity example 2][]
Despite the larger monthly payment, the shorter 20 year term means Option 2 has the lower present value. So Option 1 is better from a financial perspective.
Example 3 – Term Annuity
Your wealthy uncle offers you a 25 year annuity paying $10,000 annually at a 5% interest rate, but only if you pay him an initial lump sum upfront. How much could you afford to pay today to take him up on the offer?
We need to calculate the present value to figure out the maximum upfront payment.![Annuity example 3][]
You could pay up to $138,257 today and still come out ahead in the long run from this annuity.
As you can see, the PV
function is incredibly versatile for evaluating annuities in many different scenarios.
Next Steps
Congratulations – you’re now an annuity present value expert!
To cement your skills, try building a spreadsheet to analyze different annuity options and their present values. Play around with the inputs and see how they impact the PV.
You may also want to learn about the related Excel functions FV
and PMT
which calculate an annuity’s future value and periodic payment amount, respectively.
Mastering these basic time value of money functions will give you tremendous financial modeling capabilities in Excel. The world of annuities is now open to you!
How to calculate present value in Excel – formula examples
The previous section shows how to calculate the present value of annuity manually. The good news is that Microsoft Excel has a special PV function that does all calculations in the background and outputs the final result in a cell. PV(rate, nper, pmt, [fv], [type])
If you are not familiar with this function, its a good idea to begin with the above linked tutorial that explains the syntax.
Present value formula for annuity
When calculating the present value of annuity, i.e. a series of even cash flows, the key point is to be consistent with rate and nper supplied to a PV formula.
To get a correct periodic interest rate (rate), divide an annual interest rate by the number of compounding periods per year:
 Monthly: rate = annual interest rate / 12
 Quarterly: rate = annual interest rate / 4
 Semiannual: rate = annual interest rate / 2
To get the total number of periods (nper), multiply the length of an annuity in years by the number of periods per year:
 Monthly: nper = no. of years * 12
 Quarterly: nper = no. of years * 4
 Semiannual: nper = no. of years * 2
For this example, we will be using a series of $100 payments that we calculated manually in the first part of this tutorial and input the following data in a worksheet:
 Annual interest rate (B2): 10%
 No. of years (B3): 3
 Annual payment (B4): 100
Please notice that the payment is expressed by a negative number because its an outflow.
Assuming the payments are made at the end of each year, you can calculate the present value with this formula:
As shown in the below, the PV formula returns the same result as the manual calculation – $248.69.
If the payments are made monthly, then add one more input cell (B5) for the number of periods per year (12 on our case). To get the correct present value, convert an annual interest rate to a monthly rate (B2/B5) and provide the total number of periods for annuity (B3*B5):
=PV(B2/B5, B3*B5, B4)
How to Calculate Annuities Using Excel  Present Value of Annuity for Asset Valuation
How to find present value of annuity in Excel?
Using the PV Function to Find Present Value of Annuity in Excel In this second method, we will use the PV function to calculate the present value of the annuity. This function represents the present value of an annuity, loan or investment based on a constant interest rate.
How to calculate annuity in Excel?
Excel can perform complex calculations and has several formulas for just about any role within finance and banking, including unique annuity calculations that use present and future value of annuity formulas. The basic annuity formula in Excel for present value is =PV (RATE,NPER,PMT). • PMT is the amount of each payment.
How to calculate present value for annuity due?
With an annuity due, payments are made at the beginning of the period, instead of the end. To calculate present value for an annuity due, use 1 for the type argument. In the example shown, the formula in F9 is: Note the inputs (which come from column F) are the same as the original formula. The only difference is type = 1.
What is an example of a present value of annuity?
For example, if the payments for the following insurance agreement are made at yearend (ordinary annuity), the present value of the annuity would be. Whereas, if the payments for the following insurance agreement are made at the beginning of the period (annuity due), the present value of the annuity would be