A Merton Model (Löffler and Posch); Finding a Company's Probability of Default

This Merton model uses the foundations presented in Löffler and Posch's “Credit Risk and Modeling Using Excel and VBA” released November 15, 2010. This model is the implementation of the Merton model with a one-year horizon, there is a multi-year Merton model in “Credit Risk and Modeling Using Excel and VBA” that includes dividend payouts and accrued interest, but I have spent a lot of time already on this model and I want to move onto different projects. However, I do plan to come back to this sometime in the near future to complete the model with dividend payouts and accrued interest because the real-life implementation of this model is less accurate without those two factors. 

I predict that this model will run into some trouble when using it for firms with negative equity (such as $HD and $AAL). Like all other models, this model is not perfect so keep that in mind and make sure not to rely solely on this model when using it in credit analysis. 

The model itself is simple to use: 

1. Keep updating the dates when using the model, the best way to do this is to just replace the dates that are already present. 
2. Fill in the quarterly balance sheet data, add in any accounts that are not present using ctrl+shift++
3. Update the 1-yr treasury rate, data is available online 

Note: 

• Asset Drift rate (μ) was calculated using a Fama French 3 Factor model, I wrote about this in my previous blog. 
• Asset values iter K+1 can be calculated using excel VBA as well, this is the equation:

• Inputting the balance sheet data will be the hardest part of this model in my opinion 

Link to the model in google sheets 

https://docs.google.com/spreadsheets/d/1BxMjGXTcWEldwUAcILeAYvwQjDXyjNhH4C5pfqzpGgQ/edit?usp=sharing

Remember, download as Excel. 

Link to Löffler and Posch, “Credit Risk and Modeling Using Excel and VBA”

https://books.google.com/books?id=lmCFu9PinLEC&pg=PA32&lpg=PA32&dq=bsd+excel&source=bl&ots=-hdRPmkho-&sig=ACfU3U2oZzLFUwNpjCsOrVBEekj9a-yCyA&hl=en&sa=X&ved=2ahUKEwiwnYaVzbTqAhXRTN8KHYanB8MQ6AEwAnoECA0QAQ#v=onepage&q&f=true

If you are interested in the ideas behind Merton Model read below. 

The Merton Model is an analysis model used to assess the credit risk of a company’s debt. You can use the Merton Model to: 

1. Understand how capable a company is at meeting its financial obligations 
2. Servicing its debt 
3. Weighing the general possibility that it will go into credit default 

First a little history… 

This model was created by Robert C. Merton who is widely recognized for being part of the Black Scholes model, commonly used today in options pricing. Some may also recognize his name for being part of the Long-Term Capital Management Fund (LTCM). LTCM was an asset management company that included some of the biggest names on Wall Street and in academia and it used the black Scholes model successfully (40%+ returns) before Russia defaulted on its domestic local currency bonds. This should serve as a warning to never rely fully on this model because there are always extreme outliers that have massive consequences. 

Robert C. Merton 
Let’s move onto the idea behind this model… 

Since a company is both financed by equity and debt, the Merton model is a structured model in that it will use the share price of the equity of a firm to calculate the probability of default. 

Assumptions 

1. No dividends are paid out 
2. Market movements are unpredictable 
3. No commissions are included 
4. Underlying stocks’ volatility and risk-free rates are constant 
5. Returns on underlying stocks are regularly distributed 

So, what is the Formula? 

Where… 

• E= Theoretical value of a company’s equity 
• Vt= Value of the company’s assets in period t 
• K=Value of the company’s debt 
• t= Current time period 
• T=Future time period 
• r= Risk free interest rate 
• N=Cumulative standard normal distribution 
• e= Exponential term 
• σ = Standard deviation of stock returns 
• D1 = (Ln(Vt/K)+(r+((σ^2)/2))ΔT)/( σ(√ΔT)
• D2 = D1 – σ √ΔT

So, what does this mean? 

It’s saying that the Value of the company (or Assets) at the present time is equal to the equity and liabilities at the present time. 

• Value(0)=Equity(0)+Debt(0) 

The future value of the debt is the minimum of two values 

1. Debt(0)*(1+i)^T  (Debt at time 0 plus interest) 
2. Value(T) (value of the company at the future time) 

So… Debt(T)=Min(Debt(0)*(1+i)^T, Value(T)) 

The future value of the equity will be the maximum of two values 

1. Value(T)-Debt(T) (Future value of assets- future value of debt) 
2. 0 

So… Equity(T)=Max(Value(T)-Debt(T),0) 

In simpler terms 

Scenario 1: The company does well 

• The company is still in business so Equity(T)=Value(T)-Debt(T) 
• Debt is repaid so Debt(T)=Debt(0)*(1+i)^T

Scenario 2: Game over for the company  

• Company is liquidated: Equity(T) = 0 
• Debt investors receive the remaining asset value of the company: Debt(T)=Value(T) 

So, equity in this model acts as a long call on the company and debt acts as a short put on the company. If you are not familiar with options reference the graph below. 

A Long Call (Equity)

A Short Put (Debt)

Seeing this, we can use the Black Scholes formula in pricing options when it comes to the equity and debt of the company. 

• Equity(0) = c (premium for a call option) 
• Debt(T) = k (Strike price) 
• I*Debt(0)=p (premium for a put option) 
• Value(T)=St (Share price at time T) 
• Value(0) = S0 (Share price at time o)

Some problems with the Merton model 

1. Asset values are hard to determine at a specific time T 
2. We assume we know the volatility of the asset value 

So, using the Black Scholes formula... 

Equity(0)=Value(0)N(d1)-Debt(T)e^(-rT)N(D2)

I*Debt(0)=Debt(T)e^(rT)N(-D2)-Value(0)N(-d1)

• We know that P(ST<K)=N(-d2) (the probability that the put will be in the money) 
• Thus, the probability of Default at time T is P(Value(T)=< Debt(T))=(N(-d2))