Microsoft Office Tutorials and References

In Depth Information

**Figuring Loan Calculations**

5. Enter
/12
to divide the annual interest rate to get the monthly

interest rate.

6. Enter a comma (
,).

7. Click the cell where you entered the number of payments, or enter the

cell address.

8. Enter a comma (
,).

9. Click the cell where you entered the principal amount, or just enter

the cell address.

10. Type a
), and press Enter.

Watch those percents! Remember that a percent is really one one-hundredth,

so 5 percent is the numerical value 0.05. You can format values to display as

percents in Excel, but you must enter the proper value.

Figure 5-4 shows how I set up a worksheet with values and returned the

periodic payment amount for a loan. The amount is expressed as a negative

number because payments are a cash flow out. For example, you may be

considering taking a loan out from the bank for some house additions. Using real

numbers, the loan may be structured like this:

✓
A loan amount of $15,000 (the principal)

✓
An annual interest rate of 5 percent

✓
A monthly payment period

✓
A payment period of 24 payments

This summarizes four of the key parameters. The PMT function figures out

the fifth — the periodic payment, which is the amount you have to shell out

each month.

Figure 5-4:

The PMT

function

calculates

the loan

payment

amount.