How to Calculate Debt Service Coverage Ratio (DSCR) in Excel

This guide will explain how to calculate the debt service coverage ratio or DSCR of an organization.

The Debt Service Coverage Ratio or DSCR is a financial metric that assesses the company’s ability to pay off their debts. This metric is often used by investors or creditors to evaluate the risk associated  with providing loans or credit to a particular business.

For instance, consider you are interested in acquiring a new property for your business and need a mortgage. The lender may require you to demonstrate the property’s ability to cover the debt from the mortgage using the DSCR.

In this guide, we will look into the data you need to calculate the DSCR. We’ll also provide a step-by-step tutorial on how to compute this metric using Excel formulas.

A Real Example of Calculating DSCR in Excel

To calculate a company’s debt service coverage ratio, we’ll need the following information:

  • Net Operating Income – This figure refers to the total revenue generated from the business minus operating expenses.
  • Total Debt Service – This figure refers to the total amount of money needed to pay off existing debt obligations over a specific period. This total must include both principal and interest payments.

Once we have these values, we can use the following formula to find the DSCR:

DSCR = Net Operating Income / Total Debt Service

If the company’s DSCR is equal to 1, then this indicates that the company generates enough income to pay off their debt. However, a DSCR lower than 1 indicates that the company has insufficient income to pay off their debt and may require an additional source of income.

In practice, a DSCR of at least 2 is considered desirable and shows that a company can cover two times its debt. 

Let’s take a look at a simple example where we’ll need to calculate the debt service coverage ratio of multiple companies.

calculate debt service coverage ration

The table above contains data indicating the net operating income of multiple companies. Given that each company has its own debt obligations, we want to determine which company is most capable of fulfilling its obligations.

To find the debt service coverage ratio for Company A, we can use the following formula:

=B2/C2

We simply need to divide the net operating income of the business by their total debt service.

We can then copy this formula using the Fill Handle tool to find the ratio of the remaining companies.

use formula to calculate debt service coverage ration

In the example above, we find out that Company E is the company most at risk of not fulfilling its debt obligations.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to calculate the debt service coverage ratio of a company in Excel.

How to Calculate Debt Service Coverage Ratio in Excel

  1. First, input the net operating income and total debt service of the company you wish to calculate the DSCR of.
    input the net operating income and total debt service of the companyIn the above example, we wrote that Company A has a net operating income of $600,000 and a total debt service of $450,000.
  2. In a new blank cell, we’ll divide the net operating income by the total debt service.
    divide the net operating income by the total debt serviceTo find the DSCR of Company A, we’ll use the formula =B2/C2.
  3. Hit the Enter key to evaluate the function.
    evaluate function
  4. Use the Fill Handle tool to copy the formula down the column.
    copy the formula down to calculate debt service coverage ratio of each company

FAQs

  1. Where can I find Net Operating Income (NOI) and Total Debt Service in Excel?
    You will need to input these values manually or reference cells containing these values. NOI is the income from core operations, and Total Debt Service is the sum of principal and interest payments.
    To calculate the net operating income, just take the total revenue of your business and subtract the operating expenses. Operating expenses can include maintenance costs, property taxes, insurance, and other costs.

  2. Are there industry standards for acceptable DSCR values?
    While acceptable DSCR values can vary by industry, a DSCR above 1 is preferred. However, some lenders may have specific thresholds based on their risk tolerance.

To learn more about using Excel for assessing finances, you can read our post on how to forecast cash flow in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like