A D E H 1 4 5 There are 2 questions below. You are required to attempt both. Please use cell reference and formula (similar to Excel 2 formula) to solve the problems so that the spreadsheet is dynamic one. Questions Ratio&CF CmnsizeV CmnsizeH AmortSch 1. You borrow $100,000 today and will repay the loan in equal installments at the end of the next 5 years. How much is your annual payment if the interest rate is 9.5%? Please prepare the schedule below. Loan amount= $100,000 Year Beg. Balance Annual Pmt Interest Pmt Principal Pmt End. Balance Time to repay- 5 years 1 Interest rate= 9.50% 2 3 Annual payment 4 5 6 7 8 9 10 11 12 13 14 15 16 5 years 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 2. If you borrow $100,000 today & repay it in equal installments at the end of months during the next 5 years, how much is your monthly payment if the interest rate is 9.5%? Please prepare the schedule below. Loan amount $100,000 Year Month Beg. Balance Monthly Pmt Interest Pmt Principal Pmt End. Balance Time to repay- 1 60 months 2 Interest rate= 9.50% per year 3 0.79% per mont 4 5 Monthly payment 6 1 7 8 9 10 11 12 13 14 15 16 17 18 2 19 20 21 22 23 24 25 26 27 28 29 30 3 31 32 33 34 35 36 37 38 39 40 41 42 4 43 44 45 46 47 48 49 50 51 52 53 54 5 55 56 57 58 59 60 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75