Microsoft Office Tutorials and References

In Depth Information

**Using the PMT Function**

Figure 3-38

Money payments to pay back a $200,000 loan

Financial functio
ns

category

PMT function calculates loan payments

monthly payment expressed

as a negative value (because

Excel treats it as an expense)

Trouble?
If you see ##### in cell B10, the column width is too small to view the

entire value. Increase the width of column B to display the monthly payment value.

Diane and Glenn would have to pay more than $1,400 per month for 20 years to

repay a $200,000 loan at 6 percent interest. This is still too high. Diane is interested in

other possibilities. Because you already set up the worksheet, you can quickly try other

scenarios without having to reenter any formulas. Diane wonders whether extending the

length of the loan would reduce the monthly payment by a sizeable margin. She asks

you to calculate the monthly payment for different loan options.

To analyze other loan options:

◗

1.
Change the value in cell B6 to
. The amount of the monthly payment drops to
30

$1,199.10, which is a decrease of $200 per month.

◗

2.
Change the value of cell B8 to
175,000
. For this smaller home loan, the monthly

payment drops even further to $1,049.21 per month. This is almost exactly equal

to what the couple is currently paying in rent.

◗

3.
Save the workbook, and then close it.

You’ve completed your work on Diane and Glenn’s budget. Your analysis has shown

Diane several important things. Her projected budget allows the couple to transfer

enough money to the home savings account to make a down payment on a home in

about three years. This savings plan leaves enough funds in the main savings account

to cover their monthly expenses. Finally, the possible monthly mortgage payments for a

loan will not be substantially more than what Diane and Glenn are currently paying in

rent. So, not only will Diane and Glenn be able to save enough to make the initial down

payment, their monthly income should also cover the monthly payments. Of course, all

budgets must be revised periodically to meet changing expenses and income, and many

different options exist in the home mortgage market. For example, Diane and Glenn

could receive a mortgage with a lower interest rate or one that requires a smaller down

payment. Your work has given Diane and Glenn enough information to make informed

choices about their immediate ﬁ nancial future.