Project $3$: Mortgage Comparison

In this final project you will create a spreadsheet which will calculate mortgage terms to build amortization schedules for a $30$ year loan and a $15$ year loan.$$ In addition, we will compute a compounding retirement account to calculate the balance after $40$ years under different terms. Last, we will briefly investigate the effects that purchasing a home has on our taxes. There are many other life situations and other variables to consider when purchasing a home, but I chose to focus on comparing the $30$ year and $15$ year loans because currently that is a hot topic. Also, I too often hear that buying a house is a necessity because of the tax savings. Let$$s look at exactly how much we are saving per year by writing off the mortgage interest. $*$

You will be using the Excel mortgage comparison file to complete this assignment. In the template you will find $2$ sheets. The first sheet is titled $30$ year and will be used for calculations with the terms of the $30$ year$$ mortgage and the second sheet is titled $15$ year$$ and this sheet will be used to compute the terms of the $15$ year mortgage. You will see what is essentially broken into four areas: Terms, Amortization Schedule, Investment Schedule, and the Tax Schedule.

I set up the project and what is highlighted in yellow are the parts you need to complete. However, please note that I only highlighted the first few rows in the Amortization Schedule and the Investment Schedule. After the $20\%$ down payment the amount of the mortgage is $$250,000.$ The fixed rate is $5\%$ for the $30-$year mortgage and $4\mathrm{.}5\%$ for the $15-$year mortgage. Each month $$2,000$ total is allocated to the mortgage payment and savings.

Part I

$1.(10$ points$)$ Type your name in the title and fill in the terms for each scenario in sheet $1$ and $2.$ Cells$-$ A$6$:D$6$

$2.(10$ points$)$Calculate the monthly payment using the equation for both terms. Cell$-$ E$6$

$3.(10$ points$)$Use the built in PMT function in Excel to verify the payment is correct. Cell$-$ F$6$

$4.(40$ points$)$Create Amortization tables for both sheets. Cells$-$ A$11$:E$11($and down for the length of the mortgage$)$

Part II

$5.(10$ points$)$In the Investment column calculate the amount of money that will be invested each month for both sheets. Remember once the house is paid off the full $$2000$ will be invested. Cells$-$ G$11$:down for $40$ years

$6.(40$ points$)$In the Accumulation column calculate the balance of the investment each month assuming it grew at an interest rate of $12\%$ per year $($Cell$-$ G$6)$ over $40$ years for both sheets. Cells$-$ H$11$:down for $40$ years

$7.(20$ points$)$Use the built in FV function of Excel to verify the total amount accumulated in both sheets. Remember at some point the investment amount changes; therefore, you will need to use the FV function more than once. Final Answer: Cell$-$ H$6$