# How to Calculate Loan Payment in Excel (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF