What is an Annuity?
An annuity, often associated with retirement planning, represents a fixed stream of payments received either annually or monthly after a specified period. A growing annuity, on the other hand, involves payments that increase consistently over a predetermined number of cycles, with each period’s payment rising by a fixed percentage.
Dataset Overview
For our working purposes, we will use the following data set.
Method 1 – Using NPV Function to Calculate the Present Value of a Growing Annuity
- Determine the Growing Payment Stream:
- Given an initial investment of $8,000, calculate the growing payment for the second year using the formula:
=C6*(1+$F$6)
-
- Press Enter and get the growing payment for the second year which is $8,440.
- Use the AutoFill feature to drag the formula to the lower cells of the column.
- Calculate the Present Value (PV) of the Growing Annuity:
- Apply the NPV function in Excel to find the present value of the growing annuity. Enter the following formula:
=NPV(F5,C6:C15)
- Press Enter to get the required growing annuity which is $63,648.30.
Method 2 – Applying FV Function to Determine Future Value of Growing Annuity
Set Up Data for Calculation:
- Add an extra data input (Payment in cell G8) to calculate the growing annuity.
- Calculate the payment value for the second year using the formula in cell C7:
Payment = Initial Investment * (1 + Growth Rate)
- Use the Fill Handle tool to copy the formula to the remaining cells to calculate the future stream of payments.
- Calculate the Present Value (PV) of the Growing Annuity:
- Similar to Method 1, use the NPV function to find the present value of the growing annuity in cell G10:
=NPV(G5,C6:C15)
- Determine the Future Value (FV) of the Growing Annuity:
- Calculate the future value of the first payment using the formula in cell D6:
=FV($G$5,($G$7-B6),-C6)
- Drag down the formula to calculate future values for the remaining payments.
- Sum up the future values to obtain the desired future value of the growing annuity in cell G11.
=SUM(D5:D15)
- Press Enter and you will get the desired future value of the growing annuity.
Read More: How to Calculate Deferred Annuity in Excel
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Calculate Annuity Payments in Excel
- How to Calculate Annuity Factor in Excel
- How to Calculate Annuity Due in Excel
<< Go Back to Excel Annuity Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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