Download Article
Calculate interest payments with simple Excel formulas
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
-
Open a Blank Workbook. Launch Excel, then click Blank workbook to get started.
- Skip this step on Mac.
-
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 -
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 .
-
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 .
-
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 .
-
Select cell B4 . Simply click B4 to select it. This is where you'll enter the formula to calculate your interest payment.
-
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 .
Advertisement
Community Q&A
Search
-
QuestionThe 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 Answer6% 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.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
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
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
Advertisement
Warnings
- Interest rates are subject to change. Make sure you read the fine print on your interest agreement before you calculate your interest.Thanks
Advertisement
About This Article
Article Summary
X
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?
Thanks to all authors for creating a page that has been read 579,526 times.
Advertisement
