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

The debt service coverage ratio (DSCR) is used in corporate finance to measure the amount of a company’s cash flow 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 a debt service coverage ratio for a company using Microsoft Excel and information from a company’s financial statements.

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 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:

$\begin{aligned}&\text{DSCR}=\frac{\text{Net Operating Income}}{\text{Total Debt Service}}\\&\textbf{where:}\\&\text{DSCR}=\text{Debt service coverage ratio}\end{aligned}$​DSCR=TotalDebtServiceNetOperatingIncome​where:DSCR=Debtservicecoverageratio​

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:

As an example, let’s say Company A has net operating income of $2,000,000 for one year and 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:

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:

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:

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 drop-down menu that appears. Click on cell D4, right-click, and choose to click Paste from the drop-down. Alternatively, the keyboard shortcut to copy content in Excel is Control + C on PC or Command + C on Mac, and the keyboard shortcut to paste content in Excel is Control + V on PC or Command + V on Mac.

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 that relate to property loans that were not covered in this article.

The Bottom Line

The DSCR is calculated by dividing net operating income by total debt service and compares a company’s operating income with its upcoming debt obligations. Total debt service includes interest and principal on a company’s lease, interest, principal, and sinking fund payments. Using Excel does not require a complex formula to calculate the DSCR.