This is your next "brain stretching'exercise. The intent of these assignments is to push you to explore and develop your skills. You are welcome to invest whatever effort you like into these, though, the purpose is not for you to sink days of effort into them. Most brain stretching exercises can be completed relatively quickly if you look for ways of being efficient in your work. 1.e., if you are engaged in a highly repetitive task that seems like it will take hours to complete, there is likely a better way to proceed. My hope is that you will completely solve these challenges, but, I'll be lenient on the grading provided you've given it a real effort. I'm also willing to offer advice if you are stuck. If you've made some headway and aren't sure what to do next, please feel free to drop by my office. Part 1: For this assignment it is important that the final product is linked to your original input cells. For example, if I change the interest rate or down payment amount in your inputs, all of the outputs should change automatically. 1. We are purchasing a $250,000.00 house using a down payment that's equivalent to 20% of the value, and will be financing the balance. The loan will be for 15 years, at 4.5% with monthly payments. 2. In excel, create a section for your inputs. (purchase price, down payment percentage, interest rate, loan duration, etc.) 3. Create an amortization table that shows the initial balance at month zero, and the ending balance at the end of the month. To do this, you'll need to calculate the interest that accrues during the month, the mortgage payment, and include a placeholder for any 'extra' payments above and beyond the required mortgage payment (you'll probably want this in an input cell as well). 4. With that you can calculate the initial and ending balance for the each month 5. You should be able to copy these formulas to generate the entire amortization table until the loan is paid off. I.e., once you've structured the first few rows of your amortization table you should be able to use the Excel fill handle to copy those rows and complete the rest of your table automatically. Note: This should be structured so that changes in the inputs (loan amount, rate, etc.) will change the data in the amortization table Note: This should be structured so that changes in the inputs (loan amount, rate, etc.) will change the data in the amortization table Part 2 Assuming no 'extra' payments, i.e., just the standard mortgage payment... 1. What is the original loan amount? 2. What is the total of all of the payments? 3. What is the total amount of interest pald? Part 3 Making additional payments (for example, an extra $50 every month-though you can try different amounts) will impact how fast the loan pays off? Your amortization table should show this. How many payments does making an additional $50 or an additional $100 per month save you, and what is the total of those savings? Part 4 BONUS Have excel calculate the exact date that the loan pays off if you change the amount of the 'extra' payment. The goal is to have a payoff date calculate next to your inputs so that a change in the inputs will change the payoff date automatically Create a graph that shows a horizontal line representing the mortgage payment. On that graph include a second line that represents the proportion of the payment that is interest. For example, if your mortgage payment was $800.00, and $533.33 of the 37th payment was covering the Interest, the second line should be about 2/3 of the way from zero to the line for the mortgage payment at the 37th payment. To do this, you'll likely want to use the X-Y scatter plot and include two series on the primary axis