NEEDS TO BE DONE WITH EXCEL. THANK YOU! PLEASE HELP WITH THE FORMULAS.

Sandra is interested in financing the purchase of a property for sale for $150,000, and has $5,000 to invest as a down payment.

The Best Mortgage Company is offering a new mortgage loan product called, the Stable Mortgage. The mortgage loan is comprised of both a fixed interest rate and adjustable interest rate components.

The Stable Mortgage terms she is receiving from Best Mortgage Company are:

• There are 2% in discount points, and allows 75% of the mortgage loan amount to be at a

  fixed rate of interest, while 25% to be at an adjustable rate of interest.

• The fixed portion of the loan is for 30-years at 10.5% interest.

• The adjustable rate portion of the loan also has a 30-year term, and has the following

  terms: o A 9% initial interest rate using an index of one-year U.S. Treasuries with a 200bps

  margin. o Monthly mortgage loan payments adjust each year.

• The adjustable portion of the loan neither has an interest rate or payment cap.

• There is no prepayment penalty.

  The projected one-year U.S. Treasury rates are 10% for (the beginning of) year 2; 11% for (the beginning of) year 3; 8% for (the beginning of) year 4; and 12% for (the beginning of) year 5.

3. What are Sandra's monthly payments and end-of-year mortgage loan balances for the first five years? (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.)

4. Now, what are Sandras monthly payments and end-of-year mortgage loan balances for the first five years assuming that there is a 9.5% initial interest rate, AND, Maria being a strong negotiator, was able to get an annual 1% interest rate cap. (Note: while a calculation is necessary, it is acceptable to calculate amortization on an annual basis.)