By Kenny Whitelaw-Jones in Financial Modelling Handbook — Jan 6, 2022

Debt modelling 3: Modelling interest

All debt interest is calculated as a rate multiplied by a balance. Always. The complexity is "what is the correct rate" and "what is the correct balance".

Download the lesson start file:

To obtain the worked example file to accompany this chapter buy the financial modelling handbook

In our modelling case, we are told that we have an all-in annual interest rate of 6.3% (made up of a 2.5% swap rate plus a 3.8% senior debt margin).

We're also told that the period interest rate will be calculated based on an actual / 360-day count convention.

This means that the quarterly interest rate will be calculated as the annual rate / 360 * days in the quarter.

Let's walk it through.

Step 1: Grab the number of days in the period

We have already calculated this and used it in the working capital calculation.

I can therefore copy the link from that sheet using Skill 4

Step 2: Add the necessary inputs

These are the swap rate (2.5%), the margin (3.8%), and the number of days in the year given the day count convention (360).

Step 3: Calculate the period rate based on the formula above.

Step 4: Create the interest payment block

Now that we have the period rate, we must multiply by the correct balance. The beginning balance is the correct one to use in this case since principal repayments are made at the end of the period.

Step 5: Sign switch and link into financial statements

Debt interest goes through both the income statement and the cash flow statement. Thereby reducing (liability) and cash (asset) by the same amount.

I can copy the sign switch line from debt principal as it's the closest one.

Step 6: Check IRR impact

We stated previously that IRR was overstated since we hadn't modelled interest, thereby making the debt free money.

It's worth a quick check on IRR to ensure that the IRR went down when we added interest into our model.

Our output sheet confirms that IRR has reacted in the way we would expect.

Download the model completed to this point: