Site icon Best Excel Tutorial

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:

Enter the variables for your loan or credit card account in the cells from B1 down to B3.

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.

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.

Exit mobile version