Question

Jason Scott has applied for a mortgage to purchase a house, and he will go to settlement in two months. His loan can be locked in now at the current market interest rate of 7% and a cost of $1,000. He also has the option of waiting one month and locking in the rate available at that time at a cost of $500. Finally, he can choose to accept the market rate available at settlement in two months at no cost. Assume that interest rates will either increase by 0.5% (0.3 probability), remain unchanged (0.5 probability), or decrease by 0.5% (0.2 probability) at the end one month.

Rates can also increase, remain unchanged, or decrease by another 0.5% at the end on the second month. If rates increase after one month, the probability that they will increase, remain unchanged, and decrease at the end of the second month is 0.5, 0.25, and 0.25, respectively. If rates remain unchanged after one month, the probability that they will increase, remain unchanged, and decrease at the end of the second month is 0.25, 0.5, and 0.25, respectively. If rates decrease after one month, the probability that they will increase, remain unchanged, and decrease at the end of the second month is 0.25, 0.25, and 0.5, respectively.

Assuming that Jason will stay in the house for 5 years, each 0.5% increase in the interest rate of his mortgage will cost him $2,400. Each 0.5% decrease in the rate will likewise save him $2,400. What strategy would you recommend?

How do you set this up in excel?

Answer 1

The above problem can be addressed in various ways.

Strategy 1

First look at the first strategy of the freezing the rate now. Its a fairly simple decision that the rate is frozen at 7% with no risk of volatility in the rates. And to arrive at the decision the total cost is $1,000. Hence the cost of the decision is $,1000

Strategy 2

Second Strategy - Is to freeze the decision one month after. There is a possibility of changes in interest rates. And each change in interest rate has some advantages / disadvantages.

So if the rate of interest after a month increases by 0.5% the cost of that would be $2,400 with a probability of 0.3. Therefore the expected loss would be 2,400 x 0.3 = 720.

If the rate of interest remains same there isn't going to be ay additional cost whose probability is 0.5. Therefore the expected gain or loss is 0.

If the rate of interest reduces by 0.5% Jason would save $2,400 whose probability is 0.2. Therefore the expected gain from this would be $2,400 x 0.2 = 480.

Therefore the expected gain / loss by deferring the decision by one month is 720 + 0 - 480 = 240. Additionally, there is a cost to defer the decision by a month and that is $500. Hence the total expected cost of this decision making is $240 + $500 = $740.

Strategy 3

In Strategy 3 there are various possibilities.

Month1		Month 2
Status	Probability (P1)	Status	Probability (P2)	Total Probability (P1 x P2)	Total Cost	Expected Value (Total Probability X Total Cost)
Increase	0.3	Increase	0.5	0.15	4,800	720
Increase	0.3	Same	0.25	0.075	2,400	180
Increase	0.3	Decrease	0.25	0.075	0	0
Same	0.5	Increase	0.25	0.125	2,400	300
Same	0.5	Same	0.5	0.25	0	0
Same	0.5	Decrease	0.25	0.125	-2,400	-300
Decrease	0.2	Increase	0.5	0.10	0	0
Decrease	0.2	Same	0.25	0.05	-2,400	-120
Decrease	0.2	Decrease	0.25	0.05	-4,800	-240

The total expected Cost of the decision is $540.

From the above computation it is clear that the expected cost of the 3 decisions are $1000, $740 and $540.

Hence Jason Scott should go for the settlement in 2 months. Yes, going for settlement after 2 months will bring about the risk of being exposed to the market rate fluctuations and its impact.