# How to Calculate Interest on a Loan in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with borrowed loans, we have to calculate the amount of interest or capital that we have to pay for that loan. We can easily calculate interest on a loan in Excel using in-built financial functions named PMT, IPMT, PPMTÂ and CUMIPMT. In this article, I will show you how to use these functions to calculate the interest for a given period, interest in a given year, and interest rate.

## 1. Determining Fixed Loan Repayment for Every Month or Year

You can use the PMT function to calculate the fixed interest on a loan in Excel for a certain period.

Letâ€™s assume a scenario where we have a loan that amounts to \$5000. The annual interest rate for the loan is 4% per annum. The loan was taken for 5 years. We need to calculate the interest from this given data. In this section, we will discuss five different methods to calculate interest on the loan in Excel.

Introduction to PMT Function

Function Objective:

Calculates the repayment for a loan based on a constant payment and constant interest rate.

Syntax:

=PMT(rate, per, nper, pv, [fv], [type])

Argument Explanation:

Argument Required/Optional Explanation
Rate Required Interest rate per period.
Nper Required The total number of payments for the loan.
Pv Required The present value, or the total amount that a series of future payments is worth now. Also known as the principal.
Fv Optional The future value or a cash balance you want to attain after the last payment is made. If we do not insert a value for fv, it will be assumed as 0 (the future value of a loan, for example, is 0).
Type Optional The number 0 or 1. It indicates the time when the payments are due. If the type is omitted, it is assumed to be 0.

Return Parameter:

Payment for a loan is based on constant payments and a constant interest rate.

Step 1:

• Our first step is to select the cell where we want to have our total repayment for a certain period. We will select cell C10 to calculate the total repayment for a month.
• Now we will write down the PMT formula in that cell.
`=PMT(C4/12, C7, C8)`

Formula Breakdown:

• Â  Â  Â C4 = Rate(First Argument) = Annual Interest Rate = 4%

As we are calculating fixed repayment for a month, we have divided it by the number of months in a year, 12.

• Â  Â  Â C7 = Npr(Second Argument) = Total number of payments = 60

We have 5 years to pay back the loan. 5 years have a total of (5X12) = 60 months

• Â  Â  Â C8 = Pv(Third Argument) = Total loan amount or Principal = \$5,000

Â Step 2:

• Upon clicking ENTER, we will get the fixed amount to pay every month or the monthly fixed repayment amount. This amount is the same for every month. It includes the portion of capital or principal and also the interest amount we have to pay in the first month.

Step 3:

• We can also calculate fixed repayment for every year. To calculate the repayment amount to pay every year, we have to use the following formula.
`=PMT(F4, F6, F8)`
• As we can see, we do not have to divide the annual interest rate by 12. We are calculating the amount for a year. And the Npr or the total number of payments is now 5 as we have 5 years to pay off the loan. The image below shows the fixed annual repayment amount.

## 2. Calculating the Interest Payment on a Loan for a Specific Month or Year

Although monthly or yearly repayment amounts over the loan term will be the same, the proportion of interest and capital you repay each period varies over the term. At the start of the loan you pay mostly interest and a little capital, but by the end of the term, you pay a little interest and mostly capital.

For each period of the loan, you can calculate the interest amount by using the IPMT function.

Introduction to IPMT Function

Function Objective:

Calculates the interest payment for a given period like a specific month or year.

Syntax:

=IPMT(rate, per, nper, pv, [fv], [type])

Argument Explanation:

Argument Required/Optional Explanation
Rate Required The interest rate per period.
Per
Required The period for which you want to find the interest. It must be in the range 1 to Nper
Nper Required The total number of payment periods in an annuity.
Pv Required The present value, or the total amount that a series of future payments is worth now. Also known as the principal.
Fv Optional The future value or a cash balance you want to attain after the last payment is made. If we do not insert a value for fv, it will be assumed as 0 (the future value of a loan, for example, is 0).
Type Optional The number 0 or 1. It indicates the time when the payments are due. If the type is omitted, it is assumed to be 0.

Return Parameter:

Interest payment for a given period for an investment is based on periodic, constant payments and a constant interest rate.

Step 1:

• Our first step is to select a cell and write down the formula of IPMT. We will select cell C10 and write down the following formula.
`=IPMT(C4/12, 1, C7, C8)`

Formula Breakdown:
• C4 = Rate(First Argument) = Annual Interest Rate = 4%

As we are calculating interest payment for a month, we have divided it by the number of months in a year, 12.

