ASM Arman
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.
Read More: How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
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 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 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.
Read More: How to Calculate Interest in Excel with Payments (3 Examples)
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. 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 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. But 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.
Read More: Create Late Payment Interest Calculator in Excel and Download for Free
Similar Readings
- How to Create GST Interest Calculator in Excel (with Useful Steps)
- How to Make HELOC Payment Calculator Using Principal and Interest in Excel
- Daily Loan Interest Calculator in Excel (Download for Free)
- How to Calculate Home Loan Interest in Excel (2 Easy Ways)
- How to Create Prejudgment Interest Calculator in Excel
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.
Read More: How to Use Cumulative Interest Formula in Excel (3 Easy Ways)
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.
Read More: How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)
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.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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 will find it very easy to calculate interest on your loans in Excel. If you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!
Related Articles
- How to Create SIP Interest Calculator in Excel (with Easy Steps)
- How to Create TDS Interest Calculator in Excel
- Calculation of Interest During Construction in Excel
- Car Loan Calculator in Excel Sheet – Download Free Template
- How to Calculate Credit Card Interest in Excel (3 Easy Steps)
- Bank Interest Calculator in Excel Sheet – Download Free Template
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)