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.