ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Calculate Simple Interest in Excel

Updated on April 26, 2016

Making excel spreadsheet

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.

When do we pay more interest if the calculation takes place several times a year?

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.

What is the maximum rate at which they borrow money?

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Howard Schneider 2 years ago from Parsippany, New Jersey

      Great information and an excellent Hub, Usemybee.

    • Usemybee profile image
      Author

      Jozef 2 years ago from Slovenia

      Thank you very much for your comments, HSchneider.

    Click to Rate This Article