Technical Detail

In this model, since all variables are mutually dependent, we use iterative process to calculate the values. First, we assume the Asset to $100 million, according to the D/A rate, we can get the value of Debt. In order to calculate the systematic risk of equity βE, we need data from user’s input include systematic risk of asset βA, systematic risk of debt βD, Tax Rate and D/A Ratio. Note that the relationship between D/A and D/E is
D/E=D/A1-D/A
So, we can get systematic risk of equity by βE= βA+1-T βA- βD*(D/E)
Then, we can use CAPM to calculate rD and rE. Taxable Income is calculated as Taxable Income=Operating Income EBIT0-Debt* rD+ Distress Cost*Asset
Using the Taxable income and Tax Rate, we get Net Income by the formula
Net Income=Taxable Income*(1-Tax Rate)
Since equity of capital is assumed to be perpetuity, Equity is calculated as
Equity=Net Income/ rE
After this, we add Equity and Debt to change the value of Asset that we assumed, then we get the iterative calculation.
When calculate WACC, we use the formula
WACC= rE*EA+ rD1-T*DA+FA*(1-T)
In the working area, there is a column recorded Asset which rounded to decimals. By searching the max value of this column, we set up conditional formatting to highlight the rows which contain this largest value. At the same time, we add the function for user to backup and restore their input. The storage of input is implemented in the working area.

Under two special scenarios of Modigliani-Miller theory, one is without Taxes and the other is with Taxes. In the first case, the market value of the firm is unaffected by the taxes nor debt, we change the Tax Rate, systematic risk of debt and financial distress cost into 0 before running the model. In the second case with taxes but financial distress cost, we remain the tax rate unchanged and convert systematic risk of debt and financial