Modelling Principal Pre-payments-Part 1

Fimodo speaks to four financial modelling experts to get a brief history of their professional development, ailment and how that has changed over time.

Those participating in the discussion are Nick Crawley, buy John Stroud, Simon Selkrig and Danielle Stein Fairhurst.

These Financial Modelling professionals are Fimodo authors and industry experts.
Those participating in the discussion (click each name to read their answer):

nick-crawley

Nick Crawley
Managing Director, Navigator Project Finance
www.navigatorpf.com

“Approach” incorporated, speed, accuracy, specific industry knowledge, technical Excel and VBA skills, presentation, complexity, flexibility. As my career has evolved the areas within which I focus have changed primarily due to the evolution of my responsibilities from building financial models to the management of a modelling company.

The formative years

In the early part of my career I was rapidly increasing my knowledge of the project finance industry, from a zero-base and how modelling fits in. At this time presentation was important but I didn’t have the time or incentive to focus too much on it, although I always felt that it was important. I strove to build models that could do everything but were still underpinned by relatively simple formula, albeit with too much optionality and VBA. Honestly, attendance at a Navigator course such as Project Finance Modelling (A) would have delivered huge benefits to me at this stage.

My approach gets tested

In the middle part of my career to date, I was in investment banking and am comfortable that I had sufficient industry knowledge, technical skills and speed to be able to say, at the time, I was the best financial modeller that I knew. Models needed to be built quickly, look reasonable and be capable of handling comprehensive Monte Carlo analysis. The approach worked well enough to be trialed commercially so I did…

My approach goes commercial

15 years later I run Navigator, arguably the world’s largest dedicated project finance transaction modelling team, we produce several transaction models a week, my challenges these days are clearly different. Our models must be built by teams and with the core attributes that include:

  • accuracy,
  • presentation
  • flexibility
  • presentation
  • speed to build
  • and presentation…(see the picture starting to emerge?!)

Underpinning all of the above objectives are the principles of consistency and simplicity. If model construction is performed in the same way both mechanically and visually and if the calculations themselves are performed in a simple manner then it allows us fulfill the above objectives and spend more time:

  • listening to our clients
  • checking the models
  • fully understanding the transaction

…rather than time spent ‘spreadsheeting’. Combine this approach with efficiency and a dedication to excellent customer service and it’s a good start to a profitable and scalable enterprise.

What’s next?

Upon reflection I would say that my approach has shifted from modelling in the relative shelter of a large accounting firm, has been tested and has evolved throughout my time in banking and I have now arrived at an approach which I can say is ultra-professional and definitely commercial. I am now working with a team, internal and external, on the next stage of our methodology development, which will make it even simpler but also even faster to build, manage and audit.

Conclusion – go “back to basics”

Along this journey I have filtered out the temptation to use a lot of the features in Excel and build in lots of un-required flexibility, I have questioned every formula used in a project finance model and embraced a ‘back to basics’ methodology forged with an almost obsessive focus on presentation in a way that actually saves time…too good to be true!

john-stroud

John Stroud
Partner, Digital Advisory
www.digitadvisory.com

I think my approach has been reasonably consistent over my career, however I do spend more time on planning (define, specify & design) than I used to. I’m a great subscriber to the 5 P’s. I also think experience allows me to anticipate what stakeholders really need and to be better able to identify the potential pitfalls.

I think modellers tend to go through a metamorphosis after 5 plus years. Rather than just developing and running the models experience allows them to really drive the analysis and provide advice on structuring, financial risk as well as being able to confidently liaise with equity, debt and senior management.

simon-selkrig

Simon Selkrig
Financial Modelling Professional

Theoretical “in the box” approach conveyed in university course materials and lecturers, through to a more practical “outside the box” and value-adding client focused approach

I am a lot harder on myself, legacy models and hard to follow models. No longer do I just accept financial models as the way they are, but instead, I attempt to understand them fully, rework or completely rebuild the model.

I take a more conceptual, structured, disciplined approach to my financial modelling; I am continuously brainstorming, reading new financial modelling books, websites and blogs (like Fimodo!). I am trying to improve the way I build, manage and revise my financial models.

I try to be smarter with my financial modelling, via more flexible (maintain plenty of spare defined business unit names or subsidiaries), more refined use of tick-boxes and drop-down boxes, and less hard coding of financial numbers.

I attempt less replication and more creation with my financial models; within reason of course!

I tailor source financial data through financial year or business unit specific import worksheets, rather than merely copying financial data across the financial model in an ad hoc way.

I am focused on keeping my financial model error-free, via the use of error checks which are summarised on an Error Check worksheet.

I build scenario/sensitivity aspects to my financial model, as I don’t believe in merely reporting historical, actual and budget numbers; but rather generate value-adding and thought provoking financial modelling, forecasting and charting.

danielle

Danielle Stein Fairhurst
Principal Consultant, Plum Solutions
www.plumsolutions.com.au

I first discovered financial modelling when I started out working as an Analyst in Investment Banking in London. Of course, the term was not used very widely back then, and my approach to financial modelling has certainly become much more structured throughout my career. Back then, it was something I did instinctively, and now that I’ve become a specialist in the field, I put a lot more thought into the design and structure of the models. In fact, I probably didn’t even have “Financial Modelling” listed on my CV – I would have written “Advanced Excel” instead.

Financial modelling has achieved a much higher profile nowadays and has become a discipline in its own right. However, I think that many people still struggle to distinguish the difference between a spreadsheet and a financial model and think that in order to be a better financial modeller they just need to brush up on their Excel skills. Of course, we know that there is a heck of a lot more to it than that! Looking at developing the skill-set of a financial modeller (including, but certainly not limited to Excel skills!) is something that is an important part of the Plum Solutions Financial Modelling in Excel workshops.

Another phenomenon that has evolved in recent years is the use of the web for collating and publishing information, and educating people about niche subjects like Financial Modelling. I always encourage anyone interested in improving their skills to check out the resources readily available online.

When I first set up Plum Solutions in 2004, it was very much a bricks and mortar consultancy with a token online presence. In recent years, though the advent of Web 2.0 technologies have helped it to become a global company which provides consulting and training services all over the world.

I travel regularly throughout Australia and overseas delivering both public and in-house training sessions, and the online training courses mean that those unable to attend a session can do so virtually, and at their own pace.


Excel is the backbone to any custom built financial model, Sildenafil
and one of the core attributes of a financial modeller is to have good technical Excel skills.  When struggling with their financial models, website like this
some managers’ first reaction is to send their staff on an advanced Excel course to improve their modelling skills.  However, approved
with training budgets under constant scrutiny you really need to make sure that you get the best value out of your training options.

Is a training course what you need?

When considering an Advanced Excel course, there are a few points you should consider:

  1. As financial modellers, our use of Excel is quite narrow.  I know it’s hard for us to conceive, but there is a whole world of Excel use outside the finance industry!  Statisticians, database programmers, and engineers to name a few are able to use Excel’s advanced functions to create non-financial spreadsheets.  Most Advanced Excel courses are very broad, and will cover functions and capabilities which do not apply to your needs as a financial modeller.  You may learn a few tricks, but the time and money spent could be invested elsewhere.
  2. Research show that a large percentage of the skills learned in training courses are not retained.  Will you really remember everything that you learned?  A programme of continuous, applied learning is often more effective than an intensive training course.
  3. Are your Excel skills really the problem?  Following best practice and mastering the logic behind a financial model is more important than building in complex formulas, and for the most part you will want to keep formulas as simple as possible.

Alternatives to attending a public ‘Advanced Excel Course’

Other options to improve your financial modelling skills without going on an Advanced Excel Course include:

  1. Take an interest in what your work colleagues have done and how they did it – you can always learn from the techniques of others.
  2. Read other peoples’ models!  Writers read books, actors watch movies to learn techniques.  Sometimes the best way to learn is to see what someone else has done.  Try taking someone else’s model apart and see how they built it.
  3. Read specialist financial modelling publications – there are often useful articles about specific techniques or subscribe to online “Excel Tips” email newsletters

Select the right training option for you

These strategies are great for continually improving your Excel and financial modelling skill set, and they certainly won’t break the budget, but what if you really need to give your skills a quick boost to get you ready for that new project coming up?  There are a number of options available:

  1. Try to find a course specifically aimed at Excel for financial modellers, and even better – find one dedicated to your industry.  Although good financial modelling skills are relevant across many different industries, there is nothing like specialised training dedicated to the modelling issues inherent in your own industry.  In recent years many more specialist financial modelling courses have become available, and most major cities have public courses available.
  2. If your company is able to arrange a group in-house training course, this will be even better as you may be able to use the templates and models actually used within your organisation.  You also have the added advantage of choosing a time and location that suits you.
  3. If you can afford a private session, this is the most convenient and time-efficient method as you can ask questions and only cover topics that are useful to you.  Many specialist training companies provide Excel one-on-one mentoring sessions.
  4. In addition, there are some fantastic publicly online resources including tutorials, articles, blogs, forums and videos specialising in Excel and Financial Modelling which can be done in your own time rather than taking an entire day or two out to attend a course.

So, do you really need an ‘Advanced Excel Course’?

In summary, an advanced Excel course would certainly benefit someone who plans on extensively using Excel in their role for multiple purposes.   However, if your main objective is to become an expert financial modeller, focus instead on mastering the few tools you need such as logic and methodology, rather than expending time and money to learn tools you won’t use.
Excel is the backbone to any custom built financial model, unhealthy
and one of the core attributes of a financial modeller is to have good technical Excel skills.  When struggling with their financial models, stuff
some managers’ first reaction is to send their staff on an advanced Excel course to improve their modelling skills.  However, with training budgets under constant scrutiny you really need to make sure that you get the best value out of your training options.

Is a training course what you need?

When considering an Advanced Excel course, there are a few points you should consider:

  1. As financial modellers, our use of Excel is quite narrow.  I know it’s hard for us to conceive, but there is a whole world of Excel use outside the finance industry!  Statisticians, database programmers, and engineers to name a few are able to use Excel’s advanced functions to create non-financial spreadsheets.  Most Advanced Excel courses are very broad, and will cover functions and capabilities which do not apply to your needs as a financial modeller.  You may learn a few tricks, but the time and money spent could be invested elsewhere.
  2. Research show that a large percentage of the skills learned in training courses are not retained.  Will you really remember everything that you learned?  A programme of continuous, applied learning is often more effective than an intensive training course.
  3. Are your Excel skills really the problem?  Following best practice and mastering the logic behind a financial model is more important than building in complex formulas, and for the most part you will want to keep formulas as simple as possible.

Alternatives to attending a public ‘Advanced Excel Course’

Other options to improve your financial modelling skills without going on an Advanced Excel Course include:

  1. Take an interest in what your work colleagues have done and how they did it – you can always learn from the techniques of others.
  2. Read other peoples’ models!  Writers read books, actors watch movies to learn techniques.  Sometimes the best way to learn is to see what someone else has done.  Try taking someone else’s model apart and see how they built it.
  3. Read specialist financial modelling publications – there are often useful articles about specific techniques or subscribe to online “Excel Tips” email newsletters

Select the right training option for you

These strategies are great for continually improving your Excel and financial modelling skill set, and they certainly won’t break the budget, but what if you really need to give your skills a quick boost to get you ready for that new project coming up?  There are a number of options available:

  1. Try to find a course specifically aimed at Excel for financial modellers, and even better – find one dedicated to your industry.  Although good financial modelling skills are relevant across many different industries, there is nothing like specialised training dedicated to the modelling issues inherent in your own industry.  In recent years many more specialist financial modelling courses have become available, and most major cities have public courses available.
  2. If your company is able to arrange a group in-house training course, this will be even better as you may be able to use the templates and models actually used within your organisation.  You also have the added advantage of choosing a time and location that suits you.
  3. If you can afford a private session, this is the most convenient and time-efficient method as you can ask questions and only cover topics that are useful to you.  Many specialist training companies provide Excel one-on-one mentoring sessions.
  4. In addition, there are some fantastic publicly online resources including tutorials, articles, blogs, forums and videos specialising in Excel and Financial Modelling which can be done in your own time rather than taking an entire day or two out to attend a course.

