Consider the following options for purchasing a $300,000 home financed over 15 years:

1. Conventional financing with 80% LTV, Rate = 4.50%

2. Conventional financing with 10% down (15 years at 4.50%) and a second mortgage (10 years at 6.00%) for 10% of the purchase price.

3. 97% conventional, PMI is 0.75% annually, interest rate is 5.25%

4. FHA with 3.5% down, upfront MIP is 1.75% of the loan amount and is added to the loan amount, monthly MIP is 0.90% annually.

You have $60,000 cash plus cash needed to cover closing costs.

Any cash less than 20% of the purchase price is considered available for investing. Assume that the investor will earn a long-run average of the S&P 500 Index (assume 10.00%).

You will create an extended amortization table for each mortgage option. Label each page of your Excel file indicating the mortgage type. On each page, include a clearly defined table of inputs that reflect the relevant inputs for the mortgage type. Your input tables should be identical except for the information included. Note that some input options will be zero (0).

1. Calculate the monthly payment (This will always include P&I and may include: a) second mortgage payment, 2) PMI, or 3) MIP for each loan.) Assume closing cost are paid at closing and not rolled into the loan (except for the FHA upfront MIP).

2. Calculate the after-tax monthly payment for each loan over the 15-year term of the loan.

3. Calculate the annual after-tax cost (IRR) for each year for each loan over the term of the loan.

4. Calculate the value of the home and the equity earned on the home over the life of the loan. (Assume an annual appreciation rate of 1.9%.)

5. Calculate the terminal value (FV) of your investment (difference between $60,000 down and other down payments) during the term of the mortgage.

6. Calculate the terminal value (FV) assuming you invest the difference between monthly payments on 80% LTV and 97% LTV mortgage payments. You may use after-tax payments for this calculation.

7. In the long run, does it matter which option you choose?

8. Construct a graph representing the annual IRR for each option over the 15-year loan term. Be sure to correctly label the graph and legend.