Debt Service Coverage Ratio Formula in Excel

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

Debt and Income Statement-Debt Service Coverage Ratio Formula in Excel

Balance Sheet

Balance Sheet-Debt Service Coverage Ratio Formula in Excel

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).

DSCR Calculation

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.

Debt and Income Statement

Steps: Insert the below formula (i.e., Net Income/Total Debt Service) in any blank cell.

=H10/E10

DSCR Calculation-Debt Service Coverage Ratio Formula in Excel

🔼 Press ENTER, and you’ll get the DSCR instantly as depicted in the below picture.

DSCR

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


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.

Balance Sheet-Debt Service Coverage Ratio Formula in Excel

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.

Debt Service Coverage Ratio (DSCR) Calculation

Step 2: Use the ENTER key then drag the Fill Handle to the right to appear the DSCRs for each column.

DSCR

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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo