How to Apply Present Value of Annuity Formula in Excel

For a person working in the finance or banking sector, the calculation of present value is an important part. It is commonly used as the term time value of money. Microsoft Excel makes it simple. In this article, we will discuss how to apply the present value of the annuity formula in Excel.

What Is Present Value of Annuity?

First, let us know what the word annuity means. For example, you are putting some money as a deposit to pay for something that requires payment in several installments such as a mortgage, loan, etc. This interval of payments can be weekly, monthly, or even yearly. This series of payments with an equal interval is called an annuity.

Now comes the present value of the annuity. It is the value of those 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

Before getting to know the formula to calculate the present value of the annuity, the timing of the payment should be considered firsthand. It can be of two types:

Ordinary Annuity: It determines that the cash flow is received at the end of the mentioned period. Use 0 for annuity type.

Annuity Due: It determines that the cash flow is received at the beginning of that period. Use 1 for the annuity type.

Let us take an example to calculate the present value of the annuity formula in Excel. Here the dataset represents the values of annual interest rate, number of years, period of payment, and time.

Now we will get it using 2 simple methods.

1. Calculating Present Value of Annuity with Mathematical Formula in Excel

Before starting, here is the mathematical formula for ordinary annuity and annuity due for present value in Excel.

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))

Here,

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 Ordinary Annuity

Now, we will calculate the present value of the ordinary annuity.

First, select cell C10.
Then, insert this formula:

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

After that, press Enter.
Finally, you will get the present value of the ordinary annuity.

1.2 Present Value of Annuity Due

Now we will apply the present value of the annuity due formula. Let’s see how it works.

First, select cell C10.
Next, insert this formula:

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

Now, press Enter.
Finally, we have our final output.

2. 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. Here, we will find out both ordinary annuity and annuity due based on the present value.

2.1 Present Value of Ordinary Annuity

Let us see how to calculate the present value of the ordinary annuity.

In the beginning, select cell C11.
Here, insert the formula below:

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

After that, press Enter.
Finally, we have our present value of the ordinary annuity.

2.2 Present Value of Annuity Due

The calculation process of the present value of annuity due is described below:

First, select cell C11.
Now, insert this formula:

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

After this, press Enter.
That’s it, we have our final result.

Things to Remember

Be sure about the units you use for specifying the rate and period of payment because the present value of the annuity is 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, otherwise, it will show a #DIV/0 error.

Download Practice Workbook

Get the sample file and practice by yourself.

Present Value of Annuity Formula.xlsx

Conclusion

Therefore, I hope it was a helpful article for you on how to apply the present value of the annuity formula in excel with 2 simple methods.

<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!