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