# How to Calculate Simple Interest in Excel

Updated on April 23, 2019 Please consider of line and columns, that you will be being able to copy formulae.

Enter the interest rate, as well as all the dates and amounts. The beginning of period represents first date; amount of balance is put on this next to him. Enter dates, when you had the inflow or outflow. Last date is the date on which the period ends for which you counting interest. The amount of the latter date is zero; it does not affect the calculation of interest

## Calculation of the number of days

As a rule, when determining the beginning and end of the interest period is considered the first day of the beginning of business on the last day did not.

As a rule, the statement of interest is considered the actual number of days in your calendar, taking into account the actual number of days in a year (365/366).

The contract must be precisely defined the mode of counting the days. The bank must disclose to the customer the implications of choosing each different method of counting days, as described in the first two paragraphs of this section.

## Calculate basis

The basis for the calculation of simple (proportional) interest is the sum of the opening balance and all inflows and outflows. In cell D7 enter the formula =SUM (B\$6: B6). \$ in the first cell, so that the row when copying will not be updated (will always B6). Cell is copied in all below it.

## Calculate simple interest

Copy the formula =D7*((C\$3/365/100) * C7) in cell E7, and from there copy it all below it. We must pay attention; that at leap year instead of 365 uses 366. If period stretches after more years, we distribute it at the end of year.

## Calculate total interest

All interest must add. This is done with the function =SUM( E7: E14). You need to adjust the formula according to how many periods it was in your case.

See results

## The difference between simple and compound method

The graph shows the difference between simple and compound method of interest calculation. To make the difference more visible, we used a very high interest rate. In a low interest rate, the difference is small, but not unimportant.

See results

## The Simple Interest Formula

Date
Amount +/-
Days
Simple Basis
Simple Interest
Compound Basis
Compound Interest
Difference
31/5/2013
820

5/6/2013
-315
5
820.00
0.90
820.00
0.86
0.03
9/6/2013
-85
4
505.00
0.44
505.86
0.43
0.02
10/6/2013
-145
1
420.00
0.09
421.29
0.09
0.00
13/6/2013
1520
3
275.00
0.18
276.38
0.17
0.01
20/6/2013
-420
7
1795.00
2.75
1796.56
2.65
0.1
24/6/2013
-171
4
1375.00
1.21
1379.21
1.16
0.0
29/6/2013
-351
5
1204.00
1.32
1209.37
1.28
0.0
30/6/2013
0
1
853.00
0.19
859.65
0.18
0.01
Total

7.08

6.83
0.25

## Related

0 of 8192 characters used
• AUTHOR

Jozef

6 years ago from Slovenia

• 