Percentages need to be entered in decimal format, for instance 3% would be entered as .03 in cell B12.)

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?

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?

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?

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?

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 13 INPUT DATA: 14 15 Loan amount 20,000 16 Interest rate 8.000 17 Number of years 20 18 19 20 MODEL-GENERATED DATA 21 22 Amortization schedule: 23 24 Year Payment Interest 25 1 2,037.04 1,600.00 26 2 2,037.04 1,565.04 27 3 2,037.04 1,527.28 28 4 2,037.04 1,486.49 29 5 2,037.04 1,442.45 30 6 2,037.04 1,394.88 31 7 2,037.04 1,343.51 32 8 2,037.04 1,288.03 33 9 2,037.04 1,228.11 34 10 2,037.04 1.163.39 35 11 2.037.04 1,093.50 36 12 2.037.04 1,018.01 37 13 2.037.04 936.49 38 14 2.037.04 848.45 39 15 2.037.04 753.36 40 16 2.037.04 650.67 17 02704 620 76 C04 GRAPH INSTRUCTIONS 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 1407 Remaining PV of Balance Payments 19,562.96 1,886.15 19,090.95 1.746.44 18,581.18 1,617.07 18,030.63 1,497.29 17,436.04 1,386.38 16,793.87 1,283.68 16,100.34 1.188.60 15,351.32 1,100.55 14,542.39 1,019.03 13,668.73 943.55 12,725.19 873.65 11.706.16 808.94 10.605.61 749.02 9,417.01 693.53 8,133.33 642.16 6,746.95 594.59 10 AR 550 55 Amortization schedule Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Payment 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2,037.04 2.037.04 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 PV of Balance Payments 19,562.96 1,886.15 19,090.95 1,746.44 18,581.18 1,617.07 18,030.63 1,497.29 17,436.04 1,386.38 16.793.87 1,283.68 16,100.34 1,188.60 15,351.32 1,100.55 14,542.39 1,019.03 13,668.73 943.55 12,725.19 873.65 11.706.16 808.94 10,605.61 749.02 9,417.01 693.53 8,133.33 642.16 6,746.95 594.59 5,249.66 550.55 3,632.59 509.77 1,886.15 472.01 0.00 437.04 20,000.00 CFINA 2500 2000 1500 Payment Amount Principal interest 1000 500 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20