How to Calculate CAGR in Excel

Step-by-Step Guide

1. 1

   Step 1: Open Microsoft Excel.

   Either click on the green X icon on the dock or opening it from the Applications folder in Microsoft Office.
2. 2

   Step 2: Open a New Workbook.

   ,, Click Names > Create and choose Top Row , 23,512 Enter to cell C2 the Beginning Value of the investment, e.g. 14,500 Enter to cell E3 the number of Years the investment was outstanding up to the Ending Value, e.g. 3 Enter to cell D3 an = and then the formula, 1/Years.
   
   Enter to cell F3 the value, 1 , Click Column and then AutoFit Selection, and Alignment Center.
   
   Highlight column A and choose Format, then Cells.
   
   Under the Number menu, and Percentage category, choose 2 decimal places.
   
   Highlight columns B:
   C and choose Format, then Cells.
   
   Under the number Number in the Custom category, enter $#,##0 Highlight column D and choose Format, then Cells.
   
   Under the Number menu in the Number category, enter .0000 , For the example numbers given, you should see a CAGR result of
   24.93% Thus, your CAGR for your three-year investment is equal to
   24.93%, representing the smoothed annualized gain you earned over your investment time horizon. ,
3. 3

   Step 3: Enter the Headings/Defined Variables: Enter to cell A1 the label

4. 4

   Step 4: CAGR Enter to cell B1 the label

5. 5

   Step 5: Ending_Value Enter to cell C1 the label

6. 6

   Step 6: Beginning_Value Enter to cell D1 the label

7. 7

   Step 7: _1_OverYears Enter to cell E1 the label

8. 8

   Step 8: Years Enter to cell F1 the label

9. 9

   Step 9: Highlight columns B:F and select Insert from the top menu.

10. 10

    Step 10: Enter the formulas and values: Enter to cell A2 an = and then the formula ((Ending_Value/Beginning_Value)^(_1_OverYears))-_1 Enter to cell B2 the Ending Value of the investment

11. 11

    Step 11: Format your cells: Highlight columns A:F and select Format from the top menu.

12. 12

    Step 12: Review the value in cell A2.

13. 13

    Step 13: Then you would construct a Table of similar investments:

Detailed Guide

About the Author