Is Excel ready for negative interest rates?

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

image 91 473x201 - Is Excel ready for negative interest rates?

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’.

image 93 473x133 - Is Excel ready for negative interest rates?

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.

image 94 - Is Excel ready for negative interest rates?

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:

image 95 473x204 - Is Excel ready for negative interest rates?

Add a warning or stop with Data | Data Tools | Data Validation

image 96 - Is Excel ready for negative interest rates?

In this example, there’s a warning for any interest rate below zero.

image 97 - Is Excel ready for negative interest rates?

Highlighting Negative Interest Rates

There are many options for highlighting unusual Excel values, including negative interest rates. Here are a few:

image 98 473x139 - Is Excel ready for negative interest rates?

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.

image 99 - Is Excel ready for negative interest rates?

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

subs profile e1563205311409 - Is Excel ready for negative interest rates?
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address