Interest Paid in a Monthly model - how to setup quarterly interest payments

6 posts / 0 new
Last post
Kevin Ballinger A+ 8
Interest Paid in a Monthly model - how to setup quarterly interest payments

I'm just double-checking that I have not missed this from the available Debt Modules: it appears to me that Interest Payable is always set to be equal to Interest Paid in the Generic model. I can introduce movements for Historical balances of my other Payables in "WC" but the Interest Payable line appears missing.

Before I embark on creating a new link, likely with a new Block in the Capital sheet to forecast the expected (explicit) Interest payment profile, can somebody tell me if they have come across this before? i.e. Monthly Interest accruals in the P&L, but quarterly Cash flow to match a 3m LIBOR loan for example.

Many thanks.

Michael Hutchens A+ 189

Hi Kevin,

The debt modules in the Generic Financial Model libraries are very basic, and designed to be used as a template for creating your own customized debt modules. Hence, by default interest paid equals interest expense in every period, but feel free to change these calculations as required.

You should not need to amend any module links when doing this, as the debt module already allows for interest payable balances on the balance sheet resulting from interest expense not being paid in the period in which it is incurred.

I've attached a fairly crude example of how the interest paid formula within the monthly Debt (Amounts) module could be customized to support interest expense being paid quarterly, as per the image below. This is crude because it assumes that the 1st period in the model is always the first month in a quarter, so always pays the opening interest payable balance in the first period, and then quarterly interest payments every 3rd month after this month.

This will work fine, as the income statement will have the same tax expense but the cash flow statement will reflect quarterly cash payments of interest instead of monthly.

We're very aware that we need to add a whole range of more advanced debt modules, and we're working to build these at the moment as a part of our upcoming Deals Modeling content libraries. In the interim, you will need to customize the existing modules as required.

Note finally that the existing modules all use categories layout sections, meaning that each debt facility is a category and therefore changes to formulas within any one facility will be applied to all facilities. We're going to move away from this approach in the future, and instead use one module per debt facility - which can then be duplicated or mirrored - but in the interim be aware of the current approach as some new Modano users don't realize that all debt facility formulas within each debt module are auto-aligned...

I hope this helps. M.

Kevin Ballinger A+ 8

Michael, OK - got it. Appreciate the prompt reply....Thanks.

Franz Kiel A+ 2

Hi Michael,

I'm just checking how far you are with the new debt modules? Are they available in only one specific module library? Just asking before I start customizing monthly payment delays.

I'm looking for the best way to model forecast interest payables for quarterly/half yearly interest payments. I want to use it in rolling monthly historical and forecast models.



Blake Gavin Miller A 2

I'm also interested in this thread. 


My challenge is both adjusting the timing of the debt repayment but also needing to capitals some interest - aka adds to the loan balance instead of being a payment.   Getting the cash flow right seems to be a matter of a Trigger to pay not pay (Although I am going to insert Michael's formulae above when I have a chance). 

Are there any suggestions on who I could capitalise the interest? 


Kevin Ballinger A+ 8

The simplest approach I would take here is to drawdown in the Interest Payment period. You maintain the dependencies in the Model (accruing Int Exp and Int Payable), and each quarter link the Drawdown to the Interest Paid - you will see full movements on the Cash flow statement, but Cash will be flat (Interest Paid = Loan Drawn i.e. net nil). Your P&L will work unchanged, and the BS just tracks the payable, then each quarter it flips from your payable liability to Debt liability.

Hope that makes sense.