Microsoft Office Tutorials and References
In Depth Information
Using the PMT Function
Money payments to pay back a $200,000 loan
Financial functio ns
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.