RATE Differs from HP Calculator for Periods Other than Annual

WINDOWS                   | MACINTOSH

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 while 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

1. 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%.
   
   

2. 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%.

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

REFERENCES

"Online Help," version 5.0 "Function Reference," version 4.0, page 347 "Function Reference," version 3.0, page 192