How to Apply Future Value of an Annuity Formula in Excel

Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Again, we can also create simple formulas using desired cell values. From time to time, we need to determine many useful values in an excel worksheet. That’s why formulas are essential. This article will show you 2 easy methods to apply the Future Value of an Annuity Formula in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


Introduction to Annuity

An Annuity can be termed as a financial product that refers to a series of successive equal payments. This amount is either received by you or paid by you. It goes on for a specific number of periods and is spaced equally in time. Accordingly, the Future Value of an Annuity means the value of these series of payments at some date in the future. Again, the future value of annuity are of two types:

  • Future Value of Ordinary Annuity
  • Future Value of Annuity Due

The Future Value of Ordinary Annuity is a repeating payment made at the end of each period. While the Annuity Due requires the payment at the beginning of each period.


2 Easy Ways to Apply Future Value of an Annuity Formula in Excel

In this article, we’ll show you all the 2 ways to get the future value of an Annuity Formula in Excel. One way is to use an excel built-in function. And in another method, we’ll create a formula manually. To illustrate, we’ll use a sample dataset as an example. For instance, the below dataset represents the fixed Payment amount ($5,500), Interest Rate (7.3%), and the number of Periods (24). So, learn both methods carefully to apply them whenever you need to calculate annuity.


1. Insert Excel FV Function to Get Future Value of an Annuity

In our first method, we’ll insert the FV function to get the Future Value of an Annuity in excel. The FV function gives out the future value of an investment. To get the value, we need to input the interest rate, the number of periods to pay the installments, and the fixed payment amount. Therefore, follow the steps below to perform the task.


1.1 Regular Annuity

STEPS:

  • First, we’ll calculate the future value of the Regular Annuity.
  • For this purpose, select cell C9.
  • Then, type the formula:
=FV(C6,C7,-C5,0,0)
  • Press Enter afterward.
  • Thus, you’ll get the precise output.

Insert Excel FV Function to Get Future Value of an Annuity


1.2 Annuity Due

STEPS:

  • Now, we’ll find out the future value of the Annuity Due.
  • In this regard, choose cell C9.
  • Subsequently, insert the formula:
=FV(C6,C7,-C5,0,1)
  • Return the result by pressing Enter.
  • Hence, you’ll see the accurate annuity due.

Insert Excel FV Function to Get Future Value of an Annuity


2. Find Future Value of an Annuity Manually with Simple Formula in Excel

However, we can also create a simple formula manually by following the annuity equation. So, learn the following process to carry out the operation.


2.1 Ordinary Annuity

Firstly, we’ll use the equation,

Ordinary Annuity = P * [(1 + i)n – 1] / i

Here,

P = Payment

i = Rate of Interest

n = Periods

Hence, go through the steps.

STEPS:

  • In the beginning, click cell C9.
  • Next, input the formula:
=C5*((1+C6)^(C7)-1)/C6
  • After that, press Enter.
  • In this way, it’ll return the future value of the ordinary annuity.

Find Future Value of an Annuity Manually with Simple Formula in Excel


2.2 Annuity Due

Similarly, we’ll execute the formula:

Annuity Due = P * [(1 + i)n – 1] * (1 + i) / i

Therefore, learn the steps below.

STEPS:

  • First, click cell C9 at first.
  • Then insert the formula:
=C5*((1+C6)^(C7)-1)*(1+C6)/C6
  • Next, press Enter.
  • Thus, it’ll return the annuity due which you’ll receive or need to pay at the beginning of the period.


Conclusion

Henceforth, you will be able to get the Future Value of an Annuity Formula in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo