How to Calculate Payment in Excel?

In this article, you will learn how to

-Apply direct formula to calculate monthly payment

-Use PMT function to calculate monthly payment

-Use PMT function with a compound period to calculate monthly payment

-Use 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 the goal monthly payments.

We have used Microsoft 365 to prepare this article. But these methods are applicable to Excel 2021, 2019, 2016, 2013, 2010, and 2007.
We will calculate payments in Excel for business and financial purposes.

Download Practice Workbook

Calculate Payment in Excel.xlsx

What Are the Ways to Calculate Monthly Payment in Excel?

This article will demonstrate to you how to calculate monthly loan payments in Excel including mortgage loan payments, credit card loan payments, and down payments. We will also cover how to compute down payments, the goal monthly payments, and loan payoff time. We will apply Excel’s PMT function and PV function to compute monthly payments depending on various scenarios.

1. Applying Direct Formula to Calculate Monthly Payment

Now, I will apply the conventional or direct formula to calculate the monthly payment in Excel.

Write down the following formula in the C9 cell.

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

Press Enter and see the loan repayment schedule as monthly payments.

2. Utilizing PMT Function to Calculate Monthly Payment

We will use the PMT function to calculate the loan payment in Excel. To get a better idea about the PMT function, you can follow the overview image below.

Apply the following formula of the PMT function 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.
Here, 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 what you received out of the bank. Thus, the PV of the PMT function is 10,000.
You will have three years to pay off the principal and interest. Given that this is a monthly payment, your total period length will be 36 months or three years multiplied by twelve.
The PMT function will finally display a value of $332.14 in cell C9. Since I put a minus sign (-ve) before the loan amount, the value is positive. The PMT function returns negative values otherwise.

3. Using PMT Function with Compounded Period to Calculate Monthly Payment

We will compute the loan payment using the PMT function with compounded period in Excel.

Insert the following formula in cell C8 to calculate the monthly payment.

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

Press Enter and you will get the following output.
Here, the interest rate will be compounded semi-annually (every six months), So, divide 12% by 2 to get 6%.
The total period length will be 36 months or three years multiplied by twelve.
The payments are then made on a monthly basis. So, over the course of six months, you will pay an interest rate of 6%. If you do the calculations, it will be (1+x)6 = 1.06, where x is your monthly interest throughout the 6 months of payments. So, using this equation, we can easily compute the value of x: x = 1.06(1/6) – 1 = 0.00975879. As a result, the value of x is 0.00975879.

4. Using Formulas Tab to Calculate Monthly Payment

Use the PMT function from the Insert Function dialog box on the Formulas tab to calculate the loan payment.

Firstly, select the C8 cell.
Then, go to the Formulas tab.
Now, click on the Insert Function command.

Here, the Insert Function dialog box will open. Choose PMT. Click OK.

Now, we will put the necessary function arguments in the Function Arguments dialog box. Select the C5, C7, and C6 cells for Rate, Nper, and Pv arguments. Click OK.

Finally, you will see the monthly payment in the below image for repaying the loan.

How to Calculate Monthly Mortgage Payment in Excel?

To compute the monthly mortgage payment, apply the PMT function.

Insert the following formula in cell C10 to calculate the monthly payment for the given data of your mortgage.

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

When you press the Enter key, you will get the monthly EMI to repay the mortgage loan.

How to Calculate Monthly Credit Card Payment in Excel?

If you want to calculate the credit card payment in Excel, you can use the PMT function.

To determine the payment on a monthly basis, insert the following formula in cell C8.

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

Hit Enter, and you will get the monthly payment for paying off the credit card loan.

How to Calculate Down Payment in Excel?

Here, 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. Then, you will see the loan amount paid by monthly payments.

Now, you can get your required down payment by subtracting the loan payment from the car’s value.

How to Calculate Loan Paying Off Time in Excel?

Now, I will calculate the number of periods in Excel by using the NPER function. To calculate the number of periods in any month, quarterly, or semi-annually, you can follow the overview image below using the NPER function. Click on the image below to get a better view.

Insert the following formula in cell C8 to calculate the number of payment periods.

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

After pressing the Enter key, you will see how much time you need to repay the loan for the given loan conditions. That is if you pay $150 monthly, then you will need 17 years to pay off the loan under these conditions here.

How to Calculate Goal Monthly Payment in Excel?

Here, we will calculate the goal monthly payment in Excel by using the PMT function.

Type the following formula in cell C11 to calculate the monthly payment if you want to know how much you need to pay monthly to pay off the loan within your desired time period.

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

Hit Enter, and you will get the result. That is if you want to pay off your loan within 12 years under these conditions, you need to pay $586.60 monthly here.

Things to Remember

To maintain readability and consistency, format cells containing payment amounts as Currency or 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 times.

Frequently Asked Question

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

Payment is indicated by the PMT full form in Excel. An extremely useful function for calculating payments in finance and investment categories. You will get several examples of this function in this article.

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

In Excel, 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. The formula will calculate your regular payment amount.

3. In Excel, is PMT negative?

The reason that the Excel PMT function gives a negative result indicates that you are making payments to your lender. You can also input the Loan Amount as a negative number if you prefer to have the PMT function produce a positive value.

Conclusion

In this article, we have shown simple ways to calculate payments in Excel and demonstrated different types of loan payments by giving multiple examples with proper explanations. This article will teach you how to figure up monthly loan payments in Excel, including mortgage loan payments, credit card loan payments, and down payments.

This post will also show you how to compute down payments, the goal monthly payments, and loan payoff time. We will use Excel’s PMT function and PV function to compute monthly payments depending on various scenarios You can browse our website and check our knowledge hub section for more useful and informative articles about how to calculate payments in Excel.

Calculate Payment in Excel: Knowledge Hub

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!