This guide explains how to calculate an organization’s Debt Service Coverage Ratio (DSCR).
The Debt Service Coverage Ratio (DSCR) is a financial metric used to assess a company’s ability to repay its debts. Investors and creditors often use this metric to evaluate the risk of lending to a business.
For example, if you’re planning to buy a new property for your business and need a mortgage, the lender might ask you to prove that the property can cover the mortgage debt using the DSCR.
In this guide, we’ll examine the necessary data for calculating the DSCR and provide a step-by-step tutorial on how to compute it using Excel formulas.
A Real Example of Calculating DSCR in Excel
To calculate a company’s debt service coverage ratio, you’ll need the following information:
-
Net Operating Income: This is the total revenue generated by the business minus its operating expenses.
-
Total Debt Service: This represents the total amount of money required to cover existing debt obligations over a specific period, including both principal and interest payments.
Once you have these values, use the following formula to calculate the DSCR:
DSCR = Net Operating Income / Total Debt Service
A DSCR of 1 indicates that the company generates enough income to cover its debt. However, a DSCR below 1 suggests that the company’s income is insufficient to cover its debt, potentially requiring an additional income source.
Ideally, a DSCR of at least 2 is considered desirable, showing that a company can cover its debt twice over.
Let’s consider a simple example where we need to calculate the debt service coverage ratio for several companies.
The table contains data showing the net operating income for multiple companies. Given that each company has different debt obligations, we want to identify which company is best positioned to meet those obligations.
To calculate the debt service coverage ratio for Company A, use this formula:
=B2/C2
This divides the business’s net operating income by its total debt service.
Then, copy the formula using the Fill Handle tool to calculate the ratio for the remaining companies.
In this example, we see that Company E faces the highest risk of defaulting on its debt obligations.
How to Calculate Debt Service Coverage Ratio in Excel
Method 1: Using Formulas Directly
This method is very effective because it directly uses Excel formulas to calculate the DSCR, providing immediate and precise results.
Step 1: Input the Net Operating Income and Total Debt Service of the company for which you want to calculate the DSCR.
Step 2: In a blank cell, divide the net operating income by the total debt service.
=B2/C2
To find the DSCR of Company A, enter the formula =B2/C2.
Step 3: Hit the Enter key to evaluate the function.
Step 4: Use the Fill Handle tool to copy the formula down the column to apply to other companies’ data.
Method 2: Manual Calculation
This method involves manually calculating the DSCR by performing the division operation in Excel. It’s less efficient than using formulas directly but can be useful for understanding the calculation process.
Step 1: Determine the Net Operating Income (NOI) and Total Debt Service (TDS) for the company.
Step 2: Open Microsoft Excel and create a table with two columns: NOI and TDS.
Step 3: Enter the corresponding values for each company in their respective rows and columns.
Step 4: In a new column labeled DSCR, manually calculate the DSCR for each company by dividing the NOI by the TDS for that company. For example, if the NOI is in cell B2 and the TDS is in cell C2, the formula in the DSCR column (e.g., D2) would be =B2/C2.
Step 5: Press Enter to calculate the DSCR for the first company.
Step 6: Drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the remaining companies. Excel will automatically adjust the cell references for each row.
FAQs
-
Where can I find Net Operating Income (NOI) and Total Debt Service in Excel?
You will need to enter these values manually or reference cells containing these values. NOI is the income from core operations, and Total Debt Service is the sum of principal and interest payments.
To calculate the net operating income, take the total revenue of your business and subtract the operating expenses. Operating expenses can include maintenance costs, property taxes, insurance, and other costs. -
Are there industry standards for acceptable DSCR values?
Acceptable DSCR values vary by industry, but a DSCR above 1 is preferred. However, some lenders may have specific thresholds based on their risk tolerance.
That concludes this guide! You can now calculate the Debt Service Coverage Ratio in Excel using the most effective methods.