< Go Back

Calculate simple interest

There is a formula in Excel which calculates simple interest by multiplying the principal, the rate, and the term. Consider the example demonstrated below in which the formula in C5 is =C2*C3*C4

In order to do a simple interest calculation in Excel using the COUNTA function, follow the procedure below:

  • Click on Formulas on the menu bar in Excel.
  • Next, click on More Functions and point the cursor on Statistical.
  • Scroll down the list that displays and click on COUNTA.

The dialogue box will display as shown below.

Figure 1. How to access COUNTA formula in Excel

Example 1

  • The value 1 represents the span of cells to which you want to apply the formula. In the example below, it is C2 to C4. Type C2:C4.
  • Click on OK.

It will show you the result displayed in the screenshot below:

Figure 2. The result displayed by COUNTA

Interpretation of the Result

What is displayed above shows that for a given amount of money (principal), say $5000, invested at the interest rate of 5 percent per year for 15 years, using the COUNTA function to calculate the interest generated, that is, =C2*C3*C4 the answer is $3,750. It is possible to verify this formula by manually doing the calculation as shown below:

Interest = Amount X Rate X Term

= 5000X0.05X15

= 3,750.

Alternatively, you can still calculate the simple interest by simply typing the formula above into the cell on the right of the row you are interested in. Hit the enter key when you finish typing and the result will show.

The General Formula 

The general formula for calculating simple interest in Excel is shown below:

Interest = Principal*Rate*Term

This means that you have to multiply the principal by the rate and by the term.

In the example demonstrated above, the amount of $5000 is invested at the rate of 5% per annum for a period of 15 years. The formula imputed into C5 is

=C2*C3*C4

Example 2

Calculate the simple interest on $3000 invested at the rate of 10% per annum for 2 years using Excel.

Figure 3. Simple interest on $3000 invested at the rate of 10% per annum for 2 years

The same principle applies. In this example, the formula in C5 is

=C2*C3*C4

It can also be verified by doing the calculation physically as shown below:

Interest = Amount X Rate X Years

= 3000 X 10 X 2

= 600.

Example 3

Calculate the simple interest on $500 invested at the rate of 6% per annum for 10 years using Excel.

Figure 4. Simple interest on $500 invested at the rate of 6% per annum for 10 years

In the example above, the formula in C5 is

=C2*C3*C4

However, because the values are different, the interest calculated is different also. This too can also be verified by doing the calculation physically as shown below:

Interest = Amount X Rate X Years

= 500 X 6 X 10

= 300.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar