Reference no: EM133542643

Assignment

Question I. The current risk-free rate of interest is 3% and the expected return on the market is 10%.

i. Using EXCEL's Data Table feature create a two-way data table to determine the cost of equity by varying both Beta and the risk-free rate of interest. Use Beta values of 0.70, 0.85, 1.00, 1.15, 1.30, and 1.45, and risk-free interest rate values of 4%, 5%, 6%, 7%, and 8%.

ii. Explain how the cost of equity is impacted by:

a. A change in Beta
b. A change in the risk-free interest rate.

Question II. A rapidly growing firm is currently paying a dividend of $2.50. The annual dividend growth rate is expected to be 8% for the next year, then 6% for the next 2 years, then 4% for the next 3 years, and 3% thereafter. The expected return on the market is 7%, the risk-free rate is 2% and the firm's Beta is 1.25.

i. Calculate the estimated price (intrinsic value) for a share of this firm's stock.
ii. If an analyst uses an8% rule, is this stock overvalued, undervalued, or fairly priced if the current stock price is $57?
iii. Using EXCEL's Text Box Feature explain the purpose of employing a8% rule in this valuation process?

Question III. The Excel file Stock Return Datacontains monthly data for several stocks and the S&P 500 Index (i.e., the market).

i. Compute the Beta for Dell using the Variance-Covariance relationship.
ii. Compute the annualized return on a Compounded Basisfor Dell.
iii. Compute the annual standard deviation for Dell.

Question IV. The Excel file Stock Return Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).

i. Compute the equation of the Characteristic Line for Cin.
ii. Explain what the intercept term in this equation measures relative to the market return.
iii. Explain what the slope term in this equation measures relative to the market return.
iv. Conduct a complete hypothesis test to determine if ?Significant.
v. Conduct a complete hypothesis test to determine if β is Significant.
vi. Compute the R2 value associated with this equation.
vii. Explain what R2 measures for this equation.
viii. Construct a graph of the Characteristic Line for Cin.

a. On this graph (using EXCEL's graphing features) include a trendline, the computed equation, and the R2 value.
b. Label the axes and include a title for the graph.

Question V. An investment has the following cash flows:

May22, 2015 $ -1600
January20, 2016 $ +1200
August9, 2017 $ +800
February10, 2018 $ +700
July27, 2019 $ +900
December15, 2020 $ +200
June24, 2021 $ -2400

i. Calculate the Net Present Value of this investment. Assume the annual discount rate is 8%.

ii. Create a data table and graph illustrating the impact of the discount rate on the Net Present Value of this investment. Include a title and label the axes for the graph.

iii. Use Solver to determine the Internal Rate of Return(s) associated with this investment.

Question VI. Suppose the Price/Earnings Ratio for the S&P 500 is 21 and the dividend payout ratio of the S&P 500 is 45%. The future growth rate of dividends is expected to be 4.75%.

i. Use Goal Seek or Solver to determine the dividend growth rate that would yield an expected Market return of 8%.

Question VII. An investment has a cost of $5600. The investment will have a payout of X at the end of the first year. This initial payout X will grow at the rate of 8% per year for the next 2 years, then by 6% per year for the next 4 years,then by 5% for the next 2 years, and then at the rate of 3% per year for the following 3 years. You believe the riskiness of this investment is 10%.

i. Calculate the smallest X that would entice you to invest.

Question VIII. You are considering buying a risky bond. The bond has a $1,000 face value, a 1-year maturity, and a coupon rate of 14%. Coupon payments are made annually. You believe the probability the company will survive to pay off the bond is 55%. You also believe there is a 45% probability the company will default within 2months,in which case you will be able to recover 36% of the bond's face value at the end of year 1. The bond is selling for $725.

i. Calculate the expected return on this bond.
ii. Use Goal Seek or Solver to determine the probability of survival that would yield an expected return of 12%.

Question IX. An employee plans to investin a retirement fund at thebeginning of each of the next 15 years. The employee will initially invest $30,000 in the first year and increase the annual investment amount by 4% in each subsequent year. The employee believes she will earn 12% annually on her investments in the first 2 years, then 9% annually in the next 4 years,then 7% annually in the next 3 years, and then 4% annually in each of the final6 years before she retires.

i. How much money will the employee have in the retirement fund when she retires?
ii. What would be the internal rate of return associated with her investments and her final retirement position?

Question X. A college savings education plan claims that had you invested $900quarterly in the plan for the last 25 years you would have accumulated $450,000. Assuming the investments were made at the beginning of each quarterly period, calculate the effective compound annual rate of return an investor would have received using either Goal Seek or Solver.

Question XI. This question relates to the computation of a firm's cost of equity.

i. Assume the current selling price of a firm's stock is: $125. Using the Excel file Dividend Data, compute the firm's annualized cost of equity using the Gordon dividend model.

Question XII. Compute the Present Value of the following cash flows using a continuously compounded annual interest rate of 8.0%.

Year Cash Flows
0.85 500
1.30 745
2.45 465
3.75 675
4.60 300
5.28 800

Question XIII. Suppose at time 0 you had $3500 in the bank and 21 years later you had $12,500. Use Solver to calculate the effective annual rate of return if the bank pays interest continuously.

Question XIV. If today is May 18, 2016, and the cost of capital r = 5%, what is the net present value of the following cash flows as of May 18, 2016:

06/25/17 $-1,200
08/08/18 $ +700
10/22/19 $ +2200
06/14/20 $ +990
08/18/21 $ +685
06/25/22 $ +845

Question XV. As discussed in class and using EXCEL's Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

i. Security Market Line

Question XVI. As discussed in class and using EXCEL's Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

i. Characteristic Line