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.

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.

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