The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X Open spreadsheet a. Complete an amortization schedule for a $35,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 12% compounded annually. Round all answers to the nearest cent. Ending Beginning Repayment of Principal Balance Year Balance Payment Interest $ $ 1 $ 2 $ $ $ $ b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all answers to two decimal places. % Principal % Interest Year 1: Year 2: % Year 3: c. Why do these percentages change over time? I. These percentages change over time because even though the total payment is constant the amount of interest paid each year is declining as the remaining or outstanding balance declines. II. These percentages change over time because even though the total payment is constant the amount of interest paid each year is increasing as the remaining or outstanding balance declines III. These percentages change over time because even though the total payment is constant the amount of interest paid each year is declining as the remaining or outstanding balance increases IV. These percentages change over time because even though the total payment is constant the amount of interest paid each year is increasing as the remaining or outstanding balance increases V. These percentages do not change over time; interest and principal are each a constant percentage of the total payment Tell me what you want to do File Review View Help Home Insert Data XCut ab Wrap Text AutoSum AA Arial 10 General Copy Sort &Fir ConditionalFormat Paste Insert Delete Format Merge & Center BIUD ab EE3 3 Clear Format Painter Formatting as Table Sele Filter Clipboard Number Undo Font Alignment Styles Cells Editing B9 B C A D E F G H I J L 1 Amortization schedule 2 3 Loan amount to be repaid (PV) $35,000.00 12.00% 4 Interest rate (r) Length of loan (in years) 6 5 3 a. Setting up amortization table Calculation of loan payment 7 Formula #N/A 9 Repayment of Principal 10 Remaining Balance Year Beginning Balance Payment Interest 11 1 12 2 13 3 14 15 b. Calculating % of Payment Representing Interest and Principal for Each Year Payment % Representing Principal Payment % Representing Interest Check: Total = 100 % 16 Year 17 1 18 19 20 File Review View Help Tell me what you want to do Home Insert Data X Cut A A ab Wrap Text AutoSum General Arial 10 Copy ConditionalFormat Sort & Fir Paste Insert Delete Format B IUD ab Merge & Center Clear A Filter Sele Format Painter Formatting as Table Font Undo Number Editing Clipboard Alignment Styles Cells fx B9 B A D E F G H I K L 17 1 18 2 19 3 20 21 Formulas Repayment of Principal Beginning Balance #N/A Remaining Balance #N/A 22 Year Payment #N/A Interest 23 1 #N/A #N/A 24 #N/A #N/A #N/A #N/A #N/A 25 #N/A #N/A #N/A #N/A #N/A 26 27 b. Calculating % of Payment Representing Interest and Principal for Each Year Payment % Representing Principal #N/A Payment % Representing Interest #N/A Check: Total = 100 % 28 Year 29 1 #N/A 30 #N/A #N/A #N/A 31 #N/A #N/A #N/A 32 33 34 35 36