How to Calculate the Average Growth Rate of an Investment

Financial investors use the term "average growth rate" for a calculation that determines the best investment over time given present value, future value, and number of periods per year of an investment. Some investors call this calculation an "annualized yield rate" or "average rate of return." An important point to remember is that an annualized rate is always consistent: it results in percent-per-year figures.

Unfortunately, Microsoft Excel does not include an average growth rate function. But not to worry. Use the following formula for this calculation:
=((FV/PV)^(1/n))^ m-1 , where FV is future value, PV is present value, n is the number of investment periods, and m is the periods per year factor.

To calculate the average growth rate (AGR) in Microsoft Excel by using the formula above, follow these steps:

Difficulty: Moderately Challenging

Instructions

Things You'll Need:

• Computer
• MS Excel spreadsheet software
• Basic familiarity with spreadsheets
• High school math skills

1. Open a new spreadsheet (or “workbook” in Microsoftese) in Excel.

2. Type the following column titles in the worksheet:
   
   A1: FV B1: PV C1: N D1: M E1: AGR

3. Type (or copy and insert) the following formula in cell E2:
   =((A2/B2)^(1/C2))^D2-1

4. Remember that the “caret symbol” (^) in an Excel formula indicates a “superscript,” or a powers function, (e.g., 3 squared, 3x3, or 9, in an Excel worksheet looks like this: 3^2.)

5. Work this out: Assume you’re buying a pickup truck full of 24k gold for $10,000. The seller guarantees (Ha!) that the gold will be worth $120,000 in ten years. You never invest in anything that has an annual growth rate of less than 25 percent. Should you buy the gold? (Bubba’s not throwing in the pickup truck.) Here’s how it breaks out: FV (future value) = $120,000, PV (present value of your hard-earned cash) = $10,000, n = 120 periods (months in this case, extended over ten years), and m = 12 periods/year. Type these values in the worksheet as follows:
   A2: 120000 B2: 10000 C2: 120 D2: 12

6. The formula in cell E2 calculates = .282089 (that’s an AGR of 28.21% per year, and more than meets your basic requirement for a 25% return).

7. Try another example: Type (or copy and insert) the following formula in cell E2:
   =((A3/B3)^(1/C3))^D3-1. (This is the same formula as the one above, except it moves down one row so you can plug in the new numbers.)

8. The formula in cell E3 calculates = .364262 (an AGR of 36.43% per year).

9. That sounds pretty good. But keep in mind that you’re relying on Bubba’s promise of a 1,200% return on your investment over the long haul.

Tips & Warnings

• When doing your own estimates of average growth rate you might want to be very conservative in estimating the future value of your investment. Then decide whether you can live with the growth rate.