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%

Please show excel formulas...

Homework Answers

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.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
(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. 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...
Dividends on Preferred and Common Stock Sunbird Theatre Inc. owns and operates movie theaters throughout Florida...
Dividends on Preferred and Common Stock Sunbird Theatre Inc. owns and operates movie theaters throughout Florida and Georgia. Sunbird Theatre Inc. has declared the following annual dividends over a six-year period: 2011, $20,000; 2012, $36,000; 2013, $70,000; 2014, $90,000; 2015, $102,000 and 2016, $150,000. During the entire period ended December 31 of each year, the outstanding stock of the company was composed of 100,000 shares of cumulative preferred 1% stock, $30 par, and 400,000 shares of common stock, $20 par....
1- In the previous 5 years, Google paid an annual dividend as follows: Year   Dividends 2011...
1- In the previous 5 years, Google paid an annual dividend as follows: Year   Dividends 2011 - 2.7 2010- 2.5 2009- 2.2 2008- 1.8 2007- 1.5 Google is expected to pay a dividends of $3 in the next year (2012). What is the cost of equity of Google if its current stock price is $90? 2- As a technology-based firm, Google has a high beta of 1.4. if the risk-free rate of return is 5% and the market risk premium...
AC312 – Comprehensive Articulation Project This project should be completed using Excel (with formulas and linked...
AC312 – Comprehensive Articulation Project This project should be completed using Excel (with formulas and linked data). Below are the deliverables: 1. Prepare a Multi-Step Income Statement for the year ended 2018. This statement should be flexibly designed (formulas in cells). To the right of your dollars in this statement, show common-sized percentages based on sales (vertical analysis). 2. Show journal entries, adjusting entries and closing entries for the below additional information…none of the journal entries for 2018 have been...
BA233 - Accounting for Managers M6 Step 1 Learning Activity Silver Computer Corporation Use the financial...
BA233 - Accounting for Managers M6 Step 1 Learning Activity Silver Computer Corporation Use the financial statements below to complete a financial ratio analysis. The ratios should be calculated on the ratio worksheet. Make sure to reference amounts from the statements onto your ratios. Be sure that your answer uses the correct format for that ratio: times, percentage, days, dollar amount. Ratio Format: Show with your answer whether the ratio is times, perentage, or dollar amount. For example: o Current...
Corporations: Organization, Stock Transactions, and Dividends Using the annual report of the corporation that you have...
Corporations: Organization, Stock Transactions, and Dividends Using the annual report of the corporation that you have been researching these weeks, take a look at that corporation's Statement of Stockholders' Equity. Tell us about the common stock that it issued including its outstanding shares. Also, take a look at a website, such as Yahoo Finance and find information about the corporation's price per share and whether or not it issues a dividend. Your corporation could have probably raised a lot of...
Fiscal year ends in December. 2017-12 2016-12 2015-12 2014-12 2013-12 Assets Current assets Cash and cash...
Fiscal year ends in December. 2017-12 2016-12 2015-12 2014-12 2013-12 Assets Current assets Cash and cash equivalents                  401                  603                  628                  669                  592 Short-term investments                     18                  417                  810                  292                  487 Receivables                  970                  938                  982               1,129               1,052 Inventories                  372                  407                  350                  273                  252 Deferred income taxes                  126                  141                  155 Other current assets                  154                  122                     70                    ...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly O'Brien, David Roberts, and Barbara Smalley. O'Brien and Roberts, both MDs, were on the research faculty at the Chicago Medical School at the time; O'Brien specialized in biochemistry and molecular biology, and Roberts specialized in immunology and medical microbiology. Smalley, who has a PhD, served a department chair of the Microbiology Department at the same school. The company started as a research and development...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly O'Brien, David Roberts, and Barbara Smalley. O'Brien and Roberts, both MDs, were on the research faculty at the Chicago Medical School at the time; O'Brien specialized in biochemistry and molecular biology, and Roberts specialized in immunology and medical microbiology. Smalley, who has a PhD, served a department chair of the Microbiology Department at the same school. The company started as a research and development...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly...
Illinois Bio Technologies Illinois Bio Technologies (IBTECH) was founded in Rosemont, Illinois, in 1992 by Kelly O'Brien, David Roberts, and Barbara Smalley. O'Brien and Roberts, both MDs, were on the research faculty at the Chicago Medical School at the time; O'Brien specialized in biochemistry and molecular biology, and Roberts specialized in immunology and medical microbiology. Smalley, who has a PhD, served a department chair of the Microbiology Department at the same school. The company started as a research and development...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT