XL: How to Compute the Average Growth Rate of an Investment

ID: Q123198

The information in this article applies to:

Microsoft Excel for Windows, versions 2.0, 2.01, 2.1, 2.10c, 2.10d, 3.0, 3.0a, 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 2.20, 2.2a, 3.0, 3.0a, 4.0, 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SUMMARY

The "average growth rate" is a calculation used by financial investors to determine the best investment over time given present value, future value, and number of periods per year of an investment. This calculation can also be referred to as an "annualized yield rate" or "average rate of return." Note that an annualized rate is always consistent in that it results in percent-per-year figures.

Microsoft Excel does not include an average growth rate function. However, you can 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.

MORE INFORMATION

Investors want a consistent method to judge investment options. There are various optional terms given to a method of calculation to compare these options. One popular term is "average growth rate," and two others are "annualized yield rate" and "average rate of return."

The most important point is that the method returns consistent unit values.

All results should return an average annual percent-per-year figure. The results can then be compared directly to determine the best choice of the options considered. You can calculate this result in Microsoft Excel by using the common yield formula and adding an average compensation factor.

Example 1

Assume an investment where FV= $120,000, PV=$10,000, n=120 months, and m=12 months/year.

AGR = ((120000/10000)^(1/120))^12 - 1 = 0.282089

AGR = 28.21% per year

Example 2

Assume an investment where FV= $120,000, PV=$10,000, n=10 years, and m=1 year/year.

AGR = ((120000/10000)^(1/10))^1 - 1 = 0.282089

AGR = 28.21% per year

REFERENCES

"Handbook of Fixed Income Securities," Richard D. Irwin Inc., 1991 pages 79-80

Additional query words: howto XL98 XL97 XL7 XL5 XL4 XL3 2.00 3.00 4.00 4.00a 5.00c Annualized Average Rate Return Yield compound growth cumulative annual lotus function interest

Keywords : xlformula
Version : MACINTOSH:2.20,2.2a,3.0,3.0a,4.0,5.0,5.0a,98; WINDOWS:2.0,2.01,2.1,2.10c,2.10d,3.0,3.0a,4.0,4.0a,5.0,5.0c,7.0,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type :