Financial
Merton distance to default: lognormal model and a one-year linear buffer
Calculate an illustrative distance to default from asset value, debt, annual asset drift and volatility, with explicit model assumptions and input checks.
Model distance is not a legal insolvency assessment
The basic model assumes geometric Brownian asset dynamics, constant drift and volatility, and a single debt payment at maturity. Default is assessed at maturity; assets below the debt face value today do not by themselves establish a current legal default.
Use annual asset drift and asset volatility with a horizon in years. The assets and debt inputs must use the same currency and scale. Asset value and volatility normally require estimation; stock price and stock volatility are not interchangeable with these inputs.
B4 is an assumed physical asset drift. Substituting a risk-free drift changes the measure to risk-neutral under the basic no-payout model. The result is a model quantity, not a calibrated forecast or a credit recommendation.
| Metric | Formula | Assets 150, debt 100 | Assets 90, debt 100 |
|---|---|---|---|
| Debt face value / current assets | B3 / B2 | 66.67% | 111.11% |
| Exact Merton DD (sigmas) | (LN(V/D) + (mu-0.5*sig^2)*T) / (sig*SQRT(T)) | 0.4542 / 0.25 = +1.8169 sigmas | -0.2264 sigmas (< 0!) |
| Illustrative one-year linear buffer | (B2 - B3) / (B2 * B5) | 50 / 37.5 = +1.3333 sigmas | -10 / 22.5 = -0.4444 sigmas |
Lognormal distance using the assumed drift
The log drift subtracts half the squared volatility. Assets, debt, volatility and horizon must be positive numeric values; drift may be negative. Display rounding is applied only at the end.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0,B5<=0,B6<=0),"Invalid inputs",ROUND((LN(B2 / B3) + (B4 - 0.5 * (B5 ^ 2)) * B6) / (B5 * SQRT(B6)), 4)))A separate one-year linear buffer
This simplified ratio divides the current asset-minus-debt difference by one-year dollar asset volatility. It ignores drift and is fixed to a one-year scale, irrespective of B6. It is not an implementation of the proprietary KMV expected-default-frequency methodology, nor a substitute for the lognormal formula.
=IF(COUNT(B2:B3,B5)<>3,"Invalid inputs",IF(OR(B2<=0,B3<=0,B5<=0),"Invalid inputs",ROUND((B2 - B3) / (B2 * B5), 4)))Try the example
Use the original values or change them to test your understanding.