Question

You plan to buy a $220000 home with a 10% down payment. The bank you want to finance the purchase suggests two options (use semiannual compounding): Option 1: 20-year mortgage at 8.66% APR Option 2: 30-year mortgage at 11.31% APR. What is the equivalent monthly interest rate for each option? Equivalent monthly interest rate Option 1: Equivalent monthly interest rate Option 2: What is the monthly payment of each option? Monthly payment Option 1: $ Monthly payment Option 2: $

please answer all parts

Answer 1

Equivalent monthly interest rate = Effective annual interest rate (EAR) / Number of months in a year Here, When compounded semiannually, effective annual interest rate = (1 + Annual interest rate/2)-1 Therefore, Option-1 Option-2 Annual interest rate 8.66% 11.31% Number of semiannuals in a year 1 2 2 Effective annual interest rate 8.8475% 11.6298% [((1+8.66%/2)^2) - 1 = ((1+4.33%)^2) - 1 = 8.8475%; ((1+11.31%/2)^2) - 1 = ((1+5.66%)^2) - 1 = 11.6298%] Number of months in a year 12 12 Equivalent monthly interest rate 0.7373% 0.9691% [8.8475% / 12 = 0.7373%; 11.6298% / 12 = 0.9691%] or = 0.74% 0.97% [If rounded off 2 decimal places] Monthly Interest rate, RATE # of monthly installments required, NPER Initial loan amount, PV Balance in loan at the end, FV Monthly installment type, TYPE Equal monthly payment required Option-1 0.7373% 240 $198,000 $0 Option-2 0.9691% [As calculated above) 360 [For Optio-1: 20 years x 12; For Optio-2: 30 years x 12 --> There are 12 months in a year] $198,000 ($220,000 x (1-10%) = $220,000 x (1 - 0.10) = $220,000 x 0.90 = $198,000] $0 [Because all will be paid off over loan's tenure] 0 = 0 if installments paid at the end of each month; 1 if installments paid at the beginning of each month $1,980.41 [Ref: NOTE] $1,762.08 or = $1,762 $1,980 [lf rounded off to zero decimal places]

NOTE: For Option-1: Equal monthly payment required is calculated using EXCEL FUNCTION PMT(rate,nper,pv,fv,type) where rate=0.7217%; nper=240; pv=-220000; fv=0; type=0; pv is negative as it denotes cash inflow; type=0 as installments are paid at the end of each month only. For Option-2: Equal monthly payment required is calculated using EXCEL FUNCTION PMT(rate,nper,pv, fv,type) where rate=0.9425%;nper=360; pv=-220000; fv=0; type=0; pv is negative as it denotes cash inflow; type=0 as installments are paid at the end of each month only. A 2 Monthly Interest rate, RATE 3 # of monthly installments required, NPER 4 Initial loan amount, PV 5 Balance in loan at the end, FV Monthly installment type, TYPE 7 Equal monthly payment required B с Option-1 Option-2 0.7373% 0.9691% [As calculated above] =20*12 =30*12 [For Optio-1: 20 years x 12; For Optio-2: 30 years x 12 --> There are 12 months in a year] =220000*(1-10%) =220000*(1-10%) ($220,000 X (1-10%) = $220,000 x (1 -0.10) = $220,000 x 0.90 = $198,000] $0 $0 [Because all will be paid off over loan's tenure) 0 = 0 if installments paid at the end of each month; 1 if installments paid at the beginning of each month =PMT(B2,B3,-B4,B5,B6) EPMT(C2,C3,-C4,C5,C6) [Ref: NOTE] 8