XL: RATE Differs from HP Calculator for Periods Other than Annual

ID: Q76849

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

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

Keywords :
Version : WINDOWS:2.x,3.x,4.0,5.0,7.0,97; MACINTOSH:2.x,3.0,4.0,5.0
Platform :
Issue type :