- Categories
- Computers and Electronics
- Software
- Office
- Spreadsheets
- Microsoft Excel
Download Article
Calculate interest payments with simple Excel formulas
Explore this Article
Written byAlex Kwan|Edited byJack Lloyd
Last Updated: March 19, 2024Tested
Download Article
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
Advertisem*nt
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.
5
Enter the number of payments you have left. This goes in cell B3. If you're on a 12-month plan, for example, you would type 12 into cell B3.
6
Select cell B4. 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.
Advertisem*nt
Community Q&A
Search
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 wikiHowYesNo
Not Helpful 24Helpful 11
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Advertisem*nt
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
Helpful0Not Helpful0
Submit a Tip
All tip submissions are carefully reviewed before being published
Submit
Thanks for submitting a tip for review!
Advertisem*nt
Warnings
Interest rates are subject to change. Make sure you read the fine print on your interest agreement before you calculate your interest.
Thanks
Helpful0Not Helpful0
Advertisem*nt
You Might Also Like
Advertisem*nt
About This Article
Written by:
Alex Kwan
Certified Public Accountant
This article was written by Alex Kwan and by wikiHow staff writer, Jack Lloyd. Alex Kwan is a Certified Public Accountant (CPA) and the CEO of Flex Tax and Consulting Group in the San Francisco Bay Area. He has also served as a Vice President for one of the top five Private Equity Firms. With over a decade of experience practicing public accounting, he specializes in client-centered accounting and consulting, R&D tax services, and the small business sector. This article has been viewed 573,835 times.
How helpful is this?
Co-authors: 13
Updated: March 19, 2024
Views:573,835
Categories: Microsoft Excel
Article SummaryX
1.Label rows for Principal, Interest, Periods, and Payment.
2.Enter total value in the Principal row.
3.Enter the interest rate into the Interest row.
4.Enter the amount of remaining payments in the Periods row.
5.Click the first blank cell in the Payments row.
6.Type " =IPMT(B2, 1, B3, B1)" into the cell.
7.Press Enter.
Did this summary help you?
In other languages
- Send fan mail to authors
Thanks to all authors for creating a page that has been read 573,835 times.
Is this article up to date?
Advertisem*nt
