Hence, in this article, I’m going to explain how to calculate Loan Payment in Excel.

## How to Calculate Loan Payment in Excel: 4 Suitable Examples

Here, I will explain **4 **examples of how to calculate Loan Payment in Excel. For your better understanding, I am going to use the following dataset. Which contains **two **columns with **Payment Details**.

**Read More:** How to Calculate Monthly Payment on a Loan in Excel

### 1. Using PMT Function to Calculate Loan Payment in Excel

You can use **the PMT function** to calculate **Loan Payment** in Excel. Here, the **PMT** function is a built-in function in Excel for calculating payment per period.

**Steps:**

- At first, select a different cell
**C10**where you want to keep the**Monthly Payment**. - Then, you should use the following formula in the
**C10**cell.

`=PMT(C7,C8*12,-C5)`

**Formula Breakdown**

Here, I have used the** PMT** function which calculates the monthly or annual payment based on a loan with a constant interest rate and regular payment.

- In this function,
**C7**denotes the monthly interest rate of**0.58%**. - Then,
**C8**denotes the total payment period in terms of the year which is**5**. Thus, I have multiplied by**12**to calculate the monthly payment. - And,
**C5**denotes the present value which is**$40,000**. Here, the**Minus**sign denotes**Loan**as debit.

- Now, press
**ENTER**to get the**Monthly Payment**.

At this time, you can see the amount of the **Monthly Payment**.

**Read More:** How to Calculate Auto Loan Payment in Excel

### 2. Use of PV Function to Calculate Total Affordable Loan

You can use **the PV function** to calculate the total **affordable Loan**. In addition, you should take **Monthly Payment** as given data, and then you will get how many loans you can afford for a certain situation. The steps are given below.

**Steps:**

- Select a different cell
**C10**where you want to keep the**Affordable Loan**. - Use the formula given below in the
**C10**cell.

`=PV(C7,C8*12,-C5)`

- Press
**ENTER**to get the**Affordable Loan**.

Finally, you will get the amount of the **Affordable Loan**.

**Formula Breakdown**

In this formula, the **PV **function returns the present affordable amount of a Loan.

**C7**denotes**rate**as the monthly interest rate.**C8**denotes the total payment period in terms of the year which is**5**. Thus, I have multiplied by**12**for the monthly payments.- Finally,
**C5**denotes**PMT**as the affordable monthly payment. Here, the**Minus**sign denotes monthly payment as debit.

### 3. Employing NPER Function to Calculate Required Payment Period

You can employ **the NPER function** to calculate the required time period for paying the **Loan. **The steps are given below.

**Steps:**

- I select the
**C10**cell for calculating the required period. - Use the following formula in the
**C10**cell.

`=NPER(C7,-C8,C5,,0)`

- Finally, press
**ENTER**to get the**Required Period**.

**Formula Breakdown**

In this formula, the **NPER** function gives the required payment period as months.

**C7**denotes the monthly interest rate as**Rate**.**C8**denotes payment made in each period as**PMT**. Here, the**Minus**sign denotes monthly payment as debit.**C5**denotes the total loan as**present value****(PV)**.- As the
**Future Value**is unknown so**FV**will be**Blank**. - Finally,
**0**denotes the the**type**as ending of the period.

Now, you can continue the following steps to make the period as a whole number.

- You have to select cell
**C10**. - From the
**Home**tab >> you should go to the**Number**command. - Press the
**Decrease Decimal**feature until the fractional number converts to the whole number.

Lastly, you will see the following output.

**Read More:** How to Calculate Monthly Mortgage Payment in Excel

### 4. Use of Generic Formula to Calculate Loan Payment in Excel

Do you want to know how to calculate loan payment in Excel with a simple interest rate?

You can employ a **generic** formula for the calculation of the total **Loan Payment** with a simple interest rate.

**Steps:**

- Select a different cell
**C10**where you want to keep the**Total Payment**. - Use the formula given below in the
**C10**cell.

`=C5*(1+C7*(C8*12))`

**Formula Breakdown**

- In this formula, I have converted years to months by multiplying
**12**with**C8 cell value**.**Output: 60.**

- Then, I multiplied the Monthly Interest Rate,
**C6**with**60.****Output:0.35.**

- After that, I have added 1.
**Output: 1.35.**

- Furthermore, I have multiplied the whole result with the Loan,
**C5**.**Output: $54,000.**

- I will use the following formula in
**C11**cell.

`=C10/(C8*12)`

Here, I have calculated the monthly payment by dividing the total payment by the total months.

- Now, press
**ENTER**to get the result.

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

## Calculating Interest Rate of Loan Payment in Excel

We know how to calculate loan payment in Excel. So now you can use **the RATE function** to calculate the **Interest Rate** of **Loan Payment** in Excel. The steps are given below.

**Steps:**

- Firstly, select a different cell
**C8**where you want to keep the**Interest Rate**. - Use the following formula in the
**C8**cell.

`=RATE(C7*12,-C6,C5,,0)`

- Finally, press
**ENTER**to get the**Interest Rate**.

**Formula Breakdown**

Here, the **RATE** function will return the** monthly rate **in percentage of **Loan Payment**.

- Now,
**C7**denotes the**NPER**as the total payment period in terms of the year which is**5**. Thus, I have multiplied it by**12**for the monthly payment system. - Then,
**C6**denotes payment made in each period as**PMT**. Here, the**Minus**sign denotes monthly payment as debit. - After that,
**C5**denotes the**Loan**as**present value****(PV)**. - Then, as the
**Future Value**is unknown so**FV**will be**Blank**. - Finally,
**0**denotes the**type**as the ending of the period.

## 💬 Things to Remember

- The
**four**examples are for**four**different given information. So, you should try to choose the method to do the calculation of**Loan Payment**according to your information.

## Practice Section

Now, you can practice the explained methods on how to calculate loan payment in Excel by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained **4 **examples of how to calculate **Loan Payment** in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

**Related Articles**

- How to Calculate Car Payment in Excel
- How to Calculate Coupon Payment in Excel
- How to Calculate a Lease Payment in Excel
- How to Calculate Down Payment in Excel Using VLOOKUP
- How to Calculate Balloon Payment in Excel

**<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel**