I am trying to replicate the way I believe a banking mainframe calculates
call account interest. Interest is calculated on the daily closing balance
through the month and posted as a single amount the following month. With
some simple calculations I can get within a few cents for the month, but it
is important I can do this to the exact cent. The rules appear to be:-

1) Where the balance or rate changes from day to day, the daily interest is
(balance * rate/360), rounded to 2 decimals.
2) Where the balance and rate are the same from day to day, the system
aggregates the balance, so the calculation is (sum of balances * rate/360) =
X.
3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.

I have the precise balance and rate for each day (Col A = date, Col B = bal,
Col C = rate). The balance and/or rate may change daily, or be constant for
several days. I have been able to get this far by a series of crude
calculations where I manually enter the number of days the balance and rate
are static. However, I need to automate this for many individual accounts,
bearing in mind that balances and rates are almost random (including being
positive or negative), and the varying length of months.

This community has never failed to amaze me with its knowledge and support,
so I will greatly appreciate all suggestions. Also, if anyone has actual
experience of how mainframes do this and thinks there are subtleties to the
calculation I have missed (or am barking up the wrong tree), please speak up.

KeenKiwi wrote...
....
is important I can do this to the exact cent. The rules appear to
be:-

Meaning you're guessing?

1) Where the balance or rate changes from day to day, the daily
interest is (balance * rate/360), rounded to 2 decimals.

Are you sure about this? Small discrepancies very likely arise from
what might appear to be slight differences in interest rates. This is
especially so where there's rounding. What do you mean by 2 decimal
places? The rate as a PERCENTAGE is rounded to 2 decimal places, so
3.6%/360 = 0.01%? If you mean 0.036/360 rounded to 0.00, you've got a
problem.

2) Where the balance and rate are the same from day to day, the
system aggregates the balance, so the calculation is (sum of
balances * rate/360) = X.

So you're not compounding from one day to the next?

3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.
....

Yup, simple interest.

. . . However, I need to automate this for many individual
accounts, bearing in mind that balances and rates are almost
random (including being positive or negative), and the varying
length of months.

So you don't work for this bank and can't get access to the mainframe
source code?

As for how this could be done on mainframes, the older mainframe
programming languages have many data types that Excel lacks. Many of
those are fixed point types with implicit rounding defined by their
implementation, but assume bankers rounding. With regard to
procedures, there are at least as many ways to code them on mainframes
as there are ways to conditionally sum in Excel.

"Harlan Grove" wrote:

KeenKiwi wrote...
....
is important I can do this to the exact cent. The rules appear to
be:-

Meaning you're guessing?
Pretty much; I've tried a few different options, this is the only way so far
that has given an exact match for the examples I've got.


1) Where the balance or rate changes from day to day, the daily
interest is (balance * rate/360), rounded to 2 decimals.

Are you sure about this?

Only insofar as I have gotten the right answer, whereas other options I've
tried (such as deducting the previous days running total from the new one,
and rounding that) give differences.

Small discrepancies very likely arise from
what might appear to be slight differences in interest rates. This is
especially so where there's rounding. What do you mean by 2 decimal
places? The rate as a PERCENTAGE is rounded to 2 decimal places, so
3.6%/360 = 0.01%? If you mean 0.036/360 rounded to 0.00, you've got a
problem.

The result is rounded, as in 3*.222 = .67

2) Where the balance and rate are the same from day to day, the
system aggregates the balance, so the calculation is (sum of
balances * rate/360) = X.

So you're not compounding from one day to the next?

3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.
....

Yup, simple interest.

. . . However, I need to automate this for many individual
accounts, bearing in mind that balances and rates are almost
random (including being positive or negative), and the varying
length of months.

So you don't work for this bank and can't get access to the mainframe
source code?

Therein lies the problem.

As for how this could be done on mainframes, the older mainframe
programming languages have many data types that Excel lacks. Many of
those are fixed point types with implicit rounding defined by their
implementation, but assume bankers rounding. With regard to
procedures, there are at least as many ways to code them on mainframes
as there are ways to conditionally sum in Excel.

So, do you have some ideas how I can achieve this in Excel? My main issue is
knowing how to sum for the days when the balance/rate is unchanged, and
counting those days; once I have that I can test it extensively and validate
my assumptions. Cheers.

Here's what I came up with for automation:
Sample data in A2:C17 (see below)

