Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I am not sure about the rest of them, could you plz give a specific Excel code with the following function in the second graph?
I am not sure about the rest of them, could you plz give a specific Excel code with the following function in the second graph? Thank you.
Problem 16-1 Gladstone Corporation is about to launch a new product. Depending on the success of the new product, Gladstone may have one of four values next year: $150 million $135 million, $95 million and $80 million. These outcomes are all equally likely, and this risk is diversifiable. Gladstone will not make any payouts to investors during the year. Suppose the risk-free interest rate is 5% and assume perfect capital markets. Complete the steps below us ing cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. What is the initial value of Gladstone's equity without leverage? Now suppose Gladstone has zero-coupon debt with a $100 million face value due next year. b. What is the initial value of Gladstone's debt? c. What is the yield-to-maturity of the debt? What is its expected return? d. What is the initial value of Gladstone's equity? What is Gladstone's total value with leverage? Total to all investors (million) Probability Outcome 1 Outcome 2 $150.00 $135.00 25% 25% Outcome 3 $95.00 25% Outcome 4 $80.00 25% Risk-free interest rate 5% Face value of debt (million) $100.00 Outcome 1 Outcome 2 Outcome 3 Outcome 4 Weighted equity value (million) Debt value (million) Weighted debt value (million) Equity value with leverage (million) Weighted equity value with leverage (million) a. What is the initial value of Gladstone's equity without leverage? $115.00 Weighted average equity (million) Initial value of equity without leverage (million) $109.52 Now suppose Gladstone has zero-coupon debt with a $100 million face value due next year. b. What is the initial value of Gladstone's debt? S89.29 Weighted average debt (million) Most likely value of debt (million) c. What is the yield-to-maturity of the debt? What is its expected return? Promised return (YTM) Expected return of debt 12.00% 5.00% d. What is the initial value of Gladstone's equity? What is Gladstone's total value with leverage? $20.24 Weighted average equity with leverage (million) Most likely value of equity with leverage (million) Total value with leverage (million) $109.52 Requirements 1. Start Excel - completed. 2. To calculate the company's most likely equity value without leverage you will need to calculate the weighted average of the equity values without leverage under the four scenarios. In cell D21, by using cell references, calculate the weighted equity value without leverage under the first scenario by multiplying the equity value (total to all investors) by the probability of the outcome (1 pt.). Copy cell D21 and paste it onto cells E21:G21 (1 pt.). 3. In cell D29, by using cell references and the function SUM, calculate the weighted average equity value without leverage (1 pt.). 4. In cell D31, by using cell references and the function PV, calculate the present value of the weighted average equity value without leverage (1 pt.) Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average equity value from Step 3 in your calculations. 5. To calculate the company's most likely debt value you will need to compute the weighted average of the debt values under the four scenarios. For each possible outcome, the debtholders of the company will receive either the face value of the debt or the total value of the company, whichever is lower. In cell D22. by using cell references and the function MIN, calculate the minimum between the face value of the debt and the total to all investors (1 pt.). Copy cell D22 and paste it onto cells E22:G22 (1 pt.). 6. In cell D23, by using cell references, calculate the weighted debt value under the first scenario by multiplying the debt value by the probability of the outcome (1 pt.). Copy cell D23 and paste it onto cells E23:G23 (1 pt.). 7. In cell D36, by using cell references and the function SUM, calculate the weighted average debt value (1 pt.). 8. In cell D37, by using cell references and the function PV, compute the present value of the weighted average debt value (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average debt value from Step 7 in your calculations. 9. In cell D41, by using cell references and the function RATE, calculate the yield-to-maturity of the debt (1 pt.). Note: The PV argument of the function RATE is expected as a negative value. Do not enter any value for the Guess argument of the function RATE. 10. In cell D42, by using cell references and the function RATE, calculate the expected return of the debt (1 pt.). The expected retum of debt is determined by finding the rate that will equate the most likely value of debt today with the weighted average of debt in one year. Note: The PV argument of the function RATE is expected as a negative value. Do not enter any value for the Guess argument of the function RATE. 11. To calculate the company's most likely equity value with leverage, you will first need to calculate the weighted average of the equity values with leverage under the four scenarios. In cell D24, by using cell references, calculate the equity value with leverage for the first scenario (1 pt.). Copy cell D24 and paste it onto cells E24:G24 (1 pt.) 12. In cell D25, by using cell references, calculate weighted equity value with leverage by multiplying the equity value with leverage by the probability of the outcome (1 pt.). Copy cell D25 and paste it onto cells E25:G25 (1 pt.). 13. In cell D46, by using cell references and the function SUM, calculate the weighted average equity value with leverage (1 pt.). 14. In cell D48, by using cell references and the function PV, compute the present value of the weighted average equity value with leverage (1 pt.) Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average equity value with leverage from Step 13 in your calculations. 15. In cell D49, by using cell references, calculate the most likely total value of the company with leverage (1 pt.). Note: Refer to the values from Step 8 and 14 in your calculations. 16. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Problem 16-1 Gladstone Corporation is about to launch a new product. Depending on the success of the new product, Gladstone may have one of four values next year: $150 million $135 million, $95 million and $80 million. These outcomes are all equally likely, and this risk is diversifiable. Gladstone will not make any payouts to investors during the year. Suppose the risk-free interest rate is 5% and assume perfect capital markets. Complete the steps below us ing cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. What is the initial value of Gladstone's equity without leverage? Now suppose Gladstone has zero-coupon debt with a $100 million face value due next year. b. What is the initial value of Gladstone's debt? c. What is the yield-to-maturity of the debt? What is its expected return? d. What is the initial value of Gladstone's equity? What is Gladstone's total value with leverage? Total to all investors (million) Probability Outcome 1 Outcome 2 $150.00 $135.00 25% 25% Outcome 3 $95.00 25% Outcome 4 $80.00 25% Risk-free interest rate 5% Face value of debt (million) $100.00 Outcome 1 Outcome 2 Outcome 3 Outcome 4 Weighted equity value (million) Debt value (million) Weighted debt value (million) Equity value with leverage (million) Weighted equity value with leverage (million) a. What is the initial value of Gladstone's equity without leverage? $115.00 Weighted average equity (million) Initial value of equity without leverage (million) $109.52 Now suppose Gladstone has zero-coupon debt with a $100 million face value due next year. b. What is the initial value of Gladstone's debt? S89.29 Weighted average debt (million) Most likely value of debt (million) c. What is the yield-to-maturity of the debt? What is its expected return? Promised return (YTM) Expected return of debt 12.00% 5.00% d. What is the initial value of Gladstone's equity? What is Gladstone's total value with leverage? $20.24 Weighted average equity with leverage (million) Most likely value of equity with leverage (million) Total value with leverage (million) $109.52 Requirements 1. Start Excel - completed. 2. To calculate the company's most likely equity value without leverage you will need to calculate the weighted average of the equity values without leverage under the four scenarios. In cell D21, by using cell references, calculate the weighted equity value without leverage under the first scenario by multiplying the equity value (total to all investors) by the probability of the outcome (1 pt.). Copy cell D21 and paste it onto cells E21:G21 (1 pt.). 3. In cell D29, by using cell references and the function SUM, calculate the weighted average equity value without leverage (1 pt.). 4. In cell D31, by using cell references and the function PV, calculate the present value of the weighted average equity value without leverage (1 pt.) Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average equity value from Step 3 in your calculations. 5. To calculate the company's most likely debt value you will need to compute the weighted average of the debt values under the four scenarios. For each possible outcome, the debtholders of the company will receive either the face value of the debt or the total value of the company, whichever is lower. In cell D22. by using cell references and the function MIN, calculate the minimum between the face value of the debt and the total to all investors (1 pt.). Copy cell D22 and paste it onto cells E22:G22 (1 pt.). 6. In cell D23, by using cell references, calculate the weighted debt value under the first scenario by multiplying the debt value by the probability of the outcome (1 pt.). Copy cell D23 and paste it onto cells E23:G23 (1 pt.). 7. In cell D36, by using cell references and the function SUM, calculate the weighted average debt value (1 pt.). 8. In cell D37, by using cell references and the function PV, compute the present value of the weighted average debt value (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average debt value from Step 7 in your calculations. 9. In cell D41, by using cell references and the function RATE, calculate the yield-to-maturity of the debt (1 pt.). Note: The PV argument of the function RATE is expected as a negative value. Do not enter any value for the Guess argument of the function RATE. 10. In cell D42, by using cell references and the function RATE, calculate the expected return of the debt (1 pt.). The expected retum of debt is determined by finding the rate that will equate the most likely value of debt today with the weighted average of debt in one year. Note: The PV argument of the function RATE is expected as a negative value. Do not enter any value for the Guess argument of the function RATE. 11. To calculate the company's most likely equity value with leverage, you will first need to calculate the weighted average of the equity values with leverage under the four scenarios. In cell D24, by using cell references, calculate the equity value with leverage for the first scenario (1 pt.). Copy cell D24 and paste it onto cells E24:G24 (1 pt.) 12. In cell D25, by using cell references, calculate weighted equity value with leverage by multiplying the equity value with leverage by the probability of the outcome (1 pt.). Copy cell D25 and paste it onto cells E25:G25 (1 pt.). 13. In cell D46, by using cell references and the function SUM, calculate the weighted average equity value with leverage (1 pt.). 14. In cell D48, by using cell references and the function PV, compute the present value of the weighted average equity value with leverage (1 pt.) Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the weighted average equity value with leverage from Step 13 in your calculations. 15. In cell D49, by using cell references, calculate the most likely total value of the company with leverage (1 pt.). Note: Refer to the values from Step 8 and 14 in your calculations. 16. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directedStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started