interest calculation

A table to enter the money people pay and withdraw. The money may
change, the interest rate may change. I need to calculate the interest
they get at the end of the period.

This seems to be spreadsheet stuff, but the banks use database systems,
don't they?

I think about these fields:
Person-ID
Date
New Balance (numeric or statistic)
Interest Reate (numeric)

A new record is crated whenever the balance or the interest rate
changes. So we get this in one year:

Date Balance Interest
Jan 1st 0 2%
Feb 19th 2500 2%
March 1st 2500 2.25%
March 13th 3000 2.25%
Dec 31st 3000 2.25%

When I enter the record on March 31st, I'm able to calculate the
interest for Feb 19th to March 31st: 40 days, 2500 balance, 2% -> result
5.48. Same procedure for every following step.

But how would I set up the calculation? I have to keep in the mind that
there will be records for different clients in the table. I can think
about this:

Start with the client, go to related entries on the account table, sort
and walk through all the records, starting at the end, storing the date
in a variable, go back on record and use the variable to calculate the
number of days etc. It looks a bit messy to me.

Things might be easier to handle if every record has a start and end
date, so both the number of days and the interest amount can be
calculated with simple calc functions. I'd have to make sure that every
end date match the previous start date and that the records cover the
entire period.

Suggestions?
--
http://clk.ch

Re: interest calculation

In article <1i44ajm.jtn06f1aisd9qN%clk@freesurf.ch>, clk@freesurf.ch
(Christoph Kaufmann) wrote:

> A table to enter the money people pay and withdraw. The money may
> change, the interest rate may change. I need to calculate the interest
> they get at the end of the period.
>
> This seems to be spreadsheet stuff, but the banks use database systems,
> don't they?
>
> I think about these fields:
> Person-ID
> Date
> New Balance (numeric or statistic)
> Interest Reate (numeric)
>
> A new record is crated whenever the balance or the interest rate
> changes. So we get this in one year:
>
> Date Balance Interest
> Jan 1st 0 2%
> Feb 19th 2500 2%
> March 1st 2500 2.25%
> March 13th 3000 2.25%
> Dec 31st 3000 2.25%
>
> When I enter the record on March 31st, I'm able to calculate the
> interest for Feb 19th to March 31st: 40 days, 2500 balance, 2% -> result
> 5.48. Same procedure for every following step.
>
> But how would I set up the calculation? I have to keep in the mind that
> there will be records for different clients in the table. I can think
> about this:
>
> Start with the client, go to related entries on the account table, sort
> and walk through all the records, starting at the end, storing the date
> in a variable, go back on record and use the variable to calculate the
> number of days etc. It looks a bit messy to me.
>
> Things might be easier to handle if every record has a start and end
> date, so both the number of days and the interest amount can be
> calculated with simple calc functions. I'd have to make sure that every
> end date match the previous start date and that the records cover the
> entire period.
>
> Suggestions?

Start and end dates would make it MUCH easier, otherwise you'll need a
script or complicated relationship set-up to work out the time periods.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: interest calculation

On Sep 8, 1:48 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> A table to enter the money people pay and withdraw. The money may
> change, the interest rate may change. I need to calculate the interest
> they get at the end of the period.
>
> This seems to be spreadsheet stuff, but the banks use database systems,
> don't they?
>
> I think about these fields:
> Person-ID
> Date
> New Balance (numeric or statistic)
> Interest Reate (numeric)
>
> A new record is crated whenever the balance or the interest rate
> changes. So we get this in one year:
>
> Date Balance Interest
> Jan 1st 0 2%
> Feb 19th 2500 2%
> March 1st 2500 2.25%
> March 13th 3000 2.25%
> Dec 31st 3000 2.25%
>
> When I enter the record on March 31st, I'm able to calculate the
> interest for Feb 19th to March 31st: 40 days, 2500 balance, 2% -> result
> 5.48. Same procedure for every following step.
>
> But how would I set up the calculation? I have to keep in the mind that
> there will be records for different clients in the table. I can think
> about this:
>
> Start with the client, go to related entries on the account table, sort
> and walk through all the records, starting at the end, storing the date
> in a variable, go back on record and use the variable to calculate the
> number of days etc. It looks a bit messy to me.
>
> Things might be easier to handle if every record has a start and end
> date, so both the number of days and the interest amount can be
> calculated with simple calc functions. I'd have to make sure that every
> end date match the previous start date and that the records cover the
> entire period.
>
> Suggestions?
> --http://clk.ch

For starters I wouldn't store the interest rate in the same table as
the transactions.

I also wouldn't recommend computing interest from 'transaction to
transaction', but rather directly on the interest period, regardless
of what the transaction history is.

How are you computing/compounding interest, and when is it getting
paid out?

Re: interest calculation

d-42 wrote:

> For starters I wouldn't store the interest rate in the same table as
> the transactions.
>
> I also wouldn't recommend computing interest from 'transaction to
> transaction', but rather directly on the interest period, regardless
> of what the transaction history is.

This seems to be the best way to do it. I started designing the database
yesterday, it was easier than I'd thought.

> How are you computing/compounding interest, and when is it getting
> paid out?

I use one record if the balance or the interest rate changes. The record
has a from date and a to date.

The to date is empty until a new record is created for the same loan.
Then the from date of the new record is used as to date of the previous
record. I'll have to script the creation of new record to make this
sure.

There is no number field for the balance but two for payments (in and
out). I use statistic fields to sum up all the payments and a self join
to select the past records only in order to get a balance for every
record.

Once a record has a form date and a to date, a simple calc fields
calculates the interest for the period.

I just started playing around with this. In order to pay out the
interest, I need a table "Closings". It will relate to the records in
the history like a invoice table to the line item table. The closed
records will bear the serial number of the closing record.

In order to close a period on Sep 30th, I'll have to put this date into
the To Date field of the latest history record. I'll also create a new
record for the new period.
--
http://clk.ch

Re: interest calculation

On Sep 13, 11:50 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> d-42 wrote:
> > For starters I wouldn't store the interest rate in the same table as
> > the transactions.
>
> > I also wouldn't recommend computing interest from 'transaction to
> > transaction', but rather directly on the interest period, regardless
> > of what the transaction history is.
>
> This seems to be the best way to do it. I started designing the database
> yesterday, it was easier than I'd thought.
>

Yes, but its numerically unstable.



> I use one record if the balance or the interest rate changes. The
> record has a from date and a to date.

Storing the interest rate with the transactions is needlessly
denormalized, and worse it forces you to create a new record for every
client whenever the rate changes, instead of just one record for the
new rate. I realize that it won't happen -that- often, and space is
cheap, but this is 'ugly design'.


> Once a record has a form date and a to date, a simple calc fields
> calculates the interest for the period.

Its also numerically unstable, and the interest computed will vary,
due to round off error, based on the number of transactions, and where
they are distributed in a month, even if they are 'null'.

I assume you are computing compound interest as something like.

b = balance
r = interest (1+ rate / 365 [or 366 if its a leap year])
t = days between transactions

and so:

compound = b(1+r)^t

which is fine. But suppose t = 40, and then you had a pair of
transactions on day 6, and day 12 to add 5000, and then withdraw 5000
on the same day, on both days.

That shouldn't affect the interest computed one iota, ever, right.
Because the closing daily and minimum daily balance is the same for
the entire month.

So the interest compounded along one 40 day term, or over 6 then 6,
then 28 day terms should be identical.

And its even mathematically equivalent, such that:

b(1+r) ^40 = [[ b(1+r)^6 ](1+r)^6](1+r)^28

However, if you compute them numerically, iteratively, the final
result may well end up slightly different due to the differences in
the accumulated round off error. This is *unacceptable* in financial
systems.

Moreover, what if they had deposited 5000 into an empty account on the
1st. Then withdrew it all every morning, and redeposited it every
afternoon. No competent bank would pay a cent of interest on that
money, as the minimum daily balance was 0 every single day.

Your proposed technique however, would pay them quite well. Again, I
realize this is a perverse case, but financial software in particular
should NOT be susceptible to problems like this.

What it should do is loop over every day determining the minimum
balance for that day, and compute the interest for that day at a
specified precision, accumulating it and then rounding it off and
depositing on the date at which interest is paid.

An algorithm like that is numerically stable. If the minimum daily
balance for the month is 5000.00. it will compute the exact same
result whether there are no transactions, or 2,000,000 transactions.

-regards,
Dave

Re: interest calculation

In article <1189711748.063442.118560@19g2000hsx.googlegroups.com>, d-42
wrote:

> On Sep 13, 11:50 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> > d-42 wrote:
> > > For starters I wouldn't store the interest rate in the same table as
> > > the transactions.
> >
> > > I also wouldn't recommend computing interest from 'transaction to
> > > transaction', but rather directly on the interest period, regardless
> > > of what the transaction history is.
> >
> > This seems to be the best way to do it. I started designing the database
> > yesterday, it was easier than I'd thought.
> >
>
> Yes, but its numerically unstable.
>
>
>
> > I use one record if the balance or the interest rate changes. The
> > record has a from date and a to date.
>
> Storing the interest rate with the transactions is needlessly
> denormalized, and worse it forces you to create a new record for every
> client whenever the rate changes, instead of just one record for the
> new rate. I realize that it won't happen -that- often, and space is
> cheap, but this is 'ugly design'.

It depends on what the systme is tracking. If it's tracking something
like term investments, then the interest rate doesn't change within a
transaction, only at the start of a new term period.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: interest calculation

On Sep 13, 1:54 pm, Helpful Harry
wrote:
> In article <1189711748.063442.118...@19g2000hsx.googlegroups.com>, d-42
>
>
>
> wrote:
> > On Sep 13, 11:50 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> > > d-42 wrote:
> > > > For starters I wouldn't store the interest rate in the same table as
> > > > the transactions.
>
> > > > I also wouldn't recommend computing interest from 'transaction to
> > > > transaction', but rather directly on the interest period, regardless
> > > > of what the transaction history is.
>
> > > This seems to be the best way to do it. I started designing the database
> > > yesterday, it was easier than I'd thought.
>
> > Yes, but its numerically unstable.
>
> > > I use one record if the balance or the interest rate changes. The
> > > record has a from date and a to date.
>
> > Storing the interest rate with the transactions is needlessly
> > denormalized, and worse it forces you to create a new record for every
> > client whenever the rate changes, instead of just one record for the
> > new rate. I realize that it won't happen -that- often, and space is
> > cheap, but this is 'ugly design'.
>
> It depends on what the systme is tracking. If it's tracking something
> like term investments, then the interest rate doesn't change within a
> transaction, only at the start of a new term period.

Sure if he were tracking fixed rate GICs or something, then yeah, you
buy one at 4.5%, and at the end of the term you get your interest, and
it would be ok, even sensible to put the GIC rate as part of the GIC
record entry.

But he's not, if you look at the sample data, its pretty much a
standard bank savings account floating on a variable rate (or
equivalent mechanics). Deposits going in at random intervals, interest
rates fluctuating, etc.

-cheers,
Dave

Re: interest calculation

In article <1189724871.186242.122480@50g2000hsm.googlegroups.com>, d-42
wrote:

> On Sep 13, 1:54 pm, Helpful Harry
> wrote:
> > In article <1189711748.063442.118...@19g2000hsx.googlegroups.com>, d-42
> > wrote:
> > > On Sep 13, 11:50 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> > > > d-42 wrote:
> > > > >
> > > > > For starters I wouldn't store the interest rate in the same table as
> > > > > the transactions.
> > > > >
> > > > > I also wouldn't recommend computing interest from 'transaction to
> > > > > transaction', but rather directly on the interest period, regardless
> > > > > of what the transaction history is.
> > > >
> > > > This seems to be the best way to do it. I started designing the database
> > > > yesterday, it was easier than I'd thought.
> > >
> > > Yes, but its numerically unstable.
> > >
> > > > I use one record if the balance or the interest rate changes. The
> > > > record has a from date and a to date.
> > >
> > > Storing the interest rate with the transactions is needlessly
> > > denormalized, and worse it forces you to create a new record for every
> > > client whenever the rate changes, instead of just one record for the
> > > new rate. I realize that it won't happen -that- often, and space is
> > > cheap, but this is 'ugly design'.
> >
> > It depends on what the systme is tracking. If it's tracking something
> > like term investments, then the interest rate doesn't change within a
> > transaction, only at the start of a new term period.
>
> Sure if he were tracking fixed rate GICs or something, then yeah, you
> buy one at 4.5%, and at the end of the term you get your interest, and
> it would be ok, even sensible to put the GIC rate as part of the GIC
> record entry.
>
> But he's not, if you look at the sample data, its pretty much a
> standard bank savings account floating on a variable rate (or
> equivalent mechanics). Deposits going in at random intervals, interest
> rates fluctuating, etc.

That's just your assumption though. All we really have are a list of
dates, amounts and rates - that could be a normal bank account, it
could be a term investment rolling over at the end of each term (with
extra money added), etc.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: interest calculation

d-42 hat geschrieben:

>Storing the interest rate with the transactions is needlessly
>denormalized, and worse it forces you to create a new record for every
>client whenever the rate changes, instead of just one record for the
>new rate. I realize that it won't happen -that- often, and space is
>cheap, but this is 'ugly design'.

Guilty. It's a loan system with approx. one or two records per year,
and I can't put many hours in it , which is why I keep things as
simple as possible.

>However, if you compute them numerically, iteratively, the final
>result may well end up slightly different due to the differences in
>the accumulated round off error. This is *unacceptable* in financial
>systems.
>
>Moreover, what if they had deposited 5000 into an empty account on the
>1st. Then withdrew it all every morning, and redeposited it every
>afternoon. No competent bank would pay a cent of interest on that
>money, as the minimum daily balance was 0 every single day.
>
>Your proposed technique however, would pay them quite well. Again, I
>realize this is a perverse case, but financial software in particular
>should NOT be susceptible to problems like this.
>
>What it should do is loop over every day determining the minimum
>balance for that day, and compute the interest for that day at a
>specified precision, accumulating it and then rounding it off and
>depositing on the date at which interest is paid.
>
>An algorithm like that is numerically stable. If the minimum daily
>balance for the month is 5000.00. it will compute the exact same
>result whether there are no transactions, or 2,000,000 transactions.

I didn't realize these two shortcomings of my approach. I'll put
warnings in the documentation. I stick to my approach because of the
above mentioned nature of this project. I'd to it "right" in this
project if I had to work with the database myself so I could act as
beta tester and fix and improve the solution again and again.

Thank you for your input! I'll save this posting for the day when I'll
have to do a real banking system and get the proper budget for the
purpose.
--
http://clk.ch