How to Get Future Value of Annuity Formula in Excel

This guide will discuss how to get the future value of the annuity formula in Excel using the FV function.

The rules for using the FV function are as follows:

  • The units used to describe the rate and nper must be consistent. 
  • Cash spent or paid out must be a negative value. 
  • Cash received or earned must be a positive value.

Excel is a handy tool that allows us to perform various functions and operations on our data sets. Furthermore, Excel has numerous default functions that make the process and calculation easier. 

An annuity is defined as the series of consecutive equal payments a person pays or receives over some time at a specific frequency. Additionally, a future value of an annuity refers to the specific value of these payments at a future date. 

So there are two types of the future value of an annuity. Firstly, the future value of the ordinary annuity, which refers to repeated payments done at the end of each period. The second type is the future value of an annuity due, which means the payment needed at the beginning of each period.

Essentially, theFV function in Excel is used to calculate the future value of an investment based on the given interest rate. Moreover, the payments can be done periodically, constantly, or through a single lump sum. 

In Excel, we can utilize the FV function to calculate the future value of an annuity easily.  But, we can also manually create a formula to get the future value of an annuity. 

Let’s take a sample scenario.

Suppose you are working in the finance department of your company. So a $10,000 payment is made each year for 20 years. Furthermore, it has a 5% interest rate. Because you want to calculate this faster, you use the FV function in Excel.

After we input the values in Excel, it returned a FV function of $330,659.54. So this is your future value of the ordinary annuity. 

Before we start learning more about how to get the future value of an annuity formula in Excel, let’s first tackle how to write the FV function.

 

 

The Anatomy of the FV Function

The syntax or the way we write the FV function is as follows:

=FV(rate;nper;pmt;[pv];[type])

Let’s dissect this formula and understand what each term means:

  • = this is how we always start any function in Excel.
  • FV() is our FV function. This function returns the future value of an investment depending on whether the payments were made periodically, constantly, or through a single lump sum. 
  • rate refers to the interest rate of each period. 
  • nper refers to the total payment periods made or present in the investment. 
  • pmt is the payment done or given each period. Moreover, this value will never change in the course of the annuity. 
  • pv is an optional argument. It refers to the value or amount that has been made in the current period. Basically, this is the present or current amount of payments made. 
  • type can be 0 or 1, depending on when the payment is due. The number 0 is assumed as payment due at the end of the period. So that makes 1 as payment due at the beginning of the period. Also, this is an optional argument. If this is omitted, the FV function automatically assumes 0.

Great! Now let’s check a real example of how to get the future value of an annuity formula in Excel.

 

 

A Real Example of Getting Future Value of Annuity Formula in Excel

Let’s take a sample data set where we need to calculate the future value of an annuity formula in Excel.

For example, company A makes a payment of $15,000 each year for 10 years. And this has an interest rate of 4%. Then, what is the future value of annuity given these series of payments?

Additionally, these payments are made at the end of the year. So it is a future value of the ordinary annuity with a formula of =P*[(1+i)n-1]/i. Whereas P refers to the payment amount, n refers to the payment period, and i is the interest rate.Preparing the table of values

 

Essentially, we can calculate the future value of an annuity in Excel in two ways. Firstly, we can use the FV function in Excel. Secondly, we can manually calculate it using the formula. Either way, performing the two methods in Excel is easy.

With the given information from the example, the future value of the annuity is $180,091.61 or rounded off to $180,092. Whether we use the FV function or do it manually with the formula, it will return the same answer.Final output after getting the future value of annuity

 

If the payment was made at the beginning of each year, it will become a future value of an annuity due, which has a formula of =P*[(1+i)n-1]*(1+i)/i

You can make your own copy of the spreadsheet above using the link attached below. 

Awesome! Now let’s learn the process of getting the future value of an annuity formula in Excel.

 

 

How to Get Future Value of Annuity Formula in Excel

In this section, we will learn the step-by-step process of how to get the value of an annuity formula in Excel using the FV function. Additionally, let’s try to obtain it manually using a formula. 

1. Firstly, create a table to input all the values needed to solve for the future value of an annuity. So these are the payment amount, the interest rate, and the payment period.Table of values

2. Secondly, we need to start the FV function in any blank cell. In this case, we will input the FV function in B8. So start the function with an equal sign and type “=FV(B6;B5;-B4;0;0)“. Then, press the Enter key to return the result.Future Value of Annuity Formula in Excel

3. And that’s it! We have returned the future value of an annuity in Excel.Future Value of Annuity Formula in Excel

4. Additionally, let’s try the formula manually. We assume the payment is made at the end of the year. So we will use the future value of an ordinary annuity formula which is =P*[(1+i)n-1]/i.

Simply input the appropriate values or cell reference in the formula. In this case, we will type in “=B4*((1+B6)^B5-1)/B6”. Lastly, press the Enter key to return the result.Future Value of Annuity Formula in Excel

5. And tada! We have done the same process manually.Future Value of Annuity Formula in Excel

That’s pretty much it! Now you have two convenient and easy methods to get the future value of an annuity formula in Excel whenever you need to. 

Are you interested in learning more about what Excel can do? You can now use the FV function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like