How to Calculate Simple Interest Loan Payments in Excel (5 Methods)

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.


Download Practice Workbook

You can download the practice workbook from here:


What Is 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.


5 Methods to Calculate Simple Interest Loan Payments in Excel

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


2. Using PMT & POWER Functions

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

Steps:

  • At 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 loan with 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 as credit. Besides, 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. Use of 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.


4. Use of 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. Employing 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.


Conclusion

I hope you found this article helpful. Here, I have explained 5 methods of how to calculate simple interest loan payments 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.

Musiha
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo