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.