“Modelling pre-payments can cause brain failure and will lead to an overly complex model”
At some point in the career of project finance modeller comes the inevitable demand from a manager “but principal pre-payments are in the term sheet so we have to model them” – and the fun begins.
Download the Excel file for this tutorial
Having said that, including these mechanics causes so much complication and inevitably confusion that it is company policy not to include them. However it is important that a project finance modeller knows what is meant commercially by these terms and just how complex it is to include them. I should say that this is my interpretation after 15+ years of intensive modelling, banking and consulting in the field – I am sure there are other ways to skin the problem and I would be pleased to hear if any of you think this can be done differently or more efficiently. Now where were we…
What is a pre-payment?
So what is a prepayment? Well lets say you have a loan of $100. Because things are going well for your project you have $50 additional funds which you would like to pay off in addition to a scheduled $25 principal repayment. The payment in the period becomes $75. If the term sheet makes provision for pre-payments (payments ahead of schedule) then the $25 is assumed to be paid but the additional $50 is treated in one of 3 other ways:
- Inverse order of maturity
- Pro-rata
- Additional
Let’s take a look at how the $50 is applied the most popular of which is the Inverse Order of Maturity (“IOM”) and the simplest of which is “Additional”. I will explain the IOM method here and will explain the pro-rata in a further posting.
Modelling the Inverse order of Maturity
Under an IOM regime the $50 is deducted from the last scheduled payments, this reduces the life of the loan but does not affect the planned schedule of payments. So if the last scheduled payment was $15 then there would be an additional $5 to still allocate, this is deducted from the n-1 payment. So you can see that it would be very easy to get caught in a circular reference here, a payment made today is applied at the end of the loan. So how is it done without getting caught in a tangled web of circular references or double counting?
How is it done?
The logic that needs to be modelled is centered on keeping track of how much is remaining at the back end of the loan, taking into account any pre-payments that have already reduced the balance in the tail. This is managed through a matrix showing the reduction in every remaining period (starting from the back) due to surplus funds in each previous period. By accepting the additional rows that are required for this matrix the modeller retains all important transparency which is essential in these calculations.
The matrix shows the layout for identifying how much can be repaid in the period.
The formula within these cells is quite long, the screenshot below shows what it is doing although this isn’t the clearest way to communicate it! In the screen shot below the reference to the following lines mean…
- line 15 is the scheduled repayment line
- row 11 is the funds that are applied in the period for the sweep
- row 9 is the period number(1,2,..,9,10)
In the account itself a pre-payment is reflected in the following way, note the way the last payments are negated preferentially.
Summary
In summary the financial modelling of prepayments such as the inverse order of maturity method is very complicated and will definitely cause users, managers and auditors to ask many more questions than the accuracy it delivers, let alone the chance to introduce errors or circular references.
This is a very impressive and yet simple solution to the inverse order of maturity! I haven’t had the chance to code it up in Excel but will save it in my reference library for future use in my financial models.
I have not yet been to any of your financial modelling training courses in London – do you cover topic like this in your debt modelling courses?
Hi Blair,
Well I am glad you think it is simple, it took years to work out and I personally wouldn’t say that it is simple! I think it is as simple as such a complex concept can be.
The important point I think is that each pre-payment can be tracked to see what it is being applied against. This matrix approach can be readily dropped for depreciation calculations but for this concept I would recommend it is preserved (at the risk of many lines of code, with just one unique formula of course). I will post a downloadable file of my workings on the Navigator website soon.
On our Debt Modelling course we discuss concepts such as prepayments but draw the line before modelling them. That course covers such topics as sculpted repayments (without vba or circular references), mezzanine debt and how it fits in, it goes into the DSRA/c in detail and depending on the (small) class make up on the day we can discuss concepts such as this, > 1 IRR, the XIRR and higher end concepts. I will let you assess the course on our website rather than plug it here. http://www.navigatorPF.com
Nick
I have wondered this meaning many time before. It is very useful to see that I was correct to leave from my models. Even now I see it I do not thinking it would be worth much.
I also say this site is fantastic, I am a lawyer but use and build finance models for our projects too – I watch this site and my team are using for reference. Great!
Hi Nick,
This is impressive stuff. Could you please attach a sample workbook?
Kind Regards,
Bhavik
Hi Bhavik,
File uploaded.
Nick
Hi,
There is something which I am unclear of, and that is the split between paying the capital sum and interest. Assuming your last repayment schedule is 1000 (900 to capital, 100 to interest) and you make a Cash Sweep of 1000. Does it still pay 900 to capital and 100 to interest, or does it pay 900 to capital and the remaining 100 is paid to the capital in the 2nd last scheduled repayment?
Regards,
Adrian