my spreadsheet obsession, etc.

[wodehouse]

so, with the thread about YM demographics, I guess I am one of the older residents here (I can think of a few that I know are older than me). I am 61.

I have always been interested in "personal finance" as a general topic and especially in my own life. Being of an inquiring, mathematical-minded persuasion I have analyzed all sorts of issues and questions from a mathematical standpoint trying to find the "optimal" solution. (to my chagrin, or not, I even inadvertently "talked" a young co-worker out of buying a new car for his young family by giving him my paper worksheet for "an affordable car", then again, maybe I did him a favor).

The development of computer spreadsheets really fired up my obsession. I have spreadsheets to do everything.

And there has been nothing like turning 60 (and now 61) to concentrate my thinking on retirement. And now my thinking is not so much as "how much money will I need for retirement"...since I only have a few more working years left I'll have to just work with whatever I have...in fact, I can pretty much calculate what my retirement nest egg will be, but now I am looking more at how to distribute this nest egg over the (hopefully) many years of retirement. I've gone through this exercise in a pretty detailed manner maybe 8-10 years ago, and I have always run through those retirement planners that show how long your money will last based on various variables (Vanguard had a real nice one issued on diskette back in the '90's).

But since there are more women on YM than guys, I suspect that there aren't so many folks possessed by spreadsheet mania like I am.

I remember this one young guy at the old YM. He had a really fancy budgeting spreadsheet he desperately wanted everyone to try. It was too gimmicky with too many colors (cells and text) for me.

Anyway, I digress. So, does anyone have good ideas on developing a method to draw down ones assets over the unpredictable years of retirement? I would like to hear.

   

[wodehouse]

One thing that was not clear to me, and apparently isn't clear to the people creating these on-line retirement calculators, is that my wife, who's never worked under SS, will be eligible for a SS benefit of 50% of my own amount once she attains retirement age. But this seems to be the case (new documents at ss.gov make this clear to me now).

So that is nice, more income!

But it throws in some interesting wrinkles. DW is 11 years younger than me. So it'll be quite some time before that extra "bonus" money kicks in. But I can account for this in my spreadsheet.

And her age presents another wrinkle. Say I croak before she attains her retirement age. Then she'll lose out on my SS, which ends at death, and may get her own for a number of years! oops! Got to plan for that possibility.

And SS itself. In my spreadsheet I am providing for a factor so that the full amount projected on the SS website is not assumed. In other words, I'm assuming that I won't get the full SS due to funding issues. I assume that the "loss" will increase over time. Trying to be conservative.

[wodehouse]

Oh, yes, many of those on-line retirement planners do not automatically account for spousal SS benefits. Seems to me that they should.

[Deleted]

Wodehouse,

If I recall, you had a pretty good NW. I tried looking you up at Networth IQ and the site is wonky AGAIN!

I don't recall what you're invested in and that is likely to matter. If Uncle phil5185 chimes in he's likely to tell you to keep it simple, like put yourself into a 60/40 split and draw down 4% a year. Or take a look at one of those named retirement MF (say Target 2017) see what they have and craft a personal retirement MF accordingly.

DH's and my retirement plan is a little complicated because of our investments. For now we are just living on the income of our investments. This partly due to how long we are likely to be retired. 45 years for DH and 48 for me. We are using the services of USAA Wealth Management to do some modeling. At some point we expect our oil revenues to run out and we're going to need to cash in and invest a rental house. The WM team can do some modeling using the Monte Carlo strategy to ensure we have a high probability of success and tweak our holdings.

[wodehouse]

When I'm feeling *really* negative, I don't include any SS. But that's probably too pessimistic. For now. 

[shanendoah]

I also love spreadsheets. I call it "playing" in Excel.

With ad-ins, I know you can run your own Monte Carlo simulations in excel, but I think- keep it simple. You absolutely can NOT account for every possibility. So think of the most likely (or most extreme) ones and run scenarios for those. If you choose the extremes, then you have a worst case scenario plan and a best case scenario plan, which are easily adapted to what ever happens in real life.

One thing I might double check on SS though (and I'm not an expert), is that I thought that if you died, your spouse would have an option of getting your benefit or their own (maybe it is 50% of your benefit, or her own benefit is she'd worked), but you might want to double check on that.

Since you are in your 60s now, I expect you will get SS. You might be among the last group to get SS, but I think it's late Gen Xers who will be the first to lose out.

[wodehouse]

Bonny, I'm mostly in stocks (via funds) and cash (too much); no bonds at this time (now does not seem to be the time to get into bonds at these low rates...bad news for the capital once rates rise). Rental RE is a bit tempting, as we live in an area that caters to vacationers (lots of foreigners come here to vacation), but I fear I am too lazy/tired to do this.

[Deleted]

Aug 2, 2013 10:35:09 GMT -5 wodehouse said:

Bonny, I'm mostly in stocks (via funds) and cash (too much); no bonds at this time (now does not seem to be the time to get into bonds at these low rates...bad news for the capital once rates rise). Rental RE is a bit tempting, as we live in an area that caters to vacationers (lots of foreigners come here to vacation), but I fear I am too lazy/tired to do this.

We are invested in some intermediate bond funds. I know the returns stink but we feel it's better to get some low returns than the no returns in cash.

And you're correct that managing your own real estate is work, and vacation rentals more work if you're doing everything yourself. We do have one vacation rental but it's 500 miles away. Therefore, that's the only property we have which is professionally managed. I really don't recommend buying vacation rental property as a financial investment. I would only recommend going that route if you plan on using the property yourself or if you plan on using the property as a future retirement home. While the vacation rental rents look great on paper, don't forget all the extra costs like utilities and a cleaning service after every guest. We don't have a mortgage and still have an operating loss of about 2k/yr. We would probably break even if I managed it myself. And don't forget we also have capital improvements/repairs costs almost every year.

If you want a truly passive real estate investment you should investigate REITs. But be careful and do your homework. There's a lot of junk out there with everyone and his uncle calling their mutual fund a REIT.

[busymom]

I LOVE a good spreadsheet!    It probably runs in the family.  My Dad used to make his own, by hand.  In fact, while cleaning out my parent's house, I found a spreadsheet of his that showed 40 YEARS of monthly grocery expenses. 

[wodehouse]

busymom... Awesome!

[wodehouse]

One thing evident from my latest spreadsheet and trying various scenarios:

The financial prospects for DW as a widow are much better if I do not take an early retirement. If I retire early her survivor's financial prospects are not as good, whether I die young or old, versus my retirement at normal age...even with an early death in that case. Interesting. She'd like me to retire early. I'll have to show her this then.

[Deleted]

Aug 2, 2013 14:02:37 GMT -5 wodehouse said:

One thing evident from my latest spreadsheet and trying various scenarios:

The financial prospects for DW as a widow are much better if I do not take an early retirement. If I retire early her survivor's financial prospects are not as good, whether I die young or old, versus my retirement at normal age...even with an early death in that case. Interesting. She'd like me to retire early. I'll have to show her this then.

Wodehouse,

NWIQ is back up and I can't find your profile anymore. But I seem to recall that yours was north of $1M. I know this is sacrilegious here on YM but it isn't all about the money. Your DW would like to spend some good years with you while you're both healthy.

[wodehouse]

Bonny, thanks for the tip. I wholeheartedly agree that "sometimes livin' is more important than makin' a livin'"  (a quote from a framed newspaper cartoon I have in my office).

I do want to have some assurance that DW, if widowed, will not have to live with hardship due to finances.

[Deleted]

Aug 2, 2013 10:03:24 GMT -5 wodehouse said:

But since there are more women on YM than guys, I suspect that there aren't so many folks possessed by spreadsheet mania like I am.
<snip>

Anyway, I digress. So, does anyone have good ideas on developing a method to draw down ones assets over the unpredictable years of retirement? I would like to hear.

   

Wanna bet?  I'm 60 and female and I've been using spreadsheets since VisiCalc and MultiPlan.  Then Lotus 1-2-3 came along.  What an improvement!  I have 4 major spreadsheets. 

1.  My checking account but it's a lot more complicated than a checkbook register because it allows me to keep reserves for various things (home repair, vacation, etc.) and have 2 running balances:  one that reconciles to the bank and one that shows what I actually have to spend.

2.  Another is my long-term savings projection:  I've been tracking what I have at year-end, what I added in savings, investment results (not always an addition ) and year-end balance, then projecting it to my retirement in 5 years with some reasonable assumptions about inflation, future savings and investment returns.    I'll probably adapt this to use after retirement when I get an idea of how our spending goes. 

3.  I finally started doing internal rate of return calculations on all my investment accounts.

4.  I also have a spreadsheet which has been tracking assets since 1991. About 10 years ago I started adding a column with new money deposited during the year so I could tell how much of the change was new money and how much was investment results. Boy, did that get depressing near the end of 2008. Five years' worth of investment gains evaporated. I don't think the account has quite recovered but it's sure doing a lot better. The graph of the balance by month really helps though. You can look back at what seemed to be a catastrophe at the time and it now looks like a temporary blip. Even after the financial crisis, while it was a deep trough, balances started going up again. I LOVE graphs, BTW. I started playing with the when a friend who worked for a company called Software Publishing gave me a beta version of Harvard Presentation Graphics. I think Microsoft put them out of business by pretty much incorporating all the good features into Excel.

[Deleted]

@athena53 said:

About 10 years ago I started adding a column with new money deposited during the year so I could tell how much of the change was new money and how much was investment results. Boy, did that get depressing near the end of 2008. Five years' worth of investment gains evaporated. I don't think the account has quite recovered but it's sure doing a lot better. The graph of the balance by month really helps though. You can look back at what seemed to be a catastrophe at the time and it now looks like a temporary blip.

