Learn project finance modeling, debt sizing mechanics, running upside/downside cases and more. The macroeconomic environment can influence the minimum DSCR a lender requires, among other factors. Lenders might be more tolerant of lower ratios if the economy is expanding. To learn more about using Excel for assessing finances, you can read our post on how to forecast cash flow in Excel.
Suppose the DSCR of a commercial real estate (CRE) building is 1.25x – i.e. the minimum threshold of most investors and lenders alike. The formula to calculate the net operating income (NOI) of a property is as follows. If you want to compare the DSCR of multiple companies, you can follow the same steps beginning in Row 4 for the second company name, followed by its financial data. Now that we’ve covered the basics of debt analysis let’s get started with Excel.
How to Calculate Debt Service Coverage Ratio (DSCR) in Excel
However, it often serves as a proxy for it because it’s easy to calculate, and both its definition and its purposes are generally agreed-upon across jurisdictions. Lenders will set debt sizing parameters, typically including a gearing (or leverage) ratio debt service coverage ratio formula in excel (Loan to Cost Ratio) and a DCR (sometimes a LLCR in addition to, or instead of, a DCR). While the gearing ratio helps to ensure that equity has skin in the game, the DCR target ratio helps to ensure that a minimum DCR is maintained at all times.
Other financial ratios are usually a single snapshot of a company’s health; therefore, DSCR may be a more true representation of a company’s operations. By dividing the commercial property’s NOI by the debt service – which we can determine using the PMT function in Excel – we can set the size of the loan appropriately. One exception to this rule is to evaluate a company’s DSCR to similar companies within the same industry. Some sectors (i.e. airlines or real estate) are heavily reliant on debt and will likely have lower DSCR calculations due to high debt service.
What the Debt-Service Coverage Ratio Can Tell You?
Annual debt service is an important quantity because you need this value to determine the debt service coverage ratio. In this article, we will use 3 ideal examples to explain the annual debt service. Using these examples, you can also find the value of the annual debt service easily. Before we dive into Excel, it’s helpful to review a few key concepts in debt analysis.
