Google Sheets is a web-based application that includes many financial functions. As such, Sheets users can set up a wide variety of investment spreadsheets. The compound annual growth rate, otherwise CAGR, is one of the handy formulas Sheets users can add to their spreadsheets with or without the application’s functions. With the CAGR formula, you can set up a spreadsheet that shows the average annual growth rate, in percentage terms, for a series of figures over multiple time periods.
The CAGR Formula
The compound annual growth rate formula is widely used for investment analysis. Spreadsheets often include tables for a series of values that show you the year-to-year growth rate for each figure. For example, an investment might grow by 23%, 11% and 13% in three separate years. However, what would the average annual percentage increase be for those years?
You could find an average percentage increase for those values by adding 23%, 11% and 13% together and then dividing them by three. That would amount to an average annual growth of 15.66%. However, that does not factor in the compounding effects of investments. CAGR is an average that also takes compounding into account, so percentage figures are not included within its formula.
The CAGR formula is: EV / BV ^ (1/n) – 1. EV and BV are the ending and beginning values that you replace with cell references. The n in the formula is the number of time periods, which is usually years or months.
How to Add the CAGR Formula to Sheets Spreadsheets
Now let’s add the CAGR formula to a spreadsheet. First, check out this calculator that returns the compound average growth rate for numbers entered. On that page, you enter the starting and ending values along with the number of periods. You can set up exactly the same calculator in Sheets.
First, open a blank spreadsheet in Sheets. Enter ‘Beginning Value’ in cell B3 of the spreadsheet. Select B4 and input ‘Ending Value’ in that cell. Enter ‘Number of periods’ in cell B5, and you might need to widen the B column a little to fit that row heading in. Your spreadsheet should match the one in the snapshot directly below.
Now you can add the CAGR formula to the spreadsheet. First, enter ‘The CAGR’ in cell B7, which is another row heading. Select C7 and click within the fx bar. Enter ‘=(C4/C3)^(1/2)-1’ in the fx bar, and press the Return key. Cell C7 will include the compound annual growth rate formula as shown in the snapshot directly below.
Now it’s time to enter some numbers into the spreadsheet. Enter ‘1,000’ in cell C3, and input ‘2,250’ as the ending value in C4. Enter ‘4’ in cell C4 for the number of time periods.
Cell C7 will return the value 0.2247448714. To convert that cell to a percentage, select C7 and press the Format as percentage button. Then the compound annual growth rate value in C7 will amount to 22.47% as shown directly below.
As a final touch, add some formatting to the compound annual growth rate calculator. Select the cell range B3:C7 with cursor, and press the Borders button on the toolbar. Select to add borders to all cells in the calculator as below.
Select the cells B3:B7 by dragging the cursor over them. Press the Bold button on the toolbar. That option adds bold text formatting to the headings.
You can add colors to the calculator’s cells. Select the cells to format, and click the Fill color toolbar option. That opens a palette from which you can choose new colors.
The POW Function
You can also calculate the compound annual growth rate with Sheets’ POW function. The syntax for that function is: POW (base, exponent). For the CAGR formula, the base is the end value / beginning value and the exponent is 1/n.
Select cell C8 in your spreadsheet. Enter ‘=POW(C4/C3,1/C5)-1’ in the fx bar, and press the Return key. Cell C8 will include the CAGR value of 0.2247448714. Convert the C8 formatting to percentage as you did for C7. The POW function cell now includes exactly the same common annual grow rate figure as C7.
That’s how you can set up a CAGR calculator in Sheets with the annual growth rate formula and POW function. RATE and IRR are also two other functions that you can find the compound annual growth rate with. To add the CAGR formula to Excel spreadsheets, check out this Tech Junkie guide.