I know this is a little O-T but isn't being a little older with perspective wonderful? Some of our real estate investments are within 10% of their all time highs. I believe some of them may exceed and then with the next correction come down to where we are now. I think John Bogle's advice to not open your investment statements in 2010 were spot on!

[svwashout]

Aug 2, 2013 10:03:24 GMT -5 wodehouse said:

Anyway, I digress. So, does anyone have good ideas on developing a method to draw down ones assets over the unpredictable years of retirement? I would like to hear.

   

Some discussion about a new tool on the FIRE boards:
www.early-retirement.org/forums/f28/a-new-retirement-calculator-from-blackrock-67745.html

I have no pension and am not expecting much from SS. I don't plan to use the usual inflation adjusted fixed withdrawal rate. I'm thinking about a variation on the percentage of remaining portfolio method with a two and twenty cash out each year. This would be 2% of the portfolio value plus 20% of the marked to market annual return. I figure a 10% cash reserve is enough ballast to begin with. Any surplus that isn't spent or replenishing the reserve I'll probably re-invest.

I don't use spreadsheets so I can't say whether this scheme is more or less reliable than a fixed 3% inflation adjusted withdrawal rate based on initial value. Because my plan is based on remaining portfolio it's impossible to draw down to zero, but it is possible to have years with very low income. This is where the numbers 2, 20, and 10 may need to be tweaked to get an acceptably low failure rate based on back-testing. One advantage with this scheme is that a V-shaped crash and recovery would do much less damage than with the fixed rate method because a huge drop cashes out a large chunk of your portfolio at a very bad time.

[Deleted]

Aug 3, 2013 11:24:42 GMT -5 @bonnap said:

@athena53 said:
I know this is a little O-T but isn't being a little older with perspective wonderful? Some of our real estate investments are within 10% of their all time highs. I believe some of them may exceed and then with the next correction come down to where we are now. I think John Bogle's advice to not open your investment statements in 2010 were spot on!

I agree; I learned that lesson early. I remember the October, 1987 crash and the recovery because I was charting my 401(k) balance. It's probably one of the reasons I knew not to panic and sell out at the bottom in 2008.

[Deleted]

Do you mean the 1987 crash vs the 2007?

I was still pretty young at 26 but I do remember the lawyers I was working for salivating at the "buying opportunity".

Unfortunately I didn't have any money at the time so I missed out!

[Deleted]

Aug 3, 2013 14:45:28 GMT -5 @bonnap said:

Do you mean the 1987 crash vs the 2007?

Yeah- I just went back and edited it!  My husband at the time panicked and called his sister and BIL (very successful investors) and asked what to do.  Their response was "we're buying more!".  Like you, I didn't have a lot of spare cash to invest, but even then (I would have been 34) I knew that made sense.

[wodehouse]

athena, that's awesome! You've been at it longer than I have. I started using spreadsheets seriously with Excel 3 (I had fiddled around with "FirstChoice" previously). My check book register goes back to 1993.

I added an IRR calculation for my fund investments several years ago. I don't use Excel's built-in functions as there is too much data and the time points are not uniform, but it is a pretty simple method that I use. I have a cell that is the (assumed) IRR %, a column of other cells finds the present value (PV) of each (appropriate) transaction based on the duration from then until now, using the IRR %. Another cell sum these these PVs. Then I use the Excel "Goal Seek" function to find the target IRR % so that the summed amount equals the actual current value of the account. Easy.

There should be a sub-forum for discussion of spreadsheet tips and techniques!

A couple of my favorites are: I like color but I minimize the number of colors I use (I use the same principles in the hundreds/thousands of engineering spreadsheets I create); I use a light green for "input" cells and in tables I shade calculated cells a light gray. Once a worksheet is completed and all I do is 'use' it regularly (like an "app"), I turn off the gridlines and the row/column headings; I find them visually distracting otherwise. Then I "protect" the worksheet and only the "input" cells are unlocked; then I can use the Tab key to move sequentially through the input cells for speed and convenience and with no risk to damaging formulas.

[happyscooter]

busymom, that's the ONLY thing we didn't find when we were cleaning out DH's parent's house. 

[cronewitch]

I started using spreadsheets in Unix in 1983. Excel is so much nicer. I have a nice big spreadsheet open all the time at home and have usually 6-20 open at a time at work. I tease my boss that the only answer I have to questions is "I have a spreadsheet" She asked me Friday what items we paid tax when we bought and which we paid use tax on when we used them. I told her told her my only answer is "I have a spreadsheet", do you want to see my spreadsheet? She seems to think I know everything, that question should have gone to purchasing, good thing I trained the last few purchasing people so made them some spreadsheets.