From: Lobster 
Newsgroups: uk.finance
Subject: Re: Calculating interest due using bank statement in Excel
Date: Sun, 26 Feb 2006 15:56:47 GMT

Martin wrote:
> "Lobster"  wrote in message 
> news:rfpLf.15158$gB4.10305@newsfe4-gui.ntli.net...
> 
> 
> 
>>I was rather hoping I wasn't going to have to fill in all the blank dates 
>>where there are no transactions (because I want to carry out the 
>>calulation over enough months to make it representative); but apart from 
>>that, how would I handle days where there is more than one transaction? 
>>Each transaction appears on its own row on the spreadsheet.
> 
> 
> In a blank column, in row 2, enter...
> 
> Interest = Balance * InterestRate * ( ( DateRow2 ) - ( DateRow1) ) / 365
> 
> Replicate this formula all the way down, then sum the Interest at the foot 
> of the column.
> 
> Enter InterestRate as a referenced fixed cell eg $F$1, so you can tweak it 
> later.

Brilliant!  That works perfectly.  It's very neat, because where you 
have two adjacent rows for two transactions on the same day, then 
(daterow2-daterow1) is going to be zero, therefore the interest earned 
is zero too.  The only time (daterow2-daterow1) is not zero is for the 
last transaction on a day, and in that case, the interest is not zero.

And I think I've just discovered I need to change my current account to 
one earning a decent rate of interest, too!

Cheers
David