spreadsheet 6.d.1 (Merton Model)

[Laughlin1]

Dear David,



I am currently reviewing learning spreadsheet 6.d.1 (Merton Model) and have noticed that mu (rather than sigma) is in the denominator of the formula in cell E43 (and F43, G43, H43 and I43), which calculates the ‘d1’ value. Is this an error in the spreadsheet or am I missing something?



Thanks

Maurice

 

[David Harper CFA FRM]

Hi Maurice,

You didn't miss anything. On the contrary, this is my error. (the denominator is using square root rule to scale annual volatility, and the n-year volatility in the denominator is standardizing the n-year geometric return in the numerator into a ratio that is essentially a z-value)

I just fixed the spreadsheet @ http://www.bionicturtle.com/premium/spreadsheet/6.c.1_merton_model_pd_equity/

(I check by comparing calcs to textbook output, but as the volatility and exp return are both 20% in Stulz example, this error escaped me). Thank for spotting, sorry for confusion - David

 

[potomoc]

David
Looks like you may have mis-characterized your spreadsheet label. In your spreadsheet database, 6.c.1 is assigned to the Altman Optimize Loan Portfolio. This spreadsheet should read 6.d.1. My 2cents!
Cheers
Joe

 

[David Harper CFA FRM]

Hi Joe,

For 2010, it is meant to be 6.c because it corresponds to the 6.c. tutorial that includes the assigned de Servigny chapter which reviews the Merton model. So, I think the XLS at 6.c is correct: http://www.bionicturtle.com/premium/spreadsheet/6.c.1_merton_model_pd_equity/

However, you do have a good point: last year (2009) the Merton was 6.d.1 and Altman's Optimize Loan Portfolio was 6.c.1., such that the Altman's is not assigned this year so I "moved" the no-longer-assigned XLS to 6.x.1 Altman’s Optimize Loan Portfolio 15 Sep 2009 (and colored it red to signify not necessary).... but the zoho link still betrays the old 6.c.1 (confusing, sorry!), so I will fix that to eliminate any semblance of 6.c.1

... but hopefully it's clear that Merton is meant to be 6.c.1 to associate with tutorial 6.c.1, i think my vidoes and xls labels are thusly consistent in that regard?

Thanks, David

 

[papillonring]

Hi David

Is the error corrected in the spreadsheet link below? I tried opening the link that you have provided but it is not working.
http://www.bionicturtle.com/how-to/spreadsheet/6.c.1_merton_model_pd_equity/

Also, in the Servigny example, I see that the total ST and LT debt is $13 and the Firm Value is $12.75. This means it has a negative Equity Value of $0.25.... Is this right and does this make sense? Could there possibly be a negative Equity Value?

 

[David Harper CFA FRM]

HI Papillonring,

Yes, that error was corrected and the fix is reflected in the XLS. The link you just posted appears to work fine (and I checked: you would have access to it).

Can you please let me know if you really cannot access this link … because you should be able to??

Re the de Servigny example, you are exactly correct, his example (Ch 3) does assume debt > assets (13 > 12.X). As equity is the residual (i.e., Equity = Assets - Liabilities; could occur if asset values shrink but debt does not), negative book/market equity is possible. In this case, however the PD is not as high as you'd expect b/c he uses the ~KMV rule and the threshold of $10 is all that really enters. So, the resulting PD() is the same as you'd get from a Merton model with $10 total debt.


David