Question

You have just purchased a house and have obtained a 15-year, $200,000 mortgage with an interest rate of 10 percent. Use Excel and show all work—use formulas where useful—do not just key in answers. Assume annual payments and use tables provided. Required: What is your annual payment? Assuming you bought the house on January 1, what is the principal balance after one year? After 10 years? After four years, mortgage rates drop to 8 percent for 15-year fixed-rate mortgages. Assume you refinance for 8 percent with 15-year fixed-rate mortgage (yes, 15 years). You still have the old 10 percent mortgage you signed four years ago and you plan to live in the house for another five years. The total cost to refinance the mortgage is $3,000 including legal fees, closing costs, and points. The rate on a five-year CD is 3 percent. Should you refinance your mortgage or invest the $3,000 in a CD? The 3 percent CD rate is your opportunity cost of capital. Compare the present value of the savings on the payments to the future value of the CD for the 5 years that you plan to live in the house.

It can be done in tables or any format and that should. Thank you

Answer 1

Annual Payment = USD 26,295

Principal balance after 1 year = USD 193,705

Principal balance after 10 years = USD 99,678

Refer following link for the excel solution.

Mortgage and Refinance.xlsx - Excel - Sign in 0 X & Share Data Review View Power Pivot Tell me what you want to do... =, === Wrap Text Σ AutoSum ' Α General 9 Fill - SSY Jabe = File Home Insert Page Layout Formulas Cut Arial 11 - A A E Copy - Paste B I U O - - A Format Painter Clipboard Font A1 x V fic A B C D E Merge & Center - $ % 48.08 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clear Sort & Find & Filter Select Editing Alignment Number G Cells K L M N O P QA Year Loan Amount 200,000 Period (Yrs) 15 Interest Rate 10.00% EMI 26,295 F G H I J Principal Opening (A) Interest (B) (D = C-B) Installment (C) Closing (A-D) 1 200,000 20,000 6,295 26,295 193,705 2 193,705 19,371 6,924 26,295 186,781 186,781 18,678 7,617 26,295 179,164 179,164 17,916 8,378 26,295 170,786 5 170,786 17,079 9,216 26,295 161,570 161,570 16,157 10,138 26,295 151,432 151,432 15,143 11,152 26,295 140,281 140,281 14,028 12,267 26,295 128,014 128,014 12,801 13,493 26,295 114,521 114,521 11,452 14,843 26,295 99,678 99,678 9,968 16,327 26,295 83,351 12 83,351 8,335 17,960 26,295 65,391 65,391 6,539 19,756 26,295 45,636 45,636 4,564 21,731 26,295 23,904 23,904 2,390 23,904 26,295 OOOO 11 13 Repayment Schedule Refinancing → Ready @ J -— -_ + 100% A 3 P * 7:16 PM 27/03/2020 E 10

Refer following link for the formulas used in excel:

Mortgage and Refinance.xlsx - Excel 0 - Sign in 0 x & Share Formulas Data Review View Power Pivot Tell me what you want to do... 11 - A A === Wrap Text AutoSum - Ay General O BSSY Jabe = File Home Insert Page Layout Cut Arial ES Copy - Paste B I Format Painter U Clipboard A1 x fc , A, E Merge & Center, $ % , 8.09 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Editing Font Alignment Number Cells ДА В C D JK Loan Amount 200000 Period (Yrs) 15 Interest Rate 0.1 EMI E-PMT(C4,C3, C2) E F G H Principal Year Opening (A) Interest (B) (D = C-B) =C2 =F2*$C$4 =12-G2 =E2+1 =J2 =F3*$C$4 =13-G3 =E3+1 =J3 =F4*$C$4 =14-G4 =E4+1 =J4 =F5*$C$4 =15-G5 =E5+1 =J5 =F6*$C$4 =16-G6 =E6+1 =J6 =F7*$C$4 =17-G7 =E7+1 =J7 =F8*$C$4 =18-G8 =E8+1 =J8 =F9*$C$4 =19-G9 =E9+1 = J9 =F10*$C$4 =110-G10 =E10+1 =J10 =F11*$C$4 =111-G11 =E11+1 =J11 =F12*$C$4 =112-G12 =E12+1 =J12 =F13*$C$4 =113-G13 =E13+1 =J13 =F14*$C$4 =114-G14 =E14+1 =J14 F15*$C$4 =I15-G15 =E15+1 =J15 =F16*$C$4 =116-G16 Installment (C) Closing (A-D) =$C$5 =F2-H2 =$C$5 =F3-H3 =$C$5 =F4-H4 =$C$5 =F5-H5 =$C$5 =F6-H6 =$C$5 =F7-H7 =$C$5 =F8-H8 =$C$5 =F9-H9 =$C$5 =F10-H10 =$C$5 =F11-H11 =$C$5 =F12-H12 =$C$5 =F13-H13 =$C$5 =F14-H14 =$C$5 F15-H15 =$C$5 =F16-H16 Repayment Schedule Refinancing Ready @ J -- + 3:53 PM = 9 4x W* 28/03/20208 03/2020 A

