Question

3. [Loan Amortization] You have just obtained a $300,000 mortgage loan from the Chase bank toward the purchase of a home at 6% APR. The amortization schedule of your mortgage is set in the monthly payments for the next 30 years.

A) What is the monthly loan payment?  

B) What is the balance of the loan after 20 years of loan payments?        

C) From the previous mortgage loan question, what will be the principal and the total interest that you pay during the first 10 years of the loan payments?

Answer 1

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

A В Mortgage Loan Amount Term(years) APR Monthly interest rate No. of monthly payment $300,000.00 30 6% 0.50% 360 5 6 8 a. Monthly loan payment $1,798.65 Loan balance after 20 years $162,010.76 10 b. 11 12 c. 13 14 Principal paid in first 10 years Interest paid in first 10 years $48,942.83 $166,895.36

Cell reference -

A Mortgage Loan Amount Term(years) APR Monthly interest rate No. of monthly payment 300000 30 0.06 =C4/12 =C3*12 8 a. Monthly loan payment =PMT(C5,C6,-C2,0) 9 10 b. Loan balance after 20 years =C2+CUMPRINC(C5, C6, C2,1,240,0) 11 12 c. 13 Principal paid in first 10 years Interest paid in first 10 years =-CUMPRINC(C5,C6, C2,1,120,0) =-CUMIPMT(C5,C6,C2,1,120,0) | 15 16

Please note : Solution for Part-C provided based on information in question because No data for previous mortgage question given above.

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