On May 8th 2014 we held the "Debt Sizing in Excel" webinar. Over 800 people registered for the event.

If you missed it, you can watch the webinar by clicking here.

During the the webinar we had a lot of questions about the model and the methods we were presenting. We tried to get through as many of the questions as possible during the webinar, the ones we didn't have time for are below.

I hope you find them useful. If you have any other questions please feel free to post them below.

Q: Fiona, could you show with 1-2 examples the flow of how the after-tax debt discount rate has been varying year to year over the maturity?

A: We recommend that you take a look at the calculations in Debt! Row 49. The calculations for how we have varied the after tax discount year by year are set out there. You will see in the model how our period calculations are based on the relevant forward rates.

Q: Is this method also applicable for Project finance transactions? I think then one should use the cashflow waterfall approach to get to CFADS.

A: A cash flow waterfall approach could also be used to arrive at CFADS. What's important is that CFADS is post tax and pre financing.

Q: Can you please discuss what discount rates are used for the CFADS/Sustainable CFADS approaches? And what is the rationale behind those rates?

A: Since we are assessing the total amount that a debt investor might be prepared to offer, we start with a discount rate that reflects the debt investor's risk appetite. Hence the discount rate is made up of

(i) a risk free yield curve and

(ii) a margin for corporate credit risk.

Then, we lower that rate to reflect the tax advantage of taking out debt (namely that interest is tax deductible). So the rate reflects both

(i) the debt investor's required rate of return; and

(ii) the tax relief on interest payments.

The higher the debt investor's rate of return; the lower the total debt capacity. But an increase in tax rate would, all other things being equal, increase total debt capacity.

Q: When using the PPMT function to calculate a simple mortgage style repayment profile, the PPMT needs a constant rate over the duration of the loan otherwise it will not repay – what interest rate would you use if the actual base rate + margin are changing? What if there are different layers of senior debt (pari-pasu)? e.g. Tranche 1 Export Credit Agency, Tranche 2 Commercial banks etc. How do you allocate the principal repayment to different tranches?

A: It is possible to use the PPMT to calculate this period's principal repayment but considering remaining debt repayment periods - along the lines of PPMT (periodic rate, 1, remaining debt periods, principal outstanding).

So each period the calculation looks at current period rate, current payments remaining and current principal outstanding. However, you will find that debt service may not be equal in each period. If the target is equal debt service throughout the repayment periods then this is best achieved using a goal seek:

Set the difference between:

(i) initial balance and

(ii) Principal repayments to zero by changing the constant debt service.

We will publish a financial modelling handbook guide on this in due course.

Q: Please can you briefly explain what "value gained from interest tax shield" means?

A: Interest is deductible against pre-tax profits. This means that the company's tax bill is reduced because of the interest charge.

Dividends, on the other hand are paid from after-tax profits and do not carry this 'tax break'. Its is known as the 'interest tax shield'. The result is that having debt in the balance sheet results in a value gain from the interest tax shield. However, this gain is only available to the extent that the value of the tax shield is greater than the rising costs of both debt and equity as the company takes on more leverage.

On the power point slide showing the WACC curve, the point of inflection on this curve (where the WACC is at its lowest point - and the company is maximising the value from the interest tax shield), is achieved at different levels for different industry sectors. If a company has low business risk, it can usually gain more from the interest tax shield because it can access more debt proportionately and at better rates to finance the assets, than companies with high business risks.

Q: Is the maximum debt capacity equivalent to 1x ratio in previous method?

A: No. Because we are using no growth cash flows to calculate the sustainable cash flows (Approach 2) whereas Approach 1 (Discounting of actual forecast CFADS method) uses the actual growth cash flows projected under the business plan.

If we used a 1x ratio for Approach 1, then we would overestimate the senior interest bearing debt capacity. Also there would be no cash flows left over for the equity capital providers.

Q: Why is it that in PF transactions, higher leverage is preferred? Specifically considering the WACC explanation showing that the optimal solution lies close to 50/50 D/E.

A: Regarding the WACC explanation on the power point showing the optimal solution lies close to 50/50. Here we are making a generalisation. Across business sectors, on average, mostly 50/50 D/E in market value terms will be around where the optimal capital structure point will be. But this is a very broad generalisation.

For the case company we used on the webinar, their business risks are higher than the average firm and so the optimal point is reached at lower levels of debt/equity. The optimal point is achieved at different D/E proportions for different industry sectors.

If a company has low business risk, it can usually gain more from the interest tax shield because it can access more debt proportionately and at better rates to finance the assets than companies with high business risks.

Q: In method 3, why is £68m the optimum point? 1/1.3, 76% - still seems quite high for a "high risk forecast ... ?

A: The company is moderately high risk rather than high risk.

This is because it is not in a cyclical sector particularly - producing low margin household cleaning products and personal care products. So there is reasonable underlying stability to the revenues.

Also historically margins achieved are not too erratic for this firm. So although capex needs are high and it has relatively high fixed operational costs, the risks are considered moderate to high. We assess this from the analysis of the sensitivity of the cash flow to the key cash drivers. We track this over time and vs. comparable companies to sense check too.

At the end of the analysis, there is always some element of subjective judgement and there may be some bankers who would consider a higher coverage.

So, in conclusion, the 1.3 x coverage takes into consideration the historic volatility of key cash drivers as well as our view on these going forward. This would mean that the senior lenders would have a reasonable expectation of repayment in full within the time frame proposed based on typical volatilities of key cash drivers at the 1.3 coverage level.

Q: Please provide detailed calculation and assumptions behind Beta calculation.

A: The detailed calculations and assumptions behind the Beta figure are done by our data provider - Thomson Reuters. Their beta is based on the Capital Asset Pricing Model. A linear regression model is used to assess the stock price of the firm vs. the market.

The Beta denotes the firm's systematic risk proportionate to the systematic risk of the general market or index used. Company specific risks (which are not measured by Beta) are taken into consideration in the calculation of the company's operational cash flows.

Q: Have you applied any of these methods to very small private companies? My space includes revenues under $5 million, where levels of sophistication are a lot less than what was presented today.

A: The methods we use for very small private companies are approaches 1 and 2, based on discounting for forecast CFADS. This translates well into small companies.

For small companies we also focus heavily (as we do with larger companies too!) on working capital management and the impact on financing needs of changes in working capital needs. We consider in detail how best to finance the permanent and seasonal / fluctuating elements of working capital and we consider different sources of finance that can be accessed to meet these needs. This can be really key for small, especially fast growing companies

Q: How did you choose the present value factor for the sustainable CF method?

A: The PV factor is calculated from two things - first the number of years or periods away the cash flow occurs, second the companies long term cost of debt after tax.

So PV = PMT/1+r^n where n is the number of periods away the cash flow occurs and r is the long term cost of borrowing after tax. However there is a little more to say on this as, in our model, we have not used a single rate for throughout the forecast period. Our calculation for r is calculated from the forward rates and therefore reflects anticipated rising interest costs based on the current outlook.

Please take a look at the calculations for the PV factor in the actual model and let us know if you have any further questions. We would be please to provide more information if required.

Q: Which method is the most common approach and most widely used in the market. Given that mid corporates are unlikely to be graded the third method may not be as relevant. I am asking mostly in the context of PF.

A: We agree that method 3 works best for graded companies, although implied ratings can be derived as shown in the model for non-graded companies.

However in all cases, our recommendation is to focus on Approach 1 and 2 for the practical debt sizing. Approach 3 is something to would really only spend time modelling and analysing for large companies in developed capital markets where the corporate bond market is deep and active across the credit grades.

The emphasis on modelling and teaching in our Debt Structuring course is on Approaches 1 and 2.

Q: Is the add back of Depreciation and Amortisation only relating to Maintenance CAPEX?

A: We can use the depreciation figure as a proxy for maintenance CAPEX.

For many companies this works reasonably well, however, not for all companies / situations. It depends where the company is in its CAPEX cycle - maybe they have recently invested heavily in modernisation and maintenance capex going forward will be lower than depreciation, or, alternatively, they may have underinvested for a number of years prior, in which case maintenance capex needs will be higher than the depreciation figure.

Q: Why do we need to adjust the cost of debt with the tax rate? Will it not be more prudent to have higher discount rate? Or take the cash flow after tax to be more precise?

A: We need to make a choice as to where to bring the tax break on debt into the analysis.

- Choice 1 would be to bring this into the cash flow - as you say: take the cash flow after tax .
- Choice 2 would be to bring the tax break into the discount rate. Our preference (and that of most practitioners) is to bring the tax break into the discount rate. This means that we calculate operational cash flows without any impact from the financing decision, then we can separately consider how to finance those operational cash flows.

In conducting the debt capacity calculation we recommend choice 2 - i.e. to use the after tax (or true) cost of borrowing to the firm.

Q: Why do you use MV(Eq)/sum(MVeq,MVdebt) for WACC weight instead of Volume(Eq)/sum(VolEq,VolDebt)?

A: We use market values rather than volume. Most practitioners and academics recommend the use of market values for the WACC formula. So for equity market value this is volume of shares * share price.

For debt we can either use the book value (the norm for small companies unless they are in financial distress) or market value when considering larger firms that have publicly traded bonds outstanding.

For large companies with publicly traded bonds outstanding, we look at the Yileld to Maturity (YTM) that bond investors require today for investing in the company's long term bonds and apply that yield to derive the present value (PV) of their current debt based on the firms current interest expense figure.

But this latter approach only really translates well for large companies. It is fine to use book value for anything other than the largest companies with publicly traded bonds outstanding for the debt weighting in the WACC calculation.

Q: Is expected equity return factored into this maximum debt size estimation approach?

A: With Approach 2 - the discounting of sustainable CFADS, because we are looking at no growth sustainable cash flows, the implication is that any growth in the cash flows above the sustainable cash flow level will belong to the Equity investors.

For this reason we do not factor in any suggested equity return in to the sustainable cash flow discount rate. Whereas, in contrast, when we work with actual growth cash flows under Approach 1, by applying a DSCR (Debt Service Cover Ratio), e.g. of 1.3, the implication is that it is the 0.3 part of the coverage ratio that goes to the equity investors.

Q: In project financing, the optimal debt : equity is 70:30, which is absolutely opposite.

A: With project finance it is often possible to reduce the risk of the projects operational cash flows through offtake agreements, throughput agreements, and via various forms of derivative contracts.

There are often a range of contingent support arrangements in place from third parties or from project sponsors. This makes the project's cash flows more certain / less risky. For this reason the debt capacity is higher (e.g. on Approach 1 we take a lower DSCR than 1.3 for the case company.) and this will result in higher D/E levels in most project financings.

We currently offer two banking related training courses - click the images below to find out more.