Compound Annual Growth Rate (CAGR) is a well-known term in financial sectors. The compound growth rate is calculated based on the investment. In this article, we will calculate the end value using CAGR in Excel with 6 easy methods.
What is the Compound Annual Growth Rate (CAGR)?
The compound annual growth rate is the rate of interest of investment comparing the end value and initial value with the number of years passed.
Mathematical Formula of CAGR:
Where,
EV=End value
IV= Initial value
n= Number of Years
So, our desired end value,
We will calculate the CAGR first and then find the end value using that CAGR.
How to Calculate End Value from CAGR in Excel: 6 Methods
We will show some methods to calculate the end value of CAGR in Excel.
The dataset below will be considered for calculation.
We will calculate the CAGR from this dataset, and then calculate the end value.
1. Use a Simple Formula to Calculate the End Value from CAGR
We will create a formula for CAGR calculation.
Now, add new cells for CAGR and End Value. We will calculate the end value for the year 2020 based on the previous data.
Step 1:
- Go to Cell C11.
- Write the following formula.
=(C10/C5)^(1/5)-1
Step 2:
- Now, press Enter.
Now, convert the growth rate into percentage form.
Step 3:
- Press Ctrl+1.
- Choose the Percentage option from the Number tab.
- Then press OK.
Look at Cell C11 now.
The CAGR is presented in the proper form.
Step 4:
- Move to Cell C14.
- Input the formula below.
=C5*((C11+1)^10)
Step 5:
- Now, press the Enter button.
Here, we get the end value for the year 2020 based on the previous values.
Read More: How to Calculate CAGR with Negative Number in Excel
2. Excel RATE Function to Calculate CAGR and End Value
The RATE function is used to get the interest of investment annually.
Step 1:
- Go to Cell C11.
- Put the formula below.
=RATE(5,0,-C5,C10)
Step 2:
- Press the Enter button.
Step 3:
- Now, we will calculate the end value of Cell C14.
- Copy and Paste the following formula.
=C5*((C11+1)^10)
Step 4:
- Again, press the Enter button.
We get the end value of the year 2020.
3. Apply POWER Function to Determine the End Value from CAGR
The POWER function calculates the output of a number based on the given power.
We will use this power function to calculate the end value of CAGR.
Step 1:
- Go to Cell C11.
- Write the formula below.
=POWER(C10/C5,1/5)-1
Step 2:
- Click the Enter button.
Step 3:
- Now, go to Cell C14 to calculate the end value.
- Put the formula on that cell.
=C5*POWER(C11+1,10)
Step 4:
- Now, press the Enter button.
Read More: Excel Formula to Calculate Average Annual Compound Growth Rate
4. Combine RRI and ROW Functions to Measure End Value
The RRI function provides an interest rate for the invested money.
The ROW function is the reference of a row number.
We will combine the RRI and ROW functions to get CAGR first.
Step 1:
- Write the formula based on the RRI and ROW functions on Cell C11.
=RRI(ROW(C10)-ROW(C5),C5,C10)
Step 2:
- Press the Enter button.
Step 3:
- Go to Cell C14 and put the following formula.
=C5*((C11+1)^10)
Step 4:
- Again, press the Enter button.
Read More: How to Calculate Future Value When CAGR Is Known in Excel
5. Use of GEOMEAN Function to Find the End Value from CAGR
The GEOMEAN function provides the geometric mean from a given range of data.
The PRODUCT function returns the multiplication of multiple numbers.
We add a column named Factor for this method.
Step 1:
- Go to Cell C6. Put the following formula in that cell.
=C6/C5
Step 2:
- Press Enter and pull the Fill Handle icon.
Step 3:
- Go to Cell C11. Put the formula below.
=GEOMEAN(D6:D10)-1
Step 4:
- Click the Enter button.
Step 5:
- Now, go to Cell C14.
- Insert the formula based on the PRODUCT function.
=PRODUCT(C5,((C11+1)^10))
Step 6:
- Finally, press the Enter button.
Read More: CAGR Formula in Excel
6. Apply Excel FV Function to Calculate the End Value from CAGR
The FV function calculates the future value of any investment.
We will calculate the future value from the dataset below.
We will calculate the end value after 10 years of the initial investment with the given CAGR.
Step 1:
- Go to Cell C10.
- Write the following formula.
=FV(C6,C7,,-C5,C9)
Step 2:
- Now, press Enter.
Things to Remember
In the case of the FV and RATE function, the initial value must be negative in the formula.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we’ve explained how to calculate the CAGR and the end value of the CAGR in Excel. I hope this will satisfy your needs.
Related Articles
- 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!
