Simple Interest Loan Calculator in Excel with Payment Schedule

The simple interest loan calculator provides one of the easiest ways to track the payment schedule of a loan. And we can do this very easily with the help of Excel. 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.

Arithmetic Formula to Calculate Simple Interest Loan

The Simple Interest Loan is the interest calculated 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.

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: Calculating the Total Interest to be Paid in Excel

To calculate the Total Interest to be paid, we are going to use the arithmetic formula of a 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: Determining the Number 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 and COUNT functions.

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 function.
• 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.

Step 3: Computing 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: Calculating 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 formula in cell C13.

Here, cells D12 and D13 represent the Total Interest Paid for the months 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: Checking the Figures in the Simple Interest Loan Calculator in Excel

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.

• Then, in cell C9 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 be $8000. Now, Total Interest to be Paid – Total Paid Interest = $2000. You can see that the green color is no more available in cell C9. This indicates we have made 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 use the AutoFill option.
• Make sure to click on cell C9 in Step 5, before selecting the Conditional Formatting feature.

Download Practice Workbook

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. Goodbye!

Related Articles

• Car Loan Calculator in Excel Sheet
• Create Home Loan Calculator in Excel Sheet with Prepayment Option
• How to Create Loan Calculator with Extra Payments in Excel

<< Go Back to Interest Calculator | Finance Template | Excel Templates