How to Calculate Annuity Factor in Excel (2 Ways)

Annuity Factor: Overview

The amount that will be paid out under an annuity arrangement at various points in time is calculated using an annuity factor. The annuity factor, for instance, will show you how much money you would receive each year if you decide to invest in an annuity. You can use this information to decide if an annuity is an appropriate choice for you. The following image represents the general Annuity PV Factor formula.

We have a sample data set where we’ll calculate annuity based on various conditions.

Method 1 – Using the PV Function to Calculate the Present Value Annuity Factor in Excel

The term “present value of annuity” describes the current worth of anticipated future annuity payments. The lower the value of an annuity, the higher the rate. If you want to know if receiving periodic payments over a number of years or a lump sum payment now will net you more money, you can calculate the present value annuity factor. Here’s the overview of the PV function for calculating the present value annuity factor.

Steps:

Select cell C5.
Insert the following formula.

=PV(2%,$B5,-1)

2 percent represents the interest rate (r). $B5 shows the years (n). The negative value of 1 conveys the payment factor.

Press Enter and cell C5 will show the value of the annuity factor.

Use the Fill Handle tool and drag it down from cell C5 to C15.

Repeat the procedure for columns D and E, replacing the first argument to 4 and 6, respectively.

We will provide an example of the present value annuity for 4% rates and a 10-year time period.
Multiply the present value annuity factor with the payment using the following formula:

=C19*D13

C19 and D13 represent the payment and the annuity factor.

Here are the results of the present value annuity.

Method 2 – Applying the FV Function to Calculate the Future Value Annuity Factor

The value of a number of recurrent payments made at a specific future date at a specific rate of return or discount rate is referred to as the future value of an annuity. The value of the future annuity increases with the rate to determine how valuable a series of payments will be at some point in the future. The FV function used to determine the future value annuity factor is depicted in the following diagram.

Steps:

Insert the following formula in cell C5.

=FV(2%,$B5,-1)

2% represents the interest rate (r). $B5 shows the years (n). The negative value of 1 conveys the payment factor.

Once you press Enter, the cell C5 will display the specified annuity factor value.

Drag the AutoFill Handle tool from cell C5 to C15.

Repeat the process for columns D and E, replacing the value of the first argument as necessary.

We’ll provide an example of a present value annuity for a time period of 10 years and a rate of 4 percent.
Multiply the payment by the present value annuity factor using the following formula.

=C19*D13

C19 and D13 represent the payment and the annuity factor.