Calculateing daily interest

[tr2yhb]

I have a series of loan transactions that I need to calculated the
daily interest on.

Example

12/31/03 (Beg. Bal) 50,000
01/08/04 100
01/10/04 100
01/26/04 100
02/22/04 -100
02/26/04 -100
03/15/04 1,000
03/28/04 1,000
09/01/04 -300
09/01/04 (note - same day - many like this) 100
12/31/04 -3,000
12/31/04 (End Bal) 48,900


How do I calculate the interest on a daily basis?

Thanks for the help.

 

[mjbigelow]

Here's how I would do it:

A B C
12/31/2003 50,000 6% <--- Type the Annual Interest Rate Here
1/8/2004 100 $8.24 <--- Type this formula here:
=SUM(C$1:C2)*(E$1/365)
1/10/2004 100 $8.25 **Fill the above formula for as many rows as you
have**
1/26/2004 100 $8.27 **This should calculate the amount of interest
owed
2/22/2004 -100 $8.25 at the end of each day. Just put a total at
the bottom
2/26/2004 -100 $8.24 to find out the total owed.**
3/15/2004 1,000 $8.40
3/28/2004 1,000 $8.56
9/1/2004 -300 $8.52
9/1/2004 100 $8.53
12/31/2004 -3,000 $8.04
12/31/2004 48,900 $16.08

If the above table looks funny, paste it into a text editor.

Let me know if that works.

Mark