Interest Rate Calculation & Debt Schedule

We will create a document resembling the asset schedule. It’s likely that you are well-versed in how to put it together.

This workflow follows the typical protocol, requiring us to index columns A & B, expand column C, freeze panes, and link Y1 to Y10 from cell E to N. I trust that you understand the basic rule for dealing with initial and final balance totals. Should you need further information on this, please check past chapters.

One can view the base rule I put in place for secured and unsecured loan types below.

In cell E8, I’ve linked the secured loan value of Year 1 to denote Y1’s closing balance – which also serves as Y2’s opening balance, in accordance with the base rule.

For Y2, the secured loan balance closes at Rs.226.65Cr, showing that new loan issues amount to Rs.119.16Cr.

In Y3, the closing balance for the secured loan is Rs.207.83Cr, indicating that part of the debt has been settled.

One can use the same approach for loans secured or unsecured and create a spreadsheet. This is how my current one appears –

The following year’s new issues and repayments can most accurately be predicted by assessing the management’s CAPEX intentions. As long as we stay aware of their instructions, it is reasonable to assume that debt levels will rise if they have taken on considerable CAPEX initiatives.

I’d like you to take a look at this video, where the CMD of HPCL delves into their CAPEX strategies and how they plan to finance them.

The CMD has provided figures to assess the amount of debt. When projecting new debt issues and payments, always refer to what the management has to suggest. This information can be gained by skimming through annual reports, analyst call transcripts and interviews with management etc.

If no available data exists, you must predict according to past trends. Our model outlines a decrease in loans over Y1 and Y2. Initially, they had a large debt, but later decreased.

The company has not released any new products and they have also paid back their loan. We anticipate similar trends to keep going in the years ahead. To make that happen –

Keep new issue at zero

Find out the average repayment.

If the technique does not suit your style, leave the debt as is. The most that can occur is our valuation may view smaller than expected, which I don’t consider to be a negative consequence.

I will maintain the debt as is in this model and finish off by creating the secured and unsecured loan.

At this juncture, it would be beneficial to examine the schedule. The starting and ending balances, along with the secure and unsecured debt must all be accounted for when trying to make an estimate of the company’s debts. When pondering this matter, I should consider if I should use 31st March’s opening balance or 1st April’s beginning balance?

To address this issue, we can calculate the average loan outstanding by taking the mean of the opening and closing balance of both secured and unsecured debt.

Firstly, from the P&L, we are able to calculate this year’s interest expense. Through dividing this total cost over the average balance of loans, we can work out the applicable interest rate for the company. From there, I have taken the necessary steps on excel in order to generate the sheet you see now.

I have done the math and found an average of the opening and closing balances for both secured and unsecured loans.

I spread the P&L interest cost among the average loan balance at the rate of interest.

Once we have determined the applicable interest rate, we can determine an approximate estimate of the future year rates by averaging them.

We have the average interest rate and loan outstanding, which enables us to calculate the future year’s interest expense by multiplying them together.

In the Profit and Loss statement, we can take action right away.

The debt schedule figures have been fed into the P&L, allowing us to make our second projection.

We can use the figures from the debt plan to complete the non-current liabilities part of the balance sheet.