Calculate Loan Interest in Given Year

When you borrow money, you are supposed to repay it gradually. Lenders, on the other hand, want to be compensated for their services and the risk they incur by lending you money. That is, you will not just repay the money you borrowed. You’ll repay the loan plus interest.

Interest is a significant source of revenue for lenders, banks, and credit card issuers. Here’s an overview of how interest works and how to calculate loan interest in given year using Excel CUMIPMT function.

Calculate loan interest in given year1

What is interest on a loan?

Interest is the cost of borrowing money from another person. If you borrow $6,000 for a personal loan, you may end up owing the lender about $7,130 in interest over the next five years. That additional $1,130 is interest.

Calculate loan interest in given year1

Calculate loan interest in given year1

As you return the loan over time, a part of each payment is applied to the amount borrowed (referred to as the principal), and the remainder is applied to interest expenses. The amount of loan interest charged by the lender is decided by factors such as your credit history, income, loan amount, loan conditions, and existing debt load.

Calculate loan interest in given year

To maximize earnings, lenders charge interest in a variety of ways. Calculating loan interest may be challenging, since many forms of interest need additional mathematics.

Simple interest

Provided a lender employs the simple interest technique, calculating loan interest is straightforward if you have the necessary information. Collect information such as the principal loan amount, the interest rate, and the total number of months or years you intend to repay the loan.


You may use the following calculation to get your total interest: Interest is calculated as the principal loan amount multiplied by the interest rate multiplied by the time period (a.k.a. the number of years in the term).

For instance, if you get a five-year loan for $20,000 at a rate of 5%, the basic interest calculation is as follows:

20,000 multiplied by .05 multiplied by five equals $5,000 in interest.

On short-term loans, you may find basic interest. However, the manner in which the majority of banks and lenders calculate interest is more sophisticated.

Amortizing loans

Numerous lenders assess interest on an amortization schedule. Student loans, mortgages, and automobile loans are often included in this category. While the monthly payment on these loans is set and the loan is paid in equal installments over time, the lender’s method of applying your payments to the loan amount varies over time.

With amortizing loans, the early payments are often high in interest, which means that less of the money you spend each month goes toward paying down the principal loan balance.

However, as time passes and you approach the payback date of your loan, the tables flip. Toward the conclusion of the term of your loan, the lender will apply the bulk of your monthly payments to the principal debt and less to interest costs.


Divide your interest rate by the total number of payments that year. If your interest rate is 6% and you make monthly payments, divide 0.06 by 12 to get 0.005.

Multiply that figure by the remaining loan total to get the amount of interest you’ll pay that month. If your loan sum is $5,000, your first month’s interest will be $25.

Subtract the interest from your set monthly payment to get the amount of principal due in the first month. If your lender has said that your fixed monthly payment would be $430.33, you will make the first month’s payment of $405.33 toward the principal. This sum is deducted from your current balance.

Repeat the procedure the next month with your new remaining loan amount, and continue doing so for each future month.

Related Posts

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Calculate Years Between Dates In Ms Excel

If you are an avid Ms Excel user, then you might have come across a task in which you needed to calculate the years between the dates; you might take it easy and do this task manually, which is also ...

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you're producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time ...

Calculate Interest Rate for Loan

The interest rate is the fee charged by a lender to a borrower and is expressed as a percentage of the principal—the lent amount. The interest rate on a loan is often expressed as an annual percentage rate, abbreviated as ...

Calculate Interest for Given Period

Using the IPMT function in Excel, we can compute the interest payment on any loan. This step-by-step tutorial will guide Excel users of all skill levels through the process to calculate interest for given period. Finally, the formula: =IPMT(B3/12,1,B5,-B2) The ...

How To Use Excel GCD Function

This post will guide you how to use Excel GCD function with syntax and examples in Microsoft excel. Description The Excel GCD function Returns the greatest common divisor of two or more integers. So you can use the GCD function ...

Calculate A Ratio From Two Numbers In Excel

In elementary mathematics, a ratio is a connection or comparison between two or more integers. For example, ratios are often expressed as ":" to demonstrate the relationship between two numbers. You would think that manually calculating a ratio from two ...

How To Use Excel RRI Function

This post will guide you how to use Excel RRI function with syntax and examples in Microsoft excel. Description The Excel RRI function Returns an equivalent interest rate for the growth of an investment. So you can use the RRI ...

CAGR Formula Examples in Excel

CAGR in Excel is a formula that calculates the compound annual growth rate for any invested amount over the specified years or timeframe. Although there is no direct function in Excel that can help us identify the CAGR value, there ...

Build Hyperlink With VLOOKUP in Excel

You might have come across a task in which you were assigned to build hyperlinks, which seems very easy, and if you are new to excel or don't have enough experience with it, then you might wonder about doing this ...