How to Calculate an Interest Payment Using Microsoft Excel

This wikiHow teaches you how to create an interest payment calculator in Microsoft Excel. You can do this on both Windows and Mac versions of Excel.

Steps

  1. Image titled Calculate an Interest Payment Using Microsoft Excel Step 1
    1
    Open Microsoft Excel. Double-click the Excel app icon, which resembles a white "X" on a dark-green background.
  2. Image titled Calculate an Interest Payment Using Microsoft Excel Step 2
    2
    Click Blank Workbook. It's in the upper-left side of the main Excel page. Doing so opens a new spreadsheet for your interest calculator.
    • Skip this step on Mac.
    Advertisement
  3. Image titled Calculate an Interest Payment Using Microsoft Excel Step 3
    3
    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
  4. Image titled Calculate an Interest Payment Using Microsoft Excel Step 4
    4
    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.
  5. Image titled Calculate an Interest Payment Using Microsoft Excel Step 5
    5
    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.
  6. Image titled Calculate an Interest Payment Using Microsoft Excel Step 6
    6
    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.
  7. Image titled Calculate an Interest Payment Using Microsoft Excel Step 7
    7
    Select cell B4. Simply click B4 to select it. This is where you'll enter the formula to calculate your interest payment.
  8. Image titled Calculate an Interest Payment Using Microsoft Excel Step 8
    8
    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.
    9. Advertisement

Community Q&A

  • 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
    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.
Advertisement

Warnings

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

You Might Also Like

Calculate Average Growth Rate in Excel
How to
Calculate Average Growth Rate in Excel
Alphabetize Cells in Excel
How to
Alphabetize Cells in Excel
Calculate Mode Using ExcelHow to Find the Mode in Excel: Single & Multiple Mode Functions
Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Create a Graph in Excel
How to
Create a Graph in Excel
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Advertisement

About This Article

Jack Lloyd
Written by:
Jack Lloyd
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Jack Lloyd. Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been viewed 501,806 times.
How helpful is this?
Co-authors: 12
Updated: March 18, 2020
Views: 501,806
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?
Advertisement