Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


More information

To calculate the AGR result in Microsoft Excel by using the formula shown in the "Summary" section of this article, follow these steps:
  1. Open a new workbook in Excel.
  2. Type the following column titles in the worksheet:
       A1: FV  B1: PV  C1: N  D1: M  E1: AGR
    					
  3. Type the following formula in cell E2:
    =((A2/B2)^(1/C2))^D2-1
  4. For the following examples, drag cell E2 to fill the "AGR" column to at least E3.

Example 1

Assume an investment where FV=$120,000, PV=$10,000, n=120 periods, 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 (an AGR of 28.21% per year).

Example 2

Assume an investment where FV=$120,000, PV=$10,000, n=8 periods, and m=1 period/year. Type these values in the worksheet as follows:
   A3: 120000  B3: 10000  C3: 8  D3: 1
				
The formula in cell E3 calculates = .364262 (an AGR of 36.43% per year).

↑ Back to the top


References

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

↑ Back to the top


Keywords: KB123198

↑ Back to the top

Article Info
Article ID : 123198
Revision : 5
Created on : 1/19/2007
Published on : 1/19/2007
Exists online : False
Views : 650