How to Calculate Debt Service Coverage Ratio in Excel



The debt service coverage ratio (DSCR) is used in corporate finance to measure the amount of a company’s cash flow that’s available to pay its current debt payments or obligations. The DSCR compares a company’s operating income with the various debt obligations due in the next year including lease, interest, and principal payments. Investors can calculate the debt service coverage ratio for a company using Microsoft Excel and information from a company’s financial statements.

Key Takeaways

  • The debt service coverage ratio compares a company’s operating income with its upcoming debt obligations.
  • DSCR is calculated by dividing net operating income by total debt service.
  • Total debt service includes interest and principal on a company’s lease, interest, principal, and sinking fund payments.
  • You can calculate DSCR using Excel fairly easily as no complex formula is needed.
  • In order to calculate DSCR, you will need financial information typically reported on a company’s financial statements or annual report.

DSCR Formula

The first step to calculating the debt service coverage ratio is to find a company’s net operating income. Net operating income is equal to revenues less operating expenses and is found on the company’s most recent income statement.

Net operating income is then divided by total debt service for the period. The resulting figure is the DSCR. Total debt service includes the repayment of interest and principal on the company’s debts and is usually calculated on an annual basis. These items can also be found on the income statement.

The DSCR formula is shown below:

Image by Julie Bang © Investopedia 2021


How to Calculate the DSCR in Excel

Before calculating the ratio, in Excel, we must first create the column and row heading names.

Row 1:

Write the title of the sheet; “Calculating the Debt Service Coverage Ratio.”

Row 2:

Write the headings, including Company and the financial data. The headings should be located and labeled as shown below:

  • A2 = Company Name
  • B2 = Net Operating Income
  • C2 = Total Debt Service
  • D2 = DSCR
  • A3, A4, and so on will be the locations of the company names.

Your headings should be aligned similar to the screenshot below:

Calculating the Debt Service Coverage Ratio in Excel Example.
 Investopedia

As an example, let’s say Company A has a net operating income of $2,000,000 for one year and the total debt servicing costs equal to $300,000 for that year.

Company A’s operating income will be reported on its income statement, and Company A’s debt servicing cost might be shown as an expense on the income statement. However, any number on the income statement is historical. Review the company’s financial note disclosures and balance sheet for information on long-term obligations including potentially escalating required payment amounts.

Row 3

We can write in the data for Company A into our spreadsheet:

  • Cell A3 = Company A
  • Cell B3 = $2,000,000
  • Cell C3 = $300,000

Please see image below for how your spreadsheet should look:

Calculating the Debt Service Coverage Ratio in Excel Example.
Investopedia 

Calculate the debt service coverage ratio in Excel:

  • As a reminder, the formula to calculate the DSCR is as follows: Net Operating Income / Total Debt Service.
  • Place your cursor in cell D3.
  • The formula in Excel will begin with the equal sign.
  • Type the DSCR formula in cell D3 as follows: =B3/C3
  • Press Enter or Return on your keyboard

See the screenshot below for how the formula should look in cell D3:

Calculating the Debt Service Coverage Ratio in Excel Example.
 Investopedia

You’ll notice that Excel automatically highlights the cells in the formula calculation as you type. Once you press Enter, the calculation will be completed, as shown below:

Calculating the Debt Service Coverage Ratio in Excel Example.
 Investopedia

As a result of the calculation, we can see that Company A generates enough net operating income to cover its debt obligations by 6.67 times in one year. In other words, the company’s income is six times larger than its required debt payments.

Comparing Multiple Companies

If you want to compare the DSCR of multiple companies, you can follow the same steps beginning in Row 4 for the second company name, followed by its financial data.

A quick tip when calculating the ratio for multiple companies: You can copy the formula from cell D3 and paste it into cell D4 once you have Row 4 completed. To copy and paste the formula, place your cursor in cell D3, right-click, and choose Copy from the dropdown menu that appears. Click on cell D4, right-click, and choose to click Paste from the dropdown. Alternatively, the keyboard shortcut to copy content in Excel is Control + C, and the keyboard shortcut to paste content in Excel is Control + V.

Interpreting DSCR

A DSCR of 1 indicates a company has generated exactly enough operating income to pay off its debt service costs. Therefore, companies should strive to achieve a DSCR greater than 1.

One exception to this rule is to evaluate a company’s DSCR to similar companies within the same industry. Some sectors (i.e. airlines or real estate) are heavily reliant on debt and will likely have lower DSCR calculations due to high debt service. Other sectors (i.e. software/technology) are more reliant on equity funding, carry less debt, and have naturally high DSCR.

Once you know how to format the formula in Excel, you can analyze the DSCR of various companies to compare and contrast before choosing to invest in one of those stocks.

The DSCR shouldn’t be used solely for determining whether a company is a good investment. Investors have many financial metrics available to them, and it’s important to compare several of those ratios to similar companies within the same sector. Also, please note that there are other debt service coverage ratios, including two of which relate to property loans that were not covered in this article.

What Is a Good DSCR?

A debt service coverage ratio of 1 or above indicates a company is generating enough income to cover its debt obligation. A ratio below 1 indicates a company may have a difficult time paying principle and interest charges in the future as it may not generate enough operating income to cover these charges as they become due.

When Should You Calculate DSCR?

DSCR is often a reporting metric required by lenders or other stakeholders that must monitor the risk of a company becoming insolvent. You should calculate DSCR whenever you want to assess the financial health of a company and it’s ability to make required cash payments when due.

What Factors Affect DSCR?

DSCR is affected by two items: operating income and debt service. Operating income is affected by the organization-wide financial performance of the company. Debt service is the credit a company has taken to finance its operations.



Source link

Leave a Reply