Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Formula 3 is wrong if you want to do the step from monthly vol to annualized vol. – Richard Feb 11 '14 at 7:47

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

share|improve this answer
thank you sir, you are a scholar and a gentleman! – user6728 Dec 12 '13 at 21:04
@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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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