How to Calculate Interest in Excel with Payments (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article shows how to calculate interest in excel with payments using 3 practical examples. It also shows how to calculate mortgage payments, the time period needed to pay off loans, and other practical interest-related problems. The following picture gives an idea about the purpose of the article.


3 Examples to Calculate Interest in Excel with Payments

We can calculate interest in Excel with payments using the IPMT function. I’m going to explain how to use this function in this case with 3 practical examples.

Syntax of the IPMT Function:

The IPMT function consists of the following arguments.

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

  • rate – interest rate per period
  • per – period number for which the interest amount will be calculated
  • nper – total number of periods
  • pv – present value/principal amount/total loan
  • [fv] – balance at the end of the last payment. Defaults to 0.
  • [type] – 1 if payment is due at the beginning of the period. 0 if payment is due at the end of the period. Defaults to 0.

1. Calculate Monthly Interest Payments on a Credit Card Debt

Assume, you have a credit card installment agreement. You have to pay $4,500 in 12 months at a 15% annual interest rate. Now, you want to calculate the amount of interest you need to pay each month.

  • Then, enter the following formula in cell C5.
=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)
  • After that, apply the formula to the cells below using the fill handle tool.

calculate monthly interest in excel with payments

Read More: How to Calculate Credit Card Interest in Excel


2. Calculate Quarterly Interest Payments for a Car Loan

Suppose you want to get a car for $20,000. But you want to pay it in 3 years in quarterly installments at a 9% annual interest rate. Now, you want to calculate how much interest you need to pay each period on the loan.

  • Then, enter the following formula in cell C5:
=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)

Calculate Quarterly Interest in Excel with Payments

Read More: How to Calculate Interest on a Loan in Excel


3. Calculate Semi-Annual Interest Payments on a Personal Loan

Now, consider getting a personal loan of $10,000 from the bank at a 5.5% annual interest rate. You want to pay the loan semi-annually in six years.

  • Now, to get an idea of how much you need to pay as interest each period, enter the following formula in cell C5.
=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)

Calculate Semi-Annual Interest in Excel with Payments

Read More: How to Calculate Principal and Interest on a Loan in Excel


How to Calculate Total Interest Payment on Loans in Excel

You can easily get the total interest using the SUM function.

  • Just enter the following formula in cell C17:
=SUM(C5:C16)

  • Alternatively, you can also apply the following formula in cell C17 to get the same result as follows.
=CUMIPMT($F$4/$F$6,$F$7,$F$8,$B$5,$B$16,$F$10)
  • The above formula consists of the CUMIPMT function with the following arguments: CUMIPMT(rate,nper,pv,start_period,end_period,type)

CUMIPMT Function

Read More: How to Calculate Accrued Interest on a Loan in Excel


More Examples to Calculate Payments & Savings in Excel

Here, I will illustrate 4 interests, payments & savings-related practical examples using the FV, PV, NPER, and PMT functions in Excel.

1. Calculate Accumulated Savings After a Certain Period

Assume, you have $1,000 in your bank account. You want to deposit $150 each month for 1.5 years. The bank offers 5% annual interest on the deposit amount.

  • Now, to see how much your savings will be at the end of 1.5 years, enter the following formula in cell C12:
=FV(C4/C6,C7,C8,C9,C10)
  • The above formula consists of the FV function with the following arguments.

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

  • The pmt argument stands for payment at the end of each period.


2. Calculate Down Payment Amount for a Car Loan

Assume, you want to buy a $30,000 car at a 2.5% interest rate over 4 years. You cannot pay any more than $500 each month.

  • Now to calculate the down payment you need to make at the beginning of the purchase, enter the following formula in cell C13.
=C4-PV(C5/C7,C8,C9,C10,C11)
  • The above formula consists of the PV function with the following arguments.

         PV(rate,nper,pmt,[fv],[type])


3. Calculate Payoff Period for a Bank Loan

Imagine, you want to get a bank loan of $3,000 at a 2.5% annual interest rate. You can pay $200 a month to pay off the loan.

  • Now, to find out how many months it will take you to pay the loan off, enter the following formula in cell C11:
=NPER(C4/C5,C6,C7,C8,C9)

         NPER(rate,pmt,pv,[fv],[type])


4. Calculate Mortgage Payments in Excel

Imagine, you want to buy a $200,000 home at a 4.9% interest rate with a 25-year mortgage.

  • Then, apply the following formula in cell C12:
=PMT(C4/C6,C7,C8,C9,C10)

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


Things to Remember

  • Entering a non-numeric value for any arguments in the IPMT function will result in #Value! error.
  • The per argument in the IPMT function must be within 1 to nper. Otherwise, a #NUM! error will occur.
  • Usually, the arguments in third brackets [ ] are optional arguments.
  • Red-colored currencies indicate negative values as they are due payments.

Download Practice Workbook

You can download the practice workbook from the download button below. It will be helpful to understand the examples more easily.


Conclusion

Now, you can easily calculate payments, interests, savings, payment periods, etc. in Excel. I hope this article helps you understand how to calculate interest in Excel with payments. For further queries or suggestions, please use the comment section below.


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.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo