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

For the present situation of the world, you should know how to calculate Loan Payment. Hence, in this article, I’m going to explain how to calculate Loan Payment in Excel.


Download Practice Workbook

You can download the practice workbook from here:


4 Examples to Calculate Loan Payment in Excel

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.

How to Calculate Loan Payment 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.

How to Calculate Loan Payment in Excel

Read More: How to Calculate Auto Loan Payment in Excel (with Easy Steps)


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:

  • Firstly, select a different cell C10 where you want to keep the Affordable Loan.
  • Secondly, use the formula given below in the C10 cell.
=PV(C7,C8*12,-C5)
  • Subsequently, 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.

  • Firstly, C7 denotes rate as the monthly interest rate.
  • Secondly, 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:

  • Firstly, I select the C10 cell for calculating the required period.
  • Secondly, use the following formula in the C10 cell.
=NPER(C7,-C8,C5,,0)
  • Finally, press ENTER to get the Required Period.

How to Calculate Loan Payment in Excel

Formula Breakdown

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

  • Firstly, C7 denotes the monthly interest rate as Rate.
  • Secondly, C8 denotes payment made in each period as PMT. Here, the Minus sign denotes monthly payment as debit.
  • Thirdly, C5 denotes the total loan as present value (PV).
  • Fourthly, 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.

  • Firstly, you have to select the cell C10.
  • Secondly, from the Home tab >> you should go to the Number command.
  • Thirdly, press the Decrease Decimal feature until the fractional number converts to the whole number.

Lastly, you will see the following output.

How to Calculate Loan Payment in Excel

Read More: How to Calculate Monthly Mortgage Payment in Excel (2 Ways)


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

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

Steps:

  • Firstly, select a different cell C10 where you want to keep the Total Payment.
  • Secondly, 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.

  • Subsequently, 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.

How to Calculate Loan Payment in Excel

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


Calculating Interest Rate of Loan Payment in Excel

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.
  • Secondly, 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 method by yourself.

How to Calculate Loan Payment in Excel


Conclusion

I hope you found this article helpful. Here, I have explained 4 examples to calculate Loan Payment in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo