Question

Problem 3 Barb and John Walton took out a $ 110,000 30 year loan for their home exactly three years ago. Their interest rate on the 30 year loan is 5.00% fixed rate. The Walton's loan payment is S 590.50 which includes principal and interest. Their remaining balance on their loan is $ 104,879. They are considering refinancing their original 30 year loan. Liberty National Bank is currently offering a 15 year 3% fixed loan for mortgage refinancing. First, determine what the Walton's new payment would be if the closing costs to refinance are $ 2,000 and would be included in the new loan. Next, compare the exact costs of continuing with the original 30 year loan with the total costs of the new loan. Should they refinance and exactly what would be the additional savings or costs?

Answer 1

Answer:

Given below is the excel sheet template used for calculating the loan repayment schedule.

The function used were - PMT, PPMT, IPMT

The costs of continuing with the current loan are high as compared to that of the new loan.

Although the new loan with a 15-year term and a 3% interest rate will have a high EMI of $738.09, it will be overall less costly as compared to the current loan with a remaining period of 27 years and EMI of $590.50.

The current loan should be refinanced as it results in an additional saving of:

EMI - $58,467.61

Interest Paid - $ 60,468.66

**The Loan amortization table for the current loan starts from 2019(current year, assumed). The month column starts with 37, as 36 months have already passed.

А MN $ 1,10,000.00 30 360 $ 1,06,879 $ 1,04,879 $ 2,000 15 180 H NEW LOAN Loan Amount Existing Loan Closing Amount Time Period (Years) Time Period (Months) Interest Rate (p.a.) Interest Rate (monthly) New EMI Principal (for 1st EMI) Interest (for 1st EMI) Loan Total EMI Paid Total Interest Paid Current (2019 onwards) $ 1,91,323.23 $ 86,445.27 New $ 1,32,855.62 $ 25,976.62 Savings/(Costs) $ 58,467.61 $ 60,468.66 5% 1 OLD LOAN 2 Loan Amount 3 Time Period (Years) 4 Time Period (Months) 5 Interest Rate (p.a.) 6 Interest Rate (monthly) 7 EMI 8 Principal (for 1st EMI) 9 Interest (for 1st EMI) 10 11 *Assuming Current Year 12 Loan Period starts at 13 Loan Period ends at $ 3% $ $ 0.42% 590.50 PMT(B5,B3,-B1,0,0) 132.17 PPMT(B5,B13,B3,$B$1,0,0) 458.33 IPMT(B5,B13,B3,-$F$12,0,0) $ $ $ 0.25% 738.09 PMT(18,16,-12,0,0) 470.89 PPMT(18,117,16, $M$ 16,0,0) 267.20 IPMT(18,117,16,-$M$ 16,0,0) 2019 2016 2045

C D E J K L M B 50 Loan Repayment Schedule 51 Year Month 52 I Loan Repayment Schedule Year Month E MI Principal Interest E MI 36 37 $ 38 $ 39 $ 40 $ 2019 2019 45 $ 46 $ 47 $ 48 $ Principal Interest Loan Outstanding $ 1,04,877.95 590.50 $ 153.51 $ 436.99$ 1,04,724.44 590.50 $ 154.15 $ 436.35 $ 1,04,570.29 590.50 $ 154.79$ 435.71 $ 1,04,415.50 590.50 $ 155.44 $ 435.06 $ 1,04,260.06 590.50 $ 156.09 434.42 $ 1,04,103.97 590.50 $ 156.74 $ 433.77 $ 1,03,947.23 590.50 $ 157.39 $ 433.11 $ 1,03,789.84 590.50 $ 158.05 $ 432.46 $ 1,03,631.80 590.50 $ 158.70 $ 431.80 $ 1,03,473.09 590.50 $ 159.37 $ 431.14 $ 1,03,313.73 590.50 $ 160.03 $ 430.47 $ 1,03,153.70 590.50 $ 160.70 $ 429.81 $ 1,02,993.00 590.50 $ 161.37 $ 429.14 $ 1,02,831.63 590.50 $ 162.04 $ 428.47 $ 1,02,669.59 590.50 $ 162.71 $ 427.79$ 1,02,506.88 590.50 $ 163.39 $ 427.11 $ 1,02,343.49 590.50 $ 164.07$ 426.43 $ 1,02,179.42 590.50 $ 164.76 $ 425.75 $ 1,02,014.66 590.50 $ 165.44 $ 425.06 $ 1,01,849.22 590.50 $ 166.13 $ 424.37$ 1,01,683.08 590.50 $ 166.82 $ 423.68 $ 1,01,516.26 590.50 $ 167.52 $ 422.98 $ 1,01,348.74 590.50 $ 168.22 $ 422.29 $ 1,01,180.52 590.50 $ 168.92 $ 421.59 $ 1,01,011.61 1 $ 2 $ 3 $ 4 $ 5 $ 6 $ 7 $ 8 $ 9 $ 10 $ 11 $ 12 $ 13 $ 14 $ 15 $ 16 $ 17 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ 738.09 $ WAITIT 470.89 $ 472.07 $ 473.25 $ 474.43 $ 475.62 $ 476.80 $ 478.00 $ 479.19 $ 480.39 $ 481.59 $ 482.79 $ 484.00 $ 485.21 $ 486.42 $ 487.64 $ 488.86 $ 490.08 $ 491.31 $ 492.54 $ 493.77 $ 495.00 $ 496.24 $ 497.48$ 498.72 $ Loan Outstanding $ 1,06,879.00 267.20 $ 1,06,408.11 266.02 $ 1,05,936.04 264.84 $ 1,05,462.80 263.66 $ 1,04,988.37 262.47 $ 1,04,512.75 261.28 $ 1,04,035.95 260.09 $ 1,03,557.95 258.89 $ 1,03,078.76 257.70 $ 1,02,598.37 256.50 $ 1,02,116.78 255.29 $ 1,01,633.98 254.08 $ 1,01,149.98 252.87 $ 1,00,664.77 251.66 $ 1,00,178.34 250.45 $ 99,690.70 249.23 $ 99,201.84 248.00 $ 98,711.76 246.78 $ 98,220.45 245.55$ 97,727.92 244.32 $ 97,234.15 243.09 $ 96,739.15 241.85$ 96,242.91 240.61 $ 95,745.43 239.36 $ 95,246.71 50 $ 51 $ 52 $ 53 $ 2020 2020 56 $ 57 $ 58 $ 59 $ 19 $ 20 $ 21 $ 22 $ 23 $ 24 $ 50

