Tuesday 7 August 2007

Calculating the Decline in Net Worth from "All Time High"

I normally update the component figures (stock portfolio, retirement account) of my investments daily, but only calculate month-to-month changes in my net worth. Having had a couple of down months in a row I was interested in calculating where my net worth is in relation to my personal "All Time High" (reached on 20th June). I track my investments using an excel spreadsheet, so to calculate the current situation in relation to the "peak" value I need to know both the maximum value in a column of data (easy using the "MAX()" function) and the latest value in the column. The second figure isn't quite as easy to automatically calculate - there's no simple function (you might expect "LAST()" to do this, but no such luck), so you need to use a fairly complex function:
=INDEX(B3:B6500,MATCH(9.99999999999999E+307,B3:B6500))
This for numeric data in column B, where the data is in rows 3 to 6500. For example, today's data is in row 1408 and my spreadsheet is currently setup with dates out to my expected retirement in 2027 (row 6500).

To check how the current value compares to my "All time high" value I calculate:
=INDEX(B3:B6500,MATCH(9.99999999999999E+307,B3:B6500))/MAX(B3:B6500)

Using this calculation in my various spreadsheet tabs (one for each portfolio component) shows that my current situation is:
Current Property Equity = 98.23% of all time high
Current Retirement Account value = 94.84% of all time high
Current Stock Portfolio value = 88.85% of all time high

Overall, current net worth = 93.87% of all time high

Although I'm fairly sanguine about recent drops in the stock market and the post-boom slump in real estate prices (they've been stagnant in Sydney for the past 4 years, and the prospect of further interest rate rises means the recent mild recovery is likely to come to an end), it highlights the fact that I really haven't suffered a major decline in my net worth. Even back in '87 when I only had a tiny investment portfolio, mainly invested in stocks, the "crash" only wiped about 25% off my net worth at that time (I also had a "defined benefit" retirement plan which wasn't affected and I didn't count in my net worth calculations - just as well as my employer converted the plan to a defined-contribution scheme prior to laying off a large chunk of the workforce in the late 90's).

It makes me wonder how well I'd cope with a total melt-down across all asset classes (such as happened in the great depression). What if my stock portfolio was down to only 10%-20% of it's peak value, property was unsaleable (and valuations down 50%), and I lost my job? Hopefully the decline would be gradual enough to have some chance to unwind my geared positions - selling off shares to eliminate my margin loans and selling our investment property in time to pay off our home loan with whatever equity remained. The tricky part would to know when to "pull the plug" on debts. The temptation would be to first view a decline in asset values as a correction (10-15% down), and then as a bear market/housing slump if the decline extended to a 20-40% drop over many months or years. In such cases selling out would be the wrong move, while holding your position (or even buying more at the "bottom") would be a good long-term move. But, if such a recession turned into a full-blown depression then a buy-and-hold mentality could easily lead to bankruptcy.

One can only hope that the world's central banks know enough to avoid a global depression, just as one hope's there won't ever be another "world war" and that global climate change doesn't need to be factored in to personal financial plans! In the absolute worst case my "Plan B" is to move the family up to my parent's farm - they have a spare shed we could live in, a rainwater tank and bore water, so we could grow our own food. This is also the "plan" in case of WWWIII, SARS or bird 'flu ;)


Copyright Enough Wealth 2007


No comments: