Every month I get a file showing customer balances by day that looks 
something like this.

CustID	Date			Balance
1		1/1/2010		100
1		1/2/2010		150
1		1/5/2010		100
2		1/1/2010		200
2		1/2/2010		250
2		1/5/2010		200

I need to calculate simple interest by day, which is easy, just take 
the rate / 365 days times the balance above.

The problem I have, is the file I get excludes weekends and holidays, 
but we need to charge interest for balances carried over those days.

In my example above you see the 1/3 & 1/4 are skipped (assuming they 
are a Sat & Sun).  But for customer 1, their $150 balance on 1/2 was 
carried over on 1/3 & 1/4.

I'm at a loss as to how to calculate the interest for those missing days.

Any hints or ideas on how I could accomplish this?

Doug Anderson <douga@fcstone.com> wrote:

> Every month I get a file showing customer balances by day that looks 
> something like this.
> 
> CustID        Date                    Balance
> 1             1/1/2010                100
> 1             1/2/2010                150
> 1             1/5/2010                100
> 2             1/1/2010                200
> 2             1/2/2010                250
> 2             1/5/2010                200
> 
> I need to calculate simple interest by day, which is easy, just take 
> the rate / 365 days times the balance above.
> 
> The problem I have, is the file I get excludes weekends and holidays,
> but we need to charge interest for balances carried over those days.

Any number of days can be calculated very easy with a number result
calculation date_2 - date_1.

You may try to get that second date - a date that says when this
particular balance started or when it ended - into each record. From
this moment on, every record is a balance period with its own number of
days, interest rate, and interest.

There are various ways to get there. There are two pitfalls: make sure
the periods to not overlap. And that there are no gaps.
-- 
http://clk.ch