Chapter 4 discusses compound interest loans, such as mortgages, car loans, or student loans, as well as amortized loans and loan schedules. Use this formatted Excel spreadsheet to answer questions on the scenarios below. (Add information from the scenario into the Input Data section to determine the payment amount, which is listed in cell F15 in the Key Output section of the spreadsheet. Percentages need to be entered in decimal format, for instance 3% would be entered as .03 in cell B12.) Click Submit Assignment to type in the answer the following scenarios: 1. Set up an amortization schedule for a $30,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 10%. What is the annual payment? 2. Set up an amortization schedule for a $30,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 20%. What is the annual payment? 3. Set up an amortization schedule for a $60,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 10%. What is the annual payment? 4. Set up an amortization schedule for a $60,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 20%. What is the annual payment? 5. After you input the data for each scenario, click on the Graph tab (second tab on the worksheet) and look at the Principal and Interest portions of the payments throughout the years. What do you notice about the amount of Principal and Interest over the years (which amount is higher in the early years, and which amount is higher in the later years) of the loan? What do you notice about the difference in Principal and Interest in the 10% scenarios compared to 20% scenarios? KEY OUTPUT: Payment 2,037.04 20,000 8.0002 20 13 INPUT DATA: 14 15 Loan amount 16 Interest rate 17 Number of years 18 19 20 MODEL-GENERATED DATA: 21 22 Amortization schedule: 23 24 Year Payment 25 1 2,037.04 26 2 2,037.04 27 3 2,037.04 28 4 2,037.04 29 5 2,037.04 30 6 2,037.04 31 7 2,037.04 32 8 2,03704 33 9 2,037.04 34 10 2,037.04 35 11 2,037.04 36 12 2,037.04 37 13 2,037.04 38 14 2,037.04 39 15 2,037.04 40 16 2,037.04 41 17 2,037.04 42 18 2,037.04 43 19 2,037.04 20 2,037.04 45 40,740.88 Interest 1,600.00 1,565.04 1,527 28 1,486.49 1,442.45 1,394.88 1,343.51 1,288.03 1.228.11 1,163.39 1,093.50 1,018.01 936.49 848.45 753.36 650.67 539.76 419.97 290.61 150.89 20,740.88 Principal Repayment 437.04 472.01 509.77 550.55 594.59 642.16 693.53 749.02 808.94 873.65 943.55 1,019.03 1,100.55 1,188,60 1,283.68 1,386.38 1,497 29 1,617.07 1.746.44 1,886.15 20,000.00 Remaining Balance 19,562.96 19,090.95 18,581.18 18,030,63 17.436.04 16,793.87 16,100.34 15,351.32 14,542.39 13,668.73 12,725.19 11,706.16 10.605.61 9,417.01 8,133.33 6,746.95 5,249.66 3,632.59 1,886.15 0.00 PV of Payments 1,886.15 1,746.44 1,617.07 1,497 29 1,386 38 1,283.68 1,188,60 1,100.55 1,019.03 943.55 873.65 808.94 749.02 693.53 642.16 594.59 550.55 509.77 472.01 437.04 20,000.00