Calculating interest due using bank statement in Excel

={Present_Value}*(1+{Interest_Rate})^{Number_of_Investment_Periods}

Simply replace the values in {} with your relevant figures. Since interest is calculated daily, you could try dividing the APR by 365 and having the number of investment periods as 365 for one year.

Marcus

In message , Marcus Fox writes

And how do you expect a novice at excel to do that?

Surely dividing by the nominal rate, not the APR, is the thing to do?

It needs a calculation for each day. Simply =(celreferenceoftodaysbalance)*(interestrate/100/365)

for each day. Then sum them.

The important point to watch is that the downloaded statement does not have a row for each day, just a row for each transaction, so rows will have to be inserted for each day in which there is no transaction and the balance from the previous day copied down. When this is done, the formula above can be input in row one and copied down then summed. The summation should occur whenever the interest is capitalised then added to the balance on the day the interest is compounded by the institution.

"Marcus Fox" wrote in message news:2s7Lf.174$ snipped-for-privacy@newsfe6-gui.ntli.net...

I may have got the wrong idea, re-reading your post, you seem to want to be able to calculate the interest rate based on the changing daily figures. Above is the formula for compound interest on a given principal amount. Basically banks calculate the interest on the daily cleared balance, so if you have balance figures for each day in the year, you would need to do this formula for each day, having the APR divided by 365. The number of investment periods is only going to be one, if you do it for each day and simply sum the interest at the bottom, so you will only need ={Present_Value}*({Interest_Rate}/365) for each day.

This may not be ideal for you, since you will have different periods of time between items on your statement, and may not have entered the balance for every day. As mentioned above, you would need daily figures to make that work.

Marcus

Bitstring , from the wonderful person Marcus Fox said

I think his problem is that the present value keeps changing. A rough estimate of the average amount in the account can be obtained by sum((datenow-datelast)*balancelast)/(totalnumberofdays)

(totalnumberofdays is just (lastdate-firstdate))

Well, I did explain that these needed to be replaced with his figures. I imagine he could refer to the formula as he types it in with his values, even if he didn't know how to copy and paste and delete all within {}. I would expect a novice with excel, especially one who uses it to track bank figures to be able to cope, and if not, it's very easy to ask to simplify what I meant.

Admit I did get the wrong idea with regard to what he was looking for first time though, have added a post that came through just after yours.

Marcus

In message , Marcus Fox writes

No, its not the APR divided by 365, its the nominal rate of interest/365.

Thanks, everyone, for their replies.

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.

David

In message , Lobster writes

It wouldnt be sufficiently accurate to be useful.

You will have to delete anything other than the last transaction per day.

You could add extra columns using an 'if' statement so that only those lines which had a date different to the one preceding it were copied to the new columns, and then sort those columns to remove the blanks, but with the greatest of respect, and no offence intended, would you be able to do that?

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.

NB - InterestRate being the annual rate, not APR. Enter it as a decimal eg

"john boyle" wrote

Agreed. A useful approximation might be: (1+APR) ^ (1/365) - 1 ...

Hmm, probably not! No offence taken: that's why I pointed out I wasn't an Excel expert! I should be able to handle formulae, but once we get into IF - THEN loop territory I start losing it... anyway, I'll hopefully have a play over the weekend with the various suggestions, and see how I get on. Thanks all.

David

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