Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
Calculating the interest rate
The RATE function tells you what the interest rate is on a loan. This function
is great for comparing loan offers. Although a loan offer will always include
an interest rate, you may want to use Excel to double-check and ensure that
some other fees are not included in the payments. Then you can compare
different loan scenarios to see which one offers the true lowest interest rate. I
don’t think anyone wants to pay more than they have to!
Some lenders charge fees as well as an annual interest rate. When these fees
are figured in, the effective interest rate will be higher than the stated interest
rate. You can use the RATE function to determine the effective interest rate
for a loan. If it’s the same as the stated interest rate, then you know no fees are
The inputs for this function are the principal, the number of payments, and the
fixed amount of the periodic payment. Here’s how to use the RATE function:
1. Enter the following in separate cells within the worksheet:
Enter the monthly payment amount as a negative number because it is a
cash flow out. You can add labels to adjacent cells to identify the values,
if you want.
2. Position the cursor in the cell where you want the results to appear.
3. Enter =RATE( to begin the function entry.
4. Click the cell where you entered the number of periods, or just enter
the cell address.
5. Enter a comma ( ,).
6. Click the cell where you entered the monthly payment amount, or just
enter the cell address.
7. Enter a comma ( ,).
8. Click the cell where you entered the principal amount, or just enter
the cell address.
9. Type a ), and press Enter.