How to Calculate Simple Interest Loan Payments in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’m going to explain how to Calculate Simple Interest Loan Payments in Excel. Here, I have described five methods of calculating Simple Interest Loan Payments in Excel.


What Is a Simple Interest Loan Payment?

Simple interest is a constant interest rate that is calculated on a loan. In the case of simple interest loan payments, you have to pay a fixed interest rate on the loan monthly.


To explain the 5 methods on how to calculate simple interest loan payments in Excel, I have used the following dataset. Which contains two columns with Payment Details.

How to Calculate Simple Interest Loan Payments in Excel


1. Applying PMT Function to Calculate Simple Interest Loan Payments in Excel

We can apply the PMT function to calculate simple interest loan payments in Excel. PMT function is a built-in function in Excel.

Steps:

  • Firstly, select a different cell C11 where you want to calculate the Monthly Payment.
  • Secondly, use the corresponding formula in the C11 cell.
=PMT(C7,C9,-C5)

Formula Breakdown

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

  • In this function, C7 denotes the monthly interest rate of 0.42%.
  •  C9 denotes the total payment period in terms of the month which is 24.
  • C5 denotes the present value which is $4000.

  • At this time, press ENTER to see the amount of the Monthly Payment.

How to Calculate Simple Interest Loan Payments in Excel

Next, I will calculate the Total Payment.

  • Now, type the following formula in cell C12.
=C11*C9

Here, in this formula, I multiplied the Monthly Payment by the total Months.

  • Now, press ENTER to see the amount of the Total Payment.

As a result, you will get the Total Payment of the Loan amount.

How to Calculate Simple Interest Loan Payments in Excel

Read More: Convert Compound Interest to Simple Interest in Excel


2. Using PMT & POWER Functions for Determining Simple Interest Loan Payments

I can use PMT and POWER functions to calculate the simple interest loan payments. The steps are given below-

Steps:

  • First, select a different cell C11 where you want to calculate the Monthly Payment.
  • Then, use the corresponding formula in the C11 cell.
=PMT(POWER((1+C6/C7),(C7/12))-1,C9,C5)

How to Calculate Simple Interest Loan Payments in Excel

 Formula Breakdown

  • POWER((1+C6/C7),(C7/12))—-> The POWER function contains a variable and raises it to fixed power.
  • POWER((1+0.000136986301369863),30.4166666666667)—-> becomes
  • POWER(1.00013698630137,30.4166666666667)—-> turns into
    • Output: 1.0041750727376
  • PMT(POWER((1+C6/C7),(C7/12))-1,C9,C5)—-> The PMT function gives the payments on the loan with a constant interest rate.
  • PMT(1.0041750727376-1,C9,C5)—-> becomes
  • PMT(0.00417507273760132,24,4000)
    • Output: ($175.50)
    • Explanation: The output denotes how much payment I have to do monthly. So, I have to pay $175.50 per month.

  • At this time, press ENTER to see the amount of the Monthly Payment.

If I use the loan as negative then the result will be in black color. Which means the amount is credited. Besides, the red color denotes the amount as debit.

How to Calculate Simple Interest Loan Payments in Excel

Furthermore, by multiplying the total months with the monthly payment, I will find out the total payment.

  • Now, type the following formula in cell C12.
=C11*C9
  • Finally, press ENTER to see the amount of the Total Payment.


3. Using Generic Formula to Calculate Simple Interest Loan Payments in Excel

You can use the generic formula to calculate the total loan payment with simple interest and constant monthly payments.

Steps:

  • Firstly, select a different cell C10 where you want to calculate the Total Payment.
  • Secondly, use the corresponding formula in the C10 cell.
=C5*(1+C6*C7)

How to Calculate Simple Interest Loan Payments in Excel

 Formula Breakdown

  • In this formula, I have multiplied Annual Interest Rate, C6 with Years, C7.
  •  After that, I have added 1.
  •  Furthermore, I have multiplied the whole result with the loan, C5.
  • At this time, press ENTER to see the amount of the Total Payment.

How to Calculate Simple Interest Loan Payments in Excel

Subsequently, I will calculate the monthly payment by dividing the total payment by the total months.

  • Firstly, select a different cell C11.
  • Secondly, write the corresponding formula in the C11 cell.
=C10/C8
  • At this time, press ENTER to see the amount of the Monthly Payment.

Read More: How to Calculate Simple Interest on Reducing Balance in Excel


4. Inserting PV Function to Calculate Total Affordable Loan

You can use the PV function to calculate the total affordable loan.

Steps:

  • Firstly, select a different cell C11 where you want to calculate the Affordable Loan.
  • Secondly, use the corresponding formula in the C11 cell.
=PV(C7,C9,C5)

Formula Breakdown

In this formula, the PV function returns the present value of an investment.

  • C7 denotes rate as the monthly interest rate.
  • C9 denotes NPER as the total period of payment.
  • C5 denotes PMT as the affordable monthly payment.

How to Calculate Simple Interest Loan Payments in Excel

  • Now, press the ENTER to get the affordable loan.


5. Using the NPER Function to Calculate Required Payment Period

You can employ the NPER function to calculate the required time period for paying the loan.

Steps:

  • Firstly, I select the C12 cell for calculating the required period.
  • Secondly, use the following formula in the C12 cell.
=NPER(C7,-C10,C5,,1)

Formula Breakdown

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

  • C7 denotes the monthly interest rate as rate.
  • C10 denotes payment made in each period as PMT.
  • C5 denotes the total loan as PV.
  • 1 denotes the beginning of the period.

  • Finally, press ENTER to get the result.

How to Calculate Simple Interest Loan Payments in Excel


Practice Section

Now, you can do practice 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 5 methods of how to calculate simple interest loan payments in Excel.

Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Simple Interest Formula in ExcelExcel for Finance | Learn Excel

What is ExcelDemy?

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

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo