Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
✓ Payment period: Loans are usually paid back by paying a periodic
amount. Most often the period is monthly.
✓ Duration of the loan: This is the count of payment periods. For example
a loan may have 36 monthly payments.
✓ Payment: The amount you pay each payment period.
Each of these factors is related to all the others. If you borrow more, your
monthly payments will be higher — that’s no surprise. If you get a low
interest rate, you may be able to pay off your loan in less time — that may be
something to consider!
The functions used to calculate loan factors work with the same group of
inputs, namely the five factors just listed. The functions typically accept
three or four inputs as data and then calculate the desired value, kind of like
the way algebra works.
Calculating the payment amount
The PMT function tells you the periodic payment amount for your loan. If
you know the principal, interest rate, and number of payments for a loan, you
can use the PMT function to calculate the payment amount. But first, a word
about interest rates.
Most loan interest rates are expressed as an annual rate. However, Excel
needs the interest rate per payment period in order to calculate properly. For
example, if you’re calculating for a loan with monthly payments, you need the
monthly interest rate. You can easily get this number by dividing the annual
interest rate by 12, the number of months in a year. To calculate a loan
payment, follow these steps:
1. Enter the loan principal, annual interest rate, and number of payment
periods in separate cells within the worksheet.
You can add labels to adjacent cells to identify the values, if desired.
2. Position the cursor in the cell where you want the results to display.
3. Enter =PMT( to begin the function entry.
A small pop-up menu shows the arguments used in the function.
4. Click the cell where you entered the interest rate, or just enter the cell