• 1 = Pr(Second Argument) = The period for which you want to find the interest Â = 1

We are calculating the interest amount for the first month. Hence Pr = 1

• C7 = Nper(Third Argument) = Total number of payments = 60
• C8 = Pv(Fourth Argument) =Â  Total loan amount or Principal = \$5,000

Step 2:

• Upon clicking ENTER, we will get the interest amount to pay in the first month.

Step 3:

• We can also calculate interest payments for a specific year. To calculate the interest amount for the last year, we have to use the following formula.
`=IPMT(F4, 5, F6, F8)`
• As we can see, we do not have to divide the annual interest rate by 12. We are calculating the amount for a year. And the Pr or the period for which we want to find the interest is now 5 as we are calculating the interest amount for the last or 5th year. Total Period (F6) is also 5 as our total period is 5 years. The image below shows the fixed annual interest amount to pay in the last or 5th year.

• We can also calculate the weekly, quarterly, and semi-annual interest payments using IPMT.

## 3. Computing Capital Payment for a Certain Interest Rate on a Loan

We can also calculate the capital payment for a specific month or year using the PPMT function of Excel.

Introduction to PPMT Function

Function Objective:

Calculates the capital payment for a given period like a specific month or year.

Â Syntax:

=PPMT(rate, per, nper, pv, [fv], [type])

Argument Explanation:

Argument Required/Optional Explanation
Rate Required The interest rate per period.
Per
Required The period for which you want to find the interest must be in the range 1 to Nper
Nper Required The total number of payment periods in an annuity.
Pv Required The present value, or the total amount that a series of future payments is worth now. Also known as the principal.
Fv Optional The future value or a cash balance you want to attain after the last payment is made. If we do not insert a value for fv, it will be assumed as 0 (the future value of a loan, for example, is 0).
Type Optional The number 0 or 1. It indicates the time when the payments are due. If the type is omitted, it is assumed to be 0.

Return Parameter:

The payment on the principal for a given period for an investment is based on periodic, constant payments and a constant interest rate.

Step 1:

• We will select cell C10 and write down the following formula of PPMT.
`=PPMT(C4/12, 1, C7, C8)`

Formula Breakdown:

• C4 = Rate(First Argument) = Annual Interest Rate = 4%

As we are calculating interest payment for a month, we have divided it by the number of months in a year, 12.

• 1 = Pr(Second Argument) = The period for which you want to find the interest Â = 1

We are calculating the interest amount for the first month. Hence Pr = 1

• C7 = Nper(Third Argument) = Total number of payments = 60
• C8 = Pv(Fourth Argument) =Â  Total loan amount or Principal = \$5,000

Step 2:

• Upon clicking ENTER, we will get the capital amount to pay in the first month.

Step 3:

• We can also calculate capital payments for a specific year. To calculate the capital amount for the last year, we have to use the following formula.
`=PPMT(F4, 5, F6, F8)`
• As we can see, we do not have to divide the annual interest rate by 12. We are calculating the amount for a year. The Pr or period for which we want to find the interest is now 5 as we are calculating the capital amount for the last or 5th year. Total Period (F6) is also 5 as our total period is 5 years. The image below shows the annual capital amount to pay in the last or 5th year.

Note: The sum of interest payment and capital payment will be equal to the fixed repayment amount that we calculated in the first method.

• Interest payment for the first month = \$16.67 [Using IPMT Function]

Capital payment for the first month = \$75.42 [Using PPMT Function]

Total payment for the first month = 16.67+75.42 = 92.09 = Total Repayment for Every Month that we calculated using PMT function in method 1.

So, the total amount to repay will be equal for every identical period. However, the interest amount and capital amount will vary from period to period.

• We can also calculate the weekly, quarterly, and semi-annual capital payments using PPMT.

## 4. Calculating Cumulative Loan Interest for a Specific Month or Year in Excel

You can use the CUMIPMT function to calculate the cumulative interest on a loan in Excel for a specific period like a specific month or year.

Introduction to CUMIPMT Function

Function Objective:

Cumulative interest paid on a loan between start_period and end_period.

Syntax:

=CUMIPMT(rate, nper, pv, start_period, end_period, [type])

Argument Explanation:

Argument Required/Optional Explanation
Rate Required The interest rate per period.
Nper Required The total number of payment periods in an annuity.
Pv Required The present value, or the lump-sum amount that a series of future payments is worth right now.
Start_period Required The first period in the calculation. Payment periods are numbered beginning with 1.
End_period
Required The last period in the calculation.
Type Required The number 0 or 1. It indicates the time when the payments are due. If the type is omitted, it is assumed to be 0.

