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.

**Download Practice Workbook**

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

- Calculate Loan Payment
- 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**