Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
5. Enter /12 to divide the annual interest rate to get the monthly
6. Enter a comma ( ,).
7. Click the cell where you entered the number of payments, or enter the
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