Annuity Table

What Is an Annuity Table?

An annuity table is a chart containing time-based factors and discount rates for structured series of payments (or annuities). This tool facilitates calculating the present value of these annuities.

These tables are used by accountants, actuaries, and those in the insurance fields to take into account how much money the annuity started with and how long that money has been there. These values are then used to determine how much money would be due to the annuitant.

The annuities in these tables are usually from receiving lump sums from insurance claims and lottery winnings, among others. The goal is to determine their present value from receiving these amounts in annuity form instead of one lump sum.

Thus, the annuitant can decide whether receiving the money as annuity payments is better than one lump sum. This is due to the time value of money.

For example, $20,000 received today is worth more than $2,000 per year for 10 years. Inflation alone makes this statement true. However, even ignoring inflation, those $20,000 could be invested today and then be worth more money after 10 years because of interest rates.

Investing those $20,000 at a competitive interest rate gives a higher present value than receiving $2,000 per year for 10 years. Thus, these tables can be used to determine present values for those $20,000 depending on interest rates and the duration of the annuity.

Calculating the Present Value of an Annuity

This calculation can be done either in Excel or a financial calculator. However, the calculation depends on the type of annuity. The types of annuities are ordinary annuities and annuities due.

An ordinary annuity has its payments made at the end of the period, whereas an annuity due has its payments made at the beginning of the period. For example, rent is an annuity due because you are paying for the month before you have made use of the space for that month.

The formula for calculating the present value of an ordinary annuity is as follows:

Where

• PV = present value of the annuity stream
• PMT = annuity payment
• r = interest rate (or discount rate)
• n = number of periods remaining in the annuity

For example, let’s assume someone wants to determine whether it’s better to receive a lump sum of $50,000 or an annuity that pays $10,000 for the next 6 years at a discount rate of 5%. The present value of the annuity is $50,757, which is greater than the lump sum of $50,000.

Therefore, the person might opt to choose the annuity instead since the present value is greater. This assumes all other factors, such as time and interest rates, remain the same. Otherwise, other decision-making situations may arise.

For example, that person might want to compare the present value of that annuity to investing the lump sum in an account with a 1% rate of return for the same time (6 years). The present value of that investment is $53,076, which is greater than the offered annuity.

PV = $50,000 (1 + 0.01)⁶ ≈ $53,076

In this case, the person might opt to choose the lump sum since they can invest it in an account that will return a higher amount than the annuity. Again, this is assuming all other factors remain the same.

For example, if the person doesn’t need any money for the foreseeable future, then investing that $50,000 for 6 years might be the best choice. However, if they’d rather receive $10,000 yearly instead of waiting for the entire 6 years, they might choose that.

Therefore, while the decision is not clear-cut, the process still aids in decision-making since calculating the present value of these annuities takes the time value of money into account. This gives a baseline and provides clear, money-based outcomes depending on the choices.

As for calculating the present value of an annuity due, it can be found as follows:

Where

• PV = present value of the annuity stream
• PMT = annuity payment
• r = interest rate (or discount rate)
• n = number of periods remaining in the annuity

Notice that the only difference between these formulas is that the latter is the former by a factor of 1+r. This is to mathematically account for receiving the payment at the beginning of the period.

Let’s see an example. Suppose you are to receive $10,000 at the beginning of each year for 8 years at a discount rate of 4%. Its present value is $70,021.

PV = [($10,000 * (1 + 0.04))/ 0.04] * [1 - 1/ (1 + 0.04)8] ≈ $70,021 

Annuity Table and the Present Value of an Annuity

Before calculating the present value using an annuity table, the table has to be constructed. This is done using the pertinent formula per cell (whether it is an ordinary annuity or annuity due). Excel is the ideal instrument for constructing these tables.

Here is an example of an ordinary annuity table per year for the next 10 years.

This table can be generated in Excel by starting it in cell A1, filling each cell from B2 to J2 with the percentage, and filling each cell from A3 to A12 with the years shown above. Doing so will help generate the table by putting the following code in cell B3:

=(1 - 1/((1 + B$2)^$A3))/ B$2

This code is the representation in Excel of the ordinary annuity formula.

After entering the code, take the cursor to the bottom-right corner of that cell (until it becomes a black plus sign) and drag it vertically to compute the first column automatically. Then, do this again but horizontally. Finally, do the same for these new cells until all columns are filled in.

These tables can be filled with any values. You could have an entire Excel sheet with percentages increasing by tenths or thousandths or even for differing period lengths. For example, you could have monthly payments, quarterly payments, etc.

In any case, now that we have constructed the annuity table, we can use it to calculate present values easily.

For example, if we wanted to determine the present value of receiving $2,000 per year for 7 years at an 8% discount rate, we simply multiply $2,000 by 5.2064, giving us approximately $10,413. (5.2064 was obtained from the cell in the Year 7 row and 8% column).

Here’s another example. A 10-year annuity paying $3,500 per year at a 5% discount rate gives a present value of approximately $27,026. This was calculated by finding the cell in the Year 10 row and 5% column (7.7217) and multiplying it by $3,500.

We can even calculate the first example with this annuity table. An annuity that pays $10,000 for the next 6 years at a discount rate of 5% was calculated to have a present value of $50,757. We can also obtain that value by multiplying $10,000 by 5.0757 (the cell in the Year 6 | 5% column).

To fill an annuity table with annuity due values, just perform the same process but use this code in cell B3. Then, expand the table to the ordinary annuity example.

=((1 - 1/((1 + B$2)^$A3))/ B$2) * (1+B$2)

It should look like this.

Calculating present values using this table is done in the same way as the previous ordinary annuity examples. The only difference is that the annuity due value is being used.

For example, if we wanted to determine the present value of an annuity due that pays $2,500 per year for 9 years at a discount rate of 4%, we simply multiply $2,500 by 7.737, giving us approximately $19,343. (7.737 was obtained from the cell in the Year 9 row | 4% column).

As seen from these examples, the benefit of these annuity tables is to quickly calculate the present value of annuities without using the formulas every time.

For example, an actuary might place a payment value in a cell somewhere in the Excel sheet and, in another cell, multiply the value in that cell by the value in the pertinent cell of the annuity table.

Researched and authored by Gilberto Morales | LinkedIn

Reviewed and Edited by Wissam El Maouch | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Accretion
• Active Return
• Bear
• Bull
• Downtrend