Financial Planning
Weighing Your Debt Load
Here's
how to use Excel to figure out whether you're making or losing money on the cash you borrow.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Originally published in Inc Magazine.)
I've talked to any number of business owners over the years
who felt buried under a mountain of debt. But none of them say
whether they're making or losing money on the cash they borrow,
or precisely how their debt would have to change for it to reach
a manageable level.
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 littleknown formula shows how
leverage, interest rates, and operating performance all affect a
company's financial results. I've named it the
financialplanning 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 brotherinlaw, 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. You therefore earn a beforetax 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 financialplanning 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 is the ratio of your earnings before interest
and taxes (EBIT) on assets (EOA). This is the return you earn on
the fraction of your business financed with equity. In other
words, 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 debttoequity ratio, the more it magnifies your
debtor's margin when you calculate your ROE.
In the example above, therefore, you're earning a debtor's
margin of
20%, which the debttoequity ratio of 9 magnifies into a return
of 180% of equity. Adding the 60% return on your own $10,000
gives you a beforetax ROE of 240%, and an aftertax 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
borrowedyour 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 financialplanning formula to your own
financial statements, you probably will 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
longterm loan of $100,000 at 10%, and a shortterm 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
total assets on the current balance sheet. For companies with
seasonal sales, I use the average assets over the past 12
months. I also use the current or the average debttoequity
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 term
"leverage." Raising your debttoequity 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 debttoequity 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.
• Watch your cash flow. Whatever your plans may tell you about
an impressive return on equity, you won't survive unless you can
pay lenders and vendors on time, and still have cash for
payroll.
