Hello, I need help having this in excel spread sheet. I have the answers but I got a 0 because I didnt know how to calculate it on a spreadsheet. Is there any way you could do so and put it as an attachment. ONLY HELP WITH EXCEL PORTION

Answers:

Answer 1.

Initial Price = $100 Dividend = $2.00 Ending Price = $125

Total Return = (Ending Price + Dividend Initial Price) / Initial Price Total Return = ($125 + $2 - $100) / $100 Total Return = 0.27 = 27.00%

Dividend Yield = Dividend / Initial Price Dividend Yield = $2.00 / $100 Dividend Yield = 0.02 = 2.00%

Capital Gain = (Ending Price Initial Price) / Initial Price Capital Gain = ($125 - $100) / $100 Capital Gain = 0.25 = 25.00%

Answer 2.

Initial Price = $100 Dividend = 4%*$100 = $4.00 Ending Price = $120

Total Return = (Ending Price + Dividend Initial Price) / Initial Price Total Return = ($120 + $4 - $100) / $100 Total Return = 0.24 = 24.00%

Answer 3.

Beta = 1.20 market rate of return = 12% risk-free rate = 5%

Rate of return = risk-free rate + beta*(market rate of return risk-free rate) Rate of return = 0.05 + 1.20*(0.12 0.05) Rate of return = 0.134 = 13.40%

Answer 4.

Weight of Common Stock = 80% Weight of Debt = 20% Cost of Equity = 12%

Before-tax Cost of Debt = 7% After-tax Cost of Debt = 7%*(1-0.30) After-tax Cost of Debt = 4.90%

WACC = Weight of Common Stock*Cost of Equity + Weight of Debt* After-tax Cost of Debt WACC = 80%*12% + 20%*4.90% WACC = 10.58%

Purpose of Assignment The purpose of this assignment is to allow the student an opportunity to calculate the rate of return of equity and debt instruments. It allows the student to understand the effects of dividends; capital gains; inflation rates; and how the nominal rate of return affects valuation and pricing. The assignment also allows the student to apply concepts related to CAPM WACC, and Flotation Costs to understand the influence of debt and equity on the company's capital structure Assignment Steps Resources: Corporate Finance Calculate the following problems in Excel and provide an overall summary of how companies make financial decisions in no less than 700 words, based on your answers: 1. Stock Valuation: A stock has an initial price of $100 per share, paid a dividend of $2.00 2. Total Return: You bought a share of 4% preferred stock for $100 last year. The market 3. CAPM: A stock has a beta of 1.20, the expected market rate of return is 12%, and a risk 4. WACC: The Corporation has a targeted capital structure of 80% common stock and 20% 5. Flotation Costs: Medina Corp. has a debt-equity ratio of .75. The company is considering per share during the year, and had an ending share price of $125. Compute the percentage total return, capital gains yield, and dividend yield price for your stock is now $120. What was your total return for last year? free rate of 5 percent. What is the expected rate of return of the stock? debt. The cost of equity is 12% and the cost of debt is 7%. The tax rate is 30%. What is the company's weighted average cost of capital (WACC)? a new plant that will cost $125 million to build. When the company issues new equity, it incurs a flotation cost of 10%. The flotation cost on new debt is 4%. What is the initial cost of the plant if the company raises all equity externally