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.

**Table of Contents**hide

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

`=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: 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**.

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

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

`=(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: 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**.

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

`=$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 **is **$8000**. Now, T**otal 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**