MODEL BUILDER 5.1: Merton Model

Given the complexity of structural models, we will put together a fairly basic version where we iterate to find many of the key variables, and move toward a model where the variables are calculated using market information.

1. Open a new workbook in Excel and save it as MB5.1_User. A complete version of this Model Builder can be found on the website. We recommend using the proxy data in the complete version to check your methods. Starting in B6, we will enter the inputs from the company's balance sheet that we need to implement the model. In B6, enter the date of the first date that you wish to model where you have the full corporate balance sheet. Generally, this is taken from corporate filings such as a 10-K or 10-Q in the United States. Fill these in for each date at which information is available, in cells B6 for the oldest, then in B7 and B8, and so on.
2. In C6 enter the short-term liabilities, generally defined as debt and other liabilities that are due within one year. We consider all of the other liabilities of the firm to be long term, and enter the long-term liabilities in D6. In E6, we will enter the number of shares outstanding (not the shares authorized, which will be listed in the financials but is not pertinent) at the first date. For all of these fields, we will fill down with data for each period as we did with the dates in column B.
3. Below the accounting information, we will enter the market information we need to implement the model. In B20 we will enter the earliest date that we are using market data, and in C20 the stock price. Make sure that the stock price is adjusted for splits (for more discussion on adjusting stock prices, refer to Chapter 6). In D20 enter the one-year risk-free rate. Fill these values down until the current time is reached.

   Additionally, in column E enter the returns for the benchmark for this stock such as the S&P 500. We may use this to calculate an expected return through the Capital Asset Pricing Model (often abbreviated CAPM).

   Above this, in E17, we will count the number of periods. Enter the formula “=COUNT(B20:B1000)”. We will reference this later. See Figure 5.4.

4. For the final set of inputs, enter the barrier threshold, and our default costs numbers. In I4 enter the short-term liabilities component of the barrier level. In the initial Merton model, 100 percent was used, so enter that value in I4. In I5 enter the long term liabilities component of 50 percent, which was the value Merton originally used. There is nothing sacred about these levels: Merton chose them initially because they made intuitive sense. Higher short-term liabilities are more critical to solvency since the company has less time to make the payments.

   Below this, enter a cost of default and impair asset assumption in I6. This does not have an impact on the probability of default; it is solely for calculations around the recovery rate. At default, a company may have expenses that can run into the tens of millions of dollars. In addition, certain assets on the company's balance sheet such as goodwill and brand intangibles may be permanently lost.

5. Now that we have all of the key inputs, calculate the key values for model implementation: the equity valuation, the face amount of liabilities, and the default barrier. In F20 use an index-match or vlookup function in order to get the correct number of shares outstanding for each period. For simplicity, we will assume that the share count stays constant all period, rising at the next report date. In F20 enter:

   =INDEX($E$6:$E$13,MATCH(B20,$B$6:$B$13,1))

   With this value we can calculate the market value of the equity in each period in column G: in G20 enter “=F20^*C20”. Extend these formulas down.

   FIGURE 5.4 Model Builder 5.1 after step 3. This proxy data is available on the website for this text.

   

6. We will use similar formulas to track the face value of the company's liabilities and the default barrier. In H20 enter the following:

   =INDEX($C$6:$C$13,MATCH(B20,$B$6:$B$13,1))

   +INDEX($D$6:$D$13,MATCH(B20,$B$6:$B$13,1))

   This adds short-term and long-term liabilities together to get the face value of all liabilities. In I20 calculate the value of the default barrier by including the weightings in I4 and I5 into the equation.

   =$I$4*INDEX($C$6:$C$13,MATCH(B20,$B$6:$B$13,1))

   +$I$5*INDEX($D$6:$D$13,MATCH(B20,$B$6:$B$13,1))

   Extend these formulas down.

