Question

You plan to purchase a $190,000 house using a 15-year mortgage obtained from your local credit union. The mortgage rate offered to you is 6.75 percent. You will make a down payment of 20 percent of the purchase price.

a. Calculate your monthly payments on this mortgage.
b. Construct the amortization schedule for the first six payments.

You plan to purchase a $270,000 house using either a 30-year mortgage obtained from your local savings bank with a rate of 7.45 percent, or a 15-year mortgage with a rate of 6.60 percent. You will make a down payment of 25 percent of the purchase price.

a. Calculate the amount of interest and, separately, principal paid on each mortgage. What is the difference in interest paid?
b. Calculate your monthly payments on the two mortgages. What is the difference in the monthly payment on the two mortgages?

Answer 1

Answer to Question 1:  

Purchase price=$190,000

Down payment is given at 20% of purchase price. Hence Loan amount= Price*(1-Down payment rate)

=$190,000*(1-0.2) = $152,000

(a) Monthly payment on this mortgage for 15 years at 6.75% interest is $1,345.06 computed as follows:

F A B C D 1 Constant Payment Mortgage or Equated Monthly Installments (EMI) Payments at the end of each period 3 EMI is calculated using the formula EMI=[P*r*(1+r)^n]/[(1+r)^n)-1 4 Where P= Principal (loan amount), 5 n= Number of installments. ie if N=number of years, n=N*12 6 r= Rate of interest per month in decimals ie. If R= Yearly rate, r=R/(100*12) All amounts in $ 8 Given that 9 Principal (loan amount) (P)= 152000 No. of years (N)= 10 Rate of interest (% p.a)=R= 6.75 No. of months (n)= 15 180 12 Calculations: 13 14 Interest rate per month in decimals (r)= 15 No. of months (n)= Formula Cell reference R/(100*12) D10/(100*12) N*12 G9*12 (1+r)= 1+G14 (1+r)^n= G16G10 [(1+r)^n]-1= G17-1 P*r*(1+r)^n= D9*G14*G17 [P*r*(1+r)^n]/[(1+r)^n]-1 G19/618 Rounded to Value 0.005625 180 1.005625 2.744675842 1.744675842 2346.697845 1345.062383 $1,345.06 20 Equated Monthly Insallments (EMI)=

(b) Amortization schedule for first six payments is given below:

M N O (6) Shedule of amortization Month Beginning Monthly EMI Interest Principal End balance interest component Component balance rate =Col (2)*(3) =Col (4)-(5) =Col (2)-(6) (1) (2) (3) (4) (5) (7) 1 152000 0.005625 1345.06 855 490.06 151509.94 2 151509.94 0.005625 1345.06 852.2433991 492.82 151017.12 3 151017.12 0.005625 1345.06 849.4712923 495.59 150521.53 4 150521.53 0.005625 1345.06 846.6835924 498.38 150023.15 5 150023.15 0.005625 1345.06 843.8802117 501.18 149521.97 6 149521.97 0.005625 1345.06 841.061062 504.00 149017.97

Question 2:

Purchase price=$270,000

Down payment Rate= 25%. Amount of loan= Price*(1-Down payment rate) = $270,000*(1-0.25) = $202,500

Option 1: 30 year loan @7.45% interest.

(a) Amount of principal repaid=$202,200. Amount of interest paid= $304,733.79

Relevant portion of amortization schedule as follows:

JK M N O U AWN Shedule of amortization Month Beginning Monthly EMI Interest Principal End balance interest component Component balance rate =Col (2)*(3) =Col (4)-(5) =Col (2)-(6) (1) (2) (3) (4) (5) (6) 1 202500 0.006208 1408.98 1257.1875 151.80 202348.20 2 202348.20 0.006208 1408.98 1256.245105 152.74 202195.47 (6) (7) 9 & & C 359 2791.94 0.006208 360 1400.29 0.006208 Total 1408.98 17.33328626 1408.98 8.693462582 507233.79 304733.7855 1391.65 1400.29 202500 1400.29 0.00

Option 2: 15 year loan @6.6% interest.

Amount of principal repaid=$202,200. Amount of interest paid= $117,025.85

Relevant portion of amortization schedule as follows:

м N Shedule of amortization Month Beginning Monthly EMI Interest Principal End balance interest component Component balance rate =Col (2)*(3) =Col (4)-(5) =Col (2)-(6) (1) (2) (3) (4) (5) (6) (7) 1 202500 0.0055 1775.14 1113.75 661.39 201838.61 2 201838.61 0.0055 1775.14 1110.112335 665.03 201173.58 178 5267.38 179 3521.21 180 1765.43 Total 0.0055 0.0055 0.0055 1775.14 28.97061065 1775.14 19.366659 1775.14 9.709885629 319525.85 117025.85 1746.17 1755.78 1765.43 202500.00 3521.21 1765.43 0.00 187 188

Difference in interest paid= 30 year loan will have interest higher by ($304,733.79-$117,025.85)= $187,707.94

(b) Monthly payments of 30 year loan is $1408.98 computed as follows:

2 2A в со 1 Constant Payment Mortgage or Equated Monthly Installments (EMI) Payments at the end of each period 3 EMI is calculated using the formula EMI=[P*r*(1+r)^n]/[(1+r)^n)-1 4 Where P= Principal (loan amount), 5 n= Number of installments. ie if N=number of years, n=N*12 6 r= Rate of interest per month in decimals ie. If R= Yearly rate, r=R/(100*12) 7 All amounts in $ 8 Given that 9 Principal (loan amount) (P)= L 202500 No. of years (N)= 10 Rate of interest (% p.a)=R= L 7.45 No. of months(n)= 360 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per month in decimals (r )= R/(100*12) D10/(100*12) 0.006208333 15 No. of months (n)= N*12 G9*12 360 16 (1+r)= 1+G14 1.006208333 17 (1+r)^n= G16^G10 9.282127429 [(1+r)^n)-1= G17-1 8.282127429 P*r*(1+r)^n= D9*G14*G17 11669.37458 20 Equated Monthly Insallments (EMI)= [P*r*(1+r)^n]/[(1+r)^n]-1 G19/618 1408.982737 Rounded to | $1,408.98

Monthly payments of 15 year loan is $ 1775.14 computed as follows:

B C D 1 Constant Payment Mortgage or Equated Monthly Installments (EMI) 2 Payments at the end of each period 3 EMI is calculated using the formula EMI=[P*r*(1+r)^n)/((1+r)^n)-1 4 Where P= Principal (loan amount), 5 n= Number of installments. ie if N= number of years, n=N*12 6 r=Rate of interest per month in decimals ie. If R= Yearly rate, r=R/(100*12) All amounts in $ Given that 9 Principal (loan amount) (P)= 202500 No. of years (N)= Rate of interest (% p.a)=R= 6.6 No. of months(n)= 7 15 12 Calculations: 13 14 Interest rate per month in decimals (r ) = 15 No. of months(n)= Formula Cell reference R/(100*12) D10/(100*12) N*12 G9*12 (1+r)= 1+G14 (1+r)^n= G16^G10 [(1+r)^n)-1= G17-1 P*r*(1+r)^n= D9*G14*G17 [P*r*(1+r)^n]/[(1+r)^n]-1 G19/618 Rounded to Value 0.0055 180 1.0055 2.683944233 1.683944233 2989.24289 1775.143636 | $1,775.14 20 Equated Monthly Insallments (EMI)= 22

(b) Difference in monthly payment: Monthly payment on 15 year loan is higher by (1,775.14-1408.98)= $366.16