In this article, we’ll discuss how to calculate the future value of an investment in Excel when the CAGR is known. This is also known as the Reverse Compound Annual Growth Rate calculation.
Calculate Future Value When CAGR Is Known in Excel: 2 Methods
Definition of CAGR (Compound Annual Growth Rate): A compound annual growth rate (CAGR) calculates the rate of return for an investment over a certain period of investment time. It estimates the growth if the investment has a nature of growing steadily on a yearly compounded basis. That’s why we also call it the “smoothed” rate of return.
On the other hand, we can calculate the future value i.e., the final value of an investment when the CAGR is known for a certain investment period. We also call this amount the Reverse Compound Annual Growth Rate.
Here, we’re going to discuss two different methods to calculate the Reverse Compound Annual Growth Rate. Let’s dive into the examples.
1. Calculate Future Value When CAGR Is Known in Excel Using Basic Formula
Using the following formula, we can easily calculate the Future Value for a certain investment period when the CAGR value is known.
FV = PV * (CAGR + 1)n
In this formula,
FV – the future value which is the final amount of an investment after the investment period ends.
PV – the starting or present value of the investment money.
CAGR – known as Compound Annual Growth Rate in percentage.
n – the number of years for which we’ll invest the money.
Let’s say we want to know the future value of an initial investment of $10,000 for an investment period of 10 years. The compound annual growth rate (CAGR) in this investment is 10%. In this illustration,
C4 – initial investment/present value (PV)
C5 – compound annual growth rate (CAGR)
C6 – no of investment periods in years (n)
In cell C8, put the following formula:
=C4*(C5+1)^C6
Applying the formula, we’ve got the future value, which is $25,937.
Read More: CAGR Formula in Excel
2. Use of the FV Function to Estimate Future Value When CAGR Is Known
The FV function calculates the future value (final investment goal) of a current investment amount for a certain number of payment periods based on a fixed interest rate (growth rate). In this method, we’ll discuss an example to calculate the future value using the FV function.
Let’s first introduce the FV function first-
The syntax for the FV function is-
FV(rate, nper, pmt, [pv], [type])
The function takes several arguments, like-
rate (required)- the annual growth rate of the investment, CAGR.
nper (required) – number of payment periods in years.
pmt (required) – payment amount per period which is blank in this calculation.
pv (optional) – present value or initial investment amount.
type (optional)- 0 for payment due at the end of the payment period and 1 for payment due at the start of the period.
In this example, we’ll use the same data from example 1 to calculate the future value. In cell C10, we configured the following formula-
=FV(C5,C6,,C4,0)
Where,
C4 – initial investment/present value (PV)
C5 – compound annual growth rate (CAGR)
C6 – no of investment periods in years (n).
C7 – payment per period which is empty in this calculation.
C8 – payment is due at the end of the period i.e., 0.
The following screenshot shows the output which is the same as the value we’ve got from the first example.
Note
- While calculating the future value using the FV function, we used a negative sign before the present value (pv) argument. It is to return the future value (fv) as a positive If we use the pv argument without the negative sign, it’ll calculate the future value as negative.
- By default, the result shows as a number with decimal places. We can remove decimal places by following the steps below:
- Go to the Home Tab.
- Navigate to the Number panel.
- choose the Decrease Decimal
Read More: How to Calculate End Value from CAGR in Excel
Download the Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to find the future value for the known value of CAGR in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- Excel Formula to Calculate Average Annual Compound Growth Rate
- How to Calculate CAGR with Negative Number in Excel
- How to Calculate 3-Year CAGR with Formula in Excel
- How to Calculate 5 Year CAGR Using Excel Formula
- How to Create CAGR Graph in Excel
<< Go Back to Compound Interest in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
