Excel is efficient for holding data and using it afterward. As a result, Balance Sheets or Debt and Income Statements existing 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.
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.
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 a better understanding.
1. Calculating Debt Service Coverage Ratio with Excel Formula from Income and Debt Statement
In cases, 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 a Template.
Read More:Â How to Do Ratio Analysis in Excel Sheet Format
2. Determining Debt Service Coverage Ratio (DSCR) from Balance Sheet with Excel Formula
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 looks like the following screenshot.
Step 1: Type the following formula in any cell.
=-C13/C12In 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 Graph Ratios in Excel
Download Excel Workbook
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.
