In this tutorial, we will present 5 practical examples of how to calculate annuity in Excel. Determining the various aspects of an annuity is a fairly straightforward task if the annuity’s interest rate, total amount, and duration period are known. However, calculating this value is only feasible when dealing with *fixed* annuities.

## What is an Annuity?

An annuity is a contract between two parties where one party invests an amount at the start, and in return will receive an annual payment from the other party for an agreed period of time. There are many variations of the formula to calculate an annuity. One basic type is as follows:

**P = C * [(1 – (1 + r)^-n) / r]**

Where,

**P = Present value of the annuity**

**C = Future cash flow stream**

**r = Interest rate**, and

**n = Number of periods (months or years)**.

## How to Calculate Annuity in Excel: 5 Practical Examples

In the following examples, we will be mainly working with **4** parameters to calculate annuity in Excel: periodic annuity, number of periods, interest rate, and present or future value of total money. The monetary values are inserted in dollars, and we’ve used months as time period units. For the interest rate, we have used the percentage format, located in the **Number** section of the **Home **tab.

### Method 1 – Using the PV Function to Calculate Annuity

**The PV function** is a financial function that calculates the present value of an investment. Let’s calculate the present amount that we need to deposit to receive a fixed annuity for the next **10 **months.

**Steps:**

- Enter the required data as in the image below.

- Double-click on cell
**C8**and enter the following formula:

`=PV(C7,C6,C5)`

- Press
**Enter**.

The amount of investment needed now to receive the required future annuity is returned.

### Method 2 – Using the PMT Function

**The PMT function** returns the periodic payment or annuity for a current loan. It requires a few parameters like time periods and interest rate to calculate the annuity.

**Steps:**

- Enter the
**Loan Amount**in cell**C5**. - Enter the
**time period**and the**interest rate**in cells**C6**and**C7**respectively.

- In cell
**C8**enter the following formula:

`=PMT(C7,C6,C5)`

- Press
**Enter**to calculate the required annuity in dollars.

### Method 3 – Using the FV Function

To calculate the future value of an investment, we can use **the FV function** in Excel. This function assumes constant annuity and constant interest rates.

**Steps:**

- Enter the
**monthly deposit**amount in dollars in cell**C5**. - Enter the
**time periods**and**interest rates**in cells**C6**and**C7**.

- Double-click on cell
**C8**and enter the following formula:

`=FV(C7,C6,C5)`

- Press
**Enter**or click on an empty cell.

The future amount that will accumulate due to the annuity is returned.

### Method 4 – Using the NPER Function to Find the Annuity Period

**The NPER function** can calculate the time period required to pay off a loan at a fixed annuity.

**Steps:**

- Insert suitable data in the relevant cells, as shown in the following image.

- Click on cell
**C8**and insert the following formula:

`=NPER(C7/12,C6,-C5)`

Note that we have a negative sign in the third parameter. This is to denote that we are lending the money to the other party. Also, we have divided the first parameter by **12** to convert the interest rate to monthly.

- Press
**Enter**to calculate the time periods required in months.

### Method 5 – Using the RATE Function

**The RATE function** is used to calculate the rate charged on a loan at a constant annuity in Excel. It can also determine the rate of return needed to cover a certain amount of an investment over a given period.

**Steps:**

- In cells
**C5**,**C6**, and**C7**enter the appropriate data as in the image below.

- In cell
**C8**enter the below formula:

`=-RATE(C7,-C6,C5)`

- Again, there is a negative sign before the second parameter of this formula because we are transferring money as a
**monthly deposit**.

- Press
**Enter**to calculate the required**interest rate**.

**Download Practice Workbook**

## Excel Annuity Formula: Knowledge Hub

- How to Calculate Deferred Annuity in Excel
- How to Calculate Annuity Payments in Excel
- How to Calculate Annuity Due in Excel
- How to Calculate Growing Annuity in Excel
- How to Calculate Annuity Factor in Excel

**<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel**