Excel is a worksheet application that is a component of the Microsoft Office program. Using Microsoft Excel, you can calculate monthly installments for any type of loan or credit card. This allows you to be more accurate in your personal budgeting to allocate enough funds for monthly installments. The best way to calculate monthly installment payments in Excel is to use the "functions" feature.
Step
Step 1. Open Microsoft Excel and a new workbook
Step 2. Save the workbook file with an appropriate and descriptive name
This will help you find work when you later need to refer to or make changes to your data
Step 3. Create a label in cell A1 decreasing to A4 for the variable and the result of your monthly installment calculation
- Type "Balance" in cell A1, "Interest rate" in cell A2 and "Period" in cell A3.
- Type "Monthly Installment" into cell A4.
Step 4. Enter the variable for the loan or credit card account in cells from B1 down to B3 to create your Excel formula
- The remaining balance due will be entered in cell B1.
- The annual interest rate, divided by the number of accrual periods in one year, will be entered in cell B2. You can use an Excel formula here, such as "=.06/12" to represent the 6 percent annual interest charged each month.
- The number of periods for your loan will be entered in cell B3. If you're calculating monthly installments for a credit card, enter the number of periods as the difference in months between today and the date you set for repayment.
- For example, if you want to pay off your credit card installments 3 years from now, enter the number of periods "36." Three years multiplied by 12 months per year equals 36.
Step 5. Select cell B4 by clicking on it
Step 6. Click the function shortcut button on the left side of the formula bar
The label on it is "fx."
Step 7. Look for the Excel formula "PMT" if it is not shown in the list
Step 8. Click to select the "PMT" function, then click the "OK" button
Step 9. Make a reference to the cell where you entered the details for each field in the "Function Arguments" window
- Click in the "Rate" field window, then click cell B2. The "Rate" field will now pull information from that cell.
- Repeat for the "Nper" field by clicking inside this field, then clicking cell B3 so that the number of periods can be drawn.
- Repeat again for the "PV" field by clicking inside the field, then clicking cell B1. This will force the loan or credit card balance to be withdrawn for the function.
Step 10. Leave the "FV" and "Type" fields blank in the "Function Arguments" window
Step 11. Complete the process by clicking the "OK" button
The calculated monthly installment will be displayed in cell B4, next to the "Monthly Installment" label
