Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
The RATE function returns the interest rate per period. This number can
be misleading. The periodic interest amount may be small enough that it is
displayed as 0 percent if the formatting in the cell isn’t set to display enough
To find out the annual rate, you simply need to take the number returned by
RATE and multiply it by 12. To do this:
1. Position the cursor in the cell where you want the annual interest rate
2. Enter a =.
3. Click the cell where the RATE function returned the periodic
4. Enter a ×.
5. Enter 12.
6. Press Enter.
As an example, assume a loan principal of $15,000 with a monthly payment
of $650. The loan is to be paid off in 24 months. Figure 5-7 shows a worksheet
with these figures. The periodic interest rate is calculated with the RATE
function, and then the annual rate is calculated by multiplying the periodic
interest rate by 12.
You can use three optional arguments with RATE:
✓ Future Value: The amount you want the loan to be worth at the end of
its life. The default is 0.
✓ Type: This tells the function whether payments are applied at the end
of the period or the beginning of the period. A value of 0 indicates the