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.

## Understanding the Debt Service Coverage Ratio (DSCR)

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 a yearly basis. These items can also be found on the income statement.

The DSCR formula is shown below:

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

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.

### Row 3

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

• Cell A3 = Write Company A's Name
• Cell B3 = \$2,000,000
• Cell C3 = \$300,000

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: