How To Calculate Monthly Payment In Excel
Here are the steps:
Launch Excel, create a new workbook and save the workbook with an appropriate name.
- 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 period 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.
Search for the “PMT” Excel formula in the “Select a function:” menu and press ok.
Create 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.
Template
You can download the Template here – Download
Further reading: Basic concepts Getting started with Excel Cell References
