please type out answer

Mr. and Mrs. Wong have an appointment tomorrow with you, their investment counselor, to discuss their plan to purchase a house in Orlando, Florida. They have saved for a down payment, so want to take out a mortgage. Their combined annual income is per year, which they estimate will increase by annually over the foreseeable future, and they are considering three different specialty -year mortgages:

Hybrid: The interest is fixed at a low introductory rate of for years and then adjusts annually to over the U.S. federal funds rate.

Interest-Only: During the first years the rate is set at , and no principal is paid. After that time, the mortgage adjusts annually to over the U.S. federal funds rate.

Negative Amortization: During the first years the rate is set at based on a principal of of the purchase price of the home, with the result that the balance on the principal actually grows during this period. After that time, the mortgage adjusts annually to over the U.S. federal funds rate.

You decide that you should create an Excel worksheet that will compute the monthly payments for the three types of loan. Of course, you have no way of predicting what the U.S. federal funds rate will be over the next years (see Figure 3 for historical values), so you decide to include three scenarios for the federal funds rate in each case:

• Scenario 1: Federal funds rate is in year 6 and then increases by per year.

• Scenario 2: Federal funds rate is steady at during the term of the loan.

• Scenario 3: Federal funds rate is in year 6 and then decreases by per year.

Figure 3

Each worksheet will show month-by-month payments for the specific type of loan. Typically, to be affordable, payments should not exceed of gross monthly income, so you will tabulate that quantity as well.

Hybrid Loan: You begin to create your worksheet by estimating of the Wongs monthly income, assuming a increase each year. (The income is computed using the compound interest formula for annual compounding.)

The next sheet shows the result, as well as the formulas for computing the interest rate in each scenario.

To compute the monthly payment, you decide to use the built-in function PMT, which has the format

where , , , ; the type, also optional, is or omitted if payments are at the end of each period, and if at the start of each period. The present value will be the outstanding principal owed on the home each time the rate is changed, so that too will need to be known. During the first years we can use as the present value the original cost of the home, but each year thereafter, the loan is re-amortized at the new interest rate, so the outstanding principal will need to be computed. Although Excel has a built-in function that calculates payment on the principal, it returns only the payment for a single period (month), so without creating a month-by-month amortization table, it would be difficult to use this function to track the outstanding principal. On the other hand, the total outstanding principal at any point in time can be computed by using the future value formula . You decide to add three more columns to your Excel worksheet to show the principal outstanding at the start of each year. Here is the spreadsheet with the formulas for the payments and outstanding principal for the first years.

The two formulas will each be copied across to the adjacent two cells for the other scenarios. A few things to notice: The negative sign before converts the negative quantity returned by to a positive amount. The dollar sign in in the formula fixes the present value for each year at the original cost of the home for the first years, during which payments are computed as for a fixed rate loan. In the formula for the balance on principal at the start of each year, the number of periods is the total number of months up through the preceding year, and the present value is the same initial price of the home each year during the -year fixed rate period.

The next sheet shows the calculated results for the fixed rate period and the new formulas to be added for the adjustable rate period starting with the sixth year.

Notice the changes: The loan is re-amortized each year starting with year 6, and the payment calculation needs to take into account the reduced, remaining lifetime of the loan each time. You now copy these formulas across for the remaining two scenarios and then copy all six formulas down the remaining rows to complete the calculation. Following is a portion of the complete worksheet showing, in red, those years during which the monthly payment will exceed of the gross monthly income.

In the third scenario the Wongs payments would more than double at the start of the sixth year and would remain above what they can reasonably afford for more years. Even if the federal funds rate were to remain at the low rate of , the monthly payments would still jump to above what the Wongs can afford at the start of the sixth year.

Interest-Only Loan: Here, the only change in the worksheet constructed previously is the computation of the payments for the first years; because the loan is interest-only during this period, the monthly payment is computed as simple interest at on the loan for a -year period:

The formula you could use in the spreadsheet in cell F3 is , and you then copy this across and down the entire block of payments for the first years. The rest of the spreadsheet (including the balance on principal) will adjust itself accordingly with the formulas you had for the hybrid loan. Below is a portion of the result, with a lot more red than in the hybrid loan case!

In all three scenarios this type of mortgage is worse for the Wongs than the hybrid loan; in particular, their payments in Scenario 3 would jump to more than double what they can afford at the start of the sixth year.

Negative Amortization Loan: Again, the only change in the worksheet is the computation of the payments for the first years. This time, the loan amortizes negatively during the initial -year period, so the payment formula in this period is adjusted to reflect this

Clearly the Wongs should steer clear of this type of loan in order to be able to continue to afford making payments!

In short, it seems unlikely that the Wongs will be able to afford payments on any of the three mortgages in question, and you decide to advise them to either seek a less expensive home or wait until their income has appreciated to enable them to afford a home of this price.

Exercises

1. In the case of a hybrid loan, what would the federal funds rate have to be in Scenario 2 to ensure that the Wongs can afford to make all payments?

2. Repeat Exercise 1 in the case of an interest-only loan.

3. Repeat Exercise 1 in the case of a negative amortization loan.

4. What home price, to the nearest , could the Wongs afford if they took out a hybrid loan, regardless of the scenario? [Hint: Adjust the original value of the loan on your spreadsheet to obtain the desired result.]

5. What home price, to the nearest , could the Wongs afford if they took out a negative amortization loan, regardless of the scenario? [Hint: Adjust the original value of the loan on your spreadsheet to obtain the desired result.]

6. How long would the Wongs need to wait before they could afford to purchase a home, assuming that their income continues to increase as above, they still have for a down payment, and the mortgage offers remain the same?