Question

(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%

Please show excel formulas...

Answer 1

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.