Jeremy has $6000 to invest. If he puts the money in a certificate of deposit (CD), he is assured of re­ceiving an effective 2.35% per year for 5 years. If he invests the money in stocks, he has a 50-50 chance of one of the following cash flow sequences for the next 5 years.

Question

Finally, Jeremy can invest his $6000 in real estate for the 5 years with the following cash flow and probability estimates.





(a) Write the spreadsheet function to display i* for each opportunity.

(b) Which of the three investment opportunities offers the best expected rate of return?

Answer 1

Certificate of Deposit

Rate of return, i* = 2.35% (from problem statement)

Stocks

Stock 1: – 6000 + 250(P/A,i*,4) + 6800(P/F,i*,5) = 0
i* = 5.80%

Function: = RATE(5,250,-6000,6550)

Stock 2: –6000 + 600(P/A,i*,4) + 4000(P/F,i*,5) = 0
i* = 1.61%

Function: = RATE(5,600,-6000,3400)

E(i*) = 5.80(0.5) + 1.61(0.5) = 3.71%

Real Estate

Prob. = 0.3
–6,000 – 425(P/A,i*,4) + 9500(P/F,i*,5) = 0
i* = 4.80%

Function: = RATE(5,-425,-6000,9925)
Prob. 0.5
–6000 + 7200(P/F,i*,5) = 0
(P/F,i*,5) = 0.6944
i* = 3.71%

Function: = RATE(5,,-6000,7200)

Prob. 0.2
–6000 + 500(P/A,i*,4) + 100(P/G,i*,4) + 5200(P/F,i*,5) = 0
i* = 6.48%

Function: = IRR(B2:B7) for cash flows entered in cells B2 through B7

E(i*) = 4.80(0.3) + 3.71(0.5) + 6.48(0.2) = 4.59%

Invest in real estate for the highest E(i*) of 4.59% per year.