*Entire Repayment tables are not shown due to their length.

DE 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 Current Loan Repayment (Last Two Years) 337 $ 590.50 $ 534.42 $ 338 $ 590.50 $ 536.65 $ 339 $ 590.50 $ 538.88 $ 340 $ 590.50 $ 541.13 $ 341 $ 590.50 $ 543.38 $ 342 $ 590.50 $ 545.65 $ 2044 343 $ 590.50 $ 547.92 $ 344 $ 590.50 $ 550.20 $ 345 $ 590.50 $ 552.50 $ 346 $ 590.50 $ 554.80 $ 347 $ 590.50 $ 557.11 $ 348 $ 590.50 $ 559.43 $ 349 $ 590.50 $ 561.76 $ 350 $ 590.50 $ 564.10 $ 351 $ 590.50 $ 566.45 $ 352 $ 590.50 $ 568.81 $ 353 $ 590.50 $ 571.18 $ 354 $ 590.50 $ 573.56 $ 2045 355 $ 590.50 $ 575.95 $ 356 $ 590.50 $ 578.35 $ 357 $ 590.50 $ 580.76 $ 358 $ 590.50 $ 583.18 $ 359 $ 590.50 $ 585.61 $ 360 $ 590.50 $ 588.05 $ Total $1,85,155.14 $1,04,999.66 $ Total (From 2019 - 2045) $ 1,85,155.14 $1,04,999.66 $ 56.08 $ 12,925.46 53.86 $ 12,388.81 51.62 $ 11,849.93 49.37 $ 11,308.80 47.12 $ 10,765.42 44.86 $ 10,219.77 42.58 $ 9,671.85 40.30 $ 9,121.64 38.01 $ 8,569.15 35.70 $ 8,014.35 33.39 $ 7,457.24 31.07 $ 6,897.81 28.74 $ 6,336.04 26.40 $ 5,771.94 24.05 $ 5,205.49 21.69 $ 4,636.67 19.32 $ 4,065.49 16.94 $ 3,491.92 14.55 $ 2,915.97 12.15 $ 2,337.61 9.74 $ 1,756.85 7.32 $ 1,173.67 4.89 $ 588.05 2.45 $ 0.00 98,604.89 98,604.89 368 369 370 371 372 373 374 375 376 377 378 379 380

New Loan Repayment Schedule (Last Two Years) Year Month EMI Principal 157 $ 738.09 $ 158 $ 738.09 $ 159 $ 738.09 $ 160 $ 738.09 $ 161 $ 738.09 $ 162 $ 738.09 $ 2032 163 $ 738.09 $ 164 $ 738.09 $ 165 $ 738.09 $ 166 $ 738.09 $ 167 $ 738.09 $ 168 $ 738.09 $ 169 $ 738.09 $ 170 $ 738.09 $ 738.09 $ 172 $ 738.09 $ 173 $ 738.09 $ 174 $ 738.09 $ 2033 175 $ 738.09 $ 176 $ 738.09 $ 177 $ 738.09 $ 178 $ 738.09 $ 179 $ 738.09 $ 180 $ 738.09 $ Total $ 1,18,648.53 $ Interest Loan Outstanding 695.16 $ 42.93 $ 16,477.16 696.89 $ 41.19 $ 15,780.26 698.64 $ 39.45 $ 15,081.63 700.38 $ 37.70 $ 14,381.24 702.13 $ 35.95 $ 13,679.11 703.89 $ 34.20 $ 12,975.22 705.65 $ 32.44 $ 12,269.57 707.41 $ 30.67 $ 11,562.16 709.18 $ 28.91 $ 10,852.98 710.95 $ 27.13 $ 10,142.02 712.73 $ 25.36 $ 9,429.29 714.51 $ 23.57 $ 8,714.78 716.30 $ 21.79 $ 7,998.48 718.09 $ 20.00 $ 7,280.39 719.89 $ 18.20 $ 6,560.50 721.69 $ 16.40 $ 5,838.82 723.49 $ 14.60 $ 5,115.33 725.30 $ 12.79 $ 4,390.03 727.11$ 10.98 $ 3,662.92 728.93 $ 9.16 $ 2,933.99 730.75 $ 7.33 $ 2,203.23 732.58 $ 5.51 $ 1,470.66 734.41 $ 3.68 $ 736.25 736.25 $ 1.84 $ 0.00 1,05,206.51 $ 40,403.92