(A bit boring now... money, numbers but what I describe below troubles me... have a read) 


A few years ago it transpired that the policy of cuts and austerity based on Armageddon calculations by two Harvard economists, Carmen Reinhart and Kenneth Rogoff, was fundamentally flawed and based on an Excel coding error. Is the PMT Excel interest formula and its daily application in the finance world an even bigger error?

No this is not about the favourite architectural home style of Osama Bin Laden and US Embassies in the Middle East... This is about a permanent "invisible" effect that is present in all arithmetic calculations that include two entities... Excel and Finance; I have to add these are two of my favourite pet hates.

You can see here on this Microsoft Excel training on Formulas

Have a look and see what is wrong...

I shall carry on... this has to do with the most used function in Excel, PMT, no not that regular monthly tension that afflicts one third of women, but an ignored tension that afflicts the entire world of accounts. At minute 2:15 there is an example "use 6% divided by 4 for quarterly payments at 6% APR" and later 3.35% divided by 12 since we are going to make monthly payments". There is a small problem with this, actually is is quite big... it is the well known compound effect.

I may have lost half of my audience by now but hang on in there.

I start with a numerical example, with a deposit because it is easier to understand interest on savings: if you place a deposit of $100,000.00 for one year at 10%, after 12 months when it matures you will have $110,000.00. You have 3 main options after the deposit matures... you recall the lot, you recall the interest or you roll it (called Principal and Interest) for another period. If you choose option 3, after another 12 months you will have $110,000.00 plus 10%, $121,000.00, the interest has been compounded. This can be calculated for any number of years (if you never recall your deposit) with a mid-complicated mathematical formula:

F = P (1 + R) (power of N)

F — Final amount
P — Original amount
R — Rate
N — number of periods  (week, month, year)

I wrote power of N to signify that the expression (1 + R) is multiplied by itself N times i.e. squared for 2 years, cubed for 3 and so on.

If you left a deposit for 10 years and compounded it, the calculation would be:

F = P (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)  (1 + R)

For $100,000.00 at 5% yearly interest, you would receive a total compounded final amount of $162,890. This works perfectly for deposits, for interest gain. However it does not work for loans, the compound effect is overlooked.

Now instead of making a deposit you apply for a loan, same $100,000.00 at the same 5% for 10 years. You make monthly fixed repayments and the PMT formula gives you an amount (calculated on 10% divided by 12!!!  as you saw on the Microsoft video and where all errors reside) of $1,060.65 with a total of payments of $127,279.

However if you save monthly $1,061 for 10 years (i.e. if you could save $1,061 monthly) at 5% rate you would end up with $164,599!!! It is an enormous difference against $127,279, $37,320 to be more accurate. I don't like this. It appears that for the same amount of money a bank will lose $37,320 after ten years or at least will start with a loss of $37,320 that they will have to recoup over that period. 

I made an Excel table of loans below. I know the amount is a bit stratospheric ($1 billion) but in the big scheme of world finance it is quite common.

You can see that if you make monthly repayments on loans there is always an overpayment "dribble" on the right column. There is always more money present in the system than the one that is really accounted for. In financial systems with low interest, that is almost negligible but it is there. $1 billion generates an extra unaccounted $45,960.89 each year, "ghost money" that will have to be somewhere and, if not taken into account (literally), will somehow cause worldwide unbalance (literally) on financial systems as it does not add up (literally again). On a 6% interest rate environment you can see that $1,677,811.86 of "ghost money" is generated, an already significant 0.116%. 

In my view this "ghost money" is what creates inflation and currency devaluation.

On societies or systems with high interest rates the "ghost money" creates utter chaos. At 130% interest will create a bulky 113% of "ghost money" in one single year, which is more than the original loan. That is the reason why most countries with high interest have by default abysmal inflation and an almost worthless currency.