A good debt service coverage ratio analysis is the primary factor in demonstrating a business’s ability to meet its debt obligations. To complete a debt service analysis, lenders calculate the debt service coverage ratio (DSCR).
DSCR may seem simple enough, Cash Flow Available divided by Debt Service, but ensuring a debt service coverage ratio formula for banks accurately represents the business’s ability to pay its debts may require a number of adjustments, especially for small business lending.
In this guide for banks and credit unions, we’ll talk about what DSCR is, how to calculate it using Google or Excel, and how to automate DSCR with financial spreading tools.
Let’s dive in by reviewing some of the basics:
What is DSCR?
DSCR is a financial ratio that measures a company's ability to repay its debts. To calculate DSCR, divide a company's net operating income (or cash available for debt service) by its total annual debt payments (or debt service).
The formula for DSCR is:
DSCR = Net Operating Income / Total Debt Service
What is a good DSCR?
If the DSCR is less than 1.0, that means the company isn’t making enough money in a year to cover its debts. A “good” DSCR ratio would be anything higher than 1.0, though many banks and credit unions want to see something closer to 1.25.
The Debt Service Coverage Ratio represents the number of times that a business’s cash available for debt service can cover its debt service obligations in a given year.
A DSCR of 1.0 is at breakeven: a company can cover its debt service exactly 1 time. This business has just enough income to cover its debt payments, without anything to spare. A DSCR of less than 1.0 means that a company does not have enough income to cover its debts.
For example, if a company has a DSCR of 5.5, it means that in one year the company can cover its total debt service 5.5 times. In other words, the company is making 5.5x as much money as it owes in debt payments for that year.
Many financial institutions have a Credit Policy requirement that the DSCR be at least 1.25, meaning that a company would have a 25% margin over its breakeven DSCR.
How to calculate DSCR in Google Docs or Excel
Many community banks and credit unions rely on Excel to calculate DSCR for lending. The simple formula can be laid out as follows:
Column A: Name of the company
Column B: Net Operating Income
Column C: Total Debt Service
Column D: Formula dividing the cell data in Column B by the cell data in Column C
Let’s say we have an example company.
Their net operating income is $3,000,000. And their total debt service is $750,000.
In column D, you would use the formula = B3/C3.
Then, hit enter.
That brings you to a DSCR of 4.00.
But as you likely know, Excel spreadsheets for DSCR rarely look this clean. And so many banks and credit unions rely on complex and interconnected spreadsheets to calculate DSCR.
Inconsistent data entry, copy/pasting, and customization can easily break internal formulas leading to headaches, inaccurate DSCR ratios, and wasted time and money.
An accurate DSCR requires consideration of a number of off-spreadsheet factors.
Experts Tips for a More Accurate DSCR
As we’re sure you know: income statements and balance sheets never take up just two cells. There are many considerations banks and credit unions need to incorporate into their formulas in order to obtain the most accurate DSCR ratio.
These include but aren’t limited to:
- Determining cash flow available for debt service (CFADS)
- Determining dividends and distributions given to owners
- Determining whether the owner is borrowing money from their other businesses
- Incorporating significant expenditures or an unexpected influx of cash, including supply chain issues or litigations, grant incomes, the selling of large equipment, etc.
- Calculating the annual debt service amount
- And factoring in new proposed debt
Let’s cover these factors in more detail:
How to Determine Available Cash Flow in a Debt Service Formula
The calculation of a DSCR ratio is simple in theory. All you should need is a company's annual income statement, which contains its net operating income, its interest expense, and its balance sheet, which shows its current portion/maturities of long term debt.
The first step in an ideal debt service coverage ratio for banks is figuring out the cash flow available for debt service (CFADS). Lenders can use EBITDA (Earnings before interest, taxes, depreciation and amortization) or NCAO (net cash flow after operations) from a cash flow analysis like Uniform Credit Analysis, or UCA. While UCA NCAO is often a more accurate calculation of CFADS, smaller businesses typically do not have the detailed financial statements necessary to use it. Regardless, either can be used in a debt service formula.
In either case, remember that these values do not take into account dividends and distributions given to owners. This is fine when considering the company alone, but in a Global Cash Flow, those may need to be subtracted out.
If not, you run the risk of cash flow being double-counted in the Global Cash Flow Analysis, once by the business as cash available for debt service and then by the owner as a part of his or her personal income.
Additionally, a business may be obligated under its operating agreement to distribute some portion of Net Profit to its owners, meaning that cash flow is not available for debt service and should be backed out in either case. The same situation applies to other owned businesses. It is not uncommon for a small business owner to borrow and lend money to different businesses or even to themselves. All of this must be accounted for when determining cash flows.
Here’s an example of a real DSCR report that shows DSCR before and after distributions:
How to Factor in Unusual Expenses
Next, you need to make any necessary adjustments to CFADS from your cash flow statement. Specifically, unusual, one-time events such as a significant expenditure or an unexpected influx of cash that are not representative of the businesses’ annual cash flow. For example, perhaps a business (small restaurant) had to purchase personal protective equipment due to a pandemic or had to pay a premium to its suppliers because of supply chain issues. That should not be an annual expense, but a one-time expenditure necessary to remain in business during a global crisis. It would not impact the business’s CFADS reality in a typical year and could be added back for a more accurate picture.
Another example might be litigation. Perhaps the business was unjustly sued, and the litigation resolved in their favor. Associated legal expenses could signify a one-time drain on the business that should not hamper its ability to pay off debt in the future. Analyzing several years’ of statements helps identify these one-off events to present a clearer picture of CFADS.
How to Create an Accurate Revenue Picture
The flipside is one-time influxes of cash that also do not represent the yearly reality of the business operations. Perhaps a significant piece of equipment was sold off, generating substantial revenue. This is not representative of dependable future income, and should be backed out of the CFADS calculation.
Similarly, certain capital campaigns or the receipt of grant income, such as the PPP loan program in 2020, are non-recurring sources of revenue, and the lender should consider whether to exclude them from CFADS.
Tips for Identifying Existing Debt
After figuring out the CFADS, it is time to determine the debt service itself. The most traditional method of calculation is to look at interest expense from the income statement and the current portion (or maturities) of long-term debt (CPLTD) from the balance sheet. The CPLTD number for this year’s debt service calculation should be taken from last year’s balance sheet, since it’s the portion of long-term debt due to be paid in the next 12 months. Of course, many small business owners are not familiar with the concept of CPLTD and do not report it on their balance sheets.
Formula for calculating existing debt
A common solution is to use a debt service schedule – a list of current debts and loans – held by the business. This may simply amount to a monthly debt payments list, but if you multiply that monthly list by 12, you have some idea of the annual debt load for the business.
Factoring Proposed Debt
Once you have the cash flow available for debt service, along with the debt service, you can calculate the existing or historic DSCR, but you’ll still need to incorporate the debt service from the new loan request (assuming there is one) to calculate the DSCR (Proposed or Pro-forma).
Keep in mind that the purpose of the new loan may also impact the calculation, like when a business that has been renting its space requests money to purchase a location instead. In such a scenario the historic rent expense may need to be added back to CFADS, as it will be replaced by the new debt service.
How to automate DSCR with financial spread analysis tools
As you can see, each one of the above tweaks and adjustments represents yet another example where Excel-based spreading can introduce errors into DCSR formulas in banking or tie you down in double-checking to be sure no mistakes were made.
But you don’t have to rely on Excel or Google Sheets for accurate DSCR calculations. Financial spreading tools can automate DSCR calculation and give you accurate lending information in just a few clicks.
What are financial spreading tools for DSCR?
Financial spreading tools enable banks and credit unions to easily upload financial data using a familiar excel-style interface. But that’s where the familiarity stops. Financial spreading tools are designed to crawl your imported financial data and accurately calculate DSCR for multiple businesses, multiple people, and multiple loans at the same time.
The best part is?
You never have to worry about your Excel spreadsheet breaking or manually calculating critical entry points. Automating your financial spreading can save you time, money, and hassle - so you can get back to the things that really matter.
Flexibility is important. Especially for community banks and credit unions who feel frustrated by the rigidity of automated spreading tools designed for large regional or national banks.
That’s where FISCAL comes in.
With built-in spreadsheet templates in a variety of formats, from tax returns to financial statements, spreading a business can be done in just a few minutes, and you can easily adjust cash flow available for the debt service as well as the debt service itself in the Debt Service panel. Flexibility allows you to take full advantage of automation while keeping you in the driver’s seat.
