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.
Write the title of the sheet; "Calculating the Debt Service Coverage Ratio."
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 a net operating income of $2,000,000 for one year and the total debt servicing costs equal to $300,000 for that year.
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
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.
Comparing Multiple Companies
If you want to compare the DSCR ratios 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.
Once you know how to format the formula in Excel, you can analyze the DSCR ratios of various companies to compare and contrast before choosing to invest in one of those stocks.
The DSCR ratio 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 that relate to property loans that were not covered in this article.