Question

without using financial formulas just basic math formulas are eligible. Please provide all formula used and variables with clear steps. Please use Goal Seek Tool in excel.

EXCEL Question 1 (Home Loan) (3marks] Construct an EXCEL spreadsheet, which projects cash flows of a home loan with yearly loan repayments, ie. the loan is paid by payments at the end of each year for n years, until the loan is completely paid off. Please use your spreadsheet to answer the following questions: (a) Given loan amount is $600,000, annual effective interest rate i is 11%, loan term is 20 years. Find the annual repayment amount. (b) Given annual effective interest rate i is 8%, loan term is 30 years. I can afford to make annual repayment amount of $72,000. How much money can I borrow? In your EXCEL spreadsheet, you are required to show the loan amount outstanding at the end of each year, after interest has been accrued and the annual repayment deducted.

Answer 1

AB C D F G H 1 Part (a) 600000 Goal Seek 3 Loan amount = 4 Yearly Payment = 1 5 interest rate = 11% Set cell: SFS27 To value: 0 By changing cell: $854 OK Year Cancel 0 Beginning Balance 600000 666000 739260 820578.6 910842.25 1011034.9 1122248.7 1245696.1 1382722.7 1534822.2 1703652.6 1891054.4 2099070.4 2329968.1 2586264.6 2870753.7 3186536.6 3537055.6 3926131.7 4358006.2 To To Payment Ending interest Principal Balance 0 66000 66000 666000 0 73260 -73260 739260 81318.6 -81318.6 820578.6 90263.65 -90263.6 910842.2 100192.6 -100193 1011035 0 111213.8 -111214 1122249 0 123447.4 -123447 1245696 0 137026.6 -137027 1382723 0 152099.5 -152099 1534822 168830.4 -168830 1703653 0 187401.8 -187402 1891054 208016 -208016 2099070 0 230897.7 -230898 2329968 256296.5 -256296 2586265 0 284489.1 -284489 2870754 0 315782.9 -315783 3186537 0 350519 -350519 3537056 0 389076.1 -389076 3926132 0 431874.5 -431874 4358006 479380.7 -479381 4837387 0 0

A D E F G H I 1 Part (a) Goal Seek Status ? X 3 Loan amount = 4 Yearly Payment = 5 interest rate = 600000 75345.382] 11% Goal Seeking with Cell F27 found a solution. Step Pause Year Target value: 0 Current value: 2.76486E-10 D OK 1 Cancel Beginning To To Ending Payment Balance interest Principal Balance 600000 75345.38 66000 9345.382 590654.6 590654.62 75345.38 64972.01 10373.37 580281.2 580281.24 75345.38 63830.94 11514.45 568766.8 568766.8 75345.38 62564.35 12781.03 555985.8 555985.76 75345.38 61158.43 14186.95 541798.8 541798.82 75345.38 59597.87 15747.51 526051.3 526051.3 75345.38 57865.64 17479.74 508571.6 508571.56 75345.38 55942.87 19402.51 489169.1 489169.05 75345.38 53808.6 21536.79 467632.3 467632.27 75345.38 51439.55 23905.83 443726.4 443726.44 75345.38 48809.91 26535.47 417191 417190.96 75345.38 45891.01 29454.38 387736.6 387736.59 75345.38 42651.02 32694.36 355042.2 355042.23 75345.38 39054.65 36290.74 318751.5 318751.49 75345.38 35062.66 40282.72 278468.8 278468.77 75345.38 30631.57 44713.82 233755 233754.96 75345.38 25713.05 49632.34 184122.6 184122.62 75345.38 20253.49 55091.89 129030.7 129030.73 75345.38 14193.38 61152 67878.72 67878.723 75345.38 7466.659 67878.72 2.76E-10

C D E 1 Part (a) 3 Loan amount = 600000 4 Yearly Payment = 75345.3821262853 5 interest rate = 0.11 6 Year 8 1 9 2 10 3 11 4 12. 136 14 7 15 8 16 9 Beginning Balance Payment to interest To Principal Ending Balance =$B$3 =$B$4 =B8*$B$5 =C8-D8 =B8-E8 =F8 =$B$4 =B9*$B$5 =C9-D9 =B9-E9 =F9 =B10*$B$5 =C10-D10 =B10-E10 =F10 =B11*$B$5 =C11-D11 =B11-E11 =F11 =B12*$B$5 =C12-D12 =B12-E12 =F12 =B13*$B$5 =C13-D13 =B13-E13 =F13 =B14*$B$5 =C14-D14 =B14-E14 =F14 =B15*$B$5 =C15-D15 =B15-E15 =F15 =B16*$B$5 =C16-D16 =B16-E16 =F16 =B17*$B$5 =C17-D17 =B17-E17 =F17 =B18*$B$5 =C18-D18 =B18-E18 =F18 =B19*$B$5 =C19-D19 =B19-E19 =F19 =B20*$B$5 =C20-D20 =B20-E20 =F20 =B21*$B$5 =C21-D21 =B21-E21 =F21 =B22*$B$5 =C22-D22 =B22-E22 =F22 =B23*$B$5 =C23-D23 =B23-E23 =F23 =B24*$B$5 =C24-D24 =B24-E24 =F24 =B25*$B$5 =C25-D25 =B25-E25 =F25 =B26*$B$5 =C26-D26 =B26-E26 =F26 =B27*$B$5 =C27-D27 =B27-E27 XXXXXXXXXXXXXXXX

А 1 Part (b) Goal Seek ? X 3 Loan amount = 4 Yearly Payment = 5 interest rate = 72000 8% Set cell: F37 To value: By changing cell: $B31 Year L OK Cancel NAMN 5556UFELGOWN Beginning Paym Ending To interest To Principal Balance ent Balance 0 72000 0 72000 -72000 -72000 72000 -5760 77760 -149760 -149760 72000 -11980.8 83980.8 -233740.8 -233740.8 72000 -18699.264 90699.264 -324440.064 -324440.064 72000 -25955.2051 97955.20512-422395.269 -422395.269 72000 -33791.6215 105791.6215 -528186.891 -528186.891 72000 -42254.9513 114254.9513 -642441.842 -642441.842 72000 -51395.3474 123395.34 74 -765837.189 -765837.189 72000 -61266.9751 133266.9751 -899104.164 -899104.164 12000 -/1928.3332 143928.3332 -1043032.5 -1043032.5 72000 -83442.5998 155442.5998 -11984/5.1 -11984/5.1 72000 -95878.0078 167878.0078 -1366353.11 -1366353.11 72000 -109308.248 181308.2484 -154/661.35 -154/661.35 72000 -123812.908 195812.9083 -17434/4.26 -1/43474.26 72000 -139477.941 211477.9409 -1954952.2 -1954952.2 72000 -156396.176 228396.1762 -2183348.38 -ZIO 540.0 7 ZUUU -174007.01 240007.0703 -24 5UUTU.ZJ - 24 UUTU.23 12UUU -13401. 200401. 2 3 - 2070417. -Z030417.JJ 7200U - ZIJT15.404 201713.4057 -2704130. -2704 IU.J 72UUU 20/0.47U SIU70.470 -2740U1.4) -234001.45 7200U -ZUSJO0.714 DJJOO.7144 -3U50420.54 -U50420.54 7200U - 270450.UZO U2450.UZT - LOOU. -3992886.37 72000 -319430.91 391430.9097 -4384317.28 -4384317.28 72000 - 350745.382 422745.3825 -4807062.66 -4807062.66 72000 -384565.013 456565.0131 -5263627.68 -5263627.68 72000 -421090.214 493090.2141 -5756717.89 -5756717.89 72000 -460537.431 532537.4313 -6289255.32 -6289255.32 72000 -503140.426 575140.4258 -6864395.75 -6864395.75 72000 -549151.66 621151.6598 -7485547.41 -7485547.41 72000 -598843.793 670843.7926 -8156391.2

