USING $650,000 create in excel

A borrower wants to evaluate the loans listed below and anticipates owning the new home for 6 years. Calculate the payments, loan balance at the end of year 6 and yield for each mortgage for the 6 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.10%

EOY 18months: 5.75% EOY 2: 5.15%

EOY 30months: 8.5% EOY 3: 9.1%

EOY 42months: 9.5% EOY 4: 10.2%

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.30 % for 30 years with 20% down payment, 1.5 point.

Mortgage B: ARM @ 6.1% for 30 years with 20% down payment, 0.75 point, adjustable annually based on the index of one-year Treasuries given plus a margin of 2.0%. 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.1% 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.25%. This loan has a payment cap of 10% and allows negative amortization.

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