Using the Capital Asset Pricing Mode and SLOPE Function Questions

Question Description

I’m working on a finance case study and need support to help me study.

This assignment is a capital budgeting case with an extra twist in which you also need to figure out the firm’s cost of capital. This involves a step-by-step process in which you will calculate the cost of debt, the cost of equity, and the weights of debt and equity. (Note that the firm uses short-term debt in the form of a revolving line of credit, which has a particular interest rate, as well as long-term debt, which has a different yield.) Please show your work in an excel spreadsheet that uses cell references and formulas.

In calculating the cost of equity, you can use CAPM. You will need to find beta, which can be done by regressing the returns for the company on the market returns over the same period. That is, the company’s returns would be the dependent variable, and the market returns will be the independent variable. The slope of this regression is the beta for the company, similar to slides 19 & 20 in the Risk_Return slides. You can find the beta using a shortcut in excel, with the SLOPE function. You can look up the slope function (=slope) in excel for the exact inputs (which are the company returns and the market returns, which are given in the case). The slope calculated in excel will be the beta to be used in CAPM to find the cost of equity. Here is the case:

Check the attached file

1. Using the Capital Asset Pricing Mode, what is the required rate of return on equity, re (cost of equity) for Tortuga? (Hint: Use the slope function in excel to find beta.)

2. Analyzing the company’s bond, what is the yield to maturity on the bond issue, rd (cost of debt)?

3. Using the market weight of equity (i.e., based on the market value of equity), the original issue amount of debt, and the outstanding portion of the revolving line of credit, what are the weights of equity and debt in the capital structure (we & wd)?

4.Using the information provided, what is the firm’s weighted average cost of capital (WACC)? 5.What are the net present value (NPV) and internal rate of return (IRR) for Projects A& B?

6. What do you suggest to Tortuga?