How to Calculate Annuity in Excel (5 Practical Examples)

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: