8
$\begingroup$

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?

$\endgroup$
  • $\begingroup$ Formula 3 is wrong if you want to do the step from monthly vol to annualized vol. $\endgroup$ – Ric Feb 11 '14 at 7:47
  • $\begingroup$ 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. $\endgroup$ – Lisa Ann Jun 6 '17 at 8:41
2
$\begingroup$

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

$\endgroup$
  • 2
    $\begingroup$ @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. $\endgroup$ – olaker Dec 12 '13 at 21:55
1
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.