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.

**Table of Contents**hide

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

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

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

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

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

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