The Present Value of the savings from the refinancing option is USD 5,100. The Future Value of the CD for the 5 year is USD 3,478. However, the correct comparison is comparing the Present Value of the savings at the end of Year 4 (i.e USD 5,100) with the cost of refinancing at the end of Year 4 (i.e USD 3,000), resulting in USD 2,100 of savings. Hence, it is better to refinance.

Refer following for the excel workings:

AS Y . ab = Mortgage and Refinance.xlsx - Excel 0 0 - Sign in X Share File Home Insert Page Layout Formulas O Ruler / Formula Bar Data Review View Power Pivot Tell me what you want to do... Split View Side by Side Hide El Synchronous Scrolling Zoom 100% Zoom to New Arrange Freeze Selection Window All Panes Unhide E Reset Window Position Zoom Window Q E Q 2 = Split → Gridlines Headings Normal Page Break Page Custom Preview Layout Views Workbook Views Switch Wind Macros Show Macros A1 - X for E F G H I J K L M N O P Q R S B C D Loan Amount 200,000 Period (Yrs) 15 Interest Rate 10.00% EMI 26,295 Revised Period (Yrs) 15 Revised Interest Rate 8.00% Revised EMI 19,953 Year Opening (A) Interest (B) From Year 5 onwards Installment Principal (D = C-B) Closing (A-D) Savings (C) Discounting Factor at 3% PV of Savings FV of the CD 1 2 6,295 6,157 5.978 200,000 193,705 186,781 179,164 170,786 164,496 157,703 150,366 142,443 133,885 124,643 114,662 103,882 92,240 79,666 66,086 51,420 35,581 3,000 End of Year 4.ie beginning of year 5 3,090 3,183 3,278 3,377 3,478 Future value of the CD at FX 20,000 19,371 18,678 17,916 13,663 13,160 12,616 12,029 11,395 10,711 9,971 9,173 8,311 7,379 6,373 5,287 4,114 2,846 1,478 10 11 12 13 14 15 16 17 18 6,924 7,617 8,378 6,290 6,793 7,337 7,924 8,557 9,242 9,981 10,780 11,642 12.574 13,580 14,666 15,839 17,106 18,475 Original Installment 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 26,295 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 19,953 193,705 186,781 179,164 170,786 164,496 157,703 150,366 142,443 133,885 124,643 114,662 103,882 92,240 79,666 66,086 51,420 35,581 18,475 0 6,342 6,342 6,342 6,342 6,342 6,342 6,342 6,342 6,342 6,342 6,342 (19,953) (19,953) (19,953) (19,953) (10,051) 0.9709 0.9426 0.9151 0.8885 0.8626 0.8375 0.8131 0.7894 0.7664 0.7441 0.7224 0.7014 0.6810 0.6611 0.6419 5,804 5,635 5,471 5,311 5,157 5,006 4,861 4,719 4,582 (13,995) (13,587) (13,191) (12,807) 5,100 28 TOTAL Repayment Schedule Refinancing + Ready @ J -— H + 85% 7:43 PM 3 E 2 x 27/03/2020 10

Refer following screenshot for the excel formulas.

Mortgage and Refinance.xlsx - Excel - Sign in 0 X & Share Data Review View Power Pivot Tell me what you want to do... BSSY Jabe = File Home Insert Page Layout Formulas Cut Arial 11 - A A ES Copy - Paste B I U O - - A Format Painter Clipboard === Wrap Text AutoSum - Ay General O Fill - Merge & Center - $ % 48.08 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clear Sort & Find & Filter Select Editing Font Alignment Number Cells F G H I J K L M L Original Installment Savings FV of the CD r9 B1 - X for E Loan Amount 200000 Period (Yrs) 15 Interest Rate 0.1 EMI --PMT(D3,D2,D1) Revised Period (Yrs) 15 Revised Interest Rate 0.08 From Year 5 onwards Revised EMI --PMT(D6,D5, G12) Principal Year Opening (A) Interest (B) 8 (D = C-B) 91 =D1 =C9*$D$3 =F9-D9 10 =B9+1 =G9 =C10*$D$3 =F10-D10 11 =B10+1 =G10 =C11*$D$3 =F11-D11 12 =B11+1 =G11 =C12*$D$3 =F12-D12 13 =B12+1 =G12 =C13*$D$6 =F13-D13 14 =B13+1 =G13 =C14*$D$6 =F14-D14 15 =B14+1 =G14 =C15*$D$6 =F15-D15 16 =B15+1 =G15 =C16*$D$6 =F16-D16 17 =B16+1 =G16 =C17*$D$6 =F17-D17 18 =B17+1 =G17 =C18*$D$6 =F18-D18 19 =B18+1 =G18 =C19*$D$6 =F19-D19 20 =B19+1 =G19 =C20*$D$6 =F20-D20 21 =B20+1 =G20 =C21*$D$6 =F21-D21 22 =B21+1 =G21 =C22*$D$6 =F22-D22 23 =B22+1 =G22 =C23*$D$6 =F23-D23 24 =B23+1 =G23 =C24*$D$6 =F24-D24 25 =B24+1 =G24 =C25*$D$6 =F25-D25 26 =B25+1 =G25 =C26*$D$6 =F26-D26 27 =B26+1 =G26 =C27*$D$6 =F27-D27 28 Repayment Schedule Refinancing + Installment Closing (A (C) D) =$D$4 =C9-E9 =$D$4 =C10-E10 =$D$4 =C11-E11 =$D$4 =C12-E12 =$D$7 =C13-E13 =$D$7 =C14-E14 =$D$7 =C15-E15 =$D$7 =C16-E16 =$D$7 =C17-E17 =$D$7 =C18-E18 =$D$7 =C19-E19 =$D$7 =C20-E20 =$D$7 =C21-E21 =$D$7 =C22-E22 =$D$7 =C23-E23 =$D$7 =C24-E24 =$D$7 =C25-E25 =$D$7 =C26-E26 =$D$7 =C27-E27 3000 End of Year 4. Le beginning of year 5 =M12*1.03 =M13*1.03 =M14*1.03 M15*1.03 =M16*1.03 Future value of the CO at 37 ='Repayment Schedule"!2 0 ="Repayment Schedule"!13 0 ='Repayment Schedule"!14 0 ="Repayment Schedule!150 ="Repayment Schedule"!16 =113-F13 ='Repayment Schedule"!17 =114-F14 ="Repayment Schedule"!18 =115-F15 ='Repayment Schedule"!19 =116-F16 ='Repayment Schedule! 10 =117-F17 ='Repayment Schedule!11 =118-F18 ="Repayment Schedule"!12 =119-F19 ='Repayment Schedule"!113 =120-F20 ='Repayment Schedule! 14 =121-F21 ='Repayment Schedule!15 =122-F22 ='Repayment Schedule! 16 =123-F23 ='Repayment Schedule"!17 =124-F24 ='Repayment Schedule!18 =125-F25 ='Repayment Schedule!19 =126-F26 ='Repayment Schedule"!20 =127-F27 TOTAL =SUM(J9:J27) Discounting PV of Savings Factor at 3% = J9*K9 0 =J10*K10 0 =J11*K11 =J12*K12 = 1/(1.03) B9 =J13*K13 = 1/(1.037B10 = J14*K14 = 1/(1.03)"B11 =J15*K15 = 1/(1.037 B12 =J16*K16 =1/(1.03) B13 =J17*K17 =1/(1.03) B14 =J18*K18 =1/(1.03) B15 =J19*K19 =1/(1.03) B16 = J20*K20 =1/(1.03) B17 =J21*K21 =1/(1.03) B18 = J22*K22 =1/(1.03) B19 = J23*K23 =1/(1.03) B20 = J24*K24 = 1/(1.03Y"B21 =J25*K25 =1/(1.03Y"B22 =J26*K26 = 1/(1.03Y"B23 = J27*K27 =SUM(L9:L27) Ready @ J -- 3:55 PM A 28/03/20208