Swiss Bob’s favourite place of work – not actually in the main building, we were fortunate to be about 50m away where we couldn’t be seen staggering back from 2-4 hour lunches
This is going to be a bit like Monday morning maffs but who knows if it will be published on a Monday. Having written part of the code for this in two banks I may plausibly claim some knowledge.
Interest Calculation as practised by banks, simples innit. If you have £1,000 in your account and you get 3.25% interest at the end of the year you will have £1,032.50 in your account. Bankers are a subset of accountants and because this is easy to understand, it must be made more complex and mysterious so only the initiated can understand what is going on. I also hope to explain why it is almost impossible for you to check their calculations.
Theoretical Calculation
For your bog standard account you need the balance history and the interest rate history. Your account will be attached to a certain interest rate when it is opened based on currency and type of account. The banks get the interest rates from Reuters every day and there are a lot of them.
Now you need the account balances. That’s easy, I see it on my statement. Oops, each transaction has two dates, the accounting date (the date it happened) and the value date (the date when the transaction amount is credited to or debited from your account. The interest calculation uses only the value date, your statement shows you the accounting date. Often they will be the same, but not always. If you deposit cash the value date may be the following day, perverted banking logic “we have to process it before it can go in your account”.
Next you need to know when your account gets calculated. It might be every month, every three, every six or every year. If your £1,000 is calculated every 3 months it would be roughly (we’ll do a real calculation later) £1032.90, a gain of 40p over calculating every year. As Einstein said, the most powerful force in the universe is compound interest.
| Date | Balance | 1Year | 1Year / 4 |
| March | 1,000.00 | 32.50 | 8.13 |
| June | 1,008.13 | 32.76 | 8.19 |
| September | 1,016.32 | 33.03 | 8.26 |
| December | 1,024.57 | 33.30 | 8.32 |
| Total | 1,032.90 |
The 1Year column is the balance times 0.0325, the 1Year / 4 is allegedly 3 months worth of the Year. The 40p extra in the Total (compared to one year = 1,032.50) is Einstein’s most powerful force in the Universe.
The interest per calculation shown there is just the annual amount divided by 4. That would be far too easy for the bankers so they do it slightly differently.
Real World Calculations
This is the input data, there are two sets, one for the balances, the other for the interest rates. Note that the interest rate change on 1st April does not match a balance change and an extra calculation will be needed just for that
| Element | Date | Balance | Date | Interest Rate |
| 1 | 2019-01-31 | 12,357.50 | 2019-02-01 | 7.15% |
| 2 | 2019-02-16 | 3,112.00 | 2019-04-01 | 7.35% |
| 3 | 2019-03-05 | 18,527.00 | 2019-04-30 | 7.35% |
| 4 | 2019-03-07 | 20,325.50 | ||
| 5 | 2019-03-19 | 2,112.30 | ||
| 6 | 2019-04-08 | 3,112.40 | ||
| 7 | 2019-04-30 | 8,112.50 |
There are a couple of points to bear in mind. We always worked with decimals to 8 places and using a yyyy-mm-dd format for the date makes it easier to get the data in sequence.
The 1 Year interest is a full years interest for that balance at that rate. See below for the 30/360 explanations but the 1 day interest is the year’s worth divided by 360 in this case. It could have been 365. This is also using the 30 day month so 16 Feb to 5 Mar is 19 days rather than the 17 you would expect. The Period Interest is just 1 day’s worth times the number of days. For those unaware of it, # is used to mean number of in the arcane world of IT and probably a few others.
| Calc# | Date | Balance | #Days | InterestRate | 1 year Interest | 1DayInterest | PeriodInterest |
| 2019-01-31 | 12,357.50 | 7.15% | |||||
| 1 | 2019-02-16 | 3,112.00 | 16 | 7.15% | 883.56125000 | 2.45433681 | 39.26938889 |
| 2 | 2019-03-05 | 18,527.00 | 19 | 7.15% | 222.50800000 | 0.61807778 | 11.74347778 |
| 3 | 2019-03-07 | 20,325.50 | 2 | 7.15% | 1,324.68050000 | 3.67966806 | 7.35933611 |
| 4 | 2019-03-19 | 2,112.30 | 12 | 7.15% | 1,453.27325000 | 4.03687014 | 48.44244167 |
| 5 | 2019-04-01 | 2,112.30 | 12 | 7.35% | 151.02945000 | 0.41952625 | 5.03431500 |
| 6 | 2019-04-08 | 3,112.40 | 7 | 7.35% | 155.25405000 | 0.43126125 | 3.01882875 |
| 7 | 2019-04-30 | 8,112.50 | 22 | 7.35% | 228.76140000 | 0.63544833 | 13.97986333 |
| Total | 90 | 128.84765153 |
This is more like a real calculation with the balance changes and an interest rate change. Now there are two things you probably won’t know about. There is a days per month definition of 30 or 31 and a days per year definition of 360/365. the 30 days a month (favoured by accountants) means all months are calculated with 30 days, even February, 31 days a month means it is real days. The days per month is used to calculate the number of days between two dates. the 360/365 are used to divide the interest for a year into that for a day, this is then multiplied by the number of days between the two dates to give the actual interest for that period. Obviously, if the balance is below zero, they used the debit interest rate, always much higher than the credit interest rate.
See the Excel DAYS360 function for more details on 30 day months. It seems there is also a US and a European version of this function, those accountants eh.
This calculation was done using the 30/360 options.
Not quite as simples as you thought but just about followable. You will not be told by the bank if you are on 30/31 or 360/365. I asked one of mine here (HSBC as you asked) and they didn’t have a clue what I was talking about. Add the fact that you don’t get value dates on your statement and you have zero chance of checking their calculations.
Business Accounts and people with big ones
If you are a big enough company or someone with millions and millions you get even more options. These are called variations and tranches. A variation is a negotiated difference to the normal interest rate. It is normally used in conjunction with tranches, You could agree with the bank that first 100,000 of your balance is calculated with the normal rate. You might get a 100,000 to 500,000 tranche with a +0.1% variation and everything over 500,000 gets a +0.2% variation.
This means that when the interest is calculated the balance will be split into 3 chunks (assuming the balance is more than 500,000) and each chunk is calculated separately then added together.
This is the data for one calculation. There is a balance of just over 2.5 million at 4% for a 13 day period.
The tranches are set and their variance (the variance is added to the basic interest rate).
| Date | Balance | #Days | Interest |
| 2019-08-12 | 2,580,532 | 13 | 4% |
| Tranche1 | 100,000 | 0.0% | |
| Tranche2 | 500,000 | 0.1% | |
| Tranche3 | 99,999,999 | 0.2% |
Here is the calculation for one period. The top row shows what it would have been normally, the total at the bottom is the sum of the three tranches. By using the tranches the customer gets about 164 more of whatever currency it is.
| Balance | ActualBalance | #Days | Rate | 1Year | 1Day | Period | |
| 2019-08-12 | 2,580,532 | 2,580,532 | 13 | 4% | 103,221 | 286.72577778 | 3,727.43511111 |
| Tranche1 | 100,000 | 100000 | 13 | 4% | 4,000 | 11.11111111 | 144.44444444 |
| Tranche2 | 500,000 | 400000 | 13 | 4.1% | 16,400 | 45.55555556 | 592.22222222 |
| Tranche3 | 99,999,999 | 2080532 | 13 | 4.2% | 87,382.344 | 242.72873333 | 3,155.47353333 |
| Total | 3,892.14020000 |
There is only one calculation period here otherwise it will get too messy to show. You can see the initial values being chopped into 3 tranches with each tranche being calculated separately. Each tranche has its interest rate with the relevant variance. I forget where it was but there was one customer who spent ages over their hot spreadsheets trying all sorts of variations to get an extra cent or two and their variance and tranches changed every week or two. I guess the bankers took one look at the mega balance and thought we need to keep this one happy. The variances and tranches are entered manually by bank employees.
Pro Rata calculations.
This where the magic of banking takes over. Your account may be calculated every three months but every day it is earning interest. You only see the interest every three months but this gives the bankers no pleasure. They have overnight dealers who lend tens or hundreds of millions to other financial institutions for a few hours. The interest charged is fractions of a percent but on millions and millions this is a lucrative earner.
Maybe you see where this is heading. The banks calculate every account every month. Those scheduled to get their interest get it, the others are called pro-rata calculations and are added together and the figures passed to the dealers as yet more money they can lend. In the Swiss bank where I worked on this they were talking of doing this every week. Probably do it every day by now. Talk about creating money out of thin air.
Retro-Active calculations
Just when you thought that was it, along comes a joker. As stated previously, a transaction uses its value date to get a slot in the account balance. Both banks where I worked on this already had interest calculation but if a value date was before the last calculation, it could not be inserted in its correct place but was deemed to have a value date of the last calculation date plus 1. Well, it probably doesn’t happen very often but when you have millions of accounts, it happens. Both banks decided that inserting it in its correct place would be wonderful.
For example if the account is calculated monthly and we are in April, a value date of February affects a previously calculated period. How do we get round this. After keeping the correct value date, we recalculate February, March and then do April as a normal month. But those first two periods have already been calculated, the balances will now be different and the credit and debit interests will be different.
Never fear, there is a solution. We have the results of the original calculation, they are stored in the original calculation data, we now have the results of the revised calculation. It is simply generating payments with difference between the two. Here is where it got very messy for the bankers (collective noun wunch) who have spent their lives with the golden rule credit interest is a positive number, debit interest is a negative number. If the original calculation gave a credit result of £100 but the revised calculation gave a result of £75, you get a credit interest difference of -£25. It took the analysts several attempts to get the explanation across to an incredulous audience, some of whom had requested this possibility. Bankers, I ask you (remember wunch). The same applies to debit interest (always negative they thought) until it turns out you can have debit interest that is positive (i.e. the customer paid more in the original calculation than in the revised one so he has to get some money back.
Odds and ends
After all the calculations are complete, a rough profit to the bank is calculated. Each type of interest rate, and there are hundreds, has something called a reference rate. This is the bank’s best guess at what it costs to have the money available. If they are charging debit interest of 6%, they may decide it costs them 4% to get the money (that is roughly what they are paying the punters who deposit the money). Two of the six percent they charge the customer is profit and goes straight into their voluminous pockets or perhaps out to the infamous dealers to play with.
They also had a five year limit on calculating accounts. To explain, some accounts get suspended, the famous ones in the swiss bank were of course Nigerian, and after about 10 years the accounts were unsuspended and needed to be calculated. With the previous 5 year limit they could only have started at 5 years ago, now they could do the calculations correctly. I think the only limit on this was whether the supporting data was still available. Rather amusing watching calculations for the 1990s running in the 2000s.
It’s their money not yours. I was working on this in a bank in Belgium when the Euro was introduced. I still remember the big email from the big boss when the EU said Euro to Euro transfers across national borders are to be treated the same as internal transfers. The big boss was horrified, the EU were stealing his money since previously, they had a nice little earner transferring Belgian Francs to Deutschmarks in Krautland. He really took it as a personal insult.
Precious Metal Accounts
More than enough people use the bank to store gold, platinum, silver and various other metals for them. Bankers being bankers, these are also accounts. Everything in a bank is an account just like everything in an insurance company is a policy. These accounts are never selected for interest calculation but they do get processed by a sister application, the charges application, as do all other accounts, bankers are very fond of their charges. Charges on precious metal accounts are eye watering but I supposed the bank has to pay for its James Bond like vaults underground.
Customers querying their interest
It doesn’t happen that often, I think, because I only had to do it once, or at least it seldom got all the way back to the programmers (in a bank you are level 3 support as well as coding for a living). It was a company in Belgium that had millions and millions in the bank. They had expected more interest than they got. Muggins got the job of checking it out. That meant extracting shed-loads of data into a spread sheet and doing the calculations. They had had a retro-active debit and this had dropped them out of the top tranche for a while, something they hadn’t realised. The end result was that the bank’s calculation was correct. Glad I didn’t have to explain it to the customer.
And so …..
The world is a funny place made even funnier by the machinations of accountants. In their desperation to make everything ever more complex, they sometimes end up confusing themselves.
Working on utility bill processing must be several times worse, that really is a can of worms.
Featured Image. DLR / Barclays, HSBC, Images George Rex – Licence CC BY-SA 2.0
© well_chuffed 2019
