Microsoft KB Archive/76849

= RATE Differs from HP Calculator for Periods Other than Annual =

Article ID: 76849

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q76849



SUMMARY
The value returned for the RATE function in Microsoft Excel will differ from that returned by a Hewlett-Packard (HP) calculator if the period is not annual.



MORE INFORMATION
The reason for this is that Microsoft Excel returns a rate of return for the designated period, but the HP calculator returns an annual rate. Therefore, if the period designated in Microsoft Excel is not annual, the returned values will differ, as the following example of an investment with quarterly payments illustrates.

Example
  Enter the following in a worksheet:

     A1:  ($1,000.00)     B1:  =RATE(4,A2,A1) A2: $315.38 A3: $315.38 A4: $315.38 A5: $315.38

This example describes a loan amount of $1000.00 with quarterly payments of $315.38. The resulting value in cell B1 is 10%.  Calculate the RATE with an HP business calculator. Enter N=4; PV=-1,000; PMT=315.38; FV=0: P/YR=4; End Mode. The RATE function will return 40%.

Both Microsoft Excel and the HP calculator are correct. The difference is that Microsoft Excel returns the quarterly rate of return while the HP calculator returns the annual rate of return.

To have Microsoft Excel return an annual rate, modify the formula as shown below:

  A1:     ($1,000.00)     B1: =RATE(4,A2,A1)*(# payments per year) A2:    $315.38 A3:    $315.38 A4:    $315.38 A5:    $315.38

Additional query words: xl2k interest rate XL

Keywords: KB76849

-

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

© Microsoft Corporation. All rights reserved.