Clarification, too late to edit....
For example, the following quarterly schedule works for your example (dates and numbers in US format):
[....]
Formulas:
Code:
E2: =B1
E3: =EDATE(E2,3)
E14: =B2
F2: =B4
F3: =MAX(ROUND(FV($B$6/4,ROWS($E$3:E3),0,-$B$4),0), ROUND(F2*(1+$B$6/4),0))
F14: =MAX(ROUND(FV(B6/4,INT(B3/3),0,-B4)*(1+MOD(B3/3,1)*B6/4),0), ROUND(F13*(1+2*B6/12),0))
E3:F3 is copied down through E13:F13.
Sorry for the incessant postings, but I feel the following clarification might be helpful.
The formulas in column F can be simplified if we make some assumptions. This is illustrated in the following table.
| E
| F
| G
| H
|
1 |
| Rounded
Cumulative |
Exact | Rounded
Posted |
2
| 3/19/2012 | 802,629 | 802,629.00 | 802,629 |
3
| 6/19/2012 | 822,193 | 822,193.08 | 822,193 |
4
| 9/19/2012 | 842,234 | 842,234.04 | 842,234 |
5
| 12/19/2012 | 862,763 | 862,763.49 | 862,763 |
6
| 3/19/2013 | 883,793 | 883,793.35 | 883,793 |
7
| 6/19/2013 | 905,336 | 905,335.82 | 905,335 |
8
| 9/19/2013 | 927,403 | 927,403.38 | 927,403 |
9
| 12/19/2013 | 950,009 | 950,008.83 | 950,008 |
10
| 3/19/2014 | 973,165 | 973,165.30 | 973,164 |
11
| 6/19/2014 | 996,886
| 996,886.20 | 996,885 |
12
| 9/19/2014 | 1,021,185 | 1,021,185.30 | 1,021,184 |
13
| 12/19/2014 | 1,046,077 | 1,046,076.70 | 1,046,075 |
14
| 2/19/2015 | 1,063,076 | 1,063,075.44 | 1,063,074 |
<tbody>
</tbody>
The formulas in F3:F13 (F3 by example) and F14 are:
F3: =ROUND(FV($B$6/4,ROWS($E$3:E3),0,-$B$4),0)
F14: =ROUND(ROUND(FV(B6/4,INT(B3/3),0,-B4),0)*(1+MOD(B3,3)*B6/12),0)
In actual practice, periodic interest should be the previous balance times the periodic rate.
The bank is free to handle fractional interest amounts in any reasonable manner, subject to local or national law.
(I don't know why zaska assumes amounts are rounded to rupees, since the rupee has been decimalized since 1957 to 1969, depending on location. So I take that for granted.)
One method is illustrated in column H: periodic interest is based on the rounded periodic balance. The formulas are:
H3: =ROUND(H2*(1+$B$6/4),0)
H14: =ROUND(H13*(1+MOD(B3,3)*B6/12),0)
But that would result in a final balance of 1,063,074 instead of 1,063,076.
Another method is illustrated in column G: periodic interest is based on the exact balance.
(Amounts are displayed rounded to 2 decimal places, but the actual amounts are more precise.) The formulas are:
G3: =G2*(1+$B$6/4)
G14: =G13*(1+MOD(B3,3)*B6/12)
However, that would result in a final balance of 1,063,075 or 1,063,075.44 when rounded to the amount actually paid; again, not 1,063,076.
A third method is illustrated in column F: periodic interest is based on the exact balance, but the
reported balance is rounded.
In that case, I believe the formula in F14 should be:
=ROUND(
FV(B6/4,INT(B3/3),0,-B4)*(1+MOD(B3,3)*B6/12),0)
But again, that would result in 1,063,075 instead of 1,063,076. In fact, it causes an off-by-one error in several of the additional examples included in zaska's posting at excelforum.com.
In contrast, the formula
=ROUND(
ROUND(FV(B6/4,INT(B3/3),0,-B4)
,0)*(1+MOD(B3,3)*B6/12),0)
works for all of those examples.
I find that surprising. It begs the question: how would the bank report year-end interest in each year that ends in an odd quarter (1- or 2-month period) because the deposit does not start at the beginning of a quarter?
In any case, it appears the bank uses the last formula to determine the final balance paid at maturity.
