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**.

`=C4*C5*C6`

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.

Now, enter the following formula in cell **B13**.

`=IF(COUNT($B$12:B12)>$C$6*12,"",B12+1)`

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.

`=IF(COUNT($B$12:B12)>$C$6*12,"",B12+1)`

- 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*.

`=(C4*C5)/12`

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**.

`=IF(B13="","",$C$8)`

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**.

`=IF(B13="","",SUM(D12+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.

`=$C$7-D36`

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**