Using the Excel POWER Function to Compute Investment Returns
You can use the Excel spreadsheet POWER function to compute the annualized return of an investment. With the POWER function enter the final value of the investment, the amount of the initial investment and the time period in years between the final value and initial investment.
The form of the POWER function is:
=POWER(Final dollar value/Initial dollar value,1/Time period)-1
Here are two examples that show you how to use the POWER function.
Example 1: Invest $5,000 for 30 Years That Grows to $40,000
Assume that you invest a lump sum of $5,000 which accumulates to $40,000 in 30 years. What is the annualized return?
In a cell of an Excel spreadsheet enter:
=POWER(40000/5000,1/30)-1
In that cell Excel returns:
0.07177
So the annualized return is 7.18 percent.
Example 2: Invest $10,000 for 40 Years That Grows to $100,000
Assume that you invest a lump sum of $10,000 which accumulates to $100,000 in 40 years. What is the annualized return?
In a cell of an Excel spreadsheet enter:
=POWER(100000/10000,1/40)-1
In that cell Excel returns:
0.05925
So the annualized return is 5.93 percent.