Hw1

The data on a loan has been collected in the Microsoft Excel Online Ne below. Open the spreadsheet and perform the required analysis to answer the questions below, um Open spreadsheet a. Complete an amortization schedule for a $42,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 6% compounded annually Round all answers to the nearest cont. Beginning Balance Repayment of Principal Ending Balance Payment Interest Year 1 $ $ $ $ $ $ $ $ 2 3 $ $ $ 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 Interest Principal Year 11 % Year 2: % Year 3: % B D E G Amortization schedule Loan amount to be repaid (PV) Interest rate() Length of loan (In years) a. Setting up amortization tablo Calculation of loan payment $42,000.00 6.00% 3 Formula SN/A Ropayment of Principal Interest Remaining Balance Check: Total 100% 0 Year Beginning Balance Payment 1 1 2 2 3 3 4 5 b. Calculating % of Payment Representing Interest and Principal for Each Your Payment % Payment x Representing Representing 16 Yoar Interest Principal 1 18 2 3 20 21 Formulas 22 Yoar Beginning Balance Payment 23 1 WNIA #NA 24 2 ANIA UNIA 25 3 #N/A N/A 26 27 b. Calculating % of Payment Representing Interest and Principal for Each Year Payment % Payment % Representing Representing 28 Year Interest Principal 29 1 WNIA ONIA IN/A 2 NA 30 31 3 #N/A UNA 32 Interest #NA ONA #N/A Repayment of Principal WNIA #N/A ONIA Remaining Balance ONIA #N/A #N/A Check: Total 100% N/A SN/A ONJA