column D is the interest formula which attempts to satisfy your rules (there
were a few questions I had on specs (I assumed that when there were several
days in a row with the same rate and balance, that the interest is calculated
on the last of those contiguous days, and nothing on the earlier days), but
maybe you can modify for any small differences), starting in D3:

=IF(AND(B3=B2,C3=C2),IF(AND(B4=B3,C4=C3),"",
(ROUND(((SUMPRODUCT(--($B$1:B2=B3),
--($C$1:C2=C3),--($D$1:D2=""),--($F$1:F2=F3),($C$1:C2))+C3)
*B3/360/(SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)),2)*
((SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)))),IF(AND(B4=B3,C4=C3),
"",ROUND(C2*B3/360,2)))

[I entered carriage returns in case that made it easier to view]

column E is a counter formula (starting from cell E3) that is used in the
formula in column D to assure that multiple noncontiguous occurrences of the
same combination of balance and rate are ignored during the combination for
the case where it needs to compute more than one days' interest:

=IF(OR(B3<B2,C3<C2),F2+1,F2)

Column

A B C D E
[cell A1] RATE balance interest counter
2 1/1/2006 0.0500 50,000 0
3 1/2/2006 0.0500 50,000 13.88 0
4 1/3/2006 0.0400 50,000 5.56 1
5 1/4/2006 0.0400 55,000 2
6 1/5/2006 0.0400 55,000 2
7 1/6/2006 0.0400 55,000 18.33 2
8 1/7/2006 0.0500 55,000 3
9 1/8/2006 0.0500 55,000 15.28 3
10 1/9/2006 0.0560 60,000 8.56 4
11 1/10/2006 0.0540 60,000 9.00 5
12 1/11/2006 0.0500 50,000 6
13 1/12/2006 0.0500 50,000 13.88 6
14 1/13/2006 0.0550 60,000 7
15 1/14/2006 0.0550 60,000 18.34 7
16 1/15/2006 0.0540 61,000 9.00 8
17 1/16/2006 0.0540 50,000 9.15 9

My resulting Interest numbers are in column D.

Note:
Some causes of small interest differences could be computing interest in
arrears (using previous day's rate) vs. in advance (current day's rate)

hth,
Dave

I dont have any insight whether your rules are correct but I might be able to
help automate this for you. Do you have a value for every day (where there
could be interest - presumably business days) or could the data skip days
where the balance would have to be assumed?

"KeenKiwi" wrote:



"Harlan Grove" wrote:

KeenKiwi wrote...
....
is important I can do this to the exact cent. The rules appear to
be:-

Meaning you're guessing?
Pretty much; I've tried a few different options, this is the only way so far
that has given an exact match for the examples I've got.


1) Where the balance or rate changes from day to day, the daily
interest is (balance * rate/360), rounded to 2 decimals.

Are you sure about this?

Only insofar as I have gotten the right answer, whereas other options I've
tried (such as deducting the previous days running total from the new one,
and rounding that) give differences.

Small discrepancies very likely arise from
what might appear to be slight differences in interest rates. This is
especially so where there's rounding. What do you mean by 2 decimal
places? The rate as a PERCENTAGE is rounded to 2 decimal places, so
3.6%/360 = 0.01%? If you mean 0.036/360 rounded to 0.00, you've got a
problem.

The result is rounded, as in 3*.222 = .67

2) Where the balance and rate are the same from day to day, the
system aggregates the balance, so the calculation is (sum of
balances * rate/360) = X.

So you're not compounding from one day to the next?

3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.
....

Yup, simple interest.

. . . However, I need to automate this for many individual
accounts, bearing in mind that balances and rates are almost
random (including being positive or negative), and the varying
length of months.

So you don't work for this bank and can't get access to the mainframe
source code?

Therein lies the problem.

As for how this could be done on mainframes, the older mainframe
programming languages have many data types that Excel lacks. Many of
those are fixed point types with implicit rounding defined by their
implementation, but assume bankers rounding. With regard to
procedures, there are at least as many ways to code them on mainframes
as there are ways to conditionally sum in Excel.

So, do you have some ideas how I can achieve this in Excel? My main issue is
knowing how to sum for the days when the balance/rate is unchanged, and
counting those days; once I have that I can test it extensively and validate
my assumptions. Cheers.

Dave, many thanks for all your effort there, really appreciated. Plenty for
me to work on there so I'll let you know how it goes in a day or 2.
Thanks again

"Dave Breitenbach" wrote:

Also note that the counter column always has a value - my post makes it look
like some of those values are in column D. This is NOT the case - that
occurred when there was no interest at all in column D since it was not the
last contiguous day of like Balance/rate.

Let me know if this works for you.

"Dave Breitenbach" wrote:

Here's what I came up with for automation:
Sample data in A2:C17 (see below)

column D is the interest formula which attempts to satisfy your rules (there
were a few questions I had on specs (I assumed that when there were several
days in a row with the same rate and balance, that the interest is calculated
on the last of those contiguous days, and nothing on the earlier days), but
maybe you can modify for any small differences), starting in D3:

=IF(AND(B3=B2,C3=C2),IF(AND(B4=B3,C4=C3),"",
(ROUND(((SUMPRODUCT(--($B$1:B2=B3),
--($C$1:C2=C3),--($D$1:D2=""),--($F$1:F2=F3),($C$1:C2))+C3)
*B3/360/(SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)),2)*
((SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)))),IF(AND(B4=B3,C4=C3),
"",ROUND(C2*B3/360,2)))

[I entered carriage returns in case that made it easier to view]

column E is a counter formula (starting from cell E3) that is used in the
formula in column D to assure that multiple noncontiguous occurrences of the
same combination of balance and rate are ignored during the combination for
the case where it needs to compute more than one days' interest:

=IF(OR(B3<B2,C3<C2),F2+1,F2)

Column

A B C D E
[cell A1] RATE balance interest counter
2 1/1/2006 0.0500 50,000 0
3 1/2/2006 0.0500 50,000 13.88 0
4 1/3/2006 0.0400 50,000 5.56 1
5 1/4/2006 0.0400 55,000 2
6 1/5/2006 0.0400 55,000 2
7 1/6/2006 0.0400 55,000 18.33 2
8 1/7/2006 0.0500 55,000 3
9 1/8/2006 0.0500 55,000 15.28 3
10 1/9/2006 0.0560 60,000 8.56 4
11 1/10/2006 0.0540 60,000 9.00 5
12 1/11/2006 0.0500 50,000 6
13 1/12/2006 0.0500 50,000 13.88 6
14 1/13/2006 0.0550 60,000 7
15 1/14/2006 0.0550 60,000 18.34 7
16 1/15/2006 0.0540 61,000 9.00 8
17 1/16/2006 0.0540 50,000 9.15 9

My resulting Interest numbers are in column D.

Note:
Some causes of small interest differences could be computing interest in
arrears (using previous day's rate) vs. in advance (current day's rate)

hth,
Dave

Also note that the counter column always has a value - my post makes it look
like some of those values are in column D. This is NOT the case - that
occurred when there was no interest at all in column D since it was not the
last contiguous day of like Balance/rate.

Let me know if this works for you.

"Dave Breitenbach" wrote:

Here's what I came up with for automation:
Sample data in A2:C17 (see below)

column D is the interest formula which attempts to satisfy your rules (there
were a few questions I had on specs (I assumed that when there were several
days in a row with the same rate and balance, that the interest is calculated
on the last of those contiguous days, and nothing on the earlier days), but
maybe you can modify for any small differences), starting in D3:

=IF(AND(B3=B2,C3=C2),IF(AND(B4=B3,C4=C3),"",
(ROUND(((SUMPRODUCT(--($B$1:B2=B3),
--($C$1:C2=C3),--($D$1:D2=""),--($F$1:F2=F3),($C$1:C2))+C3)
*B3/360/(SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)),2)*
((SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)))),IF(AND(B4=B3,C4=C3),
"",ROUND(C2*B3/360,2)))

[I entered carriage returns in case that made it easier to view]

column E is a counter formula (starting from cell E3) that is used in the
formula in column D to assure that multiple noncontiguous occurrences of the
same combination of balance and rate are ignored during the combination for
the case where it needs to compute more than one days' interest:

=IF(OR(B3<B2,C3<C2),F2+1,F2)

Column

A B C D E
[cell A1] RATE balance interest counter
2 1/1/2006 0.0500 50,000 0
3 1/2/2006 0.0500 50,000 13.88 0
4 1/3/2006 0.0400 50,000 5.56 1
5 1/4/2006 0.0400 55,000 2
6 1/5/2006 0.0400 55,000 2
7 1/6/2006 0.0400 55,000 18.33 2
8 1/7/2006 0.0500 55,000 3
9 1/8/2006 0.0500 55,000 15.28 3
10 1/9/2006 0.0560 60,000 8.56 4
11 1/10/2006 0.0540 60,000 9.00 5
12 1/11/2006 0.0500 50,000 6
13 1/12/2006 0.0500 50,000 13.88 6
14 1/13/2006 0.0550 60,000 7
15 1/14/2006 0.0550 60,000 18.34 7
16 1/15/2006 0.0540 61,000 9.00 8
17 1/16/2006 0.0540 50,000 9.15 9

My resulting Interest numbers are in column D.

Note:
Some causes of small interest differences could be computing interest in
arrears (using previous day's rate) vs. in advance (current day's rate)

hth,
Dave

OK, I've had a play with this, and it's pretty close. Firstly I may have got
a bit confused with your sample layout. In my working example, cells A1 - C1
have the column labels, and the first actual date, rate and balance are in A2
- C2. Therefore the first formulae go in D3 and E3, and the formulae in your
"Counter" column should refer to Col E, not Col F? Please confirm.

In that case, what is the formula for D2 and E2 in case B2 and/or C2 differ
from B3 and/or C3?

In your example, the calculated amounts where the balance exists for only
one day (rows 10 and 16) do not calculate correctly (they use the previous
balance, I think). I think changes to the rate are handled correctly.

Sorry I can't figure this out myself, I get the principles of your approach
but can't follow all the steps. Thanks again for your input.

"Dave Breitenbach" wrote:

Also note that the counter column always has a value - my post makes it look
like some of those values are in column D. This is NOT the case - that
occurred when there was no interest at all in column D since it was not the
last contiguous day of like Balance/rate.

Let me know if this works for you.

"Dave Breitenbach" wrote:

Here's what I came up with for automation:
Sample data in A2:C17 (see below)

column D is the interest formula which attempts to satisfy your rules (there
were a few questions I had on specs (I assumed that when there were several
days in a row with the same rate and balance, that the interest is calculated
on the last of those contiguous days, and nothing on the earlier days), but
maybe you can modify for any small differences), starting in D3:

=IF(AND(B3=B2,C3=C2),IF(AND(B4=B3,C4=C3),"",
(ROUND(((SUMPRODUCT(--($B$1:B2=B3),
--($C$1:C2=C3),--($D$1:D2=""),--($F$1:F2=F3),($C$1:C2))+C3)
*B3/360/(SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)),2)*
((SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)))),IF(AND(B4=B3,C4=C3),
"",ROUND(C2*B3/360,2)))

[I entered carriage returns in case that made it easier to view]

column E is a counter formula (starting from cell E3) that is used in the
formula in column D to assure that multiple noncontiguous occurrences of the
same combination of balance and rate are ignored during the combination for
the case where it needs to compute more than one days' interest:

=IF(OR(B3<B2,C3<C2),F2+1,F2)

Column

A B C D E
[cell A1] RATE balance interest counter
2 1/1/2006 0.0500 50,000 0
3 1/2/2006 0.0500 50,000 13.88 0
4 1/3/2006 0.0400 50,000 5.56 1
5 1/4/2006 0.0400 55,000 2
6 1/5/2006 0.0400 55,000 2
7 1/6/2006 0.0400 55,000 18.33 2
8 1/7/2006 0.0500 55,000 3
9 1/8/2006 0.0500 55,000 15.28 3
10 1/9/2006 0.0560 60,000 8.56 4
11 1/10/2006 0.0540 60,000 9.00 5
12 1/11/2006 0.0500 50,000 6
13 1/12/2006 0.0500 50,000 13.88 6
14 1/13/2006 0.0550 60,000 7
15 1/14/2006 0.0550 60,000 18.34 7
16 1/15/2006 0.0540 61,000 9.00 8
17 1/16/2006 0.0540 50,000 9.15 9

My resulting Interest numbers are in column D.

Note:
Some causes of small interest differences could be computing interest in
arrears (using previous day's rate) vs. in advance (current day's rate)

hth,
Dave

On Mar 1, 10:00 pm, KeenKiwi
wrote:
Interest is calculated on the daily closing balance
through the month and posted as a single amount the following month.
[....]
1) Where the balance or rate changes from day to day, the daily interest is
(balance * rate/360), rounded to 2 decimals.

The daily interest probably is not actually rounded. I expect only
the sum of the interest for the month to be rounded.

Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents. The easiest way to implement
"banker's rounding" is to create a UDF that uses the VBA function
Round(). I forego this complexity in the solutions below.

2) Where the balance and rate are the same from day to day, the system
aggregates the balance, so the calculation is (sum of balances * rate/360) =
X.

My bank uses 365 as the denominator, not 360. (I suspect they use 366
in leap years.) I'm sure that varies from bank to bank.

There should be no difference between #1 and #2 when you eliminate the
rounding of intermediate amounts, other than the fact that #2 is more
efficient

3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.

I am not sure what you are getting at with #3 and #4, since #3 seems
to presume the answer that you say you are trying to compute, namely
the interest (X). And again, I would not round in #3, which computes
the daily interest. Instead, only the total interest for the month
should be rounded.

I have the precise balance and rate for each day (Col A = date, Col B = bal,
Col C = rate). The balance and/or rate may change daily, or be constant for
several days.

Then the interest for the month can be computed by:

=round(sumproduct(A2:A13 - A1:A12, B2:B13, C2:C13), 2)

where A1:C1 is an entry for the ending balance of the previous month,
and A2:C13 are entries for each change in balance and/or rate during
the month in ascending date order, including an entry for the ending
balance of the month. If C1:C13 contains the annual rate, change the
formula to round(sumproduct(...)/365, 2).

If the rate is constant for the entire month (the policy of some
banks), this can be simplified to:

=round(C13*sumproduct(A1:A12 - A2:A13, B2:B13), 2)

where A13:C13 is presumed to always be the ending balance and ending
daily rate (for the month). Again, change to
round(C13*sumproduct(...)/365, 2) if C13 is an annual rate.

The VBA Round function is poorly buffered against the vagaries of binary
representations. A better VBA implementation of this rounding rule is given
at

http://groups.google.com/group/micro...7fce6145b70d69

I would be very interested in any evidence that this rounding rule has ever
been used in banking.

Jerry

"joeu2004" wrote:
...
Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents. The easiest way to implement
"banker's rounding" is to create a UDF that uses the VBA function
Round(). I forego this complexity in the solutions below.
...

On Mar 3, 4:41 am, Jerry W. Lewis wrote:
"joeu2004" wrote:
Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents.
[....]
I would be very interested in any evidence that this rounding rule has ever
been used in banking.

I would be very interested in any evidence to the contrary; or in
evidence that banks use the "normal" rounding rules. I would also be
interested in evidence that God exists, or that she does not exist.

As Harlan pointed out, some banks might still be using software
written in computer languages that might implement "banker's rounding"
together with decimal arithmetic -- for example, COBOL and RPG, as I
recall vaguely. That is sufficient "evidence", I think.

But the fact is: the difference between "banker's rounding" and
"normal" rounding arises only when the "fraction to the right" (for
want of a better description) is __exactly__ 5, and then only when the
digit to the left is odd. I suspect the situation arises almost never
in real-life daily and monthly interest calculations, unless the
bank's software truncates the intermediate amount to the "decicent"
before rounding, which I think is unlikely.

So notwithstanding the correctness of my original comment, it was
probably frivilous to mention because it is unlikely to make any
difference. Sometimes I get "obsessed" with being unduly complete.

Joe and Jerry, thanks both very much for your input. Looks like I've got a
bit of work to do to prove categorically how the interest is calculated,
particularly as regards rounding. I never realised there could be so many
different approaches to when and how rounding occurs. Leave it with me,
thanks.

"joeu2004" wrote:

On Mar 3, 4:41 am, Jerry W. Lewis wrote:
"joeu2004" wrote:
Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents.
[....]
I would be very interested in any evidence that this rounding rule has ever
been used in banking.

I would be very interested in any evidence to the contrary; or in
evidence that banks use the "normal" rounding rules. I would also be
interested in evidence that God exists, or that she does not exist.

As Harlan pointed out, some banks might still be using software
written in computer languages that might implement "banker's rounding"
together with decimal arithmetic -- for example, COBOL and RPG, as I
recall vaguely. That is sufficient "evidence", I think.

But the fact is: the difference between "banker's rounding" and
"normal" rounding arises only when the "fraction to the right" (for
want of a better description) is __exactly__ 5, and then only when the
digit to the left is odd. I suspect the situation arises almost never
in real-life daily and monthly interest calculations, unless the
bank's software truncates the intermediate amount to the "decicent"
before rounding, which I think is unlikely.

So notwithstanding the correctness of my original comment, it was
probably frivilous to mention because it is unlikely to make any
difference. Sometimes I get "obsessed" with being unduly complete.

"joeu2004" wrote:

On Mar 3, 4:41 am, Jerry W. Lewis wrote:
"joeu2004" wrote:
Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents.
[....]
I would be very interested in any evidence that this rounding rule has ever
been used in banking.

I would be very interested in any evidence to the contrary; or in
evidence that banks use the "normal" rounding rules. ...

The banking sites I have seen that give instructions for Euro conversions
explicitly specify 5-up rounding. The US IRS explicitly specifies 5-up
rounding.

More subjectively, I have never met a banker or an accountant who has ever
heard of rounding exactly 5 to an even rounded number. In fairness, must
admit that I am not aware of having met a person who programed old mainframe
banking systems, but I would expect that they had to conform to standard
accounting practice.

I am very familiar with both methods, and agree that the difference is
usually negligible when enough figures are reported. But all too often, real
data may be recorded to limited precision, where following the ASTM standard
(the so called "bankers rounding") then is clearly best practice for
minimizing the impact of rounding. It also becomes much more important in
binary where the IEEE 754 standard specifies the binary equivalent of
rounding to even.

Jerry

"Jerry W. Lewis" wrote:

"joeu2004" wrote:

On Mar 3, 4:41 am, Jerry W. Lewis wrote:
"joeu2004" wrote:
Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents.
[....]
I would be very interested in any evidence that this rounding rule has ever
been used in banking.

I would be very interested in any evidence to the contrary; or in
evidence that banks use the "normal" rounding rules. ...

The banking sites I have seen that give instructions for Euro conversions
explicitly specify 5-up rounding. The US IRS explicitly specifies 5-up
rounding.

More subjectively, I have never met a banker or an accountant who has ever
heard of rounding exactly 5 to an even rounded number. In fairness, must
admit that I am not aware of having met a person who programed old mainframe
banking systems, but I would expect that they had to conform to standard
accounting practice.
....

According to the European Commission document "The Introduction of the Euro
and the Rounding of Currency Amounts"
( http://europa.eu.int/ISPO/y2keuro/docs/ep22-en.pdf )

"Rounding has so far not been at the forefront of public interest and has
rarely been the subject of formal rules laid down in legislation; economic
operators have themselves dealt with the issue. A large variety of market
conventions and national practices exist, laying down rounding rules for
different national and international financial markets."

From this, I presume that "banker's rounding", may well have been used in
banking somewhere at some time, but likely was never the standard for banking
calculations that the name would imply.

The earliest reference that I have been able to nail down is the 1906 4th
edition of Robert Woodward's "Probability & Theory of Errors" where a
statement on p.42 suggests that rounding ties to an even rounded number was
standard practice in the preparation of mathematical tables. I would be very
interested in other pre-1940 references.

Jerry

KeenKiwi,

Your welcome.

The counter column in my example was in column F but column "e" was blank,
so that is why the counter formula referred to column "f". So, in a sense
you're right because my post did not reflect the blank column. To revise,
I'd leave the formulas as is with the blank column in Column"e", then if you
want delete the entire column E [blank] so the formulas automatically update.
Since the interest formula has many references to column "F", it would be a
pain to make all those changes.

Assuming you made the column deletion, so that the counter formula is in
column E, then Cell D2 and E2 should be left blank - this is because each of
those formulas need to refer to the cell in (or range including) the row
above them. I prefer to leave them blank as opposed to changing the formula
for the first row so nothing is hidden in columns - meaning one formula can
always be copied down and you don't need to find any changes in code as
you're reviewing your work.
In addition, I usually "grey out" those cells that are skipped so the user
knows they are not used.

The second part of your question regarding these two blank cells (D2 and E2)
was "in case B2 and/or C2 differ from B3 and/or C3?" The cell where that
condition is tested is in row 3 since each cell looks to the prior row for
that part of the test, so you don't need to determine that in the first row.
Moreover, the first row DOES need to know if the next day has matching
relevant identities, so it does test the next row (4) in row 3 here --
IF(AND(B4=B3,C4=C3).

In response to the "previous day's" balance interest computation for
one-day's interest, this question is what I was referring to in my post about
computing interest in arrears vs in advance. In the area of finance that I
work in, interest computed for one period would be paid in the next period
typically as the exact interest is not known until the end of the previous
day for which the balance is used. So in this example, you would need the
previous day's balance to apply the rate to as the interest due today is
based what was owed for the previous day.

It sounds like in your case, each computation is base on the same day's
balance and rate. I'm guessing you already modified the formula to
correspond to this spec, but if you need help let me know.

If there is any other part of the formula which you're not sure of - just
ask! I'd be happy to break it down.

Dave


PS: Other useful excel functions for rounding/truncating a Roundup,
Rounddown, Mround, Floor, and Ceiling
Check them out in Help.





"KeenKiwi" wrote:

OK, I've had a play with this, and it's pretty close. Firstly I may have got
a bit confused with your sample layout. In my working example, cells A1 - C1
have the column labels, and the first actual date, rate and balance are in A2
- C2. Therefore the first formulae go in D3 and E3, and the formulae in your
"Counter" column should refer to Col E, not Col F? Please confirm.

In that case, what is the formula for D2 and E2 in case B2 and/or C2 differ
from B3 and/or C3?

In your example, the calculated amounts where the balance exists for only
one day (rows 10 and 16) do not calculate correctly (they use the previous
balance, I think). I think changes to the rate are handled correctly.

Sorry I can't figure this out myself, I get the principles of your approach
but can't follow all the steps. Thanks again for your input.

"Dave Breitenbach" wrote:

Also note that the counter column always has a value - my post makes it look
like some of those values are in column D. This is NOT the case - that
occurred when there was no interest at all in column D since it was not the
last contiguous day of like Balance/rate.

Let me know if this works for you.

"Dave Breitenbach" wrote:

Here's what I came up with for automation:
Sample data in A2:C17 (see below)

column D is the interest formula which attempts to satisfy your rules (there
were a few questions I had on specs (I assumed that when there were several
days in a row with the same rate and balance, that the interest is calculated
on the last of those contiguous days, and nothing on the earlier days), but
maybe you can modify for any small differences), starting in D3:

=IF(AND(B3=B2,C3=C2),IF(AND(B4=B3,C4=C3),"",
(ROUND(((SUMPRODUCT(--($B$1:B2=B3),
--($C$1:C2=C3),--($D$1:D2=""),--($F$1:F2=F3),($C$1:C2))+C3)
*B3/360/(SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)),2)*
((SUMPRODUCT(--($B$1:B2=B3),--($C$1:C2=C3),
--($D$1:D2=""),--($F$1:F2=F3))+1)))),IF(AND(B4=B3,C4=C3),
"",ROUND(C2*B3/360,2)))

[I entered carriage returns in case that made it easier to view]

column E is a counter formula (starting from cell E3) that is used in the
formula in column D to assure that multiple noncontiguous occurrences of the
same combination of balance and rate are ignored during the combination for
the case where it needs to compute more than one days' interest:

=IF(OR(B3<B2,C3<C2),F2+1,F2)

Column

A B C D E
[cell A1] RATE balance interest counter
2 1/1/2006 0.0500 50,000 0
3 1/2/2006 0.0500 50,000 13.88 0
4 1/3/2006 0.0400 50,000 5.56 1
5 1/4/2006 0.0400 55,000 2
6 1/5/2006 0.0400 55,000 2
7 1/6/2006 0.0400 55,000 18.33 2
8 1/7/2006 0.0500 55,000 3
9 1/8/2006 0.0500 55,000 15.28 3
10 1/9/2006 0.0560 60,000 8.56 4
11 1/10/2006 0.0540 60,000 9.00 5
12 1/11/2006 0.0500 50,000 6
13 1/12/2006 0.0500 50,000 13.88 6
14 1/13/2006 0.0550 60,000 7
15 1/14/2006 0.0550 60,000 18.34 7
16 1/15/2006 0.0540 61,000 9.00 8
17 1/16/2006 0.0540 50,000 9.15 9

My resulting Interest numbers are in column D.

Note:
Some causes of small interest differences could be computing interest in
arrears (using previous day's rate) vs. in advance (current day's rate)

hth,
Dave