PLEASE SHOW THE FORMULAS USED IN EXCEL TO GET THESE NUMBERS

PLEASE SHOW THE FORMULAS USED IN EXCEL TO GET THESE NUMBERS

Higgs Bassoon Corporation is a custom manufacturer of bassoons and other wind instruments. Its current value of operations, which is also its value of debt plus equity, is estimated to be $200 million. Higgs has $110 million face value, zero coupon debt that is due in 3 years. The risk-free rate is 5%, and the standard deviation of returns for similar companies is 60%. The owners of Higgs Bassoon view their equity investment as an option and would like to know the value of their investment. a. Black-Scholes Option Pricing Model Total Value of Firm 200.00 tk Updated now to $110 pperations Face Value of Debt 110.00 face value Risk Free rate 5% Maturity of debt (years) 3.00 Standard Dev. 0.45 this is sigma--also known as volatility di 1.3492 use the formula from the text d2 0.5698 use the formula from the text N(d) 0.9114 use the Normsdist function in the function wizard Nd)) 0.7156 Call Price = Equity Value $ 114.52 million b. Debt value = Total Value - Equity Value = Debt yield = S 85.48 million 8.771% c. Equity value at 60% volatility Equity value at 45% volatility Percent change Using 0.6 St. Dev the 123.63000 m D14 shows the call price 114.52260 m of $123.63 milion -7.4% m 0.1 0.1 50 0. 0.0 d. Cost of Debt Face Value of DE 8.771% hint: use a data table 100 5.13% 20 5.15% 301 5.22% 40 5.38% 5.64% 60 5.99% 70 6.42% 80 6.92% 90 7.49% 100 8.11% 110 8.77% 120 9.47% 130 10.20% 1401 10.96% 150 11.74% 12.54% Yield on Debt 0.0 0.0 0.0 160 e. $ S Value of Debt Volatility Face Value of Debt 85.48 110.00 0.1 86.0708 0.2 85.9606 0.3 84.6060 0.4 81.3418 0.5 76.6861 0.6 71.2325 0.7 65.3983 0.8 59.4584 0.9 53.5949 Value of Equit Volatility Face Value of 114.52 110.00 0.1 113.9292 0.2 114.0394 0.3 115.39401 0.4 118.6582 0.5 123.3139 0.6 128.7675 0.7 134.6017 0.8 140.5416 0.9 146.4051 Higgs Bassoon Corporation is a custom manufacturer of bassoons and other wind instruments. Its current value of operations, which is also its value of debt plus equity, is estimated to be $200 million. Higgs has $110 million face value, zero coupon debt that is due in 3 years. The risk-free rate is 5%, and the standard deviation of returns for similar companies is 60%. The owners of Higgs Bassoon view their equity investment as an option and would like to know the value of their investment. a. Black-Scholes Option Pricing Model Total Value of Firm 200.00 tk Updated now to $110 pperations Face Value of Debt 110.00 face value Risk Free rate 5% Maturity of debt (years) 3.00 Standard Dev. 0.45 this is sigma--also known as volatility di 1.3492 use the formula from the text d2 0.5698 use the formula from the text N(d) 0.9114 use the Normsdist function in the function wizard Nd)) 0.7156 Call Price = Equity Value $ 114.52 million b. Debt value = Total Value - Equity Value = Debt yield = S 85.48 million 8.771% c. Equity value at 60% volatility Equity value at 45% volatility Percent change Using 0.6 St. Dev the 123.63000 m D14 shows the call price 114.52260 m of $123.63 milion -7.4% m 0.1 0.1 50 0. 0.0 d. Cost of Debt Face Value of DE 8.771% hint: use a data table 100 5.13% 20 5.15% 301 5.22% 40 5.38% 5.64% 60 5.99% 70 6.42% 80 6.92% 90 7.49% 100 8.11% 110 8.77% 120 9.47% 130 10.20% 1401 10.96% 150 11.74% 12.54% Yield on Debt 0.0 0.0 0.0 160 e. $ S Value of Debt Volatility Face Value of Debt 85.48 110.00 0.1 86.0708 0.2 85.9606 0.3 84.6060 0.4 81.3418 0.5 76.6861 0.6 71.2325 0.7 65.3983 0.8 59.4584 0.9 53.5949 Value of Equit Volatility Face Value of 114.52 110.00 0.1 113.9292 0.2 114.0394 0.3 115.39401 0.4 118.6582 0.5 123.3139 0.6 128.7675 0.7 134.6017 0.8 140.5416 0.9 146.4051