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.

monthly payment type data

Click on cell C4 then click on the function shortcut button (the symbol is “fx”) on the left of the formula bar.

insert function monthly payment

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.

pmt function

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.
See also  How to Calculate Compound Interest in Excel

function arguments

Leave the “FV” and “Type” fields blank and click ok. Your monthly payment will be shown in cell C4.

monthly payment

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.