The average growth rate is a financial term used to describe a way of predicting the rate of return on a given investment over a period of time. By factoring the present and future value of a particular investment in relation to the periods of the year, it is possible to calculate the annual rate of return, useful for the development of an investment strategy. An average growth rate calculator can be created on a Microsoft Excel spreadsheet, and can accurately determine the annual rate of return for a particular investment. This article provides step-by-step instructions on how to use Excel to accurately calculate the average growth rate of an investment.
Steps
Part 1 of 3:
Step 1. Enter and format the column header for the average growth rate calculator
Write "Future Value" in cell B1. With cell B1 still selected, click the Wrap Text button in the formatting toolbar. Select the "Formatting Brush" from the Formatting Toolbar and drag it from cell C1 down to cell F1.
Step 2. Format the cell borders of the calculator
Click in cell B1 and drag to select cells B1 to F1. On the formatting toolbar click the arrow on the "Borders" button and select "Thick Bottom Border". Click and drag to select cells B2 through F2. On the formatting toolbar click the arrow on the "Borders" button and select "Outer Borders" from the menu options. The cells of the average growth rate calculator will be outlined in black.
Step 3. Set the number formatting for the average growth rate calculator
Select "Format Cells" in Format.
- Select cells B2 and C2 and choose the "Currency (€)" option. Any value will be entered in B2 and C2 will now be shown as a quantity in euros.
- Click in cell F2 and select the "Percentage (%)" option. Any value that will be entered into F2 will now be read as a percentage. The numbers for the average growth rate calculator were formatted.
Part 2 of 3: Enter the Formula for Calculating the Annual Growth Rate of an Investment in Excel
Step 1. Enter the formula to calculate the annual growth rate
Write the following form in cell F2: = ((B2 / C2) ^ (1 / D2)) ^ E2-1. The average growth rate of an investment will now appear in cell F2 for any value you enter in cells B2 to E2.
Part 3 of 3: Testing the Average Growth Rate Calculator and Interpreting the Result
Step 1. Enter the following values in cells B2 to E2 to test the accuracy of the average growth rate calculator
Write "100.000" in cell B2, "500" in C2, "120" in D2 and "4" in E2. If the result shown in cell F2 is "19.32%", then your calculator is working fine. In other words, if an investment of € 500 yields 10,000 in a period of 10 years (120 months) and interest is paid every three months, the average growth rate is 19.32%.
