Question

With interest rates near an all-time low, a family decides to purchase their dream home. The house will cost $400,000. The family will pay 20% as a down payment, and finance the remaining balance with a 15-year fixed rate mortgage. The mortgage will call for monthly payments at a 4.50% APR. How much interest is paid on the loan in its first two years?

Answer 1

Monthly Interest rate, RATE # of total monthly payments, NPER Loan amount, PV Interest paid from, Start_Period Interest paid till, End_Period Equal monthly installment type, TYPE Interest paid during first 2 years 0.375% [Monthly interest rate = 4.5% / 12 = 0.375%] 180 [15 years x 12 --> As there will be 12 monthly payments per year] $320,000 [$400,000 x 80% as down payment made 20% on home cost] 1 [As we calculate interest paid from first installment] 24 [As we calculate interest paid till 24th installment (2 years)] 0 = 0 if paid at the end of each month; 1 if paid at the beginning of each month $27,472.11 NOTE: Interest paid during first 2 years is calculated using EXCEL FUNCTION CUMIPMT(rate,nper,pv,start period, end period, type) where rate=0.375%; nper = 180; pv=320000; start_period=1; end_period=24; type=0. Here, type = 0 as installments paid at the end of each month only. А B С D E 1 Monthly Interest rate, RATE =4.5%/12 [Monthly interest rate = 4.5% / 12 = 0.375%] 2 # of total monthly payments, NPER =15*12 (15 years x 12 --> As there will be 12 monthly payments per year] 3 Loan amount, PV =400000*80% ($400,000 x 80% as down payment made 20% on home cost] 4 Interest paid from, Start_Period 1 [As we calculate interest paid from first installment] 5 Interest paid till, End_Period 24 [As we calculate interest paid till 24th installment (2 years)] 6 Equal monthly installment type, TYPE 0 = 0 if paid at the end of each month; 1 if paid at the beginning of each month 7 Interest paid during first 2 years --CUMIPMT(B1,B2,B3,B4,B5,B6) 8