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:
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:
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.