Project financing spreadsheets

In 2004 a paper on “Financial Modelling of Project Financing Transactions” was presented to the Institute of Actuaries of Australia Financial Services Forum. It’s well worth a read if you are involved in any sort of financial modelling, whether or not you are an actuary and whether or not you are modelling project financing.

The paper includes an analysis of the risks of models and some ideas for managing the risks, gives a clear introduction to Monte Carlo simulation and why you might use it, and has a section on why actuaries might be good people for modelling project financing.

It also includes some statistics on the error rates the authors have found in spreadsheet models of project financing. The authors say “Research has shown that error rates in project financing models can be as high as 10%. Section 5 of this paper provides some statistics on error rates collected by Mercer Finance & Risk Consulting. Out of the thirty highest value projects reviewed during the 2004 financial year, nine (that is, 30%) exceeded the 10% threshold; four exceeded the 15% threshold; and one exceeded the 20% threshold.”

The wording may be misleading here. They are not saying that, for example, 10% of the models have errors. In fact, all the models (100%) that they reviewed contained errors. They are saying that in four of the models they reviewed over 15% of the unique spreadsheet formulae contained errors, and that one model had errors in over one in five of the formulae. This model was one of the smaller ones, too, so it’s no use saying “it’s only a small model, so it’ll be OK”.

Although the spreadsheets they reviewed were all modelling project financing, there is absolutely no reason to suppose that the high error rates are peculiar to the project finance field. Financial models of any sort are complex, and it’s hard (but not impossible) to write a spreadsheet that doesn’t contain errors.

So let me say, once again, that it’s important to get the process right when developing financial models (whether using a spreadsheet or specialist modelling software). Be clear what it is that you want the model to do: write a specification that is detailed enough to test against. Use appropriate techniques when building the model: something that looks like a really cool way of doing things may be difficult for other people to understand. Document the design decisions you make. Use a good change control process to keep track of what’s going on. Test the implementation against your specification. Record the tests, so that other people have some reason to believe you when you say the system has been tested. And, above all, don’t trust yourself. You are bound to make mistakes in the coding, and if you don’t look for them you won’t find them.