Personal finance 'stress-testing'

Astraeus

Evening all.

I like to keep my finances organised by Excel but I am coming unstuck with my latest spreadsheet idea - a financial 'stress test'.

The idea is to create a spreadsheet where several changeable values could reflect real-life scenarios such as rising interest rates, reduced house prices, unemployment, exhaustion of savings, in order for me to arrive at the limits of where I would be comfortable or above water. The spreadsheet is intended to look at the effect on servicability of my mortgage and other credit agreements as well as matters such as the LTV on the house and interest accruing on savings.

Before I set off on an exciting Excel adventure, has anyone done anything similar in which case would anyone be prepared to volunteer any tips or experiences?

Many thanks.

EssexExile

My initial response would be "too many variables". Anything could happen from "everything going bad" to "everything going good" & many positions in between.

Astraeus

A huge number of variables, I agree. My current thinking on the list to be provided for is:

1. Interest rates on savings;
2. Mortgage SVRs (both current and best on market);
3. Credit card APRs;
4. House price index (both Halifax and Nationwide);
5. Employment status;
6. Salary;
7. Salary deductions; and
8. Outgoings (i.e. increased living costs).

All are provided for on sliding scales covering "very good" to "very bad", i.e. salary at nil for a circumstance of non-employment to unlikely pay increases, or, for interest rates, from negative to in excess of 20% (or 40% for APRs) with the percentage change and the base value feeding into the summary calculations such as "Mortgage repayments as % of monthly net income" and "Credit card repayments as % of monthly outgoings" as well as calculating LTV on the property. I am sure, as I build it, more 'outcomes' will become apparent and be catered for.

meer53

Your spreadsheet would cause me stress ! I much prefer to deal with things as they happen than worry myself silly about stuff that might never happen. My tip would be to keep a simple log of income and expenditure and make sure you have savings to cover emergencies.

jimjames

Rather than worrying about credit card APR repayments wouldn't it be better to focus on not having credit card debt and building an emergency fund? Always worth considering what-if but I can't see it makes much sense to do so with so many items. Credit card APR for example would have zero impact on me whether it's 10% or 40%.

Astraeus

Yes, jimjames, but that is precisely the point.

An ideal case scenario would be (as is the case now) me having no credit card debt. A stress test has to look at a worst case scenario which (for instance in the event of short term unemployment) would likely necessitate credit card borrowing and thus the worst case scenario has to take it into account, together with the exhaustion of an emergency fund.

LXdaddy

Astraeus wrote: »

The idea is to create a spreadsheet where several changeable values could reflect real-life scenarios such as rising interest rates, reduced house prices, unemployment, exhaustion of savings, in order for me to arrive at the limits of where I would be comfortable or above water.

I can understand the variables that you would want to flex but what sort of "output" would you want to see?


Let's say
current case is no credit card debt,
mortgage of £m,000 at x%interest,
cash savings of £C,000 earning y% interest,
monthly income of £S
fixed monthly spending of £F
discretionary monthly spending of £D


and perhaps a few more.


What is the number you would want to look at if you (say) changed the interest rates or changed the monthly income


If you can explain what you would want to use in your test on whether you would be "comfortable or above water" then maybe it would be possible to build a model

MisterMotivated

I've done some stress testing on a couple of things such as losing my job, tracker mortgage increasing, etc. My spreadsheet has three budget tabs; standard (with lots of flexibility built in), extreme (being a total tightwad for the year) and emergency (losing my job), which I've created to see when I might have to implement some 'emergency measures', but haven't felt the need to include things like house price reduction. Surely the change in available rates due to a lower LTV would be limited to the SVR as if, for an extreme example, you went from 26% down to 8% LTV, you wouldn't necessarily have to switch to a new (worse) deal; you could stay on the SVR.

As others have suggested, there seem to be too many variables to make it meaningful. For example, will you factor in a reducing savings balance earning interest at different rates in different accounts, due to credit card interest rates increasing? What about if you sacrifice savings to clear the CC debt and avoid massive interest?

bigadaj

Sounds like a full Monte Carlo analysis would be required for the OPs requirements. A number of factors with a range of scenarios at defined increments modelled with each of those increments being varied which would be reported as a probability outcome.

I've done this for scientific and engineering outcomes using bespoke packages but am not aware it can easily be done using basic software, maybe that's an opening in the market or more probably just a case of over analysing an individual's situation.

A Google search shows examples on excel so maybe follow some of those?

Ben8282

What is the point of it all?
So you put all the info into the spreadsheet and it tells you the point at which you will become financially stressed. But to what end? To give you something to worry about?

Lingua

I use excel to keep track of my money, as well as to create a projection of where I will be in a few years' time. Perhaps something similar could be used in your situation with a few tweaks? For example, having an expenditure row for "mortgage", with each cell having a formula that calculates your repayment based upon the interest rate you enter in a separate cell. It probably would not be too difficult to then remove your income from the mix, and see how long it would take for your savings to run dry?

Please do discuss this more as it's an interesting idea that could be useful for those who are possibly at risk of being out of pocket when (if, at this rate!) BoE interest rates rise.