Question

Travis purchased a house for $325,000. He made a down payment of 20.00% of the value of the house and received a mortgage for the rest of the amount at 3.42% compounded semi-annually amortized over 15 years. The interest rate was fixed for a 6 year period.

Calculate the monthly payment amount.

Calculate the principal balance at the end of the 6 year term.

Calculate the monthly payment amount if the mortgage was renewed for another 6 years at 4.02% compounded semi-annually?

Answer 1

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

Purchase Price of House Down Payment Loan amount Term in years Term in months $325,000.00 20% $260,000.00 180 For, first 6 year term: APR, compounded semi-annually Monthly interest rate Monthly payment amount 3.42% 0.2830% $1,845.43 11 a. 12 13 b. 14 15 Principal balance after 6 year term $171,521.58 16 17 For, next 6 year APR, compounded semi-annually Monthly interest rate Remaining monthly payments Monthly payment amount 18 19 c. 20 4.02% 0.3322% 108 $1,892.70 21

Cell reference -

B Purchase Price of House Down Payment Loan amount Term in years Term in months 325000 0.2 =C2*(1-C3) 15 =C5*12 6 8 9 10 11 a. 12 13 b. For, first 6 year term: APR, compounded semi-annually Monthly interest rate Monthly payment amount 0.0342 =(1+C9/2)^(1/6)-1 =PMT(C10, C6,-C4,,0) Principal balance after 6 year term =PV(C10,(15-6)*12,-C11,,0) 15 17 For, next 6 year APR, compounded semi-annually Monthly interest rate Remaining monthly payments Monthly payment amount 0.0402 =(1+C16/2)^(1/6)-1 1=(15-6)*12 =PMTC17,018,-C13,,0) 19 c. 20 22

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