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

address.