# How to use Merton model to calculate default probability with monthly stock prices?

I want to calculate the estimated default probability with only given data the monthly returns for the last 20 years, the risk-free rate ($R_f$), equity value (EV) and the face value of debt ($D$). My steps so far are:

1. Find each month's lognormal returns: Ln(Month);
2. Subtract from each result in step 1 the average of the lognormal returns and then raise them to the power of 2 and then sum it, in order to find the monthly equity volatility;
3. Calculate the annualized equity volatility by doing $$\left(1 + \frac{\textrm{monthly equity volatility}}{12}\right)^{12 \times 20} - 1$$
4. Calculate Asset Value (AV) using the formula: AV = EV * equity volatility + D (not sure if its correct)
5. Attempt to solve the equations to derive the asset volatility but get stuck when using the Excel solver.

How to proceed?

• Formula 3 is wrong if you want to do the step from monthly vol to annualized vol.
– Ric
Feb 11 '14 at 7:47
• Excel solver is not good to solve simultaneous equations like the ones required by the Merton model. When I implemented it, I used an R package to solve the system (maybe this library: systemfit). Considering R is good also when dealing with time series, I strongly suggest to drop Excel and using R instead. Jun 6 '17 at 8:41

detailed description of the solution of this problem using Excel is in the second chapter of the book Credit Risk Modeling using Excel and VBA Gunter Löffler

• @user10706: Please refrain from posting links to stuff which is not supposed to be free. We do not want to deal with copyright infringement issues. Your answer was edited accordingly.
– olaker
Dec 12 '13 at 21:55

your steps are a bit too complicated to me.

in Step 1 and 2 you do two things: you caculate monthly log-returns and then their standard decviation.

Given prices $P_t$ indexed by time the log return is given by $$r_t = \ln(P_t/P_{t-1}) = \ln(P_t) - \ln(P_{t-1}).$$ The formula for standard-deviation (the sample estimator of it) should be clear: $$\sigma = \frac1{n-1} \sum_{t=1}^n (r_t-\bar{r})^2,$$ where $\bar{r}$ is the average return. Usually software packages have a function for standard-deviation. Then you annualize volatility $\sigma_a$ by the square-root of time rule: $$\sigma_a = \sigma \sqrt{12}.$$ So much for the first three steps. Your formula 3 is a mixture of various ways to calculate yearly and monthly returns from one another (geometric returns not log returns). But for calculating a yearly vola you need the formula above.

Last comments: Be sure you understand the math. If you mix up volatility and returns then you need to study some more.

Second: for a text book example: ok, use the past $20$ years of data. For real life: don't estimate a default probability for the, say, comming year using data that is that old. The world changes and so do firms and I don't expect data from $20$ years ago to be relevant at the moment.