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

Question

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?

Answer 1

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

Answer 2

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.