So, do you really need an ‘Advanced Excel Course’?

In summary, an advanced Excel course would certainly benefit someone who plans on extensively using Excel in their role for multiple purposes.   However, if your main objective is to become an expert financial modeller, focus instead on mastering the few tools you need such as logic and methodology, rather than expending time and money to learn tools you won’t use.
“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, case 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.

Picture1

The matrix shows the layout for identifying how much can be repaid in the period.

Picture2

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)

Picture3

In the account itself a pre-payment is reflected in the following way, note the way the last payments are negated preferentially.

Picture4

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.

Recent posts by Rickard Wärnelid

Tags: , , ,

Comments for “Modelling Principal Pre-payments-Part 1”

  1. Blair Newton says:

    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?

  2. Nick Crawley says:

    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

  3. Rebecca L'Green says:

    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!

  4. Bhavik Khatri says:

    Hi Nick,

    This is impressive stuff. Could you please attach a sample workbook?

    Kind Regards,

    Bhavik

  5. Nick Crawley says:

    Hi Bhavik,
    File uploaded.
    Nick

  6. Adrian says:

    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

  7. Nick Crawley says:

    Hi all,

    Following on from the pile of emails I recieved (and responded too!) on this subject here is a follow up hosted on the Navigator site. We have basically made the above process more streamlined by removing the matrix structure.

    I think it is important to understand and be able to model pre-payments in the more comprehensive way (as above).

    http://www.navigatorpf.com/blog/prepayment-future

    Feel free to check out, comment, use, ignore!

  8. Sourav Gangawat says:

    Hi,

    This is quite intresting stuff, however the way IOM is done, looks too complicated. I have done this numerous time with the simpler approach, where the adjustment is just the difference between the Total repayment less scheduled repayment less cash sweep.

    I have done a working example on the file you have attached to show my way of doing this, however there is no option to upload my example !

  9. Hedieh Kianyfard says:

    Hi,
    I was looking for a template on modelling cash sweep and I came across your article. However, I have different tranches of debt built into my model and I was wondering how I can apply your method for different tranches of loan with different scheduled repayment profiles? Thanks.

  10. Nick Crawley says:

    Hi Hedieh – Cash sweeps can be a modellers worst nightmare! The challenges I find are that bankers in different parts of the world have different definitions of the mechanics as well as expectations on how accurately the model will capture it.

    For now lets assume that your “Cash Sweep” can be broadly defined as the application back to senior debt of surplus funds post scheduled senior interest and principal payments have been made. The other commonly used definition is more of a project finance metric and is a stand-alone measure for application of x% of the CFADS against the entire senior debt – like a payback calculation. Article here

    http://www.corality.com/tutorials/cash-sweep-modelling

    Back to your question, when you have multiple tranches that rank equally (“pari passu”) there are two main ways of doing it:

    1) In the period
    2) Applying payments over time (hard!) – of which there are three variants
    1) Pro-rata (systematic reduction of each scheduled payment)
    2) Inverse order of maturity (IOM)
    3) Additional

    The most common approach is 1) and to simply to pro-rate the amount of cash available based on the tranches’ opening balances. I often don’t tie these in with the exact timing of the schedule facilities and if that is very important then you could set up an off-set account but I think that’s overkill. If there is extra cash then it gets repaid in the period it is available – this will mean your scheduled repayment logic will need to test for the MIN (scheduled, opening balance) throughout so as to not overpay. This is important because a cash sweep with cause an earlier repayment.

    Read the first two articles on this search result page for enlightenment!
    http://www.corality.com/?s=inverse+order+of+maturity

    I can write all week long about modelling debt in project finance transactions but I hope this short reply was useful. This as well as other related questions are covered on our one day debt course which I believe I already mentioned to you :-)

    Happy and error free modelling!

    Nick

Comment on this Article