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.

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

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

Search for the “PMT” Excel formula in the “Select a function:” menu and press ok.

pmt function

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.

function arguments

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

monthly payment

Template

You can download the Template here – Download