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.

**Table of Contents**hide

## 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:

`=SUM(C5:C8)`

Here, we have used **the SUM function **to add the values of property taxes, insurance, maintenance, and other expenses. These values are inside the first bracket because these are negative values as you are paying the taxes, insurance, and other expenses.

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

`=SUM(C5:C8)`

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

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

`=12*C10`

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

`=C5-F5`

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

`=G5`

- 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**:

`=C5*0.1`

This formula multiplies the **Starting Balance **with the **Interest Rate **to find the interest you need to pay each year.

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

`=E5+F5`

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