7. Now we have all of the variables that we need for the calculation of the asset value V and the asset volatility σ_V. Since the calculation of these values through a direct solution to equations 5.2 and 5.3 is extremely difficult, we will start with an initial guess for the asset values and the volatility, then iterate until we converge on a solution. In column J we will start with an initial guess of the asset value that is simply the sum of the liabilities and the market value of the equity. In J20, enter “=G20+H20” and extend down.
8. We are going to write a short macro to help us with our iteration, which will be performed in columns K and M. Start by taking the initial guess of the asset values in column J and finding what asset volatility σ_V is implied by the asset values. Then, plug that asset volatility number into the Black-Scholes equation to get the next iteration of the asset values. We will continue to iterate the two variables until the sum of the squared errors between the two iterations drops well below 1. Open up the VBE window with ALT + F11 and insert a new module. In the module enter the following code:

   Sub iterateMerton()
       Range(“K20:K1000”).Value = Range(“J20:J1000”).Value
       Do While Range(“sumSquaredErrors”) > 10 ^ −4
           Range(“K20:K1000”).Value = Range(“M20:M1000”).Value
       Loop
   End Sub

   Note that we haven't created the range “sumSquaredErrors” yet; we will do this in a few steps. For now, copy the cells in J20:J1000 and paste values in K20:K1000 so we have values for the first step. This isn't necessary but will help you visualize how the spreadsheet will work.

9. In column L we will calculate the daily returns of the assets in order to calculate volatility. We leave L20 empty and in L21 we enter the formula “=LN(K21/K20)”. We extend this down and above the column in L18 we enter our calculation for an annualized volatility (assuming 250 trading days in a year) of “=STDEV(L21:L1000)^*SQRT(250)”. This is our initial calculation of σ_V.
10. Now that we have σ_V we can calculate the next iteration value of asset value V through the Black-Scholes equation. However, the calculation is complex, so we will simplify it a little bit by creating a user defined function to calculate the “d₁” term of the equation. Again, open the VBA Editor window and under the macro we wrote earlier we will define our function.

    Function BlackScholesD1(assetVal, strikePrice, rfRate, assetVol,
    timeMat)
        BlackScholesD1 = (Log(assetVal / strikePrice) + (rfRate + 0.5 *
    assetVol ^ 2) * timeMat) / (assetVol * timeMat ^ 0.5)
    End Function

    Now to actually calculate the asset value in each period we will create the current period's asset values in column M. In M20 we will enter the Black-Scholes formula to calculate asset values. Enter the following (and be careful to handle the parentheses correctly):

    =(G20+H20*EXP(−D20*1)

    *NORMSDIST(BlackScholesD1(K20,I20,D20,$L$18,1))−

    $L$18)/NORMSDIST(BlackScholesD1(K20,I20,D20,$L$18,1))

    This is a rearrangement of equation 5.1 in order to solve for the asset value V.

11. While most of the difficult math is now done, we still need to create all of our reporting and outputs. In order to keep the output reporting clear, we will name some key ranges.

    E17: numDates

    D20: riskFreeRate

    G20: equityValue

    H20: bookLiabilities

    I20: defaultBarrier

    K20: finalAssetValues

12. Now we will calculate the key results from our model. Most of the results are straightforward and do not require further calculation.

    L4: Asset Volatility: “=L18”

    L5: Asset Value: “=OFFSET(finalAssetValues,numDates-1,0)”

    L6: Liability Value: “=OFFSET(bookLiabilities,numDates-(1,0)”

    L7: Sum of Squared Errors: “=SUMXMY2(K20:K1000,M20:M1000)”.

    Be sure to name this range “SumSquaredErrors” so the

    “iterateMerton()” macro references it.

    L8: Drift: we will come back to the drift calculation in a moment.

    Leave this blank for now.

    L9: Default Barrier: “=OFFSET(defaultBarrier,numDates-1,0)”

    L10: Distance to Default: “= (LN(L5/L9)+(L8 - (L4^2)/2))/L4”.

    This is equation 5.4.

    L11: One Year Default Probability: “=NORMSDIST( - L10)”

    L12: Current Liabilities: “=(OFFSET(bookLiabilities,numDates-1,0))”

    L13: Estimated Recovery: “=(OFFSET(defaultBarrier,numDates-1,0)- I6)/L12”

13. The final component of the model is in many ways the hardest to grasp: the concept of drift. Drift is the average return over the long term that can be expected on the assets. The concept of a long-term average return is fairly simple, but what value is appropriate to use is contested. Some modelers do not use drift, setting it to 0 percent; others use historical return, the risk-free rate, or a CAPM-type calculation. In the full version of our model, available online, you can see a few ways of handling this concept. Otherwise, to be conservative, setting drift to 0 is a reasonable approximation for shorter-term applications (i.e., 1 year). See Figure 5.5 for a view of the completed Merton Model.