Annual debt service is a foundational calculation for financial statement spreading and evaluating small business loans. Much like other key calculations, getting annual debt service right is essential for accurate underwriting and analysis.
As we continue to highlight ways to make financial statement spreading easier in Excel, this post will provide different approaches to calculating annual debt service and tips to ensure you get it right.
What Is Annual Debt Service?
Annual debt service is the total amount of money–including principal and interest–a business or entity must pay back to all debt obligations within a 12-month period. These debts encompass any financial obligation that must be paid over time. It can include a mortgage, vehicle loans, business loans and personal debt.
When a company needs to raise additional capital for operations, banks and credit unions use annual debt service to assess the company’s ability to service debt within a given time.
It is important to get annual debt service right because it is a foundational value that is then plugged into other formulas to get a clearer picture of a company’s financial standing. For example, you must know a business’s annual debt service to calculate debt service coverage ratio (DSCR). Annual debt service also plays a role in determining the overall size of the debt and the principal repayment schedule.
Different Ways to Calculate Annual Debt Service
There are multiple ways to calculate annual debt service using a spreadsheet application like Excel or Google Sheets. Let’s start with the textbook method when it comes to analyzing a business’s annual financial statements.
Add the Interest Expense from a given period’s Income Statement to the Current Portion (or Current Maturities) of Long Term Debt (CPLTD) from the prior period’s Balance Sheet. Depending on the financial statements, you may also need to include Short Term Debt as well as Leases.
For instance, to determine the Annual Debt Service for 2023, add the Interest Expense from the Income Statement dated 12/31/2023 to the Current Portion of Long Term Debt from the Balance Sheet dated 12/31/2022.
The benefit of this method is that it’s easy if you have enough information available to you. You will need financial statements for at least two consecutive periods, and those statements will need to include a valid, reported Current Portion of Long Term Debt on the Balance Sheet. Unfortunately for many community lenders, small business owners rarely know what CPLTD means, and often do not provide Account Prepared/Audited financial statements.
In these cases, you may be best served by requesting a current Debt Schedule, or Debt Service Schedule, from the borrower. A debt service schedule will often only contain the monthly payment, so to calculate the annual debt service, you will need to multiply the reported number by 12.
When analyzing Personal financials or tax returns, the most common method for determining annual debt service is to review a Credit Bureau report. Again, bear in mind that this will show monthly payment amounts, which will need to be multiplied by 12.
Tips for Accurately Calculating Annual Debt Service
Typical in small business lending, loan analysts will look at three years of financial statements to get a broad picture of the financial health of the company over time. There are a number of scenarios where the annual debt service may change over time or that calculating using the annual balance sheet approach doesn’t accurately reflect the borrower’s total debt obligations.
Here are some tips for ensuring you’re pulling the right data you need to accurately calculate annual debt service:
Assess the Source of Data
There are different sources for the numbers you use to calculate annual debt service. Some sources are more accurate than others, so it's always a good practice to review your data sources and consider whether it’s the best reflection of their liabilities.
Is it a customer-provided balance sheet? Are you using a debt service schedule? Is there a credit report you can use from a reputable service? Each may reflect a different view of debt service, and even honest borrowers make mistakes.
Don’t Forget to Consider Retired Debt
When assessing the annual debt service for a proposed new loan, you may find debt payments to a loan that’s been paid off. In other cases, an existing loan is maturing during the first years of the new loan. If the loan is, or will soon be, paid off, that debt may not be relevant to future calculations.
Double Count Debt
In some loan scenarios, a borrower will use the new loan to refinance or retire existing debt. In these cases, you must be sure to remove that refinanced or soon-to-be-retired debt for your total proposed annual debt service calculation.
Consider Lines of Credit
When assessing annual debt service for an existing line of credit, consider whether to use the historic debt service or an adjusted value. Although the LOC may have only been 10% drawn in a given period, the borrower often has on-demand access to the other 90% of the available line. Overnight, the interest-only payments could increase 10x, and if the line were subsequently termed out at maturity into P&I payments, the annual debt service could exceed 40-50x the original payment amount.
However, if the borrower has had the line of credit for multiple years and the recent usage is in line with historic norms, it may be acceptable to use the actual historic annual debt service.
Annual Debt Service in Excel vs. Software
Calculating annual debt service in Excel can be straightforward, however, it can also quickly get complicated in the scenarios mentioned above. There are certain pros and cons to using Excel and deciding to switch to a financial statement spreading software.
Excel Pros
- Flexibility: Excel offers countless ways to organize, format and calculate data, making it a good choice for small business lending.
- Easy to Adjust: Worksheets can be quickly changed to fit specific needs depending on the type of loan and type of customer.
- Familiarity: Loan officers and credit analysts everywhere are familiar with Excel and require little special training for basic tracking and spreading tasks.
- Cost: Most banks today own Microsoft Office and as a result, Excel, Word, and PowerPoint. It’s a low-cost tool your organization already owns.
Excel Cons
- Easy to Break: Banks and credit unions may build spreading templates that include annual debt service calculations. In the cases where people may need to adjust those calculations, it can also lead to overwriting the template and errors for the next user.
- Error Prone: When a loan officer makes changes to a worksheet that may impact a referenced cell elsewhere, it can inadvertently create an error, which eats up valuable time finding and fixing, or may even go unnoticed.
- Lack of Support: Excel has free resources and tutorials are easy to find online, but you have to know what you’re looking for and this can cost a lot of time.
Software Pros
- Historic data: Most Excel-based spreads are recreated any time a new statement is submitted or a new loan is requested. Statement spreading software maintains the previously-entered, historic annual debt service from prior periods, and allows a new column to easily be added for the most recent data.
- Accurate calculations: Trying to accommodate all of the various scenarios mentioned above in a single spreadsheet can easily lead to one adjustment overwriting or undoing another. Statement spreading software allows you to enter the actual annual debt service, and then subsequently make adjustments to how that value will be treated in various reports, analysis, etc.
- Version Control: No matter how many changes you make to an individual spread, spreading software maintains version control of all templates, making it impossible to overwrite key templates.
Software Cons
- More Than You Need: For some small banks and credit unions, Excel may be all they need to evaluate the small number of loans they process each year. However, for community banks and credit unions that do have a more active commercial loan program, software brings significant efficiencies.
- Requires Buy-In: It can be hard to get leadership and individual loan officers to agree to add, learn, and use a new software product.
If you find you’re frequently encountering annual debt service calculation errors and broken formulas, it might be time to make a change. For more on an easier, more accurate way to calculate annual debt service, reach out to schedule a demo today.
