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 inbuilt 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.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
5 Suitable Methods to Calculate Interest on a Loan in Excel
Letâ€™s assume a scenario where we have a loan that amounted 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.
1. Calculate 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.
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.
Read more: How to Calculate Accrued Interest on a Loan in Excel
2. Find Out Interest Payment On a Loan for 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 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)
 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 5^{th} 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 Â 5^{th} year.
 We can also calculate the weekly, quarterly, and semiannual interest payments using IPMT.
Read more: How to Calculate Principal and Interest on a Loan in Excel
3. Calculate Capital Payment for Specific Month or Year in Excel
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. And the Pr or the period for which we want to find the interest is now 5 as we are calculating the capital amount for the last or 5^{th} 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 5^{th} 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. But the interest amount and capital amount will vary from period to period.
 We can also calculate the weekly, quarterly, and semiannual capital payments using PPMT.
Read more: How to Calculate Accrued Interest on Fixed Deposit in Excel
Similar Readings
 How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
 Daily Loan Interest Calculator in Excel (Download for Free)
 How to Calculate Interest Rate in Excel (3 Ways)
 Create Late Payment Interest Calculator in Excel and Download for Free
4. Determine Cumulative Loan Interest for 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 lumpsum 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 between 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 5^{th} 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 4^{th} 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.
Read more: How to Calculate Gold Loan Interest in Excel
5. Calculate Compound Interest on A Loan Using FV Function
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 lumpsum 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 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.
Read more: How to Calculate Home Loan Interest in Excel
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 Type argument to 0 if payments are due at the end of the period. Set Type argument to 1 if payments are due at the beginning of the period.
Conclusion
In this article, we have learned to calculate interest on a loan in Excel.Â We learned how to calculate the total fixed repayment for every period, interest and capital payment for a specific period, cumulative and compound interest payment for a specific month or year using functions like PMT, IPMT, PPMT, CUMIPMT, and FV functions in Excel. I hope from now on you would find it very easy to calculate interest on your loans in Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
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!
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)