A matter of “principal”.

By Nick Crawley on November 15 2009

Having spent the majority of my professional life involved in the modelling and analysis of project finance transactions it is fair to say that I have spent, more time than most, thinking about and calculating the repayment of debt. This time has also been spent looking at how the calculations can be simplified, made more thorough, easier to understand, more flexible and adaptable for when a transaction progresses.

Lets break it down

Debt can be the most complicated part of a project finance model, often unnecessarily. When explaining debt repayment I distill it to its core elements:

The increase and decrease of principal
The interest and fees, the cost of having the debt available / outstanding

Drawdowns and repayments are tracked in a ‘debt account’ where the only movements in and out are principal related and tracked with an Opening Balance and Closing Balance. Interest and fees do not directly affect the principal outstanding.

+Opening Balance
+Drawdowns
- Principal repayment
= Closing Balance

The principle of principal and the PMT trap.

When calculating the principal repayment the first question to ask is

“what method will be used?”
The default answer, in Project Finance is Credit Foncier, also known as Annuity, Constant P+I, mortgage style.

Excel puts a hurdle in the way of the unsuspecting financial modeller, this is the PMT function. Remember the PMT does not calculate the principal repayment for a debt facility, it calculates an annuity payment which is the combination of both Principal and Interest. It is not a short cut and requires all of the same inputs as using the algebraic solution, which is:

A = P+I = OB . r / ( 1- ( 1+r) ^ -n )

P = OB . r/(1-(1+r)^-n) - OB . r

P = OB . r . ( 1 / (1-(1+r)^-n - 1 )

Where OB = Opening Balance, r = interest rate and n = number of repayments remaining.

So the PMT function doesn’t save any time or formula length and by using the algebraic method you can demonstrate to other users of the model that you know what you are doing!
After seeing this mistake made so many times recently we decided to post a new tutorial up on the web to walk a modeller through how to set up a basic debt account.

http://www.navigatorpf.com/training/tutorials/debt-repayment-modelling

Some tips

The reduction of debt is a principal repayment, not a principle repayment
Avoid using PMT unless you really understand what it does
Interest is the Opening Balance . Interest Rate
The closing balance is the sum of Opening Balance and all movements in the period
The opening balance is the closing balance from the period before