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 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

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 paid?

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

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.