Download Article
Download Article
This wikiHow teaches you how to find the average growth rate of an investment in Microsoft Excel. Average growth rate is a financial term used to describe a method of projecting the rate of return on a given investment over a period of time. To calculate the yearly average growth rate, use the formula "=(B3-B2)/B2". To calculate the average, use "=AVERAGE(C3:C20)".
Using Growth Rate Formula in Excel
To calculate the growth rate in Microsoft Excel, use the formula: =(B3-B2)/B2 for annualized yield rate or =AVERAGE(C3:C20) for the average growth rate. Replace 20 with the last cell of your data.
Steps
-
Label your columns. The first thing we'll need to is create column labels for our data.
- Type "Year" into A1 (the first cell in column A).
- Type "Amount" into B1.
- Type "Growth Rate" into C1.
-
Add the years for your investment to the Year column. Column A should contain a list of every year you've had the investment. In the first available cell of Column A (A2, just below the column label), type "Initial Value" or something similar. Then, in each subsequent cell, list each year.Advertisement
-
Add the value of your investment per year to the Value column. The first available cell of Column B (B2, just below the label) should contain the amount of the initial investment. Then, in B3, insert the investment's value after one full year, and repeat this for all other years.
-
Set the number formatting for the average growth rate calculator. To ensure your numbers display properly, you'll need to add numerical formatting to your cells:
- Click column A (the letter above the column) to select it, and then click the Format button on the Home tab. It'll be near the top-right corner of Excel. Then, click Format Cells on the menu, select Date in the left panel, and then choose a date format in the right panel. Click OK to save your changes.
- For column B, you'll want to format the amounts as currency. Click the "B" above column B to select it, click the Format button on the Home tab, and then click Format Cells on the menu. Click Currency in the left panel, select a currency symbol and format on the right, and then click OK .
- Column C should be formatted as percentages. Click the "C" above column C, and again, click Format , and then Format Cells . Select Percentage in the left panel, choose the number of decimal places in the right panel, and then click OK .
-
Double-click cell C3. The reason you're starting with this cell is because A3 represents the first completed year of your investment, and there's nothing to calculate for the initial investment amount.
-
Enter the formula for calculating the annualized yield rate. You can type this into the cell itself, or into the formula bar (fx) at the top of the worksheet: =(B3-B2)/B2
-
Press ↵ Enter or ⏎ Return . This displays the growth rate for the first year of your investment in cell C3. [1] X Research source
-
Apply the formula to the remaining cells in column C. To do this, click the cell containing the growth rate for the first year (C3) once, and then drag the cell's bottom-right corner downward to the bottom of your data. You can also double-click that cell's bottom-right corner to accomplish the same task. This displays the growth rate for each year.
-
Double-click a blank cell in a different column. This cell is where the average growth rate of your existing data will appear.
-
Create a formula using the AVERAGE function. The AVERAGE function tells you the mean average of a set of numbers. If we calculate the mean average of the growth rates we calculated in column C, we'll find the average growth rate of your investment. The column should look like this:
- =AVERAGE(C3:C20) (replace C20 with the address of the actual last cell containing a growth percentage in column C).
-
Press ↵ Enter or ⏎ Return . The average growth rate of your investment now appears in the cell.
Common Questions: Finding Growth Rate in Excel
How should I organize my Excel data for growth rate?
Once you input your data, make sure to label your columns. You can use Year , Amount , and Growth Rate . Make sure each column is formatted with the correct date, currency, and percentage.
What is the formula for calculating annual growth rate in Excel?
The formula for calculating annual growth rate in Excel is =(B3-B2)/B2 . Make sure to put this formula in cell C3, or the second cell of your growth rate column.
What is the formula for calculating average growth rate in Excel?
The formula for calculating average growth rate in Excel is =AVERAGE(C3:C20) . Make sure to replace C20 with the last cell of your data.
Video
Tips
About This Article
1. Create columns labeled Year, Value, and Growth Rate.
2. Format the Year column as a date.
3. Format the Value column as currency.
4. Format the Growth Rate column as percentage.
5. In the growth rate column, find the growth percentage of the first full investment year using the formula =(B3-B2)/B2
, where B3 is the investment amount after the first full year, B2 is the previous amount.
6. Apply the formula to all cells in the Growth Rate column.
7. Use the AVERAGE function to find the average of the Growth Rate column: =AVERAGE(C3:C20)
, where C3 is the first cell containing a growth percentage, and C20 is the last.
