Question

Kathy plans to move to Maryland and take a job at McCormick as the Assistant Director of HR. She and her husband Stan plan to buy a house in Garrison, MD and their budget is $500,000. They have $100,000 for the down payment and McCormick will pay for closing costs. They are considering either a 30-year mortgage at 4.5 percent annual rate or a 15 year mortgage at 4 percent.

Required:
Calculate the monthly payment for each using the PMT function.

Answer 1

Total budget = $500,000

Down payment = $100,000

Loan amount = $400,000

Case 1

Annual rate = 4.5%

Number of payment = (30*12 month) 360

Loan amount = $400,000

Monthly payment = PMT(Rate, Nper, Value of loan)

Monthly payment = PMT(4.5%, 360, 400,000)

Monthly payment = $2,026.74

Case 2

Annual rate = 4%

Number of payment = (15*12 month)180

Loan amount = $400,000

Monthly payment = PMT(Rate, Nper, Value of loan)

Monthly payment = PMT(4%, 180, 400,000)

Monthly payment = $2,958.75