In this article, we will learn to **calculate the monthly payment on a loan in Excel**. When you take a loan for a certain period, you need to make a monthly payment depending on the interest rate. Today, we will demonstrate **2 **easy methods. We can use **the PMT function **in Excel to find the monthly payment. Also, we can use the direct formula. Using these methods, you can calculate the monthly payment for a loan quickly. So, without further delay, let’s start the discussion.

**Table of Contents**hide

## Download Practice Book

You can download the practice book from here.

## Excel PMT Function Introduction

The **PMT **function calculates payments for a loan depending on constant payments and a constant interest rate. We can use this function to determine the monthly payment for a loan. The **PMT **function has **3 **required and **2 **optional arguments. Let’s get familiar with the syntax and arguments of the function.

**Syntax**

**PMT(rate, nper, pv, [fv], [type])**

**Arguments**

** Rate (required): **It is the interest rate you will pay each period. For monthly calculation, we use the value of

**interest/month**. Suppose, you took a loan with a

**12**% annual interest. Then, the monthly interest rate will be

**(12**%

**/12)**or,

**1**%.

** Nper (required): **It is the total number of payments you need to make to repay the loan. If you take a loan for

**5**years and make monthly payments, then the total number of payments is

**5*12=60**. Because you need to pay

**12**times a year.

** Pv (required): **It is called the present value. Also, known as the principal. In our case, it is the amount of the loan.

** Fv (optional): **It is known as the future value. It is the amount of money you want to attain after the last payment. In the case of a loan, you will not have to pay anything after the last payment. So, the future value will be

**0**.

** Type (optional): **This argument indicates when your payments are due.

**0**denotes payments are due at the end of the period and

**1**denotes payments are due at the beginning of the period. In the case of a loan, payments are due at the end of a period. So, you need to type

**0**in place of this argument.

## 2 Easy Ways to Calculate Monthly Payment on a Loan in Excel

To explain the methods, we will use a dataset that contains information about the yearly and monthly interest rate, number of years, total numbers of payments for a loan, and the value of the loan. Suppose, you took a loan of $**50,000 **for **5 **years with a yearly interest rate of **12**%**. **You need to calculate the monthly payment for this loan. So, the dataset will look like the picture below.

### 1. Apply Excel PMT Function to Calculate Monthly Payment on a Loan

In the first method, we will show the process to calculate the monthly payment on a loan with the **PMT **function. You need to find the value of the required arguments and insert them inside the function to find the monthly payment. This process is simple and avoids all complexities. Let’s follow the steps below to see how we can determine the monthly payment for a loan.

**STEPS:**

- In the first place, select
**Cell C6**and type the formula below to find the monthly interest rate:

`=C5/12`

Here, **Cell C5 **contains the yearly interest rate. But we need the interest rate/month to get the monthly payment. So, we have divided **Cell C5 **by **12**.

- Secondly, press
**Enter**to see the monthly interest rate.

- Thirdly, we need to find the total number of payments.
- To do so, select
**Cell C8**and type the formula below:

`=12*C7`

In this case, **Cell C7 **contains the number of years and for monthly payments, you need to pay **12 **times a year. So, we have multiplied **Cell C5 **by **12**.

- Now, hit
**Enter**and see the total number of payments. You need to pay**60**times in**5**years.

- In the following step, select
**Cell C11**and type the formula below:

`=PMT(C6,C8,C9,0,0)`

Here, **Cell C6 **contains the value of the monthly interest rate, **Cell C8 **is the total number of payments, and **Cell C9 **is the loan amount. The fourth argument is **0 **because the future value must be **0 **for a loan. Also, the fifth argument is **0 **because the payments are due after each period.

- Finally, press
**Enter**to get the monthly payment on a loan. - From the result, we can say that we need to pay $
**1,112.22**each month for**5**years to repay the loan.

- Moreover, you can use the picture below to understand the arguments clearly.

**Read More:** **How to Calculate Loan Payment in Excel (4 Suitable Examples)**

**Similar Readings**

**How to Calculate Coupon Payment in Excel (4 Suitable Examples)****Calculate Auto Loan Payment in Excel (with Easy Steps)****How to Calculate Car Payment in Excel (with Easy Steps)**

### 2. Determine Monthly Payment on a Loan Using Simple Formula in Excel

In **Excel**, we can also use the simple mathematical formula directly to determine the monthly payment on a loan. To calculate the monthly payment with the formula, we need to have the information on the yearly interest rate (**i**), loan amount (**P**), number of years (**n**), and number of payments/year (**q**). If you take a $**50,000 **loan for **5 **years at a yearly interest rate of **12**%**, **then the dataset will look like the picture below.

The general form of the formula to calculate the monthly payment for a loan can be written as:

**M = (P*i)/(q*(1-(1+(i/q))^(-n*q)))**

Here,

**M**is the amount of monthly payment.**P**is the loan amount.**q**is the number of payments per year.**i**is the constant interest rate.

Let’s follow the steps below to see how we can determine monthly payments with the mathematical formula.

**STEPS:**

- Firstly, select
**Cell C10**and type the formula below:

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

Here, **Cell C8 **is the loan amount (**P**) which is multiplied by the yearly interest rate. **Cell C5 **contains the yearly interest rate(**i**). **Cell C6 **is the number of years (**n**) and **Cell C7 **is the number of payments/year (**q**).

- After that, press
**Enter**to see the result. - In this case, we got the same value for the monthly payment as the previous one.

**Read More:** **How to Calculate Monthly Payment with APR in Excel**

## Conclusion

In this article, we have **2 **easy methods to **Calculate Monthly Payment on a Loan in Excel**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.