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

 Last reviewed: February 2, 1998
Article 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 3.00 4.00 4.00a 5.00 5.00a
5.00c 7.00 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.