Method 1 – Use a Simple Formula to Calculate the End Value from CAGR
Step 1:
- Go to Cell C11.
- Write the following formula.
=(C10/C5)^(1/5)-1
Step 2:
- Press Enter.
Convert the growth rate into percentage form.
Step 3:
- Press Ctrl+1.
- Choose the Percentage option from the Number tab.
- 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:
- Press the Enter button.
We get the end value for the year 2020 based on the previous values.
Method 2 – Excel RATE Function to Calculate CAGR and End Value
Step 1:
- Go to Cell C11.
- Put the formula below.
=RATE(5,0,-C5,C10)
Step 2:
- Press Enter.
Step 3:
- We will calculate the end value of Cell C14.
- Copy and Paste the following formula.
=C5*((C11+1)^10)
Step 4:
- Press the Enter button.
We get the end value of the year 2020.
Method 3 – Apply POWER Function to Determine the End Value from CAGR
Step 1:
- Go to Cell C11.
- Write the formula below.
=POWER(C10/C5,1/5)-1
Step 2:
- Click Enter.
Step 3:
- Go to Cell C14 to calculate the end value.
- Put the formula on that cell.
=C5*POWER(C11+1,10)
Step 4:
- Press Enter.
Method 4 – Combine RRI and ROW Functions to Measure End Value
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 Enter.
Step 3:
- Go to Cell C14 and put the following formula.
=C5*((C11+1)^10)
Step 4:
- Press Enter.
Method 5 – Use of GEOMEAN Function to Find the End Value from CAGR
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 Enter.
Step 5:
- Go to Cell C14.
- Insert the formula based on the PRODUCT function.
=PRODUCT(C5,((C11+1)^10))
Step 6:
- Press the Enter button.
Method 6 – Apply Excel FV Function to Calculate the End Value from CAGR
Step 1:
- Go to Cell C10.
- Write the following formula.
=FV(C6,C7,,-C5,C9)
Step 2:
- 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.
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!
