Stone Wood Products has a capital structure of 35 percent debt and 65 percent common equity. The managers consider this mix to be optimal and want to maintain it in the future. Net income for the coming year is expected to be $1.2 million dollars. Duke Mantee, the loan officer at the local bank, has set up the following schedule for Stone Wood Products borrowings. There are 40,000 shares of common stock outstanding. The firms interest subsidy tax rate is 40 percent.

I need help with this spread sheet, and I need all the equations used.

PROBLEM 9-26 Stone Wood Products Cost of Capital Given: 35% debt 65% equity Optimal Capital Structure Net income for the coming year$1,200,000 and Interest RatesAmount BorrowedInterest Rate 10% 12% 0 to $750,000 over $750,000 Stock price D1 $50 $5 for the coming year 9% 8% of the market price FI %; Tax rate 40% Investment Opportunities $500,000 $1,600,000 $600,000 $1,500,000 16% 12% 15% 18% a. Component costs of capital: After-tax cost of debt, ATkd (1) Aftertax cost of debt, ATkd (2) Cost of existing equity, ks Cost of new equity, kn per AT kd - BT kd (1-TR) up to $750,000 borrowed per AT kd = BT kd(1-TR) if over $750,000 borrowed per the dividend growth model per the dividend growth model b. MCC break points: Debt break point: based on 35% debt financing Equity break point: Net Income RE available Break point- so based on 65% equity financing c. MCC figures: MCC up to 1st break point (equity) MCC between 1st & 2nd break points MCC after 2nd break point 0.00% 0.00% 0.00% new stock issued expensive debt and new stock d. Investment Opportunity Schedule (IOS): RR $0