Question

Michael has a 25 year mortgage for $350000 with monthly payments of $3118.98. a) What is the nominal annual rate compounded monthly? After 3 years the interest rate has gone down to 6.75% (compounded monthly). b) What is the outstanding balance on the mortgage? c) If Michael refinances his existing mortgage at the new rate but with the same number of remaining monthly payments, what will his new payments be? d) If instead he takes out a new 25 mortgage (for the outstanding balance from part b)), what will his monthly payments be.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A B с D 1 2. 3 Mortgage Amount Term in years Monthly payments $350,000.00 25 $3,118.98 4 5 LC a) Nominal annual rate compounded monthly (APR) 9.75% After 3 years: APR 6.75% 7 8 9 10 11 12 13 14 b) Outstanding balance on the mortgage $338,543.94 Refinance: at new rate but with remaining period New monthly payments $2,464.96 15 16 17 New Mortgage at new rate with 25 years term 00 d) Monthly payments for new mortgage $2,339.04 19 20 21

Cell reference -

A B с 1 2. 3 Mortgage Amount Term in years Monthly payments 3 4 5 350000 25 3118.98 4 6 a) Nominal annual rate compounded monthly (APR) =12*RATE(C3*12,-C4,C2,0) 7 oc After 3 years: APR 9 0.0675 b) Outstanding balance on the mortgage =PV(C6/12,(C3-3)*12,-C4,,0) 9 10 11 12 13 14 15 16 17 18 Refinance: at new rate but with remaining period c) New monthly payments =PMT(C9/12,(C3-3)*12,-C11,0) New Mortgage at new rate with 25 years term d) Monthly payments for new mortgage =PMT(09/12,25*12,-C11,,0) 19 20 21 22

Hope it will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.