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

 Last reviewed: February 2, 1998
Article ID: Q123198
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 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 7.00 2.00 3.00 4.00
4.00a 5.00 5.00c Annualized Average Rate Return Yield compound growth rate
cumulative growth rate annual growth rate lotus rate function interest
Keywords : xlformula
Version : WINDOWS:4.0,5.0,5.0c,7.0,97; MACINTOSH:4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.