Fast Pluto-Tasche PD calculator for Excel

The Pluto-Tasche method is used to calculate prudent estimates on default probabilities, given some observed defaults. The idea is explained in the note at http://arxiv.org/pdf/cond-mat/0411699. The procedure is roughly equivalent to inverting the cumulative distribution function for the number of successes in correlated Bernoulli trials.

Here is a fast plugin for Microsoft Excel.

Features

• Handles large portfolios (e.g. 100,000 obligors), which choke other code.
• Uses an efficient numerical integration scheme for which explicit error bounds can be calculated.
• Newton-Raphson and bisection search methods are combined to invert probabilities.
• Binomial cumulative distribution function efficiently evaluated using a continued fraction representation of the incomplete Beta function.*
• Incorporates rigorous analytic upper bounds similar to Hoeffding's inequality to arrive at the answer faster.
• The Microsoft Excel add-in uses the XLW C++ library, which is well tested in quantitative finance.
• 64 bit and 32 bit versions are available.
• Can be compiled as a stand-alone application (for Linux or Windows) for use in automated reporting, for example.

Note that the final answers are easy to validate in standard statistical software packages, if desired. For example, for the parameters shown in the screenshot above (using R):

n_obligors<-9810 n_defaults<-35 rho<-0.15 # correlation (should be < 1) PD<-0.0334 # portfolio default probability N<-50000 # number of portfolios to simulate # simulate the coupling variable (to create the correlations) Y<-rnorm(N) # Calculate the (coupled) probabilties p<-pnorm( (qnorm(PD) + Y * sqrt(rho))/sqrt(1-rho)) # simulate the portfolio N times and return proportion with at least n_defaults defaults sum(rbinom(N,n_obligors,p)>n_defaults)/N

## [1] 0.95002

More extensive simulation tests performed with R can be viewed here.

* As in CDFLIB, Boost C++, Cephes, GNU Scientific Library and R's statistical library, which can all be used as alternatives.