Debt Schedule

9.1 – Dealing with debt

We dealt with fixed assets in the previous chapter. The fixed assets, as you realize, is the most oversized line item on the asset side of the balance sheet. In this chapter, we will deal with the debt, which is present on the liabilities side of the balance sheet.

We will use the base rule again to help us deal with debt.

If you glance over the balance sheet, on the liabilities side, 

There are three things to note here –

• • The debt numbers are ‘non-current, in nature. This means these are long-standing debt, carried across multiple years
  • Secured loan – loan against collateral (mainly in the form of tradable securities)
  • Unsecured loan – Non-collateralized loan.

Generally speaking, an unsecured loan comes at a higher rate. In our model, we have secured and unsecured loans stated separately, but this may not always be the case.

To give you a perspective, I’ve picked the balance sheet of Relaxo Footwear here to highlight the borrowings –

The borrowing is under current liability, which means the borrowing is short term in nature. As you can see, the company generalizes the ‘borrowing’ and does not specify if it’s secured or unsecured. To figure the nature of borrowing, you can dig deeper into the associated notes; note 15 in this case.

The notes specify that there is no non-current borrowing. But if you notice, there was a non-current, secured loan in 2019, which is repaid.

For FY 2020, the current loan outstanding (Rs.19.16 Crs) is secure. Further, we can also see the securities tendered for securing the loan.

Take another example –

We have the balance sheet of Biocon Limited for March 2021. The company has borrowings under both current and non-current liabilities side. Note that the company does not give any details of the nature of these borrowings in the balance sheet. Instead, the associated notes give us all the details.

Here is the snapshot of Note 14, giving the details of the borrowings under the non-current liability side –

The borrowing is both secured and unsecured.

The details of the current liability borrowings are as follows –

Now, as long as you get the split of the loans, you can build a debt schedule using the technique we will discuss in this chapter.

9.2 – Sheet setup

We will set up a sheet similar to the asset schedule. I suppose you are pretty familiar with how to go about setting up the sheet –

We follow the usual format protocol here, i.e., index columns A & B, expand column C, freeze panes, and link Y1 to Y10 from cell E to N. I hope you are comfortable with the base rule to deal with the opening and closing balance figures. Else, I’d suggest you go through the previous chapters to figure how.

As you can see below, I’ve set up the base rule for both secured and unsecured loans.

In cell E8, I’ve linked the secured loan value of Year 1 to denote the closing balance for Y1. As per the base rule, the closing balance of Y1 works as the opening balance of Y2.

For Y2, the closing balance of the secured loan is Rs.226.65 Crs, clearly suggesting that the company has new loan issues to the extent of Rs.119.16Crs.

Further, in Y3, we see that the closing balance for the secured loan is Rs.207.83Crs, which implies that the company has repaid a portion of the loan.

You can extend the same for all the years for secured and unsecured loans and build the sheet. Here is how my sheet looks now –

The next bit is the projection of how the future year new issues and repayments will look like. The best way to estimate this is by understanding the management’s CAPEX plans. If the management has ambitious CAPEX plans, I think it’s fair to project the debt, keeping the management’s guidelines in perspective.

I’d like you to watch this video clip, where the CMD of HPCL talks about CPAEX plans and the means to fund the CAPEX –

The CMD also states figures to indicate the amount via debt. The point here is that when you have to project the new issue and repayment figures, always look for what the management has to say. You can find this information by skimming through the annual reports, analysts conference call transcripts, management interviews etc.

If none of that is available, then you will have to project based on the previous trend. The trend in our model is easy to establish. In the years Y1 and Y2, the company had a large outstanding loan, which over time has reduced.

The company availed no new fresh issues, and we can also see that the company has repaid the loan. We can expect a similar trend to continue and project for the future years. To do that –

• • Keep new issue at zero
  • Calculate the average repayment

If the above technique does not fit well with your approach, let the debt remain. The worst that will happen with some debt on the book is that our final valuation may turn out a bit conservative, which is not a bad outcome, in my opinion.

I’ll keep the debt as is in this model and complete the secured and unsecured loan.

Please look at the schedule at this point. There is an opening balance and a closing balance, and then there is secured and unsecured debt. If I were to estimate the company’s debt position, how can I do that? Should I consider the opening balance as on 31^st March or the opening balance on 1^st April?

To address this, we can take the average across the opening and closing balance of both secured and unsecured debt and get the average loan outstanding.

Next, from the P&L, we know the interest expense for the year. By dividing the interest expense over the average outstanding loan, we get the interest rate applicable to the company. I’ve executed both these steps on excel, 

As you can see, I’ve calculated the average of the opening and closing balance across both secured and unsecured loans.

I’ve divided the interest expense stated in P&L over the average outstanding loan for the interest rate.

Now that we have the applicable interest rate, we can project the future year interest rates by taking an average.

At this point, we have the average interest applicable, plus we have the average loan outstanding, with we can project the future year’s interest expense as well. All we have to do is multiply the interest rate with the average loan outstanding.

We can do this directly in the P&L.

Note the numbers from the debt schedule are flowing into the P&L, and with that, we have made the 2^nd P&L projection.

We can pull the numbers from the debt schedule to complete the non-current liabilities on the balance sheet.

Key takeaways from this chapter

• • • Debt can be secured or unsecured
    • The balance sheet gives us the overall debt; associated notes give us the split between secured and unsecured loan
    • We can calculate the average of opening and closing balance across secured and unsecured loans to get the average loan
    • The best way to project debt is by understanding the management’s view on CAPEX
    • If there is no management guideline, its best to keep debt at the same level