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

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.


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.

Interest on a Loan in Microsoft 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

Introduction to PMT Function in Excel

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)

Calculate Interest in Excel Using PMT Function

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.

Calculate Interest with PMT Function

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.

Calculate Interest on a Loan in Excel

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

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)

Calculate Interest Using IPMT Function in Excel

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.

Use Excel IPMT Function to Calculate Interest on a Loan

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.

Calculate Interest Payment for a Specific Period Using IPMT Function

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

Interest Payment for Different Type of Periods

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

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)

Capital Payment for the First Month Using PPMT Function

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.

Excel PPMT Function

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.

Capital Payment for Last Year Using PPMT Function

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.

PPMT Function For Different Periods

Read more: How to Calculate Accrued Interest on Fixed Deposit in Excel


Similar Readings


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

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 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)

Excel CUMIMPT Function to Calculate Cumulative Interest

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.

How to Calculate Interest on a Loan in Excel

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

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)

Calculate Compound Interest Using FV Function

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.

Compound Interest of a Loan Using FV Function

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!!!


Further Readings

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo