Excel is efficient to hold data and use them afterward. As a result, Balance Sheets or Debt and Income Statements exiting in a Worksheet can be easily used to find the debt service coverage ratio formula in Excel.
Let’s say we have a user’s Debt and Income Statement, or Balance Sheet as shown in the image below. And we want to find the Debt Service Coverage Ratio (DSCR) from it.
Debt and Income Statement
Balance Sheet
In this article, we demonstrate the basics and find the Debt Service Coverage Ratio Formula in Excel with examples.
Download Excel Workbook
What Is Debt Service Coverage Ratio (DSCR)?
Companies, organizations, or individual finances require a parameter to predict their ability to pay their debt obligations. Therefore, those entities need to have a Balance Sheet or basic Debt and Income statement to have a clear view of their financial status. The ratio between Net Operating Income or Cash Flow and Debt Service is known as Debt Service Coverage Ratio (DSCR). And it is a must to have a DSCR of more than 1 to stay on the safe side. Thus the formula stands
Debt Service Coverage Ratio (DSCR)=Net Operating Income/Debt Service
Net Operating Income = Cash Flow after deducting all the expenses.
Debt Service = Total Obligations that are needed to be met.
​Calculating Debt Service Coverage Ratio with Formula in Excel with Examples
🔄 Basic Debt Service Coverage Ratio (DSCR) Calculation:
The fundamental entities users need to have to calculate the Debt Service Coverage Ratio (DSCR) are 2; Net Income or Cash Flow (deducting expenses), Total Debt or Debt Service. Then finding the ratio between Net Income or Cash Flow and Debt Service results in Debt Service Coverage Ratio (DSCR).
Follow the below examples for better understanding.
Example 1: Calculate Debt Service Coverage Ratio from Income and Debt Statement
In case, where users have Income and Debt Statements, they can easily find the Debt Service Coverage Ratio (DSCR) just by dividing them.
As mentioned earlier, we have an Income and Debt Statement as shown in the below image. You can get this statement from your accountant or your financial logs.
Steps: Insert the below formula (i.e., Net Income/Total Debt Service) in any blank cell.
=H10/E10
🔼 Press ENTER, and you’ll get the DSCR instantly as depicted in the below picture.
You can have your financial statement or use the attached dataset as Template.
Read More: Debt to Income Ratio Calculator in Excel (Create with Easy Steps)
Similar Readings
- How to Calculate Current Ratio in Excel (2 Suitable Examples)
- How to Use Debt to Equity Ratio Formula in Excel (3 Examples)
- How to Calculate Current Portion Of Long Term Debt in Excel
Example 2: Calculate Debt Service Coverage Ratio (DSCR) from Balance Sheet
Companies or institutions generally maintain Balance Sheets depicting their Incomes and Expenses. Using those entries, users can simply find the Debt Service Coverage Ratio (DSCR).
Suppose the Balance Sheet may look like the following screenshot.
Step 1: Type the following formula in any cell.
=-C13/C12
In the formula, C13 refers to the Net Cash Flow or Income and C12, Debt Service.
Step 2: Use the ENTER key then drag the Fill Handle to the right to appear the DSCRs for each column.
Financial Balance Statements’ fields vary from company to company. However, fundamental entries such as Net Income or Cash Flow, Debt or Liability are always present in them.
Read More: How to Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
Conclusion
In this article, we discuss the basics of Debt Service Coverage Ratio (DSCR) and find it using different Financial Statements. Hope this article clarifies your understanding of DSCR and helps you to achieve your desired outcome. Comment, if you have further inquiries or have anything to add.
Related Articles
- How to Calculate Ratio Between Two Numbers in Excel (5 Ways)
- Calculate Compa Ratio in Excel (3 Suitable Examples)
- How to Calculate Average Ratio in Excel (2 Simple Ways)
- Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
- How to Calculate Sharpe Ratio in Excel (2 Common Cases)
- Calculate Sortino Ratio in Excel (2 Methods)
- Convert Percentage to Ratio in Excel (4 Easy Ways)