Calculate Interest Payments on a Loan with the IPMT Function

Calculate interest payments with simple Excel formulas

Co-authored by Jack Lloyd

Last Updated: March 19, 2024 Tested

This wikiHow teaches you how to create an interest payment calculator in Microsoft Excel. To calculate payments, you'll just need the principal amount, interest rate, and number of payments remaining. You can then use the IPMT function to determine how much you'll have to pay in interest in each period. You can calculate interest payments in Excel on a Windows PC or a Mac.

Things You Should Know

Create row headers for Principal, Interest, Periods, and Payment.
Fill out the principal amount, interest rate, and the number of payment periods.
In the Payment row, use the formula =IPMT(B2, 1, B3, B1) to calculate the interest payment.

Steps

Download Article

1
Open a Blank Workbook. Launch Excel, then click Blank workbook to get started.
- Skip this step on Mac.
2
Set up your rows. Enter your payment headings in each of the following cells:
- Cell A1 - Type in Principal
- Cell A2 - Type in Interest
- Cell A3 - Type in Periods
- Cell A4 - Type in Payment
Advertisement
3
Enter the payment's total value. In cell B1, type in the total amount you owe.
- For example, if you bought a boat valued at $20,000 for $10,000 down, you would type 10,000 into B1.
4
Enter the current interest rate. In cell B2, type in the percentage of the interest that you have to pay each period.
- For example, if your interest rate is three percent, you would type 0.03 into B2.
This goes in cell B3. If you're on a 12-month plan, for example, you would type 12 into cell B3.
Simply click B4 to select it. This is where you'll enter the formula to calculate your interest payment.
7
Enter the interest payment formula. Type =IPMT(B2, 1, B3, B1) into cell B4 and press ↵ Enter. Doing so will calculate the amount that you'll have to pay in interest for each period.
- This doesn't give you the compounded interest, which generally gets lower as the amount you pay decreases. You can see the compounded interest by subtracting a period's worth of payment from the principal and then recalculating cell B4.

Community Q&A

Search

Add New Question

Question

The interest is 6% per annum, and the amount deposited is 500,000 on January 2016. If a member withdraws his amount on May 2016, what is the interest?

Community Answer

6% per annum is .5% monthly (.5 * 12 = 6), so that's $2500.00 in interest per month ($500,000 *.5% = $2,500, or $500,000 * .005 = $2,500). If the member withdrew in May before the interest was calculated and paid out for the month of May, then $10,000.00 ($2,500 * 4) in interest. If after, then $12,500.00 ($2,500 * 5) in interest.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 25 Helpful 11

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Tips

You can copy and paste cells A1 through B4 into another part of the spreadsheet in order to evaluate the changes made by different interest rates and terms without losing your original formula and result.

Thanks
Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!

Warnings

Interest rates are subject to change. Make sure you read the fine print on your interest agreement before you calculate your interest.

Thanks
Helpful 0 Not Helpful 0