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.
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 without using a complex formula.
- To calculate DSCR, you will need financial information typically reported on a company's financial statements or annual reports.
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:
:max_bytes(150000):strip_icc()/DSCR-b224f0db64184eae800e27598a8bc2d7.png "How to Calculate Debt Service Coverage Ratio (DSCR) in Excel (1)")
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:
:max_bytes(150000):strip_icc()/DSCR1-218052e5bc4240449f1479019381b358.jpg "How to Calculate Debt Service Coverage Ratio (DSCR) in Excel (2)")
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:
:max_bytes(150000):strip_icc()/DSCR3-4292bb231c424c8b8776c023503f560c.jpg "How to Calculate Debt Service Coverage Ratio (DSCR) in Excel (3)")
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:
:max_bytes(150000):strip_icc()/DSCR4-3141d6fbb65b4eb6b33813e86ccb2822.jpg "How to Calculate Debt Service Coverage Ratio (DSCR) in Excel (4)")
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:
:max_bytes(150000):strip_icc()/DSCR5-d5bf42881e1348a48cc306f6fdc92d6f.jpg "How to Calculate Debt Service Coverage Ratio (DSCR) in Excel (5)")
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 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.
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.
The Bottom Line
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 DSCR.
