"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.