How to Calculate Growing Annuity in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

An annuity is a term that is mainly associated with retirement. By following some simple steps in Excel, one can easily calculate a growing annuity for one’s retirement plan. You will need some specific information regarding the methods and just find out the expected growing annuity in no time. In this article, we will show you how to calculate growing annuity in Excel.


Growing Annuity

Before beginning our procedure, we will try to understand what an annuity and a growing annuity are. In simple words, an annuity is a predetermined amount of money that you will start receiving either annually or monthly after a fixed amount of time. On the other hand, a growing annuity is a series of payments or revenues that rise consistently over a predetermined number of cycles, increasing in each period by a fixed percentage.


How to Calculate Growing Annuity in Excel: 2 Handy Ways

We will show you how to calculate both the present value of the growing annuity and the future value of the growing annuity. In this article, we will use two different methods to do so. In our first procedure, we will use the NPV function of Excel to calculate the present value of the growing annuity, and in our second method, we will apply the FV function to determine the future value of the growing annuity. For our working purposes, we will use the following data set.

Handy Ways to Calculate Growing Annuity in Excel

From the data set, you can see that to calculate the growing annuity, we have the initial investment, interest rate, growth rate, and the number of years. From this information, we will determine the growing annuity.

1. Using NPV Function to Calculate Present Value of Growing Annuity in Excel

In our first procedure, we will calculate the present value of a growing annuity. To do that, we will use the NPV function. See the below-given steps for a better understanding.

Step 1:

  • Firstly, we will determine the stream of payments for calculating the growing annuity.
  • As our initial investment is $8,000 we have to calculate the growing payment from the second year.
  • To do that, type the following formula into cell C7.
=C6*(1+$F$6)

Handy Ways to Calculate Growing Annuity in Excel

Step 2:

  • Secondly, press Enter and get the growing payment for the second year which is $8,440.
  • Then, use the AutoFill feature to drag the formula for the lower cells of that specific column.

Handy Ways to Calculate Growing Annuity in Excel

Step 3:

  • Thirdly, we will calculate the present value for the growing annuity by applying the following formula of the NPV function.
=NPV(F5,C6:C15)

Handy Ways to Calculate Growing Annuity in Excel

Step 4:

  • Finally, hit the Enter button to get the required growing annuity which is $63,648.30.

Handy Ways to Calculate Growing Annuity in Excel

Read More: How to Calculate Equivalent Annual Annuity in Excel


2. Applying FV Function to Determine Future Value of Growing Annuity

We will apply the FV function in our second procedure to calculate the future value of the growing annuity. Go through the following steps to do so.

Step 1:

  • Firstly, take the following data set for calculation.
  • Here, we have added an extra data input (Payment in cell G8) cell to calculate the growing annuity.

Sample dataset for calculating FV

Step 2:

  • Apply the following formula in the cell C7 to calculate the payment value for the second year.

Calculating Stream of Future Payment for the First Year

  • Use the Fill Handle tool to copy the formula in the remaining cells to calculate the future stream of payments.

Using Fill Handle tool to Calculate Stream of Future Payments for the Remaining Years

  • Like the previous method, calculate the present value of the growing annuity using the following formula in cell G10.
=NPV(G5,C6:C15)

Calculating Present Value of Growing Annuity for Comparison

Step 3:

  • Thirdly, apply the following formula in cell D6 to determine the future value of the first payment.
=FV($G$5,($G$7-B6),-C6)

Calculating Future Value of Each Payment

  • Then, drag down the Fill Handle icon to calculate future values for the remaining payments.

Using Fill Handle tool to calculate Future Value of Each Payment

  • Finally, write the following formula with the FV function in cell G11.
=SUM(D5:D15)

Summing All Future Payments

  • Press Enter and you will get the desired future value of the growing annuity.

Future Value of Growing Annuity

Read More: How to Calculate Deferred Annuity in Excel


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to calculate the growing annuity in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below.


Related Articles


<< Go Back to Excel Annuity Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

2 Comments
  1. The FV growing annuity formula is not correct. You didn’t include the 5.5% growth rate in your calculation. What you provided is the FV formula for a regular annuity, not a growing annuity.

    The PV equation is correct, though.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 12, 2024 at 11:59 AM

      Dear VICTORI,

      Thanks for your feedback. It seems that you have mistakenly assumed that we didn’t include the 5.50% growth rate in our calculations.

      The growth rate is used for calculating the stream of future payments. As we showed this calculation in method 1 (i.e. Using the NPV Function method), we didn’t show it again in method 2 and suggested users to see it from the previous method.

      However, as it has dodged your eyes, we have included the detailed calculation in method 2 as well. You also suggested that the FV formula we provided, is for a regular annuity, not a growing annuity. This is true, and we have updated our article according to your feedback. You can check the updated article and share your feedback with us.

      Thank you again for your valuable comment.

      Regards,
      Seemanto Saha
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo