Question

You have been saving money to buy a home and today, you decided to buy a home and take out $350,000 mortgage loan from a bank. This loan requires you to make a monthly payment for 30 years and the interest rate on your loan is 4% APR compounded monthly. What is your monthly mortgage payment for this loan? Please show your excel formula in your answer and explain step-by-step calculation to arrive to your answer.

Answer 1

Excel formula: PMT(rate, nper, pv, [fv], [type])

rate = 4%/12 = 0.003333333333

nper = 30 * 12 = 360

pv = 350,000

We can ignore [fv] and [type]. They are optional.

PMT(0.003333333333, 360, 350000)

PMT = -1,670.9535340756

(Ignore negative sign, it just indicates payment is a cash outflow)

Monthly payments will be $1,670.9535340756

Using formula

PV = 350,000

Monthly interest rate, r = 4%/12 = 0.003333333333

n = 30 * 12 = 360 monthly payments

Monthly payments will be $1,670.9535340756