Calculate 5-year CAGR in Excel
A friend asks you to evaluate an investment: it grew from the start value in B1 to the end value in B2 over the years in B3, and they want its annualized rate to compare with other options. Fill the CAGR cell B4 with the compound annual growth rate. Return the rate as a decimal (for example 0.1 for 10%).
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
CAGR = ((end/start)^(1/years)) - 1. Format the result as a percentage in Excel with % formatting when you paste it back into a workbook.
FAQ
What is the CAGR formula in Excel?
Use =((B2/B1)^(1/B3))-1 when B1 is start, B2 is end, and B3 is years. Format as a percentage. The same CAGR formula works in any Excel workbook, including legacy .xls files.
Why is the expected result 0.1 instead of 10%?
Spreadsheets store percentages as decimals. 0.1 formatted as % displays as 10%.