Calculating daily interest expense

[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 (Payment) -100
02/26/04 (Payment) -100
03/15/04 1,000
03/28/04 1,000
09/01/04 (Payment) -300
09/01/04 (note - same day - many like this) 100
12/31/04 (Payment) -3,000
12/31/04 (End Bal) 48,900

How do I calculate the interest on a daily basis?

If possible I would like to use excel functions, but if not possible I
need help with the macro.

Thanks for the help!

 

[Dick Kusleika]

I assume the positive numbers are draws. Do you want to know how much
interest is accrued each day, or do you want to update the balance each time
a transaction is posted. Here's one way: Assume your data starts in A5.
in D5 put

=C5+D4 and fill down

In E6, put

=D5*$A$1*(A6-A5)/365

where A1 is the interest rate and fill down. This will give you the accrued
interest at each transaction date. You can sum this column to get the
interest for the whole year. You can also add this interest to the balance.

 

[tr2yhb]

Thank you! I will use that formula for the next 20 years - and I am
not kidding!