How to Quickly Calculate Debt Service Coverage Ratio [Guide for Banks]

Posted by Fiscal on October 07, 2022

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 for Debt Service (CFADS) divided by Debt Service, but for banks, ensuring that the debt service coverage ratio formula 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 give you every detail you could possibly want to know about DSCR and CFADS. 

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 flow available for debt service- CFADS) 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’s net operating income can cover its total debt service 5 and a half 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.

What does Cash Flow Available for Debt Service (CFADS) mean?

CFADS, also commonly called cash available for debt service (CADS), is the amount of funds available to pay for a business’s debt obligations. It assesses the various cash flows into and out of the business and looks at the ability to pay debts, both on an annual basis as well as for longer periods, and even over the lifetime of a project.

Why Use CFADS?

Cash Flow Available for Debt Service is a key formula in finance and lending for several reasons. One is that because it looks at the ability to pay debt over time and takes into account expenditures such as paying taxes, it is generally a reliable and accurate assessment of the ability to pay over the life of the loan. CFADS, as lenders know, is used in several important formulas and calculations, including:

  • Determining debt size and the principal repayment schedule
  • DSCR: Debt Service Coverage Ratio
  • LLCR: Loan Life Coverage Ratio
  • PLCR: Project Life Coverage Ratio

While small business lenders recognize that CFADS is a more accurate figure in determining the lending capacity of a project, such as building expansion, they are often going to use simple EBITDA instead, as it is faster and easier to calculate.

Now that we have the basics covered, let’s dive further into DSCR. If you want to dive right into CFADS, you can jump to that section by clicking here. 

How to calculate DSCR in Excel or Google Docs

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

How to Calculate DSCR in Excel

Let’s say we have an example company.

Their net operating income is $3,000,000. And their total debt service is $750,000. 


From there, the debt service ratio formula is:

In column D, you would use the formula = B3/C3.

Then, hit enter.

That brings you to a DSCR of 4.00.

Completed DSCR Calculation

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. 

H2: How to Calculate CFADS

Before we dive into the formula for CFADS, let’s define some of the terms that will be used in the calculation.

  • Revenue: Business income from operations and other revenue
  • Expenses: The cost of operating the business, including maintenance, leases, or labor costs
  • Net working capital adjustments: Any adjustments the business makes to get from accrual to a cash basis
  • Cash Tax: The cost of taxes paid in cash, excluding accrual tax expenses
  • Other items: These expenses could include refinancing fees or annual fees senior debt

One way to calculate CFADS is the waterfall model, a top-down method. Loan analysts start with revenue, and then factor in the outflow and inflow of cash in the order that they occur, such as operating expenses (opex), capital expenditures (capex) operating revenues, taxes, and funding.

Lenders could also use NCAO (net cash flow after operations) from a formal cash flow analysis, such as Uniform Credit Analysis (UCA). While this is certainly more accurate, small businesses often don’t have the detailed, professionally prepared financial statements needed to use this method.

CFADS formula

When evaluating a loan for a small business, your formula might look something like this:

CFADS = Revenue – Expenses +/- Net Working Capital Adjustments – Capital Expenditures – Cash Tax – Other Items

A more common method for calculating CFADS would be a bottom-up approach, especially in small business lending that commonly works from tax returns or management-prepared financials. Here, the analyst would start with Net Profit, then add back non-cash expenses like depreciation and amortization, as well as interest expense and income taxes.

Project-specific or unusual one-time items, such as rent expense if the proposed loan is to purchase an operating location or a non-recurring expense or income item, should also be adjusted to ensure an accurate reflection of the company’s typical, recurring CFADS.

Common Mistakes When Calculating CFADS

When calculating CFADS, following best practices is essential. Here are a few common mistakes.

  • Calculating CFADS for just a single year or period: Especially in small business lending, there may be significant ups and downs in a company’s financials from one year to the next, and reviewing several statement periods is important both to establish a baseline as well as for correctly identifying unusual one-time items that may need to be adjusted.
  • Not making adjustments to the text-book calculation: As stated above, this may require analyzing multiple years (typically 3) of financials to identify correctly. Other adjustments may be project-specific; if a company is purchasing an office building after renting for many years, it may be appropriate to add back the historic rent expense to the CFADS calculation.
  • Including incorrect and non-cash flow items: Items such as depreciation, cash balances and reserve account balances should not be included in the CFADS calculation.
  • Attributing account dividends and distributions incorrectly: The values of EBITDA and NCAO do not consider dividends and distributions given to owners. While this may be acceptable when looking at the company on its own (see exception below), it must be subtracted out when calculating Global Cash Flow. If not, you run the risk of double counting these items.
  • Counting required distribution of Net Profits: In some cases, a business may be obligated to distribute some of its Net Profits to owners. That cash flow is not available for debt service and should not be counted.
  • Incorrectly accounting for other entities: Small business owners may borrow or lend money from one business entity to another, or even to themselves. These transactions must be factored in when calculating cash flows.

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:

  1. Determining cash flow available for debt service (CFADS)
  2. Determining dividends and distributions given to owners
  3. Determining whether the owner is borrowing money from their other businesses
  4. Incorporating significant expenditures or an unexpected influx of cash, including supply chain issues or litigation, grant income, the selling of large equipment, etc. 
  5. Calculating the annual debt service amount
  6. 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 and 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. Institutions will have to decide which option should be used in a debt service formula for each deal.

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:

DSCR report before and after distributions-1

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’ typical annual cash flow. For example, perhaps in a particular year 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 income. This is not representative of dependable future revenues, 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 input financial data using a familiar excel-style interface without the tedious manipulation of rows and columns. Financial spreading tools then calculate and store key values like cash flow available for debt service, annual debt service, etc. and allow for quick, accurate DSCR and Global Cash Flow analysis, even for deals involving multiple businesses, multiple people, and multiple loans.

The best part is? 

You never have to worry about your Excel spreadsheet breaking or accidentally carrying formula adjustments from one deal over to the next. Automating your financial spreading can save you time, money, and hassle - so you can get back to the things that really matter. 

But 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 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.


Request a Demo of FISCAL