Debt to Income Ratio Calculator in Excel (Create with Easy Steps)

This article illustrates how to make debt to income (DTI) ratio calculator in excel. Lenders use the debt to income ratio to determine whether a person is capable of repaying a certain loan or not. You can easily make a debt-to-income ratio calculator in excel if you need to do this very often. Follow the article to learn how to do that.


Download Debt to Income Ratio Calculator

You can download the debt-to-income ratio calculator template for free from the download button below.


What Is Debt to Income Ratio?

The debt to income ratio is simply the ratio of the total monthly debts of a person to his/her total monthly income. It is usually expressed in percentages.

Assume Mr. Smith has a total recurring monthly debt of $2500. On the other hand, his gross monthly income is $7500. Then you can calculate his debt to income ratio as follows.

There are mainly two types of debt-to-income ratios:

  • The front-end,
  • And the back-end

The front-end debt to income ratio signifies the percentage of your salary that can be expensed to repay the housing expenses.

On the other hand, the back-end debt to income ratio indicates the percentage of salary that can be expensed to repay all of your monthly recurring debts.

Lenders usually follow the debt to income ratio of 28/36 to decide whether to allow a borrower a loan or not. They use this ratio to determine the capability of the borrower to repay the loan. Here, the norm is that 28% of your salary can be expensed to repay the housing-related debts. This is usually the front-end debt to income ratio. On the other hand, 36% of your salary can be used to meet all of the recurring debt payments. Therefore, it is the back-end debt to income ratio.

The higher the debt to income ratio is, the more likely you will face difficulty to repay the loan. A debt to income ratio of 28% or less is generally preferable. But for those with a steady income, a healthy debt may have a debt to income ratio of up to 35%. If the debt to income ratio reaches 43-50%, you should think about reducing your debts by paying off some of your loans. Do not let your debt to income ratio go over 50%. Otherwise, you will almost certainly fall into financial difficulty.

Lenders are now more flexible on this historical 28/36 limit as housing costs and all other prices are much higher. They may allow you a loan even if your debt to income ratio is more than 50%. But you have to pay higher interest than others.


Steps to Make a Debt to Income Ratio Calculator in Excel

Follow the steps below to make a debt-to-income ratio calculator in excel.

πŸ“Œ Step 1: Calculate Total Recurring Monthly Debt

  • First, make a list of all recurring monthly debts and enter the corresponding amounts. Then apply the following formula in cell D12 to calculate the total recurring monthly debt.
=SUM(D6:D11)

Read More: Debt Service Coverage Ratio Formula in Excel


πŸ“Œ Step 2: Input Gross Monthly Income

  • Next, you need to input your gross monthly income. If you have multiple sources of income, then make a list of them. After that, enter the following formula in cell H12 to get the gross monthly income.
=SUM(H6:H11)

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


Similar Readings


πŸ“Œ Step 3: Calculate Debt to Income Ratio

  • Now, apply the following formula in cell D14 to calculate the debt to income ratio. Then, apply the Percentage (%) cell format.
=D12/H12

debt to income ratio calculator excel

  • Then, enter the following formula in cell F14 to show remarks based on the DTI ratio.
=IF(D14<=0.28,"Healthy Debt","Reduce Your Debts!")

debt to income ratio calculator excel

You can apply Conditional Formatting to make the final result and interpretation look good. To do that-

  • Next, select β€œUse a formula to determine which cells to format”. After that, enter the following formula as the rule description. Then, click on Format.
=D14<=0.28

  • Next, choose the green color from the Fill tab and click OK.

  • Click Ok again after that.

  • Then, apply conditional formatting to show a different color if the DTI ratio is greater than 0.28. Finally, you will see the following result.

debt to income ratio calculator excel


Things to Remember

  • Don’t forget to apply the Percentage (%) format to get the DTI ratio in percentages.
  • You can apply multiple conditional formatting in the cells to show different colors based on the DTI ratio.

Conclusion

Now you know how to make a debt to income ratio calculator in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo