The Debt Service Coverage Ratio (DSCR) is used in corporate finance to measure the amount of a company’s cash flow that is available to pay off its ongoing debt payments or obligations. The DSCR compares a company’s operating income to the various debts coming due over the next year, including rent, interest and principal. Investors can calculate a company’s debt service coverage ratio using Microsoft Excel and information from a company’s financial statements.
Understand the Debt Service Coverage Ratio (DSCR).
The first step in calculating the debt service coverage ratio is to determine a company’s net operating income. Net operating income is sales less operating expenses and is reported on the company’s most recent income statement.
The net operating income is then divided by the total debt service for the period. The resulting number is the DSCR. Total Debt Service includes interest and principal payments on corporate debt and is typically calculated on an annual basis. These items are also included in the income statement.
The DSCR formula is shown below:
Debt service coverage ratio formula.
Investopedia
Before we calculate the ratio, we need to first create the column and row header names in Excel.
Row 1:
Write the title of the worksheet: “Debt Service Coverage Ratio Calculation”.
Row 2:
Enter the headings, including company and financial information. Topics should be localized and labeled as shown below:
- A2 = company name
- B2 = net farm income
- C2 = overall service, that’s what they stated
- D2 = DSCR
- A3, A4 etc will be the company name locations.
Your headings should line up the same way as in the screenshot below:
Debt Service Coverage Ratio calculation in an Excel example.
Investopedia
As an example, assume that Company A has net operating income of $2,000,000 for one year and the total cost of servicing debt that year is $300,000.
row 3
We can enter Company A’s data into our spreadsheet:
- Cell A3 = Enter the name of company A
- Cell B3 = 2,000,000
- Cell C3 = 300,000
Please see the image below to see how your spreadsheet should look like:
Debt Service Coverage Ratio calculation in an Excel example.
Investopedia
Calculate Debt Service Coverage Ratio in Excel:
- As a reminder, the DSCR calculation formula is as follows: Net Operating Income / Total Debt Service.
- Place your cursor in cell D3.
- The formula in Excel starts with the equals sign.
- Enter the DSCR formula in cell D3 as follows: =B3/C3
- Press Enter or Return on your keyboard
Look at the screenshot below to see what the formula in cell D3 should look like:
Debt Service Coverage Ratio calculation in an Excel example.
Investopedia
You will find that Excel automatically highlights the calculation formula cells as you type. Once you press Enter, the calculation will be completed as shown below:
Debt Service Coverage Ratio calculation in an Excel example.
Investopedia
The result of this calculation shows that Company A generates enough net operating income to cover its debt obligations, which is 6.67 times that in one year.
Comparison of several companies
If you want to compare multiple companies’ DSCR ratios, you can follow the same steps, starting with line 4 for the company’s last name, followed by the financial data.
A quick tip for calculating the ratio for multiple companies: You can copy the formula from cell D3 and paste it into cell D4 after filling in row 4. 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 select Paste from the drop down menu.
Once you know how the formula is formatted in Excel, you can analyze the DSCR ratios of different companies to compare and contrast them before deciding to invest in any of these stocks.
The DSCR ratio should not only be used to determine whether a company is a good investment. There are many financial metrics available to investors, and it’s important to compare some of these metrics to those of similar companies in the same industry. Please also note that there are other debt service coverage ratios, two of which relate to home loans, that have not been covered in this article.
https://www.investopedia.com/ask/answers/012015/how-do-you-use-excel-calculate-debt-service-coverage-ratio-dscr.asp