Return Parameter:

Cumulative interest paid on a loan over a specific period.

Step 1:

• First, we will select cell C10 and write down the formula below for cumulative interest for the first month.
`=CUMIPMT(C4/12, C7, C8, 1, 1, 0)`

Formula Breakdown:

• C4 = Rate(First Argument) = Annual Interest Rate = 4%

As we are calculating the cumulative interest for a month, we have divided it by the number of months in a year, 12.

• C7 = Nper(Second Argument) = Total number of payments = 60

We have 5 years to pay back the loan. 5 years have a total of (5X12) = 60 months

• C8 = Pv(Third Argument) =Â  Total loan amount or Principal = \$5,000
• 1 = Â Start_period(Fourth Argument) and End_period(Fifth Argument) = We are calculating the cumulative interest for the first month. Hence, our starting and ending period is 1.
• 0 = Type(Sixth Argument) = Payment at the end of the period.

Step 2:

After that, we will press ENTER and we will get the cumulative interest amount for the first month.

Step 3:

• We can also calculate cumulative interest for a specific year. To calculate the cumulative interest amount to pay in the last or 5th year, we have to use the following formula.
`=CUMIPMT(F4/12, F7, F8, 49,60, 0)`
• Arguments are almost similar to those used for calculating cumulative interest for the first month except for starting and ending periods. The Starting_period is 49 as the last or fifth year starts after the 4th year or (4X12) = 48 months and ends after (5X12) = 60 months. So, the End_period is 60. The image below shows the fixed annual repayment amount.

## 5. Using Excel FV Function to Calculate Compound Interest on a Loan

You can also calculate compound interest on a loan in Excel using the FV function.

Introduction to FV Function

Function Objective:

Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump-sum payment.

Syntax:

FV(rate,nper,pmt,[pv],[type])

Argument Explanation:

Argument Required/Optional Explanation
Rate Required The interest rate per period.
Nper Required The total number of payment periods in an annuity.
Pmt
Required The payment that is to be made per period. It is fixed or constant over the life of the loan or mortgage. Typically, pmt contains only the principal and interest but no fees or taxes. If pmt is omitted, you must include the pv argument.
Pv Optional The present value, or the total amount that a series of future payments is worth now. Also known as the principal.
Type Required The number 0 or 1. It indicates the time when the payments are due. If the type is omitted, it is assumed to be 0.

Return Parameter:

Future value.

Step 1:

• First, we will select cell C10 and write down the formula below for compound interest for the first month.
`=FV(C4/12, C7, 0, -C8)`

Formula Breakdown:

• C4 = Rate(First Argument) = Annual Interest Rate = 4%

As we are calculating on a monthly basis, we have divided it by the number of months in a year, 12.

• C7 = Npr(Second Argument) = Total number of payments = 60

We have 5 years to pay back the loan. 5 years have a total of (5X12) = 60 months.

• 0 = Pmt(Third Argument) =Â  The payment made each period.
• -C8 = Pv(Fourth Argument) = The present value.

Step 2:

• Then upon clicking ENTER, we will get the compound interest for the period.

## Things to Remember

• Type argument in these functions is usually optional. The number 0 or 1 indicates when payments are due. If the type is omitted, it is assumed to be 0.
• Set the Type argument to 0 if payments are due at the end of the period. Set the Type argument to 1 if payments are due at the beginning of the period.

## Related Articles

<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

OPIYO DENIS OCAYA Aug 12, 2022 at 9:49 AM

I LIKE TO USED THIS APP PLESED

• Hello, OPIYO DENIS OCAYA! You can download our practice workbook and use it as a calculator which is quite similar to using an app instead!

2. Hi, I want to calculate how much money is paid to the bank for a loan in total, so I used CUMIPMT formula, but it shows a different number than simply PMT*nper, why is that?
And can I get to PMT*nper with a single formula?

• Greetings Tom,

You are right. You can calculate the Total Paid or Payable Amount of a loan using PMT*nper or Cumulative Interest + Loan Amount. And the amount will be the same.
Make sure you enter the Start_period (i.e., minimum 1) and End_period (i.e., within the Total Periods (60)) correctly.

Regards,
Md. Maruf Islam (Exceldemy Team)

Advanced Excel Exercises with Solutions PDF