Project finance modelling from Penelope Lynch

The following are a selection of illustrative files prepared in conjunction with contributions to various discussion groups. They come unchecked, with no remit other than to illustrate some particular methodology or idea. Please feel free to view or download them. If you pass them on, or post them to other discussion groups, that's fine, but please remember to attribute them to Penny Lynch, and to ensure they are accompanied by a disclaimer regarding their unsuitability for anything other than illustration of some very specific ideas.

Since 2009 I have created Excel-based Christmas cards as Christmas greetings for my clients. Here are the files. To see them properly you will need macros enabled. To interrupt them, use 'esc'. They are increasingly large, although 2011 is the most complex...!

2011 card Note, the snowfall is random!

Excel scenario management is limited, risky, and not always easy to integrate with any macros needed to run a model (eg. re-calc macro to avoid circularity). This illustrative data input sheet includes a simple case control table, which allows any number of base and sensitivity cases to be defined, stored and selected, safely and visibly on the spreadsheet. The inputs which vary between cases are listed as rows in the table, the left hand column contains the values currently in use, selected using a choose or INDEX function, and the columns to the right each contain the values which define a specific case. The current case is selected with a single input value. For more complex data inputs, the options are entered in the body of the input sheet, as options selected between using a single input value, which in turn can comprise one of the items in the case control table. I think the example will be clearer than the explanation for this one!

Illustrative input data sheet with case control table

Calculation of interest during construction (IDC) is a common source of unnecessary circularity. The periodic nature of term loan interest means that, simply by separating drawings to fund interest payments from drawings to fund other items, a more accurate interest calculation is achived, without any spurious circularity. This file gives a very simplistic illustration of this calculation. In addition the file addresses two of the commoner genuinely circular funding calculations , equity sized as a percentage of total funding, and up-front fees for loans sized from the funding requirement. The file illustrates use of a simple copy-paste macro to solve for these values without using circular Excel code. Note that this part of the illustration will only work if macros are enabled.

Non circular IDC calculation spreadsheet

The file calculates a series of simple costs and revenues from a set of input values, then uses to them to calculate investor IRR and NPV, illustrating the basic principles of IRR and DCF analysis for project finance cash flows.

Simple IRR and DCF illustration spreadsheet

'Payback' can mean many different things. This file illustrates simple payback calculations for a selection of definitions.

Simple Payback illustration spreadsheet

Simplified illustration of calculation of IRR waterfall, where a given % of free cash is allocated to investors until a certain IRR is achieved. A different % is then allocated until a specified higher IRR is achieved, and so on.

Simplified IRR waterfall calculations

Simple illustration of the principles of calculating an NPV with a discount rate which varies over time.

Illustrative calculation of NPV with variable discount rate

The Excel data analysis tool generates histogram data from a suitable data set. However, the results are generated as fixed value, which will not update dynamically when the underlying data is amended. The example file uses excel formulae to generate frequency data dynamically, automatically updating with the rest of the spreadsheet.

Illustrative dynamic calculation of frequency data

The Indirect() function allows a cell address to be specified as a string. Generally seen as exceptional only in its lack of applicability, its ability to dynamically specify thesheet name, as well as row and column addresses, makes it a perfect tool for some specific problems. Dynamic selection between different sheets within a model (eg. multiple data sheets, sheets for multiple projects) can easily be achieved with simple naming conventions, string handling and the INDIRECT() function. This file gives a very simple example.

Dynamic sheet selection illustration

Challenged by a colleague to find a way to allow very flexible sheet selection for production of consolidated values from multiple sheets, used some very helpful information in an ancient publication by David Hager and came up with a solution. This uses the Indirect function as part of an array formula, with some simple naming conventions and input processing. This file shows the required methods.

Flexible multi-sheet consolidation illustration

Very simple illustration of a loan calculation allowing dynamic specification of grace period, interest rate and repayment period (the bare minimum needed for a project finance calculation!) This file gives a very simple example.

Dynamic repayments calculation illustration

Very simple example of use of INDEX(..) with simple counters to convert horizontal layout to vertical layout.