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.