Excel Simple Interest Loan Calculator with Payment Schedule

Simple Interest Loan Calculator provides one of the easiest ways to track the payment schedule of a loan and with the help of Excel, we can do this very easily. After reading this article you will be able to use the Simple Interest Loan Calculator to construct your payment schedule in Excel without any problem.

Download Practice Workbook

Arithmetic Formula to Calculate Simple Interest Loan

A Simple Interest Loan is one where we calculate the interest by multiplying the initial borrowed amount which is the Principal (p), Rate of Interest (r), and Time (n). The arithmetic formula to calculate the Simple Interest Loan is as follows:

I = p*n*r

Here,

I = Simple Interest (Total Interest to be Paid)

p= Principal Amount

n = Time elapsed

r = Rate of Interest

For example, a 5-year loan for $5000 with an annual interest of 15% will be as follows:

I = $5000 * 5 * 0.15 = $3750

Therefore, the total amount of interest that should be paid is $1500 in 5 years.

Now, to calculate the Monthly Payable Interest we can use the following formula.

Monthly Payable Interest = (p*r*)/12

For the previous example, the Monthly Payable Interest would be:

= (p*r*)/12 = ($5000*0.15)/12 = $62.5

Therefore, all of the interest would be paid at the end of year 5, if we pay $62.5 interest per month.

5 Steps to Create Simple Interest Loan Calculator with Payment Schedule

In the following data set, we have a bank loan of $30,000 taken at a 10% annual simple interest rate for 2 years. We need to construct a monthly Simple Interest Loan Calculator Payment Schedule for these conditions using an Excel formula.

Step 1: Compute the Total Interest to be Paid

To calculate the Total Interest to be Paid, we are going to use the Arithmetic Formula of Simple Interest Loan.

We can use the following formula in cell C7.

Here, cell C4 represents the cell of Principal Loan Amount, C5 refers to the cell of Interest Rate, C6 represents the cell of Loan Period in Years, and C7 denotes the cell of Monthly Payable Interest.

Step 2: Calculate Numbers of Months to Repay the Loan

The month when the loan is taken is considered as Month 0, as no interest is to be paid in this month. We need to pay interest at the end of this month. And that is our Month 1. We can calculate the months by using the following steps.

• First, enter 0 in cell B12 manually.

• We are going to use 2 functions of Excel here. They are the IF function and the COUNT function.

Now, enter the following formula in cell B13.

Here, cell B12 refers to the cell of Month 0.

💡 Formula Breakdown

• COUNT($B$12:B12) means we are going to count the cells that contain a number from cell B12 to another cell column B.
• Now, we are going to check if it is greater than the Loan Period*12 (Number of months) by the argument COUNT($B$12:B12)>$C$6*12 with a preceding IF
• If the above condition is true, it means that we have passed our Loan Period. So, if the condition is true then replace the cells with blank. And if the condition is not true that means we are within our Loan Period. So, increase the cell value by 1. The following argument does that.

• After that, drag the Fill Handle up to any amount of cells you want. But you will not find any values after the Loan Period ends. This formula automatically stops at the end of the last month of the last year of the Loan Period.

Similar Readings

• SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
• Excel Loan Calculator with Extra Payments (2 Examples)
• Mortgage Calculator with Extra Payments and Lump Sum in Excel

Step 3: Determine the Monthly Payable Interest

Now, we are going to calculate the Monthly Payable Interest by using our Arithmetic Formula of Monthly Payable Interest.

By using the following formula in cell C8 we can find our Monthly Payable Interest.

Now, we are going to insert this value up to the last month of our Loan Period by using the following steps.

• Again we are going to use the IF function here. We will use the following formula in cell C13.

Here, cell C13 refers to the cell of Monthly Payable Interest for the 1st month.

💡 Formula Breakdown

• By the formula =IF(B13=””,””,$C$8), we are going to check if the adjacent cell in column B is blank. If this condition is true that indicates that we have passed our Loan Period. So, replace the cell with a blank. If the condition is not true that means we are within the Loan Period. For this reason, replace the cells with the cell of  Monthly Payable Interest($C$8).

• Now use the AutoFill option to get the rest of the values up to Month 24.

Step 4: Calculate Cumulative Total Interest Paid

To calculate the Cumulative Total Interest Paid, we need to sum the present month’s payment with the amount of interest paid up to this month.

We have to do this up to the end of our Loan Period. So, we are going to use the IF function again. The logic for the IF function is: if the cell in column B is blank, we have passed our Loan Period. So, replace it with a blank. Else replace it with the sum of the previous 2 cells in column D.

•  We can use the below-given formula in cell C13.

Here, cell D12 and D13 represents the cell of the Total Interest Paid for the Month 0 and 1 respectively.

• Now, drag the Fill Handle up to the end of the 24th Month to obtain the rest of the data.

Congratulations! You have successfully created a Simple Interest Loan Calculator Payment Schedule in Excel.

Step 5: Check the Figures

In this step, we are going to check whether our Total Interest Paid from the Payment Schedule matches the value we got from Step 1(anchor) or not. We will also use Conditional Formatting for cell C9 here. We are going to subtract the Total Interest Paid of the 24th Month (cell C36) from the Total Interest To be Paid (cell C7). If the result is 0, it means our calculation is correct and the cell will be Green. To do this we will use the following steps.

• First, select cell C9 and then click on Conditional Formatting from the Home tab and click on Highlight Cell Rules. After that, select Equal To.

• After that, at the Equal To dialogue box, type 0 in the marked box in the following picture. Also, select your preferred formatting option. Then press OK.

• Afterward, in cell C9 we can use the following formula.

You can see that the cell is Green. This indicates our calculations from the Payment Schedule are correct.

On the other hand, if our calculations were wrong ( Total Interest to be Paid ≠ Total Paid Interest), there will be no green color in cell C9.

For instance, let our Total Interest to be paid is $8000. Now, Total Interest to be Paid – Total Paid Interest = $2000. You can see that the green color is no more available in the C9 cell. This indicates we have done an error in our calculations.

Things to Remember

• In Step 2, you need to use Absolute Cell Reference for the starting point of the COUNT function ($B$12:B12) and in cell $C$6.
• In Step 3, you need to use Absolute Cell Reference to fix the cell like this, $C$8. If you don’t do this, you will get the wrong data when you will use the AutoFill option.
• Make sure to click on cell C9 in Step 5, before selecting the Conditional Formatting feature.

Conclusion

We’ve finally reached the end of this article. I truly hope that this article has been able to assist you in creating your own Simple Interest Calculator Payment Schedule in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel you can visit our website ExcelDemy. Happy learning!

Related Articles

• Student Loan Payoff Calculator with Amortization Table in Excel
• Home Loan EMI Calculator with Reducing Balance in Excel
• Car Loan Amortization Schedule in Excel with Extra Payments
• Create Loan Amortization Schedule with Moratorium Period in Excel
• Create Home Loan Calculator in Excel Sheet with Prepayment Option
• Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option