求帮写excel金融学作业,在附件中,还剩两到三天,大神们求帮助!!!!!请用excel函数算,谢谢大佬们了!!!!
不太会传附件,如果需要excel原件的话留邮箱我马上发
高额悬赏!
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr] Question 2 (20 pionts) | Youare interested in purchasing the common stock of Azure Corporation. The firmrecently paid a dividend of $2.80 per share. It expects its earnings—andhence its dividends—to grow at a rate of 6.8% for the foreseeable future.Currently, similar-risk stocks have required returns of 7.2%. | To Do: | a. Given the data above, calculatethe present value of this security. Use the constant-growth model to find thestock value.
b. One year later,your broker offers to sell you additional shares of Azure at $84.
The most recent dividend paid was $2.92, and the expected growth rate forearnings remains at 6.8%. If you determine that the appropriate risk premiumis 7.02% and you observe that the risk-free rate, RF, is currently 4.98%,what is the firm’s current required return, rAzure?
c. Applyingconstant-growth model, determine the value of the stock using the newdividend and required return from part b.
d. Given yourcalculation in part c, would you buy the additional shares from your brokerat $73 per share? Explain. |
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr] Question 3 (20 pionts) | Jane is considering investingin three different stocks or creating three distinct twostock portfolios.Jane considers herself to be a rather conservative investor. She is able toobtain forecasted returns for the three securities for the years 2013 through2019. The data are as follows: | | Year | Stock A | Stock B | Stock C | | | 2013 | 10% | 10% | 12% | | | 2014 | 13% | 11% | 14% | | | 2015 | 15% | 8% | 10% | | | 2016 | 14% | 12% | 11% | | | 2017 | 16% | 10% | 9% | | | 2018 | 14% | 15% | 9% | | | 2019 | 12% | 15% | 10% | | Inany of the possible two-stock portfolios, the weight of each stock in theportfolio will be 50%. The three possible portfolio combinations are AB, AC,and BC. | To Do: | Createa spreadsheet similar to Tables 8.6 and 8.7 in Chapter 8 to answer the following:
a. Calculate theexpected return for each individual stock.
b. Calculate thestandard deviation for each individual stock.
c. Calculate theexpected returns for portfolio AB, AC, and BC.
d. Calculate thestandard deviations for portfolios AB, AC, and BC.
e. Would yourecommend that Jane invest in the single stock A or the portfolio consistingof stocks A and B? Explain your answer from a risk–return viewpoint.
f. Would yourecommend that Jane invest in the single stock B or the portfolio consistingof stocks B and C? Explain your answer from a risk–return viewpoint. |
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr] Question 4 (20 pionts) | NovaCorporation is interested in measuring the cost of each specific type ofcapital as well as the weighted average cost of capital. Historically, thefirm has raised capital in the following manner: | | Source of Capital | Weight | | | | | Long-TermDebt | 35% | | | | | Preferred Stock | 12% | | | | | Commom Stock Equity | 53% | | | | Thetax rate of the firm is currently 40%. The needed financial information anddata are as follows:
Debt Nova can raise debt byselling $1,000-par-value, 6.5% coupon interest rate, 10-year bonds on whichannual interest payments will be made. To sell the issue, an average discountof $20 per bond needs to be given. There is an associated flotation cost of 2%of par value.
Preferred stockPreferred stock can be sold under the following terms: The security has a parvalue of $100 per share, the annual dividend rate is 6% of the par value, andthe flotation cost is expected to be $4 per share. The preferred stock isexpected to sell for $102 before cost considerations.
Common stock Thecurrent price of Nova’s common stock is $35 per share. The cash dividend isexpected to be $3.25 per share next year. The firm’s dividends have grown atan annual rate of 5%, and it is expected that the dividend will continue atthis rate for the foreseeable future. The flotation costs are expected to beapproximately $2 per share. Nova can sell new common stock under theseterms.
Retained earnings The firm expects to haveavailable $100,000 of retained earnings in the coming year. Once theseretained earnings are exhausted, the firm will use new common stock as theform of common stock equity financing. (Note: When measuring this cost, thefirm does not concern itself with the tax bracket or brokerage fees ofowners.) | To Do: | Createa spreadsheet to answer the following questions:
a. Calculate the after-taxcost of debt.
b. Calculate the costof preferred stock.
c. Calculate the costof retained earnings.
d. Calculate the costof new common stock.
e. Calculate thefirm’s weighted average cost of capital using retained earnings and thecapital structure weights shown in the table above.
f. Calculate thefirm’s weighted average cost of capital using new common stock and thecapital structure weights shown in the table above. |
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr] Question 5 (20 pionts) | TheDrillago Company is involved in searching for locations in which to drill foroil.The firm’s current project requires an initial investment of $15 millionand has an estimated life of 10 years. The expected future cash inflows forthe project are as shown in the following table: | | Year | Cash Inflows |
| 1
| $600,000.00 | | 2 | $1,000,000.00 | | 3 | $1,000,000.00 | | 4 | $2,000,000.00 | | 5 | $3,000,000.00 | | 6 | $3,500,000.00 | | 7 | $4,000,000.00 | | 8 | $6,000,000.00 | | 9 | $8,000,000.00 | | 10 | $12,000,000.00 | The firm’s current cost of capital is 13%. | To Do: | Createa spreadsheet to answer the following:
a. Calculate the project’snet present value (NPV). Is the project acceptable under the NPV technique?Explain.
b. Calculate theproject’s internal rate of return (IRR). Is the project acceptable under theIRR technique? Explain.
c. In this case, didthe two methods produce the same results? Generally, is there a preferencebetween the NPV and IRR techniques? Explain.
d. Calculate thepayback period for the project. If the firm usually accepts projectsthat havepayback periods between 1 and 7 years, is this project acceptable? |
|