Reference no: EM131586364

Using 650,000 as purchase price. solve work in excel

A borrower wants to evaluate the loans listed below and anticipates owning the new home for 7 years. Calculate the payments, loan balance at the end of year 7 and yield for each mortgage for the 7 year period using spread sheets.

Based on estimated forward rates, the index to which the ARM is tied is forecast as follows:

EOY six months: 5.5%             EOY 1: 6.20%

EOY 18months: 5.75%                        EOY 2: 5.05%

EOY 30months: 8.5%              EOY 3: 9.0%

EOY 42months: 9.5%              EOY 4: 10.0%

EOY 54months: 11%               EOY 5: 12.0%

EOY 66months: 11.5%                        EOY 6: 11.5%

EOY 78months: 11.75

The five different mortgages are listed below:

Mortgage A: FRM @7.25 % for 30 years with 20% down payment, 1.5 point.

Mortgage B: ARM @ 6.0% for 30 years with 20% down payment, 0.5 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.00%. This loan has an annual interest rate cap of 1% and 5% interest cap over the life of the loan and no negative amortization.

Mortgage C: ARM @ 5.0% for 30 year with 20% down payment, 1 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 1.5%. This loan has a payment cap of 10% and allows negative amortization.

Mortgage D: ARM @ 4.25% for 30 years with 20% down payment, 1.5 points, adjustable annually based on the index of one-year Treasuries plus a margin of 1.75%. This loan has no caps.

From your answers above which loan would you choose and WHY?

Explain why the initial rate for each of the mortgages is different. Make sure to discuss the risk.