Net capital expenditure is not a relevant expense item for tax purposes and needs to be 'smoothed’ using depreciation instead of the figures in row 30 (above): Given interest received forms part of the tax paid calculation, that will need to be computed, but there is one other item I need to calculate too. Interest paid and debt repayments are our end products so let me come back to them later. So I suppose we had better get on with it! All of the yellow cells here are inputs: grey cells depict further calculations – not yet made – have to be included. Let me start with a draft cash flow statement: (Click on image to expand)įor simplicity, I assume there is only an initial debt drawdown (cell J37). It’s all about calculating CFADS suitably and I will use the attached Excel file as my example. This method may not generate the right answer and is simply not warranted. Therefore, many modellers will use macros to copy and paste as values the CFADS figure (and possibly the debt balances too) in order to avoid circularities. This can seem like an unavoidable circularity but this is simply not the case. Therefore, if CFADS is used without thought, interest will be a function of CFADS available, but CFADS is calculated after interest. However, tax is based on net profit before tax, which is after interest expense. In any case, in many instances calculating interest on opening balances is acceptable (perhaps after making the periods shorter). Therefore, I won’t be discussing this in this article. I have dealt with avoiding circularities when calculating interest on average balances before using algebra (please see here for further information). Therefore, any reference to a balance or movement other than the opening debt for the period may cause circular logic in any formulae constructed. Drawdowns are often used to fund shortfalls caused by costs such as interest paid repayments are subject to the cash available which is reduced by the amount of any interest paid. Interest is not calculated on the opening debt balance alone.Repayment calculations often get into a tangle leading to circular logic, chiefly for two reasons: Typically, this is the cash generated in the period (usually a calendar or financial year) prior to the debt principal and interest being paid (and any equity payments), but after tax has been accounted for excluding any shield on the interest repaid. Key to calculations is the amount of cash available – known as the cash flow available for debt servicing (CFADS). Modellers often revert to macros to construct the appropriate debt repayment profile. Besides, even the most proficient modellers can make a terrible mess of the three approaches cited above.
I am not saying these are the only techniques in existence (optimum debt sizing and weighted average cost of capital (WACC) optimisation approaches are others), but hey, I have to draw the line somewhere. Loan life coverage ratio (LLCR) approach.Project life coverage ratio (PLCR) approach.Debt service coverage ratio (DSCR) approach.I am going to discuss three common approaches here: This associated debt repayment profile is seldom arbitrary and it is usually forecast to meet certain criteria. Clearly, if debt is to be borrowed it must be repaid at some point. Do you have any tips on modelling debt repayments? A debt to repayĭebt is a key element of most financial models.