Simple Interest Loan Calculator Using Formula in Excel (2 Examples)

Simple interest denotes the amount estimated from the principal amount of any loan taken from a bank or the initial contribution of any bank’s saving account. The calculation process of evaluating simple interest is easy. Using Microsoft Excel, we can do this task more efficiently. In this context, we will show you three suitable examples of building a simple interest loan calculator using Excel formula. If you are curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


What Is Simple Interest?

Simple interest denotes the amount estimated from the principal amount of any loan taken from a bank or the initial contribution of any bank’s saving account. We can also say the amount of interest that gains directly from the principal amount. The mathematical expression of simple interest is:

I = P × R × n

Where,

  • I = The amount of gained interest from the principal amount.
  • P = Principal amount
  • R = Annual rate of interest
  • n = Number of period

Let’s take a look at a simple example of simple interest.

A person X deposits $5,000 in his bank account. The bank will provide 5% interest on this capital annually. Now, if he keeps his money in that account for 2 years the amount of simple interest will be,

Simple interest, I = 5000 × 0.05 × 2

        = 500


2 Suitable Examples to Build Simple Interest Loan Calculator with Excel Formula

In this article, we are going to show you two distinct approaches to building a simple interest loan calculator using Excel formula. First, we will estimate the amount of simple interest by a conventional formula, and second, by using Excel’s built-in functions.


1. Applying Conventional Formula

Here, we are going to demonstrate two examples. In the first example, we will perform the process to get the value of simple interest using the main formula. Then, in the second example, we will display how to use the main formula when the time is in months instead of years.


1.1 Simple Interest for Year

In the following example, we will estimate the value of simple interest. We consider that a person Mr. X invests in a bank account of $15,000 for 5 years. The bank authority will provide him with an annual rate of 5% interest. The steps to complete the procedure are given below.

📌 Steps:

  • At first, input all the required particulars in the range of cells D5:D7.

  • After that, select cell D9 and write down the following formula into the cell.

=D5*D6*D7

  • Press Enter.

Generating Simple Interest Calculator using Excel Formula for Year

  • You will get the value of simple interest based on the principal amount.
  • In addition, we will also calculate the total amount after the time period.
  • For that, write down the following formula into cell D10.

=D5+D9

  • Again, press Enter.

Generating Simple Interest Calculator using Excel Formula for Year

  • You will get the value of grand total and our task finished.

Generating Simple Interest Calculator using Excel Formula for Year

Thus, we can say that our formula worked perfectly, and we are able to build a simple interest loan calculator using the Excel formula.


1.2 Simple Interest for Month

In this example, we will evaluate the value of simple interest for some month periods. We are considering that person Mr. X invests in a bank account of $15,000 for 20 months. The bank authority will provide him with an annual rate of 5% interest. The steps of this process are given as follows:

📌 Steps:

  • First of all, input all the required particulars in the range of cells D5:D7.

  • Now, convert the month period into the year.
  • For that, write down the following formula into cell D8.

=D7/12

  • Press Enter.

Assemble Simple Interest Calculator using Excel Formula for Month

  • Then, select cell D10 and write down the following formula into the cell.

=D5*D6*D8

  • Again, press Enter.

Assemble Simple Interest Calculator using Excel Formula for Month

  • Within a second, you will get the value of simple interest based on the principal amount.
  • Moreover, we will also calculate the total amount after the time period.
  • For that, write down the following formula into cell D11.

=D5+D10

  • Press Enter for the last time.

Assemble Simple Interest Calculator using Excel Formula for Month

  • You will get the value of grand total and our task is completed.

Assemble Simple Interest Calculator using Excel Formula for Month

Finally, we can say that our formula worked successfully, and we are able to build a simple interest loan calculator using the Excel formula.


2. Using PMT, IPMT and PPMT Functions

In this method, we are going to use the PMT, IPMT, and PPMT functions to build a simple interest loan calculator. Our people Mr. X takes a loan of $15,000 from a bank for a year. He has to pay an annual rate of 12% interest on the loan. The procedure of this method is given below step by step:

📌 Steps:

  • First, input all the required particulars in the range of cells D7:D9.

  • Now, select cell D10 and write down the following formula in the cell to get the value of the number of installment periods.

=D9*12

  • Press Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • After that, to get the loan payment sheet, create a dataset as shown in the image.
  • Then, write down the total number of periods from 1-12.

  • After that, in cell G6, write down the following cell reference.

=D7

  • Again, press Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • Now, write down the following formula in cell H6 to get the value of EMI.

=PMT($D$8/12,$D$10,$D$7)

  • Press Enter to get the value.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • Afterward, in cell I6, write down the following formula to get the value of interest.

=IPMT($D$8/12,F6,$D$10,$D$7)

  • Similarly, press Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • Then, to get the value of the principal amount, we will use the PPMT function. Using this function, write down the following formula in cell J6.

=PPMT($D$8/12,F6,$D$10,$D$7)

  • Press Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • At last, write down the following formula to get the value of the closing balance in cell K6.

=G6+J6

  • Press Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • Now, the closing balance of period 1 will be the opening balance of period 2. So, to get the complete value, write down the following cell reference in cell G7.

=K6

  • Press the Enter.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • After that, select the range of cells H6:K6 and drag the Fill Handle icon to copy the formula up to row 7.

  • Then, select the range cells G7:K7 and double-click on the Fill Handle icon to copy the formula up to period 12.
  • At last, you will see the closing balance of period 12 is 0, which means that we have successfully cleared our loan.

  • Moreover, we can also calculate the total amount of EMI, interest, and principal amount to check the accuracy of our payment sheet.
  • For that, we will use the SUM function.
  • Now, write down the following formula in cell H18 to get the total of EMI.

=SUM(H6:H17)

  • Press Enter for the last time.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

  • Then, drag the Fill Handle icon to your right to copy the formula up to cell J18.
  • Our simple interest loan calculator is ready to use.

Simple Interest Loan Calculator Using PMT, IPMT, and PPMT Functions

In the end, we can say that all of our functions and formulas worked perfectly, and we are able to build a simple interest loan calculator using the Excel formula.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to build a simple interest loan calculator using Excel formula. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo