Calculate Interest Payments on a Loan with the IPMT Function

PDF download Download Article
Calculate interest payments with simple Excel formulas
PDF download 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

PDF download Download Article
  1. Launch Excel, then click Blank workbook to get started.
    • Skip this step on Mac.
  2. 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. 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. 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. This goes in cell B3 . If you're on a 12-month plan, for example, you would type 12 into cell B3 .
  6. Simply click B4 to select it. This is where you'll enter the formula to calculate your interest payment.
  7. 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 .
    8. Advertisement

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
      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
        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!
      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
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      How to
      Calculate Average Growth Rate in Excel
      How to
      Alphabetize Cells in Excel
      How to
      Find the Mode in Excel: Single & Multiple Mode Functions
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      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?
      In other languages
      Thanks to all authors for creating a page that has been read 599,875 times.

      Is this article up to date?

      Advertisement