Excel guide · 3 min read
How to calculate the debt-to-equity ratio in Excel
=SUMIFS(D2:D10,B2:B10,"Liability")/SUMIFS(D2:D10,B2:B10,"Equity"). This business owes $107,000 against $69,500 of owner's equity, a ratio of 1.54.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Account | Class | Interest-bearing | Balance | Month end | Total liabilities | Total equity | Debt-to-equity | Setting | Value | ||
| 2 | Cash | Asset | No | 42000 | Jul | 118000 | 58000 | 2.0344827586 | New loan (what-if) | 30000 | ||
| 3 | Receivables | Asset | No | 38500 | Aug | 114000 | 61200 | 1.862745098 | Owner draw (what-if) | 10000 | ||
| 4 | Equipment | Asset | No | 96000 | Sep | 110500 | 65000 | 1.7 | ||||
| 5 | Accounts payable | Liability | No | 21400 | Oct | 107000 | 69500 | 1.5395683453 | ||||
| 6 | Credit card | Liability | Yes | 3600 | ||||||||
| 7 | Bank loan | Liability | Yes | 64000 | ||||||||
| 8 | Equipment lease | Liability | Yes | 18000 | ||||||||
| 9 | Owner's capital | Equity | No | 40000 | ||||||||
| 10 | Retained earnings | Equity | No | 29500 |
Download this sheet as an Excel practice file (.xlsx)
Step 1: Total liabilities and equity with SUMIFS
A2:D10 is a small business's balance sheet at the end of October, one account per row with its class: Asset, Liability or Equity. Most accounting software can export a balance sheet in roughly this shape.
Rather than adding cells by hand, total each class with SUMIFS so the formula keeps working when an account is added. Liabilities: =SUMIFS(D2:D10,B2:B10,"Liability") is $107,000. Equity: =SUMIFS(D2:D10,B2:B10,"Equity") is $69,500.
Quick check: assets ($176,500) should equal liabilities plus equity ($107,000 + $69,500). If they don't, an account is missing or has the wrong class, and the ratio will be wrong too.
Result: 107000 shows as $107,000 with Currency format
107000: everything the business owes at month end.
Step 2: The debt-to-equity ratio
Divide one SUMIFS by the other: =SUMIFS(D2:D10,B2:B10,"Liability")/SUMIFS(D2:D10,B2:B10,"Equity"), about 1.54. Read it as $1.54 owed for every $1 the owners have in the business.
Show it with two decimals (Number format 0.00). Some people show it as a percentage, 154%; it's the same number, so use whichever your reader expects and don't mix them in one report.
Result: 1.5395683453 shows as 1.54 with Number format
About 1.54: total liabilities are 1.54 times owner's equity.
Debt only: leave out bills you'll pay this month
"Debt-to-equity" is used for two different calculations. The one above counts all liabilities. A stricter version counts only borrowing that charges interest: loans, leases, credit card balances. Accounts payable (supplier bills) and similar items stay out.
Mark each liability in a column like C, Interest-bearing, and sum only the Yes rows: =SUMIFS(D2:D10,C2:C10,"Yes")/SUMIFS(D2:D10,B2:B10,"Equity"). Here that's $85,600 / $69,500, about 1.23.
Neither version is wrong, but they aren't interchangeable. If a lender or an agreement mentions a ratio, use the definition written there, not the one in this guide. Label the cell with the version you used.
Result: 1.2316546763 shows as 1.23 with Number format
About 1.23: interest-bearing debt only, against owner's equity.
Month-end trend
One month tells you little. F1:I5 keeps the two totals at each month end and works out =G2/H2 for each row: July 2.03, August 1.86, September 1.70, October 1.54.
The ratio fell because liabilities came down and equity grew as profit was kept in the business. When you add a month, paste that month's two SUMIFS results as values into a new row so the history doesn't change when the balance sheet tab is refreshed.
Result: 2.0344827586 shows as 2.03 with Number format
About 2.03: the ratio at the end of July.
What a new loan or an owner draw does
A new loan adds to liabilities; the cash it brings in is an asset, so equity doesn't change. With a $30,000 loan in L2: =(SUMIFS(D2:D10,B2:B10,"Liability")+L2)/SUMIFS(D2:D10,B2:B10,"Equity") is about 1.97, up from 1.54.
An owner draw works the other way. It takes cash out and lowers equity, while liabilities stay where they are. A $10,000 draw in L3 gives =SUMIFS(D2:D10,B2:B10,"Liability")/(SUMIFS(D2:D10,B2:B10,"Equity")-L3), about 1.80.
If equity is zero or negative (losses or draws larger than everything put in), the ratio is meaningless: dividing by a negative number gives a negative ratio that looks better than a positive one. Guard it: =IF(H5<=0,"Negative equity",G5/H5). This is a way to read your own numbers, not lending or accounting advice; your accountant can tell you which balances belong in each class.
Result: 1.9712230216 shows as 1.97 with Number format
About 1.97: the ratio after borrowing another $30,000.
Common mistakes
Dividing by total assets
Liabilities / assets is the debt ratio, a different measure. Debt-to-equity divides by equity.
Mixing the all-liabilities and debt-only versions
Pick one, label it, and use it for every month in the trend.
Showing a negative ratio as if it were good
Negative equity makes the ratio negative. Wrap it in IF and show "Negative equity" instead.
Signs flipped in the export
Some exports show liabilities and equity as negative numbers. Check the totals are positive before dividing, or wrap each SUMIFS in ABS.
FAQ
What is the formula for debt to equity ratio in Excel?
Total liabilities divided by total equity. With a class column: =SUMIFS(D2:D10,B2:B10,"Liability")/SUMIFS(D2:D10,B2:B10,"Equity").
What is a good debt to equity ratio?
There's no single answer: it varies a lot by industry and by how a business is financed. Compare your own trend over time, and if a lender sets a limit, use their definition and their number.
Is accounts payable included in debt to equity?
In the common total-liabilities version, yes. In a debt-only version, no: only interest-bearing borrowing counts. State which one you use.
Can debt to equity be negative?
It comes out negative when equity is negative. That doesn't mean low debt; it means liabilities exceed assets. Show it as "Negative equity" rather than as a ratio.
Functions used
Related guides
- How to calculate the current ratio and quick ratio in Excel
- How to calculate the interest coverage ratio in Excel
- How to calculate the debt service coverage ratio (DSCR) in Excel
- Formulas for a monthly budget in Excel
- How to calculate budget vs actual variance in Excel
- How to calculate cost per unit in Excel
- How to calculate debt-to-income ratio in Excel
Updated