Content

• Steps
• Tips
• Warnings
• What do you need

Excel is a spreadsheet application that is part of the Microsoft Office suite of programs. With Microsoft Excel, you can calculate the monthly payment for any type of loan or credit card. This will allow you to more accurately calculate your personal budget and set aside sufficient funds for your monthly payments. The best way to calculate your monthly payment in Excel is to use functions.

Steps

1. 1 Start Microsoft Excel and open a new workbook.
2. 2 Save the book file with an appropriate and descriptive title.
   • This will help you find your file later if you need to refer to it or make changes.
3. 3 Create headers in cells A1 through A4 for the variables and the result of calculating your monthly payment.
   • Type “Balance” in cell A1, “Interest rate” in cell A2 and “Periods” in cell A3.
   • Type "Monthly Payment" in cell A4.
4. 4 Enter the variables for your credit or credit card account in cells B1 through B3 to create an Excel formula.
   • The debt will be entered in cell B1.
   • The annual percentage rate divided by the number of accrual periods in the year will be entered in cell B2. You can use an Excel formula such as “= 0.06 / 12” to represent 6 percent per annum, which is charged monthly.
   • The number of periods for your loan will be entered in cell B3. If you are calculating a monthly credit card payment, enter the number of periods as the difference in months between today and the date you would like to receive payment in full.
   • For example, if you want to receive a credit card bill in 3 years from today, enter the number of periods as "36". Three years multiplied by 12 months of a year equals 36.
5. 5 Select cell B4 by clicking on it.
6. 6 Click the function button on the left side of the formula bar. It contains the symbols "fx".
7. 7 Look for the PMT formula if it does not appear in the list.
8. 8 Select the "PMT" function and then press the "OK" button.
9. 9 Create cell references where you entered your data for each field in the Function Arguments window.
   • Click inside the Course box, and then click cell B2. The Course field will now take information from this cell.
   • Repeat for the Nper field by clicking inside this field and selecting cell B3 so that the value for the number of periods is taken from that cell.
   • Repeat one more time for the PV field by clicking inside the field and then clicking cell B1. This will allow the function to take the value of your credit or credit card account.
10. 10 Leave the BM and Type fields blank in the Function Arguments window.
11. 11 Complete the process by clicking the "OK" button.
    • The estimated monthly payments will be shown in cell B4, next to the “Monthly payment” text.
12. 12 End.

Tips

• Copy cells A1 through B4, and then paste these values ​​in cells D1 through E4. This will allow you to edit the details in this second calculation to consider alternative variables while maintaining the original calculations.

Warnings

• Make sure you convert the interest rate to decimal numbers correctly and the annual interest rate is divided by the number of periods in the year in which interest is calculated. If your interest is charged on a quarterly basis, the interest rate will be divided by 4. Semi-annual interest rates are divided by 2.

What do you need

• Computer
• Microsoft Excel
• Account data