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

ID: Q85853

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0, 5.0a
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, the RATE function assumes a stream of payments. If you would like to compute the interest rate for a single payment (present value) over a given period use the following formula


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

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 will be for one period, that is, for ten years, use N=10 to get the annual rate or N=120 (10*12) to get the monthly rate.

NOTE: This 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:


   RATE = ((5000/1000)^(1/10))-1 = 17.46%

This means that it would require an interest rate of 17.46% compounded annually to yield $5000.00 in 10 years from an initial investment of $1000.00.

Additional query words: 2.00 2.01 2.10 2.20 2.21 4.00a 5.00a 5.00c 7.00a 97 98 cumulative growth rate


Keywords          : xlformula 
Version           : WINDOWS:2.0,3.0,4.0,5.0,5.0c,7.0,7.0a,97; MACINTOSH: 2.0,3.0,4.0,5.0,98; os/2:2.2,3.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 25, 1999