An example of a simple Simple debt corkscrew.equal principal repayments can be downloaded here, and you can download a mortgage-style debt corkscrew here.
Debt is usually modelled by means of a debt corkscrew, so named because the principal outstanding at the beginning of the period is equal to the principal outstanding at the end of the previous period, and the principal outstanding at the end of the current period is a function of the principal outstanding at the beginning of the period, plus drawdowns, interest and repayments in the period. This creates a sawtooth (or corkscrew)-shaped dependency pattern.
An example of a Simple debt corkscrew.equal principal repayments can be downloaded here.
Pertinent points relating to the example:
- Note the monthly period used throughout the model
- As a consequence, indicators are required for repayment periods and capitalisation periods. I’ve used the modulus function here to identify which months an event occurs in. (Typically repayment and capitalisation periods are the same, although not always, as 3-month interest rates may be cheaper to hedge than 6-month interest rates, as their market is more liquid. I have used different periods to illustrate the point here.)
- It is helpful to understand when in a period a figure relates to. For short periods (like a month) we use only the beginning and end of a period. Draw downs typically happen at the beginning of a period, while fees, interest and repayments typically accrue or occur at the end of a period.
- The draw downs in this example are hard-coded. However, in a normal project model, they would feed off the capital requirements, which would in turn be a function of the capex, initial DSRA funding, postage and petties, upfront fees, etc.
- I have split interest into three rows to account for capitalisation and payment periods being different to the model period. Interest accrued occurs only on principal outstanding at the beginning of the period and draw downs, not on previous interest accrued (at least until it is capitalised, at which point it enters into principal o/s).
- You’ll note that I use OFFSET functions in calculating capitalised interest. I do this so that changing the input assumptions (B14) will flow through to the model. If the OFFSET is causing a #REF error, check the appropriate indicator using an IF statement. An example of this is in cell B23, where I calculated the equal principal repayment.
- Day count conventions: I have used an actual / 365 day count convention, which simply means that I’ve calculated the number of days in the period and divided it by 365 [days in a year] when calculating how much interest accrues. Other conventions exist. Be sure you use the correct convention as per the financing documents.
- You may hear a term such as nacq or nacs. These stand for ‘Notional annual, compounded quarterly’ and ‘Notional annual, compounded semi-annually’. These relate to the compounding convention for the interest rate. It simply means that the interest rate is stated as an annual figure, and that you should add the accrued interest back to the principal (i.e. compound it) quarterly or semi-annually, as applicable.
- Repayment methods: There are three typical repayment methods in project finance, being equal principal repayments, mortgage-style, or sculpted.
- The example uses equal principal repayments, the meaning of which is pretty obvious. As a consequence, total repayments (including interest) should be higher at the beginning and reduce over time. This is often a poor match for project cash flows, since the project’s revenues will often increase with time.
- Mortgage-style repayments imply an equal repayment over both principal and interest in every period. This can be calculated using the PMT function. Note that it is good practice to use forward-looking parameters for mortgage-style repayments, rather than calculating a single repayment from the beginning. This allows sensitivities (e.g. a delay in repayment), as well as different interest rates at different durations. It’s actually good practice for equal principal repayments too, but I didn’t do it in the example! You can download a mortgage-style debt corkscrew here.
- Sculpted repayments simply imply that the repayment schedule is manually sculpted according to criteria other than those laid out above. An often-encountered example is DSCR, in which, for example, the amount of principal repaid in each repayment period is an amount calculated to make the DSCR equal to a specified target value. Another reason to sculpt payments may be because of increasing revenues: A project may wish to have smaller early-duration repayments and larger payments towards the back end of the debt term.