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:
Things You'll Need
- MS Excel spreadsheet software
- Basic familiarity with spreadsheets
- High school math skills
Open a new spreadsheet (or "workbook" in Microsoftese) in Excel.
Type the following column titles in the worksheet:
A1: FV B1: PV C1: N D1: M E1: AGR
Type (or copy and insert) the following formula in cell E2: \=((A2/B2)^(1/C2))^D2-1
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.)
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 10 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 10 years), and m = 12 periods/year. Type these values in the worksheet as follows: A2: 120000 B2: 10000 C2: 120 D2: 12
The formula in cell E2 calculates = .282089 (that's an AGR of 28.21 percent per year, and more than meets your basic requirement for a 25 percent return).
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.)
The formula in cell E3 calculates = .364262 (an AGR of 36.43 percent per year).
That sounds pretty good. But keep in mind that you're relying on Bubba's promise of a 1,200 percent return on your investment over the long haul.