Financial Modeling I: Assignment #4 Note: There are two problems, worth 5 marks each. Problem #1: (5 marks) Use the completed version of your Assignment #1 Retirement planner model. In the original, the chart just plotted the Year End Balance as a single series and was therefore one color for both the working years and the retirement years. The objective of this problem is primarily to distinguish between the two periods on the chart. You could accomplish this by creating two separate columns: one for the working years it would have the savings balance during the working years and #N/A errors during the retirement years and a second column to with #N/A errors during the working years and the correct savings balance during the retirement years. This is effectively what the retirement planner template used to accomplish this task (they of course hid the two columns on the Calculations worksheet). This would make the table look strange and besides I want you to accomplish this by creating named formulas that reference the correct portions of the range. Start by creating a dynamic range that references all the columns of the amortization table and all the rows with actual data (i.e. not the rows with #N/S errors). This range should automatically change as the retirement age and number of years in retirement change. You can use either INDEX or OFFSET to accomplish this task. Then create named formulas that reference the Age, Yearly Savings and Withdrawals columns. You should then create separate named formulas for the working years component and the retirement years component of the Year End Balance (again either INDEX or OFFSET should be used). You will need to repeat this step for the Age column. Use these named formulas in to drive the chart. Hand in the entire Assignment #1 with the updated chart. Dont forget to document your workbook call it version 2 and explain the changes you made to the model. Problem #2: Amortization Chart (5 marks) This assignment is similar to the Mortgage Calculator example I when through in week #1. Create two charts 1) a chart showing (i) the monthly payment (ii) the interest component of the payment and (iii) the principal component of the payment all as a function of Period and 2) a chart showing outstanding balance as a function of period for the following mortgage: Original Principal: $250,000 Mortgage Rate: 4% per year compounded semi-annual Amortization Period: 25 years Payment Frequency: monthly (12 times per year), semi-monthly (24 times per year), bi-weekly (26 times per year) or weekly (52 times per year). Create a list using data validation. You should use data validation to create a drop down list as follows: {Monthly, semi-Monthly; BiWeekly; Weekly} not {12, 24, 26, 52} Use vlookup or index match to lookup the corresponding number. I should be able to adjust the four parameters above and have the charts update automatically. You might as well have an automatically updating chart title as well. Use the Excel financial functions IPMT and PPMT to find the interest and principal components of each payment. Also, you can create an array of payments (they are all the same number) by just adding the results from the IPMT and PPMT functions together. Your final product should be a worksheet with the four inputs (that I can change) and the two charts. The charts should not depend on any values calculated in worksheet cells. You will want to set this up with named formulas driving the charts. New Idea. See the screen shot below. I have included some basic outputs: Payment; Interest Component; Principal Component; Total Paid and Total Interest Paid. You should include these in your model as well. You can calculate them any way you want. Also note the scroll bar (another type of form control) and the markers on the charts. You can choose a particular period using the scroll bar and the markers automatically adjust on the chart. Dont forget to document your workbook. You will have to calculate the Effective Period Rate (EPR) to use in any of Excels financial functions. Remember the Excel functions effect and nominal