A B C D H 1 Part (b) Goal Seek Status 3 Loan amount = 4 Yearly Payment = 5 interest rate = 810560.4007 72000 8% Step Pause Goal Seeking with Cell F37 found a solution. Target value: 0 Current value: 4.84579E-09 D OK Year Cancel 18 ADNAN NGUN 20656UBREEUU WN Beginning Paym To interest To Principal Ending Balance ent Balance 810560.4007 72000 64844.83206 7155.167944 803405.2328 803405.2328 72000 64272.41862 7727.581379 795677.6514 795677.6514 72000 63654.21211 8345.787889 787331.8635 787331.8635 72000 62986.54908 9013.450921 778318.4126 778318.4126 72000 62265.47301 9734.526994 768583.8856 768583.8856 72000 61486./1085 10513.28915 758070.5964 758070.5964 12000 60645.64771 11354.35229 746/16.2441 746/16.2441 72000 59737.29953 12262./004/ 734453.5437 734453.5437 72000 58756.28349 13243./1651 721209.8212 121209.8212 72000 57696.78617 14303.21383 706906.6133 706906.6133 72000 56552.52907 15447.47093 691459.1424 691459.1424 72000 55316.73139 16683.26861 674775.8738 6747/5.8738 72000 53982.0699 18017.9301 656757.9437 656757.9437 72000 52540.6355 19459.3645 637298.5792 637298.5792 72000 50983.88634 21016.11366 616282.4655 616282.4655 72000 49302.59724 22697.40276 593585.0628 JOJOJ.UUZO 720UU 47400.OUJUZ 2401S.17470 JUJU71.OUTO JUJU71.0070 7ZUUU 40023.74742 20474.LJUJO 342077.0172 J42077.0172 72UUU 45407.00750 2072.17UUZ J14000.4200 14U00.42UU TZUUU 41120.45415 DU07.JUJOT 40DIZJ.OUUT 403IZJ.OUUT / ZUUU SOUDU.UUOOU 9342.2114 44217.2270 447773.290 7ZUUU 2702.07437 DU017.720US 4157JO.UU 413758.0039 72000 33100.64032 38899.35968 374858.6443 374858.6443 72000 29988.69154 42011.30846 332847.3358 332847.3358 72000 26627.78686 45372.21314 287475.1227 287475.1227 72000 22998.00981 49001.99019 238473.1325 238473.1325 72000 19077.8506 52922.1494 185550.9831 185550.9831 72000 14844.07865 57155.92135 128395.0617 128395.0617 72000 10271.60494 61728.39506 66666.66667 66666.66667 72000 5333.333333 66666.66667 4.84579E-09 | Sheet1 37

1 Part (b) 3 Loan amount = 4 Yearly Payment = 5 interest rate = 810560.400705179 72000 0.08 Year Payment To interest To Principal Ending Balance 8 1 9 2 10 3 11 4 12 5 136 1417 158 Toy IT 10 18 11 19 12 20 13 21 14 22 15 Beginning Balance =$B$3 =F8 =F9 =F10 =F11 =F12 =F13 =14 =F15 =Elb 17 =F18 Fly = 20 =121 =122 -rzs -r24 -rud -rzu -r27 -rzo =F29 =F30 =F31 =F32 =F33 =F34 =F35 =F36 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 - 054 -3034 -04 -3034 - 054 -3034 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =$B$4 =B8*$B$5 =B9*$B$5 =B10*$B$5 =B11*$B$5 =B12*$B$5 =B13"BŞS =B14"SBSS =B15"BŞ =B16 "SBSS =B1/"$BS =B18"$B$5 =B19"$B$5 =B20"ŞBSS =B21"$B$S =B22"$B$S =B23"$B$S -D24 popu -DZ ID -DZU DU -DZI DU -DZO DU -DL) popu =B30*$B$5 =B31*$B$5 =B32*$B$5 =B33*$B$5 =B34*$B$5 =B35*$B$5 =B36*$B$5 =B37*$B$5 =C8-D8 =C9-09 =C10-D10 =C11-D11 =C12-D12 =C13-013 =L14- 14 ELIS- 15 LIO-DIb =CI7-DI/ LIX-UIX =C19-19 =C20- 20 I-D 22-22 =C23-023 - 24-024 -CZ-UZ -L2U-UZU -27-DZI -LLO-UZO -12-23 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13 =B14-E14 =B15-E15 =BIb-Elb =B17-E17 =B18-E18 =B19-E19 =B20-520 =B2I-E21 =B22-E22 =B23-523 -D24-024 -DJ-CZ -DZU-CZU -D21-C27 -D20-CZO -D2-C23 =B30-E30 =B31-E31 =B32-E32 =B33-E33 =B34-E34 =B35-E35 =B36-E36 =B37-E37 =C30-D30 =C31-D31 =C32-D32 =C33-D33 =C34-D34 =C35-D35 =C36-036 =C37-D37

Please do rate me and mention doubts, if any, in the comments section.