Related Tables
meetings, recur_on_day, recurrence_detail - two entries get created per savings offering, 1. for period of interest calculation 2. freq of interest posting
prd_offering - one entry for every product offering creation.
prd_offering_meeting - two entries which maps a savings product offering to two meeting created in "meeting" table.
savings_offerings - contains the savings product offering specific information
account - contains core account information for savings account
savings_account - contains savings specific information
savings_performance - contains details of total deposits, total withdrawals, total_interest, missed_deposits
savings_activity_details - contains details about deposit, withdrawn, interest posted and adjustments (the entries from this table are shown on the UI "View Savings Activity Details")
account_payment -
account_trxn -
savings_trxn_detail -
savings_schedule -
NOTES: About editing Savings products
- On every edit operation new entries gets created in meeting tables, those should get updated not created again.
- Start Date (Start Date for and Active product can not be edited so it should be read only like GL Code)
- "View Change logs" seems to be working correctly for all the editable fields in "Edit Savings Product Offering"
Savings Matrix
- Group Model (with Individual Tracking/Accountability): Center and Group (per individual mode)
- Group Model (with no Individual Tracking/Accountability): Group (Complete Group mode)
- Individual Model: Client
|
Center |
Group (Per Individual) |
Client |
Group (Complete) |
---|---|---|---|---|
Voluntary |
|
|
|
|
Mandatory |
|
b |
c |
d |
Interest Calculation and Posting
Introduction
When creating a SavingsProduct, the user can define how interest calucation and posting will happen.
Mifos out-of-the-box currently supports two types of interest calculation; Average Balance and Minimum Balance.
The user may also choose from two types of time period for interest calculation; Days and Months
For interest posting, only Months are supported.
Interest Calculation
At present interest calculation is carried out by an overnight batch job (SavingsIntCalcTask). It determines when to run based on the next interest calculation date value stored in savings_account -> next_int_calc_date datable table/column.
When a savings account goes active for the first time, an initial next calculation date is determine based on the start of the fiscal year which is fixed at January 1st and the interest calculation time period chosen in the savings account product definition. For interest calculation, this can be Daily (e.g. every x days) or Monthly at the end of the month (e.g. every x months on last day of month).
So interest is only calculated for any transactions that occur within a given 'interest calculation period'. The interest calculated for a given period is stored (savings_account -> int_to_be_posted) until the next interest posting date is reached whereby the sum of all calculated interest is posted to the savings account.
Interest Calculation Dates in Months
Example:
Interest calculation is determined every 3 months so based on starting from Fiscal start of year, the valid interest calculation dates are:
Jan-01 to Mar-31
Apr-01 to Jun-30
July-01 to Sep-30
Oct-01 to Dec-31
Interest Calculation Dates in Days
Interest calculation is determined every 28 days (4 weeks) so based on starting from Fiscal start of year, the valid interest calculation dates are:
Fri Jan-01 to Fri Jan 29
Sat Jan-30 to Fri Feb 26
..
..
Interest Posting
Interest posting always occurs on a monthly basis on the last day of the month.
When interest is due for posting, its takes the value from (savings_account -> int_to_be_posted) and posts it the date of last day of month.
Interest posting is handled overnight by batch job (SavingsIntPostingTask).
Interest Posting Dates in Months
Interest calculation is determined every 1 months so based on starting from Fiscal start of year, the valid interest posting dates are:
Jan-01 to Jan-31
Feb-01 to Feb-28/29
Mar-01 to Mar-31
..
..
Dec-01 to Dec-31
Interest Calculation Formula
Interest Calculation Formula |
Balance |
Table Used |
---|---|---|
A = P(1+r/n) |
Minimum Balance |
SAVINGS_TRXN_DETAIL |
A = P(1+r/n) |
Average Balance |
SAVINGS_TRXN_DETAIL |
r = R/100 x D/F
P = AvB/D or MB/D
I = P(r) only if P is greater than or equal to minimum balance required for interest calculation otherwise the interest (I) will be zero.
AvB = Total Average Balance for duration D
MB = Minimum Balance for duration D
D = Duration/Interval (Days) for which the interest is being calculated
F = Number of Days in Financial Year
P = Principal amount (initial amount) (Average/Minimum Balance)
R = Annual rate of interest (as a decimal) (defined during savings product creation)
r = Effective interest rate for the duration D
I = Interest Amount
Interest Calculation Examples
The purpose of these interest caluclation example is to describe by example how savings work in mifos. From reading through these, you should be able to understand
- How and when dates for interest calucation and posting are determined
- How the Average Balance algorithm works
- How the Minimum Balance algorithm works
- How and when interest is calculated for a given Interest Calculation Period
- How and when interest is posted to a savings account
see Interest Calculation For Average Balance By Example
see Interest Calculation For Minimum Balance By Example
For each the average and minimum balance examples below we will use the same savings product definition:
Savings Product Details:
Interest Rate - 10%
Interest Calculation - every 1 months
Interest Posting - every 3 months
Minimum Balance - 1000 (interest calculation is only applicable when 'average balance' or 'minimum balance' exceeds this value for given calculation period.
Saving Product Details Explained
Interest Rate – The yearly rate at which interest shal be applied. (In mifos a year can be defined as 365 days or 360days in Accounting Rules configuration)
Interest Calculation Period – every 1 month on the end of the month with dates starting from start of financial year (always 1 Jan in mifos)
Interest Posting – every 3 months on the end of the month with dates starting from start of financial year (always 1 Jan in mifos)
Minimum Balance (for interest Calculation) – if the Average or Minimum Balance Principal for a given calculation period is greater or equal to Minimum Balance required then interest calculation is applicable.
Average Balance Example
Savings Account Created
Account Opened on 19 July 2010.
Savings Account Creation Info
When an account is created in a state other than 'Active', Only a SavingsPerformance is created which is used to track the performance of the account (deposits, withdrawals, interest, missed deposits etc)
Savings Account Activated
Account Activated on 20 July 2010.
Savings Account Activation Info
When an account goes active (for the first time) serveral things happen.
- account activtion date is set.
- The next interest calculation date is determined - in this case as interest calculation occurs every 1 months on end of month, the valid set of interest calculation dates are highlight in the table below.
- The next interest posting date is determined - in this case, occurs every 3 months on end of month, the valid set of interest posting dates highlighted below beside the possible interest calculation dates
- It is only through interest posting that interest is actually compounded on the account.
- Schedules are generated with mandatory/recommened amounts to track payments by group/individual clients.
Possible Interest Calculation and Posting Dates
Date |
Note |
Jan 31 2010 |
possible next calculation date |
Feb 28 2010 |
possible next calculation date |
Mar 31 2010 |
possible next calculation date and posting date |
Apr 30 2010 |
possible next calculation date |
May 31 2010 |
possible next calculation date |
Jun 30 2010 |
possible next calculation date and posting date |
July 31 2010 |
possible next calculation date |
August 31 2010 |
possible next calculation date |
September 30 2010 |
possible next calculation date and posting date |
October 31 2010 |
possible next calculation date |
November 31 2010 |
possible next calculation date |
December 31 2010 |
possible next calculation date and posting date |
Actual Interest Calculation and Posting Dates based on Account Activation
The account goes active on 20 July 2010
Date |
Total Balance |
Next Calculation Date |
Next Posting Date |
Note |
19 July 2010 |
0 |
account created pending approval |
||
20 July 2010 |
0 |
31 July 2010 |
30 September 2010 |
account activated |
Savings Account Transactions - First Non Zero Balance Transaction (Deposit/Withdrawal)
On 25th of July, a deposit of 1000 is made. No other activity occurs on the account which results in the account state looking like below.
Date |
Activity |
Amount |
Total Balance |
Next Calculation Date |
Next Posting Date |
Note |
19 July 2010 |
0 |
0 |
account created pending approval |
|||
20 July 2010 |
0 |
0 |
31 July 2010 |
30 September 2010 |
account activated |
|
25 July 2010 |
Deposit |
1000 |
1000 |
31 July 2010 |
30 September 2010 |
account activated |
As a result, on midnight of 31 July 2010 - 1 Aug 2010, the savings interest calculation batch will run and calculate interest for this account.
Calculation periods where first deposit occurs are handled is slightly special way.
Strictly speaking, the 'calculation period' is monthly so is from 1st of July to 31st of July 2010. However the account was only created on the 19th of July.
- The 'average balance' is NOT calculated from the account creation date - 19 July 2010.
- The 'average balance' is NOT calculated from the account activation date - 20 July 2010.
- The 'average balance' is calculated from the first day of activity that results in a non-zero balance which is - 25 July 2010.
Average balance worked out for july
Given the average balance equation shown above:
Starting from 25th of July:
- 25th of July to 31st July = 6 days duration (not including the 25th itself)
- 25th of July to 31st July = 1000 balance for duration
- P (July) = (1000 * (25 to 31 July) / (25 to 31 July) =(1000 * 6) / 6 = 1000 (since the P >= 1000, interest should be calculated)
- r (July) = interest / 100 * (25 to 31 July) / (Number of Days in current financial year) = (10 / 100) * (6 / 365) = 6 / 3650
- I (July) = P (r) = 1000 (6 / 3650) = 1.64 (rounding up to 2 decimal)
This interest is not posted (as its not due to be posted) but is instead stored in a interest to be posted column on savings_account. The next interest calculation date is upated to end of 1 month from last (Aug 31 2010) leaving the account looking like the following for July.
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
19 July 2010 |
0 |
0 |
0 |
account created pending approval |
|||
20 July 2010 |
0 |
0 |
0 |
31 July 2010 |
30 September 2010 |
account activated |
|
25 July 2010 |
Deposit |
1000 |
1000 |
0 |
31 July 2010 |
30 September 2010 |
deposit made |
31 July 2010 |
Interest Calculated |
1.64 |
1000 |
1.64 |
31 August 2010 |
30 September 2010 |
|
Savings Account Transactions (deposit/withdrawal)
There is no activity as the account rolls into August but a deposit is made on the 10th of August and a withdrawal near the end on the 30th of August. This leaves the account looking like this before interest calculation runs again on 31st of August.
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
1st August 2010 |
0 |
1000 |
1.64 |
31 August 2010 |
30 September 2010 |
balance brought over from July |
|
10th August 2010 |
Deposit |
500 |
1500 |
1.64 |
31 August 2010 |
30 September 2010 |
deposit of 500 |
30th August 2010 |
Withdrawal |
1000 |
500 |
1.64 |
31 August 2010 |
30 September 2010 |
withdrawal of 1000 |
Interest Calculation Period is from 1st August to 31 August = 31 days duration
For 10 days the balance was 1000, for 20 days it was 1500 and for 1 day it was 500: thus
P (Aug) = ((1000 *10) + (1500 * 20) + (500 * 1)) / 31 = (10000 + 30000 + 500) / 31 = 40500 / 31 ( >= 1000)
r (Aug) = (10 / 100) * (31 / 365) = 31 / 3650
I (Aug) = P (r) = 40500 / 31 * (31 / 3650) = 11.10
Calculated interest from this period, 11.10 is added to previously calculated interest (1.64) resulting in 12.74 interest for July and August. The interest is still not compounded.
Which results in account looking like the following for August.
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
1st August 2010 |
0 |
1000 |
1.64 |
31 August 2010 |
30 September 2010 |
balance brought over from July |
|
10th August 2010 |
Deposit |
500 |
1500 |
1.64 |
31 August 2010 |
30 September 2010 |
deposit of 500 |
30th August 2010 |
Withdrawal |
1000 |
500 |
1.64 |
31 August 2010 |
30 September 2010 |
withdrawal of 1000 |
31 August 2010 |
Interest Calculated |
11.10 |
500 |
12.74 |
30 September 2010 |
30 September 2010 |
|
Savings Account Transactions (interest posting and no interest calculation for failing to reach minimum balance)
Again, there is little activity in September but for a deposit and withdrawal but interest is due to be calculated and compounded at the end of the month.
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
01st September 2010 |
0 |
500 |
12.74 |
30 September 2010 |
30 September 2010 |
balance brought over from August |
|
15th September 2010 |
Deposit |
1000 |
1500 |
12.74 |
30 September 2010 |
30 September 2010 |
deposit of 1000 |
25th September 2010 |
Withdrawal |
500 |
1000 |
12.74 |
30 September 2010 |
30 September 2010 |
withdrawal of 500 |
First of all interest is calculated for the period.
Interest Calculation Period is from 1st September to 30 September = 30 days duration
For 15 days the balance was 500, for 10 days it was 1500 and for 5 days it was 1000: thus
P (Sept) = ((500 * 15) + (1500 * 10) + (1000 * 5)) / 30 = (7500 + 15000 + 5000) / 30 = 27500 / 30 ( is not >= 1000) so no interest should be calculated
r (Sept) = (10 / 100) * (30 / 365) = 3 / 365
I (Sept) = P (r) = 0 because of minimum balance condition
Next the interest that is due to be posted is posted to account (12.74) and this value is reset to zero, next interest calculation and posting dates are also updated making the end of month account look like.
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
01st September 2010 |
0 |
500 |
12.74 |
30 September 2010 |
30 September 2010 |
balance brought over from August |
|
15th September 2010 |
Deposit |
1000 |
1500 |
12.74 |
30 September 2010 |
30 September 2010 |
deposit of 1000 |
25th September 2010 |
Withdrawal |
500 |
1000 |
12.74 |
30 September 2010 |
30 September 2010 |
withdrawal of 500 |
31st September 2010 |
Interest Calculated and posted |
0 + 12.74 |
1012.74 |
0 |
31 October 2010 |
31 December 2010 |
interest posted |
Savings Account Transactions (adjustments or back-dated transactions)
The account at the start of october looks like the following:
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
01st October 2010 |
0 |
1012.74 |
0 |
31 October 2010 |
31 December 2010 |
balance brought forward from september |
There is no activity in October except for an adjustment of a previous transaction. (At present mifos only allows adjustment of the very last transaction so we will adjust the withdrawal transaction). Instead of withdrawing 500 on 25th of September, it should of being Zero.
So september account really looks like this now:
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
01st September 2010 |
0 |
500 |
12.74 |
30 September 2010 |
30 September 2010 |
balance brought over from August |
|
15th September 2010 |
Deposit |
1000 |
1500 |
12.74 |
30 September 2010 |
30 September 2010 |
deposit of 1000 |
25th September 2010 |
Withdrawal |
500 |
1000 |
12.74 |
30 September 2010 |
30 September 2010 |
withdrawal of 500 |
25th September 2010 |
Adjustment |
-500 |
1500 |
12.74 |
30 September 2010 |
30 September 2010 |
adjust withdrawal of 500 |
25th September 2010 |
Withdrawal |
0 |
1500 |
12.74 |
30 September 2010 |
30 September 2010 |
withdrawal of 0 |
And as a result of this Septembers interest calculation should of being:
Interest Calculation Period is from 1st September to 30 September = 30 days duration
For 15 days the balance was 500, for 10 days it was 1500 and for 5 days it was 1500: thus
P (Sept) = ((500 * 15) + (1500 * 10) + (1500 * 5)) / 30 = (7500 + 15000 + 7500) / 30 = 30000 / 30 ( is = 1000) so interest should of being calculated!
r (Sept) = (10 / 100) * (30 / 365) = 3 / 365
I (Aug) = P (r) = 30000 / 30 * (30 / 3650) = 8.2197 = 8.22
As a result Septembers closing balance and octobers opening balance should look like this:
Date |
Activity |
Amount |
Total Balance |
Interest To Be Posted |
Next Calculation Date |
Next Posting Date |
Note |
31st September 2010 |
Interest Calculated and posted |
8.22 + 12.74 |
1020.96 |
0 |
31 October 2010 |
31 December 2010 |
interest posted |
01st October 2010 |
|
0 |
1020.96 |
0 |
31 October 2010 |
31 December 2010 |
balance brought forward from september |
Savings Account Closed
Minimum Balance Example
Savings Account Created
Savings Account Activated
Savings Account Transactions (deposit/withdrawal)
Savings Account Transactions (interest calculation/posting)
Savings Account Transactions (adjustments)
Savings Account Closed
Average Balance Interest Calculation (for activities given above)
July Calculation,
the balance was opened on 20th but first activity was made on 25th, so 25th will be the day from which the interest calculation will start.
For duration of July interest calculation interval we are going to exclude 25 July as the deposit was made on that day.
Using Above given formulas
P (July) = (1000 * (25 to 31 July) / (25 to 31 July) =(1000 * 6) / 6 = 1000 (since the P >= 1000, interest should be calculated)
r (July) = interest / 100 * (25 to 31 July) / (Number of Days in current financial year) = (10 / 100) * (6 / 365) = 6 / 3650
I (July) = P (r) = 1000 (6 / 3650) = 1.64 (rounding up to 2 decimal)
August Calculation,
Using Above given formulas
P (Aug) = ((1000 * 10) + (1500 * 20) + (500 * 1)) / 31 = (10000 + 30000 + 500) / 31 = 40500 / 31 ( >= 1000)
r (Aug) = (10 / 100) * (31 / 365) = 31 / 3650
I (Aug) = P (r) = 40500 / 31 * (31 / 3650) = 11.10
September Calculation,
Using Above given formulas
P (Sept) = ((500 * 15) + (1500 * 10) + (1000 * 5)) / 30 = (7500 + 15000 + 5000) / 30 = 27500 / 30 ( is not >= 1000) so not interest should be calculated
r (Sept) = (10 / 100) * (30 / 365) = 3 / 365
I (Sept) = P (r) = 0 because of minimum balance condition
Total interest to be posted on 1 Oct midnight = 1.64 + 11.10 + 0 = 12.74
Minimum Balance Interest Calculation (for activities given above)
Interest Calculation/Posting (Notes)
Interest Calculation Date: SAVINGS_ACCOUNT.LAST_INT_CALC_DATE (fromDate), SAVINGS_ACCOUNT.NEXT_INT_CALC_DATE(toDate)
Interest Posting Date: SAVINGS_ACCOUNT.LAST_INT_POST_DATE, SAVINGS_ACCOUNT.NEXT_INT_POST_DATE
Based on the interest calculation meeting from Savings Product Offering "next interest calculation date" is set during interest calculation.
The interest calculation is done for the period determined by fromDate and toDate.
Initial (first) interest calculation has LAST_INT_CALC_DATE as null, in this case the activation date account is taken as fromDate.
Interest calculation takes place when the NEXT_INT_CALC_DATE is older than currentDate.
Interest calculation takes place when principal (P) is greater or equal to the Minimum balance required for interest calculation(Savings Product has property miniBalForIntCalc)
Calculated interest is saved in SavingsBO.interestToBePosted field after every interest calculation.
e.g. if there interest calculation period is 1 month and posting frequency is 3 month then the interest will be calculated 3 times and stored in SavingsBO.interestToBePosted. Interest Posting Batch job will pick the interest from SavingsBO.interestToBePosted.
This makes a necessary condition that Interest calculation batch job should always run before interest posting batch job.
If one interest calculation fails interest posting job has no way to determine and it will still post the interest as SavingsBO.interestToBePosted which could be zero if interest calculation job fails.
Warning
At present, once interest for a given calculation period is calculated it will never again be recalculated however it is possible for users to adjust their last transaction which could well be in the last 'calculation period' and thus the previous interest figure would be wrong.
Handy Hint
It may be just as easy to allow a user adjust ANY transaction and to calculate the interest for account based on all details every time.
Savings Schedules (Notes)
- While creating Mandatory Savings Product, you can not enter a zero amount for "Recommended amount for deposit at every meeting". But during creation of Savings Account it allows to enter zero as "Recommended amount for deposit at every meeting" which results in zero amount schedule. The transaction gets recorded for that zero due, this is probably not a bug, but it can be improved. Same situation is for Voluntary Savings Account