Question

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

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

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. You must use the provided post-WW2 long-term period as the CAPM data source.

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)?

DATA SOURCE:

 Post-WWII Annual Returns: Year S&P 500 Stocks 3-month T.Bill 1946 -8.43% 0.38% 1947 5.20% 0.57% 1948 5.70% 1.02% 1949 18.30% 1.10% 1950 30.81% 1.17% 1951 23.68% 1.48% 1952 18.15% 1.67% 1953 -1.21% 1.89% 1954 52.56% 0.96% 1955 32.60% 1.66% 1956 7.44% 2.56% 1957 -10.46% 3.23% 1958 43.72% 1.78% 1959 12.06% 3.26% 1960 0.34% 3.05% 1961 26.64% 2.27% 1962 -8.81% 2.78% 1963 22.61% 3.11% 1964 16.42% 3.51% 1965 12.40% 3.90% 1966 -9.97% 4.84% 1967 23.80% 4.33% 1968 10.81% 5.26% 1969 -8.24% 6.56% 1970 3.56% 6.69% 1971 14.22% 4.54% 1972 18.76% 3.95% 1973 -14.31% 6.73% 1974 -25.90% 7.78% 1975 37.00% 5.99% 1976 23.83% 4.97% 1977 -6.98% 5.13% 1978 6.51% 6.93% 1979 18.52% 9.94% 1980 31.74% 11.22% 1981 -4.70% 14.30% 1982 20.42% 11.01% 1983 22.34% 8.45% 1984 6.15% 9.61% 1985 31.24% 7.49% 1986 18.49% 6.04% 1987 5.81% 5.72% 1988 16.54% 6.45% 1989 31.48% 8.11% 1990 -3.06% 7.55% 1991 30.23% 5.61% 1992 7.49% 3.41% 1993 9.97% 2.98% 1994 1.33% 3.99% 1995 37.20% 5.52% 1996 22.68% 5.02% 1997 33.10% 5.05% 1998 28.34% 4.73% 1999 20.89% 4.51% 2000 -9.03% 5.76% 2001 -11.85% 3.67% 2002 -21.97% 1.66% 2003 28.36% 1.03% 2004 10.74% 1.23% 2005 4.83% 3.01% 2006 15.61% 4.68% 2007 5.48% 4.64% 2008 -36.55% 1.59% 2009 25.94% 0.14% 2010 14.82% 0.13% 2011 2.10% 0.03% 2012 15.89% 0.05% 2013 32.15% 0.07% 2014 13.52% 0.05% 2015 1.38% 0.21% 2016 11.77% 0.51% 2017 21.64% 1.39%

a. The cash flows and IRR calculation are as below:

(b) Now we calculate the average risk free rate and market return premium:

Thus the average risk free rate should be 4.05% and market return premium should be 12.28%. Hence as per CAPM, the required return of the stock should be : 4.05% + 1.25 * (12.28%) = 19.40%

(c) Using the IRR rule since the actual IRR at 6.46% is less than the CAPM required return of 19.40%, this was not a good investment. We can also perform NPV calculations on the stock cash flows with discount rate as 19.40% as below:

In excel: +NPV (19.40%, -630000, 301700, -396400, 217500, 663000) = -168665.78

Hence given the negative NPV, even NPV rule suggests that it was not a good investment.

Earn Coins

Coins can be redeemed for fabulous gifts.