Musiha Mahfuza Mukta
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:
How to Calculate Loan Payment in Excel (4 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.
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:
- 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 (2 Ways)
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.
Conclusion
I hope you found this article helpful. Here, I have explained 4 examples of how 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.
