Debt Service Coverage Ratio Formula in Excel (2 Examples)

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.

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/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 Graph Ratios in Excel

Download Excel Workbook

Debt Service Coverage Ratio Formula.xlsx

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.

<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!