Buzz
Excel is a spreadsheet application part of the Microsoft Office suite. With Microsoft Excel, you can calculate monthly payments for any loan or credit card. It helps you create more accurate budgets and allocate an appropriate amount for monthly payments. The best way to calculate monthly payments in Excel is by using the "functions" feature.
Steps
Open Microsoft Excel and create a new workbook.
Save the file with an appropriate name and description.
- This way, you can easily find the file when you need to reference or update the information.
Label cells A1 to A4 with the corresponding variables and results for monthly payment calculation.
- Type "Balance" in cell A1, "Interest Rate" in cell A2, and "Term" in cell A3.
- Type "Monthly Payment" in cell A4.
Enter the corresponding variables for the debt or credit card account in cells B1 to B3 to create the Excel formula.
- Enter the outstanding balance in cell B1.
- Enter the annual interest rate divided by the number of periods per year in cell B2. For example, you can use the formula "=.06/12" to display a 6% annual interest rate compounded monthly.
- Enter the loan term in months in cell B3. If you're calculating payments for a credit card, enter the number of months until your balance is due in full.
- For instance, if you want to pay off the debt in 3 years, enter "36" as the term. Three years equals 36 months.
Click on cell B4.
Click the shortcut button on the left side of the formula bar. It is labeled "fx".
Search for the Excel formula "PMT" if it's not already listed.
Select the "PMT" function and click the "OK" button.
Fill in the reference fields by entering detailed information for each parameter in the "Function Arguments" window.
- Click on the "Rate" field and select cell B2. The "Rate" field will display the information from this cell.
- Repeat the same process for the "Nper" field: click on it and select cell B3 to display the term.
- Do the same for the "PV" field: click inside the field and select cell B1. This will display the outstanding balance or credit card account information.
Leave the "FV" and "Type" fields blank in the "Function Arguments" window.
Click "OK" to finish the process.
- Your calculated monthly payment will appear in cell B11, next to the "Monthly Payment" label.
Done.
Tips
- Copy cells A1 through B4 and paste them into cells D1 through E4. This way, you can modify the information in the second spreadsheet to test alternate variables without affecting the original spreadsheet.
Warning
- Don't forget to convert the interest rate into a decimal and divide the annual rate by the number of periods in a year. If the interest is compounded quarterly, divide by 4. If it's compounded semi-annually, divide by 2.
What You'll Need
- Computer
- Microsoft Excel
- Account Information
