Calculating interest earned on accounts

[Aussiebear]

Whilst there are plenty of software programs around which will calculate an compare your bank statements with that which they believe is the 'correct" interest accrued, not many actually show how they calculate the interest. What I'm chasing here is not just the code but also an understanding of the proceedure so that when I march up to the bank and say I think you've made a stuff up, I would like to indicate why. Bankers seem to react with scorn and large bullets when in defense of their "good" name. Any takers on this one?

[stanl]

Not knowing your particular bank, but banks are normally required to provide an APY (annual percentage yield), a periodic rate ( where rate x number of periods = APY), and a grace period ( for both deposits and withdrawals ). So for example, if the Bank states that deposits made before the 10th of the month earn interest from the 1st, you will get a different calculation than an average daily balance for the periodic rate. You would also need to know how fees and penalties are applied.

.02

Stan

[Aussiebear]

Stan,

My dearly loved bank, which shall remain nameless for the moment, is an off spring of a vampire. But then aren't they all??

Okay, Annual Percentage Rate is based upon the 90 day bank bill plus a margin of 1.8% for those who are financially minded.

Interest is calculated upon the unpaid daily balance of the account

So I need a spread sheet which shows a month by month ( including a day by day calculation) format of the principal and interest incured for the life of the agreement

Therefore I'm looking for a start of day balance, any deposits and or withdrawals, an end of day balance and the interest incured for any date withing the period of the agreement. Each day needs to be displayed for each month, and I need to be able to enter the bank's monthly statement and then compare the two.

I also need the ability to change the interest rate on any given day. I would like to be able to print out this monthly calculation on a set form layout and where possible to amend any given days data and have that create a flow on effect for the remaining period of the agreement.

Any thing else that you feel like adding would be a plus.

With Thanks
Ted

[stanl]

Ted,

APY and APR are apples and oranges. APR is normally used by Credit Card Companies to calculate interest owed, while APY is for interest paid. APR's generally fluctuate more wildly than APY's - [my friend's Master Card went from 12% to 27% after missing a single payment], while Bank savings rates are 2.5-4.1%, US current.

While I'm sure Excel could assist, I'm not sure what a starting point would be. I know with most credit card companies, if you carry a running balance, your payments are posted against the most recent purchases, so the average daily calculations might not match your calculations within a given billing cycle.

Stan

[Aussiebear]

I'll be using it to compare bank statements for a morgage. Whilst the interest rate may change from time to time, I know it is possible to write up simple statement like sets of data in a spreadsheet. Banks will take no notice of just a set of spreadsheets if its not audited and signed off by an actuary.

I would like to make it as automated as possible so that if and when an actuary has to go through the data, the cost will not be prohibitive.

[stanl]

Oh,

In that case just do a Google Search - "filetype:xls Amortization" (leave out the quotes) - you should get tons of examples.

Stan