How to Apply Present Value of Annuity Formula in Excel

In this article, we will discuss how to determine the Present Value of an Annuity in Excel, both by using a mathematical formula and by means of Excel’s PV function.

What Is the Present Value of an Annuity?

An annuity is a series of payments with an equal interval, for example installment repayments of a mortgage or loan. The interval of payments can be weekly, monthly, or even annually.

The Present Value of an Annuity is the value of the series of payments in the future with a specific rate of discount or rate of return, and obviously within a certain period of time. The Present Value is also called the Present Discounted Value as the relation between the Discount Rate and Present Value is proportional.

Ordinary Annuity vs Annuity Due in Excel

The timing of the payments can be of two types:

Ordinary Annuity: The cash flow is received at the end of the specified period. Use 0 for this annuity type.

Annuity Due: The cash flow is received at the beginning of the specified period. Use 1 for this annuity type.

To illustrate, consider the dataset below containing values for an Annual Interest Rate, Number of Years, Payment per Period, and Number of Periods in a Year.

We will calculate the Present Value of this Annuity using 2 simple methods.

Method 1 – Using a Mathematical Formula to Calculate the Present Value of an Annuity

The mathematical formula for ordinary annuity and Present Value of an Annuity in Excel is:

PVA Ordinary = P * (1 – (1 + r/n)^-t*n) / (r/n)

PVA Due = P * (1 – (1 + r/n)^-t*n) * ((1 + r/n) / (r/n))

Where,

PVA = Present Value of Annuity

P = Periodic Payment

r = Interest Rate

t = Number of Years

n = Frequency of Occurrence in a year

1.1 – Present Value of an Ordinary Annuity

To calculate the The Present Value of an Ordinary Annuity:

In cell C10, insert this formula:

=C7*(1-(1+(C5/C8))^(-C6*C8))/(C5/C8)

Press Enter.

The Present Value of an Ordinary Annuity is returned.

1.2 – Present Value of an Annuity Due

To caculate the Present Value of an Annuity Due:

In cell C10, insert this formula:

=C7*(1-(1+C5/C8)^-C6*C8)*((1+C5/C8)/(C5/C8))

Press Enter.

The output is as follows:

Method 2 – Using the PV Function to Find the Present Value of an Annuity

Alternatively, we can use the PV function to calculate the Present Value of an Annuity. This function returns the present value of an annuity, loan or investment based on a constant interest rate. Again, we will determine Present Value for both Ordinary Annuity and Annuity Due types.

2.1 – Present Value of an Ordinary Annuity

To calculate the Present Value of an Ordinary Annuity:

In cell C11, insert the formula below:

=PV(C5,C8,-C7,C9)

Press Enter.

The Present Value is returned.

2.2 – Present Value of an Annuity Due

To calculate the Present Value of an Annuity Due:

In cell C11, insert this formula:

=PV(C5,C8,-C7,C9)

Press Enter.

The Present Value is returned.

Things to Remember

Adjust the units you use for specifying the interest rate and period of payment as needed. The Present Value of an Annuity formulas above are based on annual calculation.
The payout amount will be negative (–) and the received amount will be positive (+) in the dataset.
The Periods in a Year value cannot be blank or 0, or the formulas will return a #DIV/0 error.

Download Practice Workbook

Present Value of Annuity Formula.xlsx