Are your Excel worksheets ready for negative interest rates? The world is seeing historically low interest rates. So low that they can drop to zero or even lower rates.
Not only can Excel worksheets break with unexpected negative percentages but modelling can accidently move into negative territory. Interest rates might get more precise going to three decimal places.
Negative interest rates aren’t a crazy idea. A few banks are now ‘charging’ negative rates. That means you pay the bank to keep your money.
Bank of England warning
The Bank of England (base rate now 0.1%) has written to UK banks reminding them to check their IT systems and processes for the possibility of ‘upside down’ interest rates.
As well as big banks, everyone else should cast an eye over their worksheets and contracts for a negative or zero rate situation.
Some complex workbooks might give wrong results or fail entirely if the Interest Rate setting is zero or below.
Errors on worksheets are tolerable, because they are easy to see. The bigger worry are incorrect calculations that show bad results but no obvious error.
Negative Interest rates are a controversial move, to put it mildly. However, they were a faint possibility at the start of 2020. With Coronavirus, seemingly unthinkable economic measures are now mainstream.
The ‘Good’ News
For most people, negative interest rates are unlikely to affect them directly.
Bank deposit rates for individual investors aren’t expected to go negative. 0% deposit rates, maybe but not below that.
Most mortgage contracts have a floor or lowest possible rate. It’s worth checking to see what that lower limit is. It could be 0% or a little above.
Excel and negative interest rates
Negative Interest rates can give bad results. Check out the Mortgage Loan calculator worksheet, provided by Microsoft from Excel | File | New.
It allows a negative interest rate with no warning. As you can see, the Total Interest Paid is a negative number – meaning that the bank would be paying you.
The Amortization Table shows this clearly. The monthly principal payment is reduced by the negative interest ‘payment’.
We’re not being critical of the Microsoft workbook. It was made in very different times and the assumptions made by the developers were quite reasonable.
Beware rate ranges going negative
A financial model often tests a range of values or interest rates. For example, testing rates above and below a set rate.
That can be a problem when the lower part of the range drops below zero.
For example, a test range of +2% or –2% when the base rate is a mere 1%, puts the testing range from 3% down to -1%.
Enough decimal places?
As interest rates get low, the percentage might drop to thousandths or three decimal places. Instead of rates like 0.5% or 0.25% there could be rates like 0.125%
Make sure your cell formatting can properly display up to three decimal precision. The Percentage formatting default is two places. Change that at Home | Number | Format Cells.
A quick fix
The fast fix is to add either conditional formatting or a warning on any interest rate data entry cell.
A conditional formatting warning can look like this:
Add a warning or stop with Data | Data Tools | Data Validation
In this example, there’s a warning for any interest rate below zero.
Highlighting Negative Interest Rates
There are many options for highlighting unusual Excel values, including negative interest rates. Here are a few:
Row 2 – normal positive interest rate – for comparison
Row 3 – negative rate with the default settings of a minus sign or brackets.
Row 4 – the % and Interest in Red. Do this using a Custom Cell Formatting with [Red] as part of the negative formatting.
Row 5 – added a cell background (Fill) with Conditional Formatting.
Row 6 – adjacent warning text with an IF statement.
=IF([@[Interest Rate]]<0,"Alert Negative %","")
8 tips for great Excel Sparklines
Convert Excel formula into fixed values or text
Using 3D Formulas to range across Excel worksheets
Excel for iPad – Formula Keyboard
