Microsoft KB Archive/214113

= XL: Function to Compute Interest and Growth Rate on a Single Payment =

Article ID: 214113

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



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

= ((FV/PV)^(1/N))-1

where:
 * equals the value today (present value).
 * equals the value at the end of the time period (future value).
 * 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.

Additional query words: cumulative growth rate XL2000 xl2001 xl2002 xl97 xl98

Keywords: kbhowto KB214113

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.