Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

# Weighing Your Debt Load

## Is your company making or losing money on the cash you borrow? These ratios can help to answer that question.

This article is adapted from a column I wrote for Inc Magazine many years ago. The article How Fast Is Too Fast? discusses related ideas.

I've talked to any number of business owners over the years who felt buried under a mountain of debt.

They all complained that they had too much debt. But none of them could tell me precisely how their performance would need to improve before they could make money from the cash they had borrowed.

I'm not surprised that so many managers lack this understanding. Accounting reports are silent on the subject, as are standard techniques of financial analysis.

Fortunately, a simple but little-known formula shows how leverage, interest rates, and operating performance all affect a company's financial results. I've named it the Financial Planning Formula, and I use it to quickly combine planned operating performance with a planned debt structure to asses total financial performance.

Before getting into the particulars of how you can use the formula in your company, I want to run through an illustration of how it works in theory. And to do that, I'm going to exaggerate a bit.

Let's suppose that in a weak moment you agree to loan \$100,000 to Leach, your brother-in-law, who has promised to pay you 60% interest. (What kind of business is Leach in? Don't ask.)

And further suppose that because you have only \$10,000, you make up the difference by borrowing \$90,000 from the mob at a 40% interest rate.

At the end of the first year, Leach pays you \$60,000 interest, as promised. After you pay the mob \$36,000 your pretax earnings equal \$24,000. Before taxes, therefore, you earn a before-tax return on equity (ROE) of 240% (\$24,000 divided by \$10,000). After taxes of 30%, your earnings come to \$16,800, for an ROE of 168%.

The financial-planning formula below shows that your ROE is equal to the sum of two rates (B) and (C), which is then reduced by taxes (A).

The rate labeled B, EOA, is the ratio of your earnings before interest and taxes (EBIT) on assets. This is the return you earn on the fraction of your business financed with equity. If your company were debt free, this ratio would equal your total ROE before taxes.

Suppose, for example, that you gave Leach \$100,000 that you withdrew from savings. With no interest of your own to pay, your earnings before taxes would be \$60,000 and your equity would be \$100,000, yielding a 60% ROE before taxes, compared with the 240% (before taxes) in the illustration.

The rate labeled C shows your total pretax return on the fraction of your business financed with debt. This rate is the product of two calculations.

One calculation is the difference between the EOA and the interest rate, which I call the debtor's margin. This is the difference between what you earn on every dollar borrowed and what you pay.

The other calculation is your debt divided by your equity. The higher this debt-to-equity ratio, the more it magnifies your debtor's margin when you calculate your ROE.

So in the example above, you're earning a debtor's margin of 20%, which the debt-to-equity ratio of 9 magnifies into a return of 180% of equity. Adding the 60% return on your own \$10,000 gives you a before-tax ROE of 240%, and an after-tax ROE of 168%.

In the second year, the loan starts to sour. Business is terrible, Leach says, the market's flooded, and he can pay you only 40% interest this year. When you plug these results into the formula, you get an ROE of 28%.

Because Leach has paid you the same interest rate that you're paying the mob, you break even on every dollar you've borrowed--your debtor's margin equals zero. Your only profits come from the \$4,000 return on your own \$10,000. After taxes, you're left with a measly ROE of 28%, or \$2,800.

In the third year, Leach brings even worse news: he can pay only 10% interest. The magic of leverage turns your negative debtor's margin of 30% into a negative return of 270% on equity. With the positive return of 10% on your own investment, your ROE becomes a negative 260% before taxes and a negative 182% after taxes.

So Leach has paid you \$10,000; two guys from the mob are at your door to collect their \$36,000; and you don't have the cash to pay them.

### How to calculate your own ratios

When you apply the financial-planning formula to your own financial statements, you'll probably discover at least two practical concerns.

First, unlike the example, your own company has several sources of debt. Suppose, for instance, that you have total debt of \$350,000, which consists of \$200,000 of accounts payable, a long-term loan of \$100,000 at 10%, and a short-term note of \$50,000 at 12%. Also assume that you have an EOA of 9% and a net worth of \$150,000.

Your average interest rate is 4.57% (\$10,000 + \$6,000 divided by \$350,000), and your financial benefit from the use of debt can be calculated using the formulas below.

However, this calculation makes your performance look deceptively good, because the average interest rate includes a large portion of accounts payable in its calculation. To get a better picture of what's actually happening with your performance, calculate the financial benefit separately for each type of debt, as shown below.

Of course, this doesn't change your results. The total still equals 10.33%. But this approach shows you that your low EOA costs you money for every dollar you borrow.

The other problem you're likely to find is that your financial results are rather messy. Perhaps it's the middle of the year and you aren't sure what your EBIT is going to be. Or it may be that you've borrowed a lot of money recently, which throws all your ratios off. What, then, are the correct values to use in the financial planning formula?

During the year, I generally calculate the EOA by dividing the EBIT over the most recent 12 months (the "rolling EBIT") by the average assets during that time. I also use the average debt-to-equity ratio, and the stated interest rates for debt.

### How to use the ratios in your company

• Pay attention to your EOA, your ratio of EBIT on assets. Few people talk about this simple ratio, but it's a critical measure of business performance. If your EOA is comfortably above your interest rate, you're making money on borrowed cash. If your EOA is below your interest rate, you're losing money on what you borrow. And because no ratio has pinpoint accuracy, if your EOA is about equal to your interest rate, assume the worst.

• As you know, leverage multiplies good and bad performance. In this regard, I prefer the British term "gearing" to the American term "leverage." Raising your debt-to-equity ratio shifts your financial performance into a higher gear, which causes your business to move forward or backward more rapidly.

• Because your EOA rises in good times and falls in bad times, and because leverage magnifies the effect of these swings on your ROE, be sure your financial structure allows your company to hang on for the bad times. If your debt-to-equity ratio is so high that a bad year could devastate your equity, start now to reduce the ratio. And look for ways to shore up your EOA as well, either by improving earnings, reducing assets, or both.

• When you evaluate your ROE, balance your returns against your risks. For example, in the second year of Leach's loan I referred to a "measly" ROE of 28%, although many managers would be delighted with that return. But considering the huge risks this enterprise bears, that 28% return signaled failure.

• Above all, watch your cash flow. Whatever your financial analyses may tell you about an impressive return on equity, you won't survive unless you can pay your bills on time, and still have cash for payroll.

Finally, here's how I derived the Financial Planning Formula:

ExcelUser, Inc.
http://www.ExcelUser.com

Copyright © 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

### Excel Dashboards

Create professional quality dashboard reports with Excel.