How to calculate monthly payment in Excel
Excel is the spreadsheet application component of the Microsoft Office. Using Microsoft Excel, you can calculate a monthly payment for any type of loan, mortgage or credit card.
Data preparation
Here are the steps:
- Click on cell A1 and type “Balance”.
- Click on cell A2 and type “Interest rate”.
- Click on cell A3 and type “Period”.
- Click on cell A4 and type “Monthly payment”.
Enter the variables for your loan or credit card account in the cells from B1 down to B3.
- The due balance will be entered in cell C1.
- Type the annual interest rate, divided by the number of accrual periods in a year in Cell C2 (You can use an Excel formula here, such as “=.06/12” to represent 6 percent interest that is accrued monthly).
- Type the number of periods for your load in cell C3.
Click on cell C4 then click on the function shortcut button (the symbol is “fx”) on the left of the formula bar.
PMT function
You can use the PMT function. The PMT function calculates the periodic payment for a loan or investment based on constant payments and a constant interest rate. Here are the steps to use the PMT function:
Search for the “PMT” Excel formula in the “Select a function:” menu.
Create a cell reference in which your details have been entered.
- Type cell C2 inside the “Rate” field window. The “Rate” field will now pull the information from this cell.
- Type cell C3 inside the “Nper” field window.
- Type cell C1 inside the “PV” field window.
Leave the “FV” and “Type” fields blank and click ok. Your monthly payment will be shown in cell C4.
It’s important to note that the PMT function assumes that payments are made at the end of each period. If payments are made at the beginning of each period, you can adjust the formula by adding a “1” to the end of the function, like this: “=PMT(rate/12,term*12,-amount,1)”. This tells Excel to assume that payments are made at the beginning of each period instead of at the end.
Leave a Reply