Using the headers in tab A4 of the Excel spreadsheet and the input values included in the green box: a. Create a monthly amortization schedule for a fixed rate 15-year mortgage. Make sure that any value that the user (me in this case) changes in the green box will be reflected in your amortization schedule. Your monthly payment calculation should be included in cell J5 and should also automatically change with any changes to the values in the green box. b. Next to the amortization schedule, create an Excel graph that shows the remaining mortgage balance overtime. Make sure that your graph is labeled appropriately. c. Include a vlookup function in cell L7 that will reflect the remaining mortgage balance associated with the end of the month entered by the user into cell J7. For example, if the user enters 34 into cell J7, cell L7 will automatically show the remaining mortgage balance after 34 months.

B c D E G . M Month 1 Beginning balance Interest Principal Ending Balance Interest rate Original balance 3.50% $ 400,000 2 Monthly Payment 3 4 5 Remaining balance after months 6 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 20 A2 A3 A4 A5 A6 AZ A8 A9 A10 A11 A12 A13 + Abc 123 Thank BI U V B c D E G . M Month 1 Beginning balance Interest Principal Ending Balance Interest rate Original balance 3.50% $ 400,000 2 Monthly Payment 3 4 5 Remaining balance after months 6 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 20 A2 A3 A4 A5 A6 AZ A8 A9 A10 A11 A12 A13 + Abc 123 Thank BI U V