RATE Differs from HP Calculator for Periods Other than Annual (76849)

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

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

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