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.

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 by 12 to get the annual debt service. It is an essential quantity because it helps us to get the debt service coverage ratio.

How to Calculate Annual Debt Service in Excel: 3 Ideal Examples

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

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.

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.

Download Practice Book

You can download the practice book from here.

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. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

<< Go Back to Debt Calculation | Formula List | Learn Excel