XL: Function to Compute Interest and Growth Rate on a Single Payment (214113)
The information in this article applies to:
- Microsoft Excel 2000
- Microsoft Excel 2002
- Microsoft Excel 97 for Windows
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q214113 SUMMARY
In Microsoft Excel, the RATE function assumes a stream of payments.
If you want to compute the interest rate for a single payment (present value) over a given period, use the following formula
where:
- PV equals the value today (present value).
- FV equals the value at the end of the time period (future value).
- N equals the total number of periods.
Keep in mind that the rate is for 1 period; therefore, for 10 years, use N=10 to obtain the annual rate, or use N=120 (10*12) to obtain the monthly rate.
NOTE: This function is the equivalent of the @RATE function in Lotus 1-2-3 when used to find a compound growth rate.
Example
To find the annual rate of interest accrued by $1000.00 invested today
with an expected yield of $5000.00 in 10 years, use the following function:
= ((5000/1000)^(1/10))-1 = 17.46%
This means that an interest rate of 17.46% compounded annually is required to yield $5000.00 in 10 years from an initial investment of
$1000.00.
| Modification Type: | Major | Last Reviewed: | 5/28/2003 |
|---|
| Keywords: | kbhowto KB214113 |
|---|
|