How to Apply Present Value of Annuity Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Workbook

Get the sample file and practice by yourself.


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. Those 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

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 annuity type.

2 Simple Applications of Present Value Annuity Formula in Excel

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 and period of payment and time.

Present Value of Annuity Formula Excel


Now we will get it using 2 simple methods.

1. Get Present Value of Annuity with Mathematical Formula

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

Calculate Present Value of Annuity with Mathematical Formula

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

Read More: How to Find Interest Rate in Future Value Annuity (2 Examples)


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

Calculate Present Value of Annuity with Mathematical Formula

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

Read More: How to Calculate Annuity Payments in Excel (4 Suitable Examples)


2. Use 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 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)

Use PV Function to Calculate Present Value of Annuity in Excel

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

Read More: How to Calculate Annuity in Excel (5 Practical Examples)


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

Use PV Function to Calculate Present Value of Annuity in Excel

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

Read More: How to Calculate Annuity Factor in Excel (2 Ways)


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.

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. Let us know your suggestions in the comment box. Follow ExcelDemy for more excel blogs like this.


Related Articles

Sanjida Mehrun Guria

Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo