How to Calculate Present Value of Annuity in Excel

Updated April 19, 2025

Imagine you’re offered a choice: receive $1,000 every year for the next decade or take a lump sum today. Which is worth more? You can answer this using the present value of an annuity, a calculation that compares future cash flows to their current worth in today’s dollars. In this article, you’ll learn how to calculate the present value of both ordinary and growing annuities using Excel.

What is the present value of an annuity?

The present value of an annuity shows what a series of future payments is worth right now. It’s based on the concept that money today is worth more than money tomorrow due to its potential to earn interest.

You might use the present value calculation in scenarios like evaluating investments, comparing loan options, or assessing retirement plans with regular payments. For instance, if you’re promised $5,000 annually for 5 years, calculating the present value helps you understand what this stream of payments would be worth if converted into a single lump-sum amount today. The calculation considers a discount rate, which could represent interest rates or inflation, to reflect the current worth accurately.

Types of annuities

There are two common types of annuities you’ll encounter when performing present value calculations:

Ordinary annuity: Pays out at the end of each period. An example is receiving an annual pension check each December 31st.
Growing annuity: Payments increase each period, typically to keep pace with inflation or other growth factors. A common example is a salary that increases by 3% every year.

Clearly distinguishing these annuities ensures you choose the correct formula and set up your Excel sheet accurately.

How to calculate the present value of an ordinary annuity in Excel?

To find the present value of an ordinary annuity, you’ll need three inputs:

Payment amount: The amount received each period (e.g., $1,000 per year).
Discount rate: The rate used to discount future payments to today’s value (e.g., 5% or 0.05).
Number of periods: The total number of payments (e.g., 5 years).

As a quick reminder, an ordinary annuity means payments occur at the end of each period. Before using Excel, let’s briefly look at the formula behind the calculation:

PV = P \times \frac{1 - (1 + r)^{-n}}{r}

Where:

P is the payment per period (e.g., $1,000 each year).
r is the discount rate per period (e.g., 5% or 0.05).
n is the number of periods (e.g., 5 years).

Using our example of $1,000 annual payments for 5 years with a 5% discount rate, the formula looks like this:

PV = 1000 \times \frac{1 - (1 + 0.05)^{-5}}{0.05}

After calculation, the present value is approximately $4,329.48. Excel simplifies this calculation with the built-in PV function, eliminating the need for manual computation.

Calculating it in Excel

Enter your data:
1. In cell A1, type Payment; in A2, enter 1000.
2. In cell B1, type Discount Rate; in B2, enter 0.05.
3. In cell C1, type Periods; in C2, enter 5.
Calculate the present value:
1. In cell D1, type Present Value; in D2, type =PV(B2, C2, -A2).
2. Press Enter, and you’ll get the present value of the ordinary annuity, which is approximately $4,329.48.

Example for calculating present value of ordinary annuity in Excel

Why the negative payment?

Excel’s PV function treats payments you receive as negative values (cash inflows). Entering the payment as negative (-A2) ensures the final present value is positive, clearly reflecting money coming to you.

How to calculate the present value of a growing annuity in Excel?

Calculating the present value of a growing annuity is slightly more complex since payments increase each period—for example, rent payments that rise 3% each year. You’ll need the following information:

First payment: The initial payment you receive (e.g., $1,000).
Discount rate: The rate used to discount future payments to today’s dollars (e.g., 5% or 0.05).
Growth rate: The rate at which payments grow each period (e.g., 3% or 0.03).
Number of periods: Total number of payments (e.g., 5 years).

Excel doesn’t have a built-in function specifically for growing annuities, so you’ll input the formula directly into a cell.

The formula for the present value of a growing annuity is:

PV = P \times \frac{1 - \left(\frac{1 + g}{1 + r}\right)^n}{r - g}

Where:

P is the first payment (e.g., $1,000).
g is the growth rate per period (e.g., 3% or 0.03).
r is the discount rate per period (e.g., 5% or 0.05).
n is the number of periods (e.g., 5 years).

If your initial payment is $1,000, growing by 3% each year for 5 years, with a discount rate of 5%, the formula becomes:

PV = 1000 \times \frac{1 - \left(\frac{1 + 0.03}{1 + 0.05}\right)^5}{0.05 - 0.03}

This calculates to approximately $4583.92. Notice that because payments grow each year, this value is higher than an ordinary annuity with the same initial payment.

Calculating it in Excel

Enter your data:
1. In cell A1, type First Payment; in A2, enter 1000.
2. In cell B1, type Discount Rate; in B2, enter 0.05.
3. In cell C1, type Growth Rate; in C2, enter 0.03.
4. In cell D1, type Periods; in D2, enter 5.
Calculate the present value:
1. In cell E1, type Present Value; in E2, type =A2*((1-((1+C2)/(1+B2))^D2)/(B2-C2)).
2. Press Enter, and you’ll get the present value of the growing annuity, which is approximately $4,583.92.

Example for calculating present value of growing annuity in Excel

Important note:

If your discount rate equals the growth rate (r = g), the standard growing annuity formula won’t work due to division by zero. In this rare case, calculate the present value simply by multiplying the first payment by the number of periods:

PV = P \times n