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**

**Table of Contents**Expand

## 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

- Calculate Loan Payment
- Calculate Monthly Payment on a Loan
- Calculate Monthly Payment with APR
- Annual Loan Payment Calculator
- Calculate Auto Loan Payment
- Calculate a Lease Payment
- Calculate Down Payment Using VLOOKUP
- Calculate Coupon Payment
- Calculate Monthly Mortgage Payment
- Calculate Car Payment
- Calculate Balloon Payment
- Line of Credit Payment Calculator
- HELOC Payment Calculator
- Snowball Payment Calculator
- Progressive Payment Calculator
- Rent Payment Excel spreadsheet
- Salary Payment Voucher Format
- Cash Payment Voucher Format
- Petty Cash Payment Voucher Format
- Proforma Invoice for Advance Payment
- Interim Payment Certificate Format

**<< Go Back to Excel for Finance | Learn Excel**