How to Calculate Annual Debt Service in Excel (3 Ideal Examples)

In Microsoft Excel, users can perform various operations and find the value of different quantities. Today, we will learn to calculate annual debt service in Excel. 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. So, without any delay, let’s start the discussion.

Download Practice Book

You can download the practice book from here.

What Is Annual Debt Service?

The annual debt service is the total amount of money you require each year to pay back all debt obligations. In general words, it is the summation of all principal and interest that you pay over a year. You can calculate the annual debt service by summing all debt services of a year. Or, you can multiply one month’s debt service with 12 to get the annual debt service. It is an essential quantity because it helps us to get the debt service coverage ratio.

3 Ideal Examples to Calculate Annual Debt Service in Excel

In this tutorial, we will show 3 different examples. The dataset will be the same for the first two examples. In the first example, we will show the calculation for one single year while in the second example, we will calculate the monthly debt service first. Then, we will find the annual debt service. And in the last example, we will find the annual debt service for different consecutive years.

1. Calculate Annual Debt Service from Balance Sheet in Excel

A balance sheet keeps the statement of assets, liabilities, and capital of a business. So, we can use the balance sheet to find the annual debt service. In the dataset below, you can see the list describing the money to pay back loans or debt for the year 2021. We can add this money to get the annual debt service for 2021. Remember, a balance sheet contains the value of assets and capital also. You can’t add them for this purpose.

Let’s follow the steps below to see how we can calculate the annual debt service.

STEPS:

• In the first place, select Cell C10 and type the formula below:

• After that, press Enter to see the annual debt service.

Read More: How to Create Pay off Credit Card Debt Calculator in Excel

2. Determine Annual Debt Service Using Monthly Information

In the previous example, we added the amounts for a whole year. But here, we will add the amount of money for a month and then, multiply it by 12. In this way, we can get the annual debt service. We have the property taxes and other expenses for January in the dataset below.

Let’s follow the steps below to learn the process of calculating annual debt service from monthly debt service.

STEPS:

• Firstly, select Cell 10 and type the formula below:

• After that, hit Enter to see the monthly debt service.

• In the following step, select Cell 11 and type the formula below:

• Finally, press Enter to see the annual debt service.

Read More: How to Calculate Before Tax Cost of Debt in Excel (3 Easy Ways)

3. Find Annual Debt Service for Different Years in Excel

In the last example, we will calculate the annual debt service for the first year to the sixth year. Here, we will solve a problem. The problem says that a corporation takes on a $300,000 loan at a rate of 10 percent for a term of 6 years. Suppose it’s an amortized loan with equal principal payments. It means the organization can repay an equal quantity of principal every period, and 10 percent interest on the outstanding principal. Here, the equal quantity of principal for every period is $50,000. As a result, it’ll have repaid all the principal additions to the interest at the end of 6 years. Let’s observe the steps below to see how we can find the annual debt service in this case.

STEPS:

• Firstly, in the dataset below, type the Starting Balance in Cell C5 and the equal amount of principal in the range F5:F10.
• Here, the starting balance is $300,000, and the equal you need to pay each year is $50,000.
• We have added an equal amount in the range F5:F10 because you need to pay this amount for 6 years.

• Secondly, we will calculate the Remaining Principal.
• To do so, select Cell G5 and type the formula below:

• Now, press Enter and drag the Fill Handle down.

• After dragging the Fill Handle down, you will see results like the picture below.

• Thirdly, select Cell C6 and type the formula below:

• Hit Enter and drag down the Fill Handle to copy the formula.
• This will show the Starting Balance for each year.

• In the following step, we will find the Annual Interest.
• So, type the formula below in Cell E5:

• Once again, press Enter and drag the Fill Handle down.

• As a result, the dataset will look like below.

• At this moment, we will calculate the Annual Debt Service for each year.
• In this case, the Annual Debt Service will be the summation of the Annual Interest and Principal.
• So, select Cell D5 and type the formula below:

• Finally, hit Enter and drag down the Fill Handle to see Annual Debt Service for each year from year 1 to year 6.

Read More: How to Calculate Current Portion Of Long Term Debt in Excel

Conclusion

In this article, we have demonstrated 3 ideal examples that Calculate Annual Debt Service in Excel. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to calculate annual debt service for different years. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Calculate Cost of Debt in Excel (3 Simple Ways)
• How to Make Credit Card Debt Reduction Calculator for Excel
• How to Use Debt to Equity Ratio Formula in Excel (3 Examples)
• Debt Snowball VS Debt Avalanche Method in Excel Spreadsheet
• Debt to Income Ratio Calculator in Excel (Create with Easy Steps)
  
• How to Create Debtors Ageing Report in Excel Format