Attached is an excel spreadsheet with 4 tabs. Most of the work is done but I need help with the formulas on the blank answers and to double check what I have already completed. I have also included the answers I am trying to get to so it will be easier (hopefully) for you to determine the correct formulas. Thank you so much for your help.

Problem 18-16 of $115 million for sure one year from now. You decide to use 100% debt financing, that $90 million. The risk-free rate is 5% and the tax rate is 40%. Assume that the investment the enda.of the year,the so NPV without leverage you would owe taxes on the the APV difference between t Calculate of this investment opportunity using method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investmen Investment today (million) Amount of debt (million) Cash flow in 1 year (million) Tax rate Risk-free rate $90.00 $90.00 $115.00 40% 5.00% a. Calculate the NPV of this investment opportunity using the APV method. Answer to get to: Tax basis at year end (million) 25 Taxes owed (million) 10 FCF at year end (million) $105.00 VU (million) $100.00 Interest expense (million) 4.5 Interest tax shield (million) 1.8 PV of ITS (million) $1.71 VL (million) $101.71 NPV (million) $11.71 b. Using your answer to part (a), calculate the WACC of the project. Debt-to-value ratio WACC 0.885 3.23% c. Verify that you get the same answer using the WACC method to calculate NPV. VL (million) NPV (million) $101.71 $11.71 d. Finally, show that flow-to-equity also correctly gives the NPV of this investmen After-tax interest expense (million) FCFE (million) NPV (million) Requirements 2.7 $12.30 $11.71 To calculate the project's NPV using the APV method, you need to calculate the levered values of the project. 1. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 2. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 3. In D20, by cell unlevered references,value, calculate the free flowvalue at year endfree (1 p To cell calculate theusing project's calculate thecash present of the 4. function PV. In cell D21, by using the function PV and cell references, calculate freeD22, cashby flow (1 pt.). 5. the In cell using cell references, calculate the interest expense (1 pt.). 6. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). To calculate the present value of the interest tax shield use the function 7. function PV and cell references, calculate the present value of the interest tax sh 8. In cell D25, by using cell references, calculate the project's levered value (1 pt.) 9. In D26, by cellofreferences, net present value the AP To cell calculate theusing WACC the projectcalculate using thethe answer from part (a),using you will us 10. WACC formula: r_wacc = r_Unlevered - (Debt/Value) * Marginal tax rate * In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). 11. In D31, using calculate theWACC WACCmethod (1 pt.).to calculate the To cell verify thatbyyou getcell the references, same answer using the 12. the project's levered value by using the function PV. In cell D35, by using the fu calculate the (1approach pt.). calculate 13. references, In D36, by cellNPV references, the NPV (1 pt.).the NPV of this in To cell show that theusing flow-to-equity also correctly gives 14. you need to calculate the free cash flow-to-equity after calculating the after-tax i D40, by by using cellcell calculate thethe after-tax interest 15. cell In D41, using references, calculate free cash flow-to-equity (1 pt To cell calculate the NPV ofreferences, this investment opportunity, you need toexpense calculate the p 16. free cash flow-to-equity using the function PV. In cell D42, by using the functio references, calculate the NPV (1 pt.). % debt financing, that is, you will borrow me that the investment is fully depreciated at difference between the project cash flow ee APV method. project. hod to calculate NPV. NPV of this investment opportunity. e APV method. to get to: project. hod to calculate NPV. NPV of this investment opportunity. u need to calculate the unlevered and s at year end (1 pt.). wed (1 pt.). h flowvalue at year endfree (1 pt.). esent of the cash flow using the ll references, calculate the present value of expense (1 pt.). tax shield (1 pt.). the function PV. In cell D24, by using the e of the interest tax shield (1 pt.). s levered value (1 pt.). ent value the APV method (1 pt.). m part (a),using you will use the project-based Marginal tax rate * r_Debt. value ratio (1 pt.). (1 pt.).to calculate the NPV, first, calculate method ll D35, by using the function PV and cell pt.).the NPV of this investment opportunity, ves culating the after-tax interest expense. In nterest h flow-to-equity (1pt.). pt.). need toexpense calculate(1the present value of the 2, by using the function PV and cell Problem 18-28 Revtek, Inc., has an equity cost of capital of 12% and a debt cost of capital of 6%. Revtek constant debt-equity ratio of 0.5, and its tax rate is 35%. a. Assuming What is Revtek's WACC given its will current debt-equity no personal taxes, how Revtek's WACCratio? change if it increases its b. to 2 and its debt cost of pay capital 6%? Now suppose investors tax remains rates of at 40% on interest income and 15% on inco c. How will Revtek's WACC change if it increases its debt-equity ratio to 2 in this d. Provide an intuitive explanation for the difference in your answers to parts (b) an Cost of equity Cost of debt Debt-equity ratio Tax rate 12.00% 6.00% 0.50 35.00% a. What is Revtek's WACC given its current debt-equity ratio? Answers to get to: 0.667 0.333 Equity-to-value ratio Debt-to-value ratio WACC 9.30% Assuming no personal taxes, how will Revtek's WACC change if it increases its b. to 2 and its debt cost of capital remains at 6%? Debt to equity ratio 2.00 Equity-to-value ratio 0.333 Debt-to-value ratio 0.667 Unlevered return 10.00% WACC 8.60% Now suppose investors pay tax rates of 40% on interest income and 15% on inco c. How will Revtek's WACC change if it increases its debt-equity ratio to 2 in this Personal tax rate on dividend income Personal tax rate on interest income After-tax cost of debt WACC before change in D/E 15.00% 40.00% 4.24% 9.41% rE with higher leverage WACC after change in D/E 19.76% 9.19% d. Provide an intuitive explanation for the difference in your answers to parts (b) an When investors pay higher taxes on interest income than equity income, the tax benefit of leverage is reduced For the same increase in leverage, the decline in the WACC is smaller in the presence of investor taxes. Requirements formula entry and to have these values available for future use, you will first cal 1. to-value and debt-to-value ratios. In cell D17, by using cell references, calculate the equity-to-value ratio (1 pt.). 2. In cell D18, by using cell references, calculate the debt-to-value ratio (1 pt.). 3. In cell D19, by using cell references, calculate the company's WACC (1 pt.). 4. In cell D25, by using cell references, calculate the equity-to-value ratio (1 pt.). 5. In cell D26, by using cell references, calculate the debt-to-value ratio (1 pt.). 6. In cell D27, by using cell references, calculate the unlevered return (pretax WAC 7. In cell D28, by using cell references, calculate the company's WACC (1 pt.). 8. In cell D37, by using cell references, calculate the after-tax cost of debt (1 pt.). In cell D39, by using cell references, calculate the company's WACC (before the 9. to-equity ratio) (1 pt.). In cell D40, by using cell references, calculate the new equity cost of capital (wi 10. leverage) (1 pt.). 11. In cell D42, by using cell references, calculate the company's WACC (1 pt.). In cell D47, type either the word increased or the word reduced (1 pt.) 12. the word decline or the word rise (1 pt.). t of capital of 6%. Revtek maintains a atio? change if it increases its debt-equity ratio income and 15% on income from equity. t-equity ratio to 2 in this case? our answers to parts (b) and (c). atio? change if it increases its debt-equity ratio income and 15% on income from equity. t-equity ratio to 2 in this case? our answers to parts (b) and (c). n equity income, the in the WACC is ure use, you will first calculate the equity- ty-to-value ratio (1 pt.). -to-value ratio (1 pt.). pany's WACC (1 pt.). ty-to-value ratio (1 pt.). -to-value ratio (1 pt.). vered return (pretax WACC) (1 pt.). pany's WACC (1 pt.). -tax cost of debt (1 pt.). pany's WACC (before the change in debtequity cost of capital (with higher pany's WACC (1 pt.). reduced (1 pt.). In cell E48, type either Problem 19-3 Assuming that Ideko's market share will increase by 0.5% per year, the required producti following five years are shown below: Sales Data Market size (000 units) Market share Production volume Growt 2005 2006 2007 h/Year 5% 10,000 10,500 11,025 0.5% 10.00% 10.50% 11.00% 1,000 1,103 1,213 level by 50%), and the cost of this expansion will be $15.0 million. Assuming the financi will be delayed accordingly, calculate the projected interest payments and the amount of interest tax shields (assuming that the interest rate on the term loans is 6.8% and the corp Balance Sheet ($000) Assets Cash and Cash Equivalents Accounts Receivable Inventories Total Current Assets Property, Plant & Equipment Goodwill Total Assets Liabilities Accounts Payable Debt Total Liabilities Stockholders' Equity Total Liabilities & Stockholders' Equit Interest rate on loan Corporate tax rate Cost of the expansion ($000) 6,164 18,493 6,165 30,822 49,500 72,332 152,654 4,654 100,000 104,654 48,000 152,654 6.80% 35% 15,000 Debt and Interest ($000) 2005 2006 2007 Outstanding debt 100,000 100,000 100,000 Interest on the term loan, 6.8% -6,800 -6,800 -6,800 Interest tax shield 2,380 2,380 2,380 **The answers are correct just want to double check to make sure that the formu Requirements In cell E34, by using relative and absolute cell references, calculate the outstand 1. for the J34, year by 2005 (1 pt.). Copy cell E34 and paste it onto cells debt F34:I34 (1 year pt.) 2 In cell using cell references, calculate the outstanding for the 2. In cell E35, by using relative and absolute cell references, calculate the interest o pt.). 3. term loan for the year 2005 (1 pt.). Copy cell E35 and paste it onto cells In cell E36, by using relative and absolute cell references, calculate the interest t 4. Note that the interest on the new loan will not be applied until the following yea for the year 2005 (1 pt.). Copy cell E36 and paste it onto cells F36:J36 (1 pt.) he required production capacities for the 2008 2009 2010 11,576 12,155 12,763 11.50% 12.00% 12.50% 1,331 1,459 1,595 Assuming the financing of the expansion s and the amount of the projected s 6.8% and the corporate tax rate is 2008 2009 2010 100,000 100,000 115,000 -6,800 -6,800 -6,800 2,380 2,380 2,380 e sure that the formulas are correct. alculate the outstanding debt F34:I34 (1 year pt.). 2010 (1 gls debt for the alculate the interest on the it onto cells F35:J35 (1 pt.). alculate the interest tax shield il the following year. ls F36:J36 (1 pt.). Problem 20-6 You own a call option on Intuit stock with a strike price of $40. The option will expire in months. a. If the stock is trading at $55 in three months, what will be the payoff of the call? b. If the stock is trading at showing $35 in three months, what will the payoff the call?o Draw a payoff diagram the value of the call at be expiration as aoffunction c. at expiration. Strike Price $40.00 a. If the stock is trading at $55 in three months, what will be the payoff of the call? Spot price in 3 months $55.00 Exercise value of call $15.00 b. If the stock is trading at $35 in three months, what will be the payoff of the call? Spot price in 3 months $35.00 $0.00 Exercise value of call Draw a payoff diagram showing the value of the call at expiration as a function o c. at expiration. Exercise Spot Price ($) Value ($) 0.00 False 5.00 False 10.00 False 15.00 False 20.00 False 25.00 False 30.00 False 35.00 False 40.00 False 45.00 50.00 55.00 1 1 1 60.00 65.00 70.00 75.00 80.00 85.00 90.00 95.00 True 1 1 1 **Don't know if this is correct at all. The only area I n 1 Spot price, exercise value and the D-Line diagram. 1 1 1 1 Requirements 1. In cell E15, by using cell references, calculate the amount that you will owe (1 p 2. In cell E21, by using cell references, calculate the exercise value of the call (1 p you need to generate a table of exercise values as a function of the stock prices a 3. stock in cellthe C26 (1 pt.).IF Inand cellabsolute C27, by and using cell references, increas In cellprice D26,ofby0 using function relative cell references, c 4. stock prices on the horizontal axis and the exercise value on the vertical axis price is greater than the strike price; if true, input the difference between the stri 5. label, input Stock Price at Expiration ($) (1 pt). As the vertical label, input the chart title, input Exercise Value of a Call Option at Expiration (1 pt.) The option will expire in exactly three be the payoff of the call? be the payoff the call?of the stock price expiration as aoffunction be the payoff of the call? be the payoff of the call? expiration as a function of the stock price is correct at all. The only area I need help on with this question is the alue and the D-Line diagram. unt that you will owe (1 pt.). cise value of the call (1 pt.). ction of the stock prices at expiration. Start with a ng cell references, increase the previous stock price relative cell references, compare whether the stock ue on the vertical axis (1 pt.). As the horizontal ifference between the strike price and the stock he vertical label, input Exercise Value ($) (1 pt.). As at Expiration (1 pt.). If necessary, resize the chart