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$
2
  • $\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$
1
  • 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$

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .