How to Calculate Payment in Excel?

In this article, we will demonstrate how to:

Apply a direct formula to calculate monthly payment.
Use the PMT function to calculate monthly payment.
Use the PMT function with a compound period to calculate monthly payment.
Use the Formulas tab to calculate monthly payment.
Calculate monthly payments on a loan, including a mortgage loan payment, a credit card loan payment, a down payment calculation, a loan payoff time, and a monthly payment goal.

We have used Microsoft 365 to prepare this article, but these methods are also applicable to Excel 2021, 2019, 2016, 2013, 2010, and 2007.

Calculate Payment in Excel

Download Practice Workbook

Calculate Payment in Excel.xlsx

What Are the Ways to Calculate Monthly Payment in Excel?

We will apply Excel’s PMT and PV functions to compute monthly payments depending on various scenarios.

Method 1 – Using a Direct Formula to Calculate Monthly Payment

First we’ll apply the conventional or direct formula to calculate the monthly payment in Excel.

Steps:

Enter the following formula in cell C9:

=(C5*C6)/(C8*(1-(1+(C6/C8))^(-C7*C8)))

Press Enter to return the loan repayment schedule in terms of monthly payments.

Method 2 – Using PMT Function to Calculate Monthly Payment

We will now use the PMT function to calculate the loan payment amount.

Enter the following formula in cell C9:

=PMT(C6/12,C7*C8,-C5,0,0)

After pressing Enter, the function will display the monthly payment after taking into account all the parameters.

Formula Explanation:

The interest rate is 12% per year. Thus, the monthly interest rate is 12% divided by 12, or 1%. Thus, the rate argument for the PMT function is 1%.

The $10,000 principal amount is the amount actually received the bank. Thus, the PV of the PMT function is 10,000.

We have three years to pay off the principal and interest. Since this is a monthly payment, the total period is 36 months (12 months * 3).

The PMT function will return a value of $332.14 in cell C9. Since we placed a minus sign (-) before the loan amount, the value is positive. The PMT function returns negative values otherwise.

Method 3 – Using PMT Function with Compounded Period to Calculate Monthly Payment

To calculate the loan payment with compounded interest using the PMT function:

Enter the following formula in cell C8:

=PMT((C5/2+1)^(1/6)-1,C7,-C6)

Press Enter to return the output.

Formula Explanation:

Here, the interest rate will be compounded semi-annually (every six months), so we divide 12% by 2 to get 6%.

The total period length will be 36 months (12 months * 3 years).

The payments are made on a monthly basis. So over the course of six months, we will pay an interest rate of 6%. This means the calculation is (1+x)6 = 1.06, where x is the monthly interest throughout the 6 months of payments. Using this equation, we can compute the value of x = 1.06(1/6) – 1 = 0.00975879.

Method 4 – Using Formulas Tab to Calculate Monthly Payment

We can access the PMT function from the Insert Function dialog box on the Formulas tab.

Steps:

Select cell C8.
Go to the Formulas tab.
Click on the Insert Function command.

The Insert Function dialog box will open.

Choose PMT.
Click OK.

Now we place the necessary function arguments in the Function Arguments dialog box.

Select cells C5, C7, and C6 for the Rate, Nper, and Pv arguments.
Click OK.

The monthly loan repayment amount is returned.

How to Calculate Monthly Mortgage Payment in Excel?

Enter the following formula in cell C10 to calculate the monthly payment on a mortgage:

=PMT((C9/12,C8*12,C7)

Press Enter to return the monthly EMI to repay the mortgage loan.

How to Calculate Monthly Credit Card Payment in Excel?

To determine the monthly payment amount, enter the following formula in cell C8:

=PMT((C5/12,C7*12,C6)

Press Enter to return the monthly credit card loan repayment.

How to Calculate Down Payment in Excel?

We will calculate the down payment in Excel using the PV function.

To compute the total loan amount paid with monthly payments, enter the following formula in cell C10:

=PV((C8/12,C7*12,-C6)

Press Enter.

The loan amount paid in monthly payments is returned.

Compute the required down payment by subtracting the loan payment from the car’s value.

How to Calculate Loan Paying Off Time in Excel?

To calculate the number of repayment periods in any month, quarterly, or semi-annually using the NPER function, refer to the overview image below.

Enter the following formula in cell C8:

=NPER(C5/12,-C7,C6)

After pressing Enter, the number of periods required to repay the loan for the given loan conditions is returned. In our example, if we pay $150 monthly, we will need 17 years to pay off the loan.

How to Calculate Monthly Payment Goal in Excel?

We use the PMT function to calculate the monthly payment goal, or how much you need to pay monthly to pay off the loan within the desired time period .

Enter the following formula in cell C11:

=PMT((C9/12,C10*12,C8)

Press Enter to return the result.

To pay off the loan within 12 years under these conditions, a payment of $586.60 monthly is needed.

Things to Remember

To maintain readability and consistency, format cells containing payment amounts as Currency, Accounting or any specific Number format.
Check that the interest rate and the number of payment periods are entered correctly. Take note of whether the rate must be divided by 12 for monthly computations or adjusted for other periods.

Frequently Asked Question

1. What is the PMT formula’s Complete form?

Several examples are provided in this article.

2. In Excel, how can I compute a basic payment?

Apply the formula “=PMT(rate, nper, pv)” to generate a basic payment. Substitute “rate” for the interest rate, “nper” for the number of payment periods, and “pv” for the present value or loan amount.

3. In Excel, is PMT negative?

The PMT function returning a negative result indicates that you are making payments to your lender. Input the Loan Amount as a negative number to have the PMT function produce a positive value.

Calculate Payment in Excel: Knowledge Hub

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!