Calculate an Interest Payment Using Microsoft Excel
Microsoft Excel provides functions for the calculations of many different financial transactions, including payments of principal and interest on loans and investments. Because some loans require interest-only payments, knowing how to calculate interest payments is crucial to budgeting. You may also have an investment that pays out monthly or quarterly interest - in this case, calculating this payment can be done the same way to determine how much income will be received. Calculating all kinds of interest payments in Excel is easy - see Step 1 below to get started.
Steps
- Set up your spreadsheet for the interest payment calculation.
- Create labels in the cells from A1 down to A4 as follows: Principal, Interest Rate, Number of Periods and Interest Payment.
- Enter the details of your transaction in the cells from B1 down to B3.
- Type in the principal owed on your loan or the amount of your investment in the cell for "Principal," B1.
- Divide your annual interest rate by 12 if you want to calculate interest on a monthly basis; divide by 4 if quarterly interest is to be calculated. Place this in cell B2.
- The number of periods involved in your loan or investment goes in the cell in B3. If calculating an interest payment for an investment with an indefinite length, use the number of interest payments in a year. This will be the same number you divided the interest rate by in the Interest Rate cell.
- For example, assume you will be calculating the monthly interest payment on an investment of $5,000 at 2.5 percent annual interest. Enter "5000" in cell B1, "=.025/12" in cell B2 and "1" in cell B3.
- Select cell B4 by clicking on it.
- Insert the IPMT function to calculate interest payments.
- Click the function shortcut button on the toolbar, labeled "fx."
- Type "interest payment" in the text entry box and then click "Go."
- Choose the "IPMT" function from the list below and then click "OK." The "Function Argument" window will launch.
- Reference the appropriate cells for each part of the argument.
- In the "Rate" field, enter "B2," "B3" is entered into the "NPER" field and "B1" is entered in the "PV" field.
- The value in the "Per" field should be "1."
- Leave the "FV" field blank.
- Click the "OK" button to complete the entries in the "Function Arguments" window and reveal the interest payment amount.
- Note that this value is a negative, because it pertains to money that is paid out.
- Finished.
Video
Template:Video:Calculate an Interest Payment Using Microsoft Excel
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.
Warnings
- Make sure you are entering your interest rate as a decimal and you are dividing it by the number of times that interest is calculated on your transaction in a calendar year.
