How do you use Excel to calculate a debt service coverage ratio (DSCR)?
There are actually three different kinds of debt service coverage ratios. or DSCR, that can be calculated in Microsoft Excel. Two of them, however, relate to specific property loans as sometimes necessitated by covenants in bank loan agreements. This article focuses on helping investors calculate the DSCR for companies based on information from financial statements.
The first step to calculating the debt service coverage ratio is to find a company's net operating income. Net operating income is equal to revenues less operating expenses and is found on the company's most recent income statement.
Net operating income is then divided by total debt service for the period. The resulting figure is the DSCR. Total debt service includes the repayment of interest and principal on the company's debts and is usually calculated on a yearly basis. This information can also be found on
the income statement.
To create a dynamic DSCR formula in Excel, do not simply run an equation that divides net operating income by debt service. Rather, title two successive cells, such as A2 and A3, "Net Operating Income" and "Debt Service," Then, adjacent from those in B2 and B3, place the respective figures from the income statement. In a separate cell, enter a formula for DSCR that uses the B2 and B3 cells rather than actual numeric values. For example: (B2 / B3)
Even for a calculation this simple, it is best to leave behind a dynamic formula that can be adjusted and recalculated automatically. One of the primary reasons to calculate DSCR is to compare it to other firms in the industry, and these comparisons are easier to run if you can simply plug in the numbers and go.Source: www.investopedia.com