Question

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable.

Please show how to do this in excel and the excel formulas.

We are given the information that Microthin’s stock price was $21 in December 2013, $29 in December 2014, $27 in December 2015, $20 in December 2016, and $26 in December 2017. It also pays annual dividend amounts varying from 2013 through 2017.

Let's assume you do the following transactions:

a) In December 2013: buy 30,000 Microthin shares;

b) In December 2014: collect the dividends ($0.39 per share) on your shares, and then sell 10,000 shares;

c) In December 2015: collect the dividends ($0.43 per share) on your remaining shares, and then buy another 15,000 shares;

d) In December 2016: collect the dividends ($0.50 per share) on your remaining shares, and then sell another 10,000 shares.

e) In December 2017: collect the dividends ($0.52 per share) on your remaining shares, and then sell all your remaining shares.

Q1: What should be the IRR during the "December 2013 – December 2017" period for your Microthin stock investment?                                                                

Q2: The year-by-year annual returns after the World War II are provided on the Excel answer sheet, the tab “Case 3”. Use =AVERAGE function to compute the post-WW2 average return for S&P stock market index (Rm) and for US “risk-free” T-bill (Rf), respectively. With such Rm and Rf amounts, and if Microthin’s stock beta = 1.25, what shall be the required return amount on Microthin stock if you apply the CAPM formula? NOTE: CAPM is for long-term stock market equilibrium, so you should NOT only use the short 2013-2017 four-year-average stock data only for CAPM purpose.

Q3: Based on your answers to Q1 and Q2, has your Microthin stock investment over the "Dec 2013 – Dec 2017” period been good or bad (using NPV and IRR rules)?

Please show how to do this in excel and the excel formulas.

Answer 1

For the IRR of the investment, we have the worksheet below:

Once we derived the cash flows, the formula used in excel is =IRR(-630000, 301700, -396400, 217500, 663000).

The average risk free rate and market return was calculated using the excel formula = Average (data for risk free return from 1946 to 2017)

Given that the average risk free rate is 4.05%, average market return premium is 12.28% and beta is given as 1.25, as per CAPM the required return should be : 4.05% + 1.25 * 12.28% = 19.40%

Given that the IRR of the investment is significantly lower than the required return, it was not a good investment. We can also calculate the NPV and check the NPV rule:

Excel formula +NPV(19.40%, -630000, 301700, -396400, 217500, 663000) = -168665.78

Since the NPV is negative, even by NPV rule it is not a good investment.