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 to do it.  [Download]


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.
Download a demo version at the bottom of this page [32 bit version targeting Windows XP onwards. Tested on Windows 10 + Office 2010].

Check here for a live demo of an older version.

Note that the final answers are easy to validate in standard statistical software packages, if desired.  For example in 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++CephesGNU Scientific Library and R's statistical library, which can be used as alternatives.
ċ
PlutoTascheExcel.zip
(191k)
Peter Windridge,
Nov 21, 2015, 4:06 AM
Comments