How to Calculate Interest Payable

Loans are not always the same. So knowing how to calculate a monthly liability as well as the interest due is very helpful in choosing a loan that is right for you. To understand exactly how to calculate it, you may have to study complex formulas, but it can also be simpler using Excel.

Table of Contents

Quick Learn about Loans

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f8/Calculate-Interest-Payments-Step-1-Version-2.jpg/v4-728px-Calculate-Interest-Payments- Step-1-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f8/Calculate-Interest-Payments-Step-1-Version-2.jpg/ v4-728px-Calculate-Interest-Payments-Step-1-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Entering loan information into an online calculator will help you quickly calculate interest payments. However, the equation for calculating interest is not simple. Fortunately, just by looking online for the term “interest payment calculator”, you can easily find the results after entering the following parameters into the calculator:

Principal debt: The value of the loan. If the loan is $5,000, the principal will be $5,000.
Interest Rate: Simply a percentage of the amount you have to pay to get a loan. The interest rate can be specified as a percentage (such as 4%) or as a decimal (0.04).
Term: Usually calculated on a monthly basis, it’s the period you have to repay the loan. For mortgages, the term is in years.
Mode of payment: Mostly “fixed term loans.” However, this method may be different for special loans. If you are unsure ask if interest and payment terms are fixed before taking out a loan. ^{[1] X Research Source}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5d/Calculate-Interest-Payments-Step-2-Version-2.jpg/v4-728px-Calculate-Interest-Payments- Step-2-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5d/Calculate-Interest-Payments-Step-2-Version-2.jpg/ v4-728px-Calculate-Interest-Payments-Step-2-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Learn about interest rates before borrowing. Interest is the fee you have to pay to get a loan. It is the interest you pay on the principal loan over the life of the loan. This ratio is as low as possible, even a difference of 0.5% is a large sum of money. ^{[2] X Source of Research} If you choose lower recurring debt, you may pay higher interest rates and more total interest but less monthly payments. People with little savings or income based on commissions and bonuses often prefer this option. However, try to only borrow at less than 10% interest whenever possible. Typical interest rates for some loans are as follows:

Car loan: 4-7% ^{[3] X Research source}
Home loan: 3-6%
Personal loan: 5-9%
Credit cards: 18-22%. That’s why you should avoid buying something big that can’t immediately pay off your credit card debt.
Borrowing by day (hot loan): 350-500%. This type of loan is very dangerous if you cannot repay within 1-2 weeks. ^{[4] X Research Sources}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3f/Calculate-Interest-Payments-Step-3-Version-2.jpg/v4-728px-Calculate-Interest-Payments- Step-3-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/3f/Calculate-Interest-Payments-Step-3-Version-2.jpg/ v4-728px-Calculate-Interest-Payments-Step-3-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Ask your accrual rate to know when you have to pay interest. The accrual rate tells you how often your creditor charges you with interest. The higher the repayment frequency, the more debt you owe because you have less time to repay the loan but in return you do not have to pay high interest. ^{[5] X Research Source} For example, consider how a loan of $100,000, compounded at 4%, is calculated in three ways:

By year: 110,412.17 USD
By month: 110,512.24 USD
By day: 110,521.28 USD

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bb/Calculate-Interest-Payments-Step-4-Version-2.jpg/v4-728px-Calculate-Interest-Payments- Step-4-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/bb/Calculate-Interest-Payments-Step-4-Version-2.jpg/ v4-728px-Calculate-Interest-Payments-Step-4-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Long-term loans will pay less each month but the total interest will be higher. The maturity is the period of time you have to repay the debt. ^{[6] X Research Sources} Each loan has a different tenor and you need to choose a loan with a term that suits your needs. Longer maturities often result in higher total interest but lower monthly payments. ^{[7] X Research Source} Suppose you borrow $20,000 to buy a car at 5% interest. Total liabilities will be:

24-month term: You have to pay a total interest of 1,058.27 USD, but each month only pay both principal and interest is 877.43 USD.
30-month term: You pay a total interest of $1,317.63, but only pay both principal and interest of $710.59 monthly.
36-month term: You have to pay a total interest of 1,579.02 USD, while the principal and interest to be paid each month is only 599.42 USD. ^{[8] X Research Sources}

Mentally Calculate the Payment

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/24/Calculate-Interest-Payments-Step-5-Version-2.jpg/v4-728px-Calculate-Interest-Payments- Step-5-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/24/Calculate-Interest-Payments-Step-5-Version-2.jpg/ v4-728px-Calculate-Interest-Payments-Step-5-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Learn the compound interest formula. Calculate periodic liabilities and interest using the following mathematical formula:

WOMEN

o

p

H

a

i

t

r

a

d

i

n

H

k

y

=

WOMEN

o

g

o

c

*

i

(

first

+

i

)

n

(

first

+

i

)

n

-

first

{displaystyle Nophaitradinhky=Nogoc*{frac {i(1+i)^{n}}{(1+i)^{n}-1}}}

Nophaitradinhky=Nogoc*{frac {i(1+i)^{n}}{(1+i)^{n}-1}}

^{[9] X Research Source}

“i” is the interest rate, “n” is the number of payments.
Like most financial equations, the formula for calculating recurring liabilities is much more complicated than simple calculations. Once you understand the principle of the numbers, you will find it very easy to calculate recurring liabilities.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7a/Calculate-Interest-Payments-Step-6.jpg/v4-728px-Calculate-Interest-Payments-Step-6. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7a/Calculate-Interest-Payments-Step-6.jpg/v4-728px-Calculate-Interest-Payments-Step- 6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Adjust the repayment frequency. Before inserting numbers into the equation, you must adjust the interest payment term “i”.

For example, let’s say you take out a loan with an interest rate of 4.5% per year, paying it off monthly.
Since you have to make monthly payments, you divide the interest rate by 12. 4.5% (0.045) divided by 12 equals 0.00375. Insert the result into “i”. ^{[10] X Research Source}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7a/Calculate-Interest-Payments-Step-7.jpg/v4-728px-Calculate-Interest-Payments-Step-7. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7a/Calculate-Interest-Payments-Step-7.jpg/v4-728px-Calculate-Interest-Payments-Step- 7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Adjust the number of repayments. To determine what “n” is, the next step is to determine the total number of repayments over the life of the loan.

Let’s say you have to make monthly payments on a 30-year loan. To find the number of repayments, multiply 30 by 12. You’ll get 360. ^{[11] X Research Source}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d8/Calculate-Interest-Payments-Step-8.jpg/v4-728px-Calculate-Interest-Payments-Step-8. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d8/Calculate-Interest-Payments-Step-8.jpg/v4-728px-Calculate-Interest-Payments-Step- 8.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Calculate the monthly debt. To find the monthly repayment value of a loan, insert the numbers into the formula. It looks scary, but take it slow and you’ll soon see results. Below are the calculation steps, let’s do it step by step.

Continuing with the example above, let’s say you borrow $100,000. The equation will be as follows: $100,000 won$ $*$ $0$ $,$ $00375$ $($ $first$ $+$ $0$ $,$ $00375$ $)$ $3$ $60$ $($ $first$ $+$ $0$ $,$ $00375$ $)$ $3$ $60$ $-$ $first$ ${displaystyle 100,000*{frac {0.00375(1+0.00375)^{3}60}{(1+0.00375)^{3}60-1}}}$ $100,000*{frac {0.00375(1+0.00375)^{3}60}{(1+0.00375)^{3}60-1}}$
$100,000 won$ $*$ $0$ $,$ $00375$ $($ $first$ $,$ $00375$ $)$ $3$ $60$ $($ $first$ $+$ $0$ $,$ $00375$ $)$ $3$ $600$ $-$ $first$ ${displaystyle 100,000*{frac {0.00375(1.00375)^{3}60}{(1+0.00375)^{3}600-1}}}$ $100,000*{frac {0.00375(1.00375)^{3}60}{(1+0.00375)^{3}600-1}}$
$100,000 won$ $*$ $0$ $,$ $00375$ $($ $3$ $,$ $84769\dots.$ $)$ $($ $first$ $+$ $0$ $,$ $00375$ $)$ $3$ $60$ $-$ $first$ ${displaystyle 100,000*{frac {0.00375(3.84769….)}{(1+0.00375)^{3}60-1}}}$ $100,000*{frac {0.00375(3.84769....)}{(1+0.00375)^{3}60-1}}$
$100,000 won$ $*$ $0$ $,$ $01442\dots..$ $($ $first$ $+$ $0$ $,$ $00375$ $)$ $3$ $60$ $-$ $first$ ${displaystyle 100,000*{frac {0.01442…..}{(1+0.00375)^{3}60-1}}}$ $100,000*{frac {0.01442.....}{(1+0.00375)^{3}60-1}}$
$100,000 won$ $*$ $0$ $,$ $01442\dots..$ $($ $first$ $,$ $00375$ $)$ $3$ $60$ $-$ $first$ ${displaystyle 100,000*{frac {0.01442…..}{(1.00375)^{3}60-1}}}$ $100,000*{frac {0.01442.....}{(1.00375)^{3}60-1}}$
$100,000 won$ $*$ $0$ $,$ $01442\dots..$ $3$ $,$ $84769\dots..$ $-$ $first$ ${displaystyle 100,000*{frac {0.01442…..}{3,84769…..-1}}}$ $100,000*{frac {0.01442.....}{3,84769.....-1}}$
$100,000 won$ $*$ $0$ $,$ $01442\dots..$ $2$ $,$ $84769\dots..$ ${displaystyle 100,000*{frac {0.01442…..}{2,84769…..}}}$ $100,000*{frac {0.01442.....}{2,84769.....}}$
$100,000 won$ $*$ $0$ $,$ $00506685\dots..$ $=$ $506$ $,$ $69$ ${displaystyle 100,000*0.00506685\dots..=506.69}$ $100,000*0.00506685.....=506.69$
506.69 USD. This is the debt you have to pay every month.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/de/Calculate-Interest-Payments-Step-9.jpg/v4-728px-Calculate-Interest-Payments-Step-9. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/de/Calculate-Interest-Payments-Step-9.jpg/v4-728px-Calculate-Interest-Payments-Step- 9.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Calculate the total interest payable. Now that you have the monthly payment results, you can now calculate the total interest payable over the loan term. Multiply the number of payments over the life of the loan by the amount of the monthly liability. Then deduct the principal. ^{[12] X Research Source}

Using the example above, multiplying 360 by 506.69 USD gives us 182,408 USD . This is the total amount you have to pay over the life of the loan.
Subtract $100,000 and the result is $82,408 . It is the total interest you pay at the end of the loan term.

Calculate Interest in Excel

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a7/Calculate-Interest-Payments-Step-10.jpg/v4-728px-Calculate-Interest-Payments-Step-10. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a7/Calculate-Interest-Payments-Step-10.jpg/v4-728px-Calculate-Interest-Payments-Step- 10.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Record the loan principal, term and interest rate in one column. In the other cells you enter their values and Excel will calculate your monthly liabilities for you. For the rest of this article, you can use the following example:

You borrow $ 100,000 to buy a house with an interest rate of 4.5%/year, a term of 30 years.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/ff/Calculate-Interest-Payments-Step-11.jpg/v4-728px-Calculate-Interest-Payments-Step-11. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/ff/Calculate-Interest-Payments-Step-11.jpg/v4-728px-Calculate-Interest-Payments-Step- 11.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Principal debit is negative. You need to let Excel know you are paying the debt. Therefore, you must write as a negative number, without the currency symbol ($).

-100,000 = Principal debt

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4d/Calculate-Interest-Payments-Step-12.jpg/v4-728px-Calculate-Interest-Payments-Step-12. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4d/Calculate-Interest-Payments-Step-12.jpg/v4-728px-Calculate-Interest-Payments-Step- 12.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Determine the number of repayments. You can leave it as the year if you want, but the result will be a year, not a month. Since most loans pay monthly, you can simply multiply the number of years by 12 to get the total number of repayments. Record this result in another cell.

-100,000 = Principal debt
360 = Number of repayments

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b0/Calculate-Interest-Payments-Step-13.jpg/v4-728px-Calculate-Interest-Payments-Step-13. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b0/Calculate-Interest-Payments-Step-13.jpg/v4-728px-Calculate-Interest-Payments-Step- 13.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Change interest rates to match the number of repayments. In this case, the interest is on an annual basis, meaning that interest will be calculated at the end of the year. However, you have to make monthly payments, which means you need to know what your monthly interest rate is. Since 4.5% is the 12-month interest rate, divide by 12 to get the interest per month. Remember to convert percentages to decimals once you have the results.

-100,000 = Principal debt
360 = Number of repayments
$4$ $,$ $5$ $%$ $twelfth$ $=$ $0$ $,$ $375$ $%$ $=$ ${displaystyle {frac {4.5%}{12}}=0.375%=}$ ${frac {4.5%}{12}}=0.375%=$ $0$ $,$ $00375$ ${displaystyle 0.00375}$ $0.00375$ = Interest payable monthly.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8e/Calculate-Interest-Payments-Step-14.jpg/v4-728px-Calculate-Interest-Payments-Step-14. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/8e/Calculate-Interest-Payments-Step-14.jpg/v4-728px-Calculate-Interest-Payments-Step- 14.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Use the =PMT function to calculate the interest payable. Excel knows how to calculate monthly payments if it knows how much interest is. So you just need to fill in the information for Excel to calculate. Click in an empty cell, then find the function bar. The toolbar is located on the right side of the spreadsheet (spreadsheet), denoted by “fx”. Click that and write “=PMT(“

Do not enter quotation marks.
If you are good at Excel, you can create an Excel formula to calculate the amount of a liability.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/46/Calculate-Interest-Payments-Step-15.jpg/v4-728px-Calculate-Interest-Payments-Step-15. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/46/Calculate-Interest-Payments-Step-15.jpg/v4-728px-Calculate-Interest-Payments-Step- 15.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Fill in the data in the correct place. Put the values for the payables in parentheses, separated by commas. In this case, you have to fill in (interest rate, term, principal,0).

With the above example, the full data is: “=PMT(0.00375,360,-100000,0)”
The last number in the sequence is 0. A zero means the ending balance (360 repayments) will be $0.
Remember to close the parenthesis.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/70/Calculate-Interest-Payments-Step-16.jpg/v4-728px-Calculate-Interest-Payments-Step-16. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/70/Calculate-Interest-Payments-Step-16.jpg/v4-728px-Calculate-Interest-Payments-Step- 16.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Press the Enter key to get the monthly payment results. If you entered the formula correctly, you will see the result in the =PMT formula cell on the worksheet.

In this case, the result will be 506.69 USD . That is your monthly debt.
If you see the error “#NUM!” or another result does not make sense, indicating that you entered the wrong data. Check the formula on the function bar again and try again.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/09/Calculate-Interest-Payments-Step-17.jpg/v4-728px-Calculate-Interest-Payments-Step-17. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/09/Calculate-Interest-Payments-Step-17.jpg/v4-728px-Calculate-Interest-Payments-Step- 17.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Calculate total liabilities by multiplying by the number of repayments. To calculate the total amount you will have to pay over the life of your loan, simply multiply your recurring liabilities by the number of repayments.

In the example above, you multiply 506.69 USD by 360 to get 182,408 USD . This is the total amount to be paid at the end of the loan period.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/be/Calculate-Interest-Payments-Step-18.jpg/v4-728px-Calculate-Interest-Payments-Step-18. jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/be/Calculate-Interest-Payments-Step-18.jpg/v4-728px-Calculate-Interest-Payments-Step- 18.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div> “}

Calculate interest by subtracting the principal from the total amount due. If you want to know the total interest payable, you just need to do the subtraction. Take the total liabilities at the end of the period minus the principal.

In this example, you subtract $182,408 from $100,000. The result is 82,408 USD . That is the total interest payable on the loan.

Make a Sample Spreadsheet to Calculate Loan Interest

The table below shows how to calculate interest using Excel, Google Docs, or similar spreadsheet programs. Just enter the number. Notice that when you see the formula $F$ $x$ $=$ ${displaystyle Fx=}$ $Fx=$ , you must enter data into the formula on the function bar “Fx”. The numbers (A2, C1, etc.) correspond to labeled cells in Excel and Google Docs.

**Sample Loan Interest Spreadsheet**
A	REMOVE	OLD	EASY
first	[Original debt]	[Number of repayments]	[Interest rate]	[Interest/month]
2	Negative loan (-100000)	Total number of monthly payments. (360)	Interest rate, expressed in decimal. (0.05)	Interest rate/month (divide the interest rate/year by 12)
3	Debt/month	FX=PMT(D2,B2,A2,0). NOTE: The last number is 0.
4	Total loan amount	FX=PRODUCT(D3,B2)
5	Interest	FX=SUM(D4,A2)

Advice

Understanding how loans are calculated will give you the tools to eliminate bad loans.
If you have intermittent sources of income and are looking at a loan with a very low interest rate but low recurring debt with a lower frequency of payments, then you should choose a loan with a longer loan term, even if the total amount is low. larger loan interest.
If you have more savings than you need and want to find the lowest interest rate loan to meet your needs, then a loan with a short term, high recurring repayments and less interest will be right for you. .

Warning

There are times when the lowest interest rates do not mean the lowest borrowing costs. If you have a thorough understanding of how interest is calculated, you can quickly calculate the true “cost” of a loan compared to the price it pays to get some of the benefits of that loan.

Steps