How to Calculate Simple Interest in Excel
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.
Mortgage Calculator with Extra Payments
- Mortgage Calculator with Extra Payments - Mortgage Calculator
Try different options and combinations of regular or non-regular extra payments and find out how and when you can pay off your mortgage.
When do we pay more interest if the calculation takes place several times a year?
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?
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
|
