Spreadsheets

[Elbata]

After reading the thread "Vanguard Shock: Too Old To Manage Accounts", so many of the comments mentioned spreadsheets.

Mea culpa, I've never used spreadsheets for my own personal finances. Back in the 90s, I used it for my invoicing, but that's about it.

So off to Google Sheets and Youtube videos. All I can say is, "What took me so long?". I'm enjoying learning Google Sheets, and for the most part, all the information is readily available, I just have to apply myself.

I now have all my finances on this one spreadsheet. I'll be able to follow the monthly ups and downs of each account, and the average of all the accounts together. I'd guess for most of you here this is old hat, but I'm actually kind of giggilly from learning this.

So that's about all I'm doing with my spreadsheet, if anyone has any suggestions, I'm all ears. It would be cool if somehow the financial institutions could implant the data into my spreadsheet automatically.

 

[RunningBum]

The nice thing about spreadsheets is that you can tailor them for your needs, as opposed to whatever someone else's program wants it to do. I know everyone doesn't enjoy doing spreadsheets, but I sure do.


No magic, but for spreadsheets I know I'll keep around, I like to use background colors, font sizes, bold and italic fonts, underlining, etc. to make things more readable, group items together, and highlight the most important fields.


For any formula that involves a potentially variable rate such as taxes, inflation, investment return, etc., always use an actual variable rather than hardcoding it. It makes "what if" scenarios a lot easier to do, as well as handle changes.


Comments, notes and other remarks help me remember what it is I'm trying to do with a spreadsheet, what thought went into the formulas, and what other considerations there are. Just for example, in my 62 vs 70 SS analysis spreadsheet I've got notes on other factors for why I might want to take early or delay beyond just the breakeven analysis.


My spreadsheets include:

Investment "Net worth" (I save a copy each EOY)
Checkbook register for each year
VPW--Long term plan for yearly withdrawals
Budget plan
Cash outflow (yearly and rolling 12 month spending average for me, but one could easily track by budget item--I just choose not to get granular)
Roth conversion calculations, including current year tax due estimates
Medical receipts for eventual HSA withdrawals, and tracking already taken withdrawals and itemized expenses.
When to take SS analysis

 

[Meadbh]

Have you checked out your bank’s website? My two banks have the ability to download all transactions for a specified date range to CSV or Excel. Anything in Excel can be imported into Google spreadsheets. You can have a lot of fun slicing and dicing the numbers.

 

[donheff]

If you are fooling around with Google Sheets don't neglect their finance functions. For example, if in cell B7 you have the ticker symbol for a holding you can return the current price with this function in another column: =GoogleFinance(B7,"closeYest") Add a cell for the number of shares and you can then multiply the current price cell times the number of shares and get the current value for that holding. You can drag and drop the formulas up and down your spreadsheet to do it for each holding. Other functions will return YTD return, etc.

 

[OldShooter]

... if anyone has any suggestions, I'm all ears. ...

At some point in your spreadsheet life you are going to seriously damage or destroy the spreadsheet that you are working on.

1) Be sure to back up periodically, the length of the period reflecting the amount of manual work you are willing to re-do when you revert to a backup.

2) During periods of heavy lifting, creating a big spreadsheet or making major changes, save a backup before every major step. If you survive the gantlet without making any mistakes, just delete the backups. If there is a screwup, a recent backup will keep you from having to start over from scratch

 

[pn3069]

I used to use spreadsheets in the past (from 2010 to early 2017) but gave them up since I was spending more time tweaking the spreadsheets than focusing on investment decisions.

I recently began using personal capital - and I love it. They import all data from most places and show a nice visual of a whole bunch of stats for my portfolio.

Now I am spending time on investment activities and letting Personal Capital do all the heavy lifting.

 

[LOL!]

I retired so that I wouldn't have to work with spreadsheets anymore.

There are plenty of free methods to monitor a portfolio and taxes without resorting to spreadsheets, so I don't use spreadsheets. But I don't mind if you use spreadsheets.

 

[PatrickA5]

I've got quite a few spreadsheets that I use for various financial functions. But, I'm no long a sophisticated spreadsheet user like I used to be in my old work days (Accounting). I can barely use the @sum function to tell you how far I've regressed. It's amazing how fast you can lose a skill if you aren't forced to use it. Nor, do I really feel like learning much either. Must be getting old.

 

[Jerry1]

Spreadsheets are a great thing. You get the most out of them if you really understand the task at hand. For example, if you are automating something you were already doing manually, the spreadsheet will help with the drudgery. The important thing is that you'll actually understand what you're doing. Unlike just picking up a program or spreadsheet and entering data and not really understanding what's going on.

Tools are tools, but it's important to understand the underlying working of the tool you're using, whether that's a spreadsheet or a program or a web site - whatever.

Enjoy!

And Donheff - thanks for that information. I didn't know you could put a holding in the spreadsheet and have it return the daily price. Cool!

 

[Lawrencewendall]

I used to use spreadsheets in the past (from 2010 to early 2017) but gave them up since I was spending more time tweaking the spreadsheets than focusing on investment decisions.

I recently began using personal capital - and I love it. They import all data from most places and show a nice visual of a whole bunch of stats for my portfolio.

Now I am spending time on investment activities and letting Personal Capital do all the heavy lifting.

+1

 

[kgtest]

I used to use spreadsheets in the past (from 2010 to early 2017) but gave them up since I was spending more time tweaking the spreadsheets than focusing on investment decisions.

I recently began using personal capital - and I love it. They import all data from most places and show a nice visual of a whole bunch of stats for my portfolio.

Now I am spending time on investment activities and letting Personal Capital do all the heavy lifting.

There is some truth to this spending a lot of time updating and tweaking. I do a lot of what-if's that I'm not sure personal capital gives me. Perhaps after 7yrs of bull run it might be appealing to let someone else to it.

I can't stomach the extra fee. My investments expenses are less than .06% and my spreadsheets help me keep them low like that.

 

[scrabbler1]

Even though I had been working for 10 years and had worked with spreadsheets the whole time, I bought my first home PC in 1995 and quickly created some spreadsheets for my personal stuff. I owned some mutual funds so I put my paper stuff into spreadsheets so I could keep track of purchases and sales and do cap gain stuff whenever I sold shares. I also put my checkbook register into a spreadsheet and attached to it a skeleton version of my income tax forms. I also created a spreadsheet to keep track of monthly totals of my portfolio and quarterly totals for my 401k.


I have another spreadsheet which shows my annual totals by year for income and expenses including taxes and tax rates.


When I began putting together my early retirement plan, I created a spreadsheet to estimate my retirement budget from age ~45 to age ~60, when the first of my reinforcements would arrive. This spreadsheet, appropriately named "RETIRE," was by far the most useful one in my ER planning.

 

[capjak]

I used to use spreadsheets in the past (from 2010 to early 2017) but gave them up since I was spending more time tweaking the spreadsheets than focusing on investment decisions.

I recently began using personal capital - and I love it. They import all data from most places and show a nice visual of a whole bunch of stats for my portfolio.

Now I am spending time on investment activities and letting Personal Capital do all the heavy lifting.

+1 for personal capital

 

[marko]

After this forum, spreadsheets are my second biggest online time consumer!

 

[PaddyMac]

I use Apple Numbers a lot now and also wonder how I got on without it. The best spreadsheet I made is our entire Tax Return! Being self-employed, I can enter the income for the year and fill out all the worksheets, and right at the bottom it tells me how much tax we owe. I get within $1 of TaxAct, and that's just due to rounding issues on worksheets.

 

[pn3069]

+1 for personal capital

I like the fact that they use 2 factor authentication (which I enabled for every login) and they ask you to name each device. No security is fool-proof but I feel better with personal capital.

I also occasionally check the names of the devices in the list to make sure no one created another device name on my behalf.

The main issue I had with using spreadsheets was that I was getting creative with data display issues and it started consuming so much time that my family began to complain. After 7 years one fine day I said 'nuff is 'nuff and went to personal capital.

In addition to PC, many other brokers also offer aggregation services... fidelity and schwab to name a few.

I am now realizing there is more value I can get from my time than pouring over spreadsheets...

To each their own.... all the best to you in this holiday season !

 

[retired1]

I used to use spreadsheets in the past (from 2010 to early 2017) but gave them up since I was spending more time tweaking the spreadsheets than focusing on investment decisions.

I recently began using personal capital - and I love it. They import all data from most places and show a nice visual of a whole bunch of stats for my portfolio.

Now I am spending time on investment activities and letting Personal Capital do all the heavy lifting.

Hmm.... I'm usually leery... but willing to try Personal Capital. What is it and where can I get it ? Is it "secure" ?

 

[pn3069]

Hmm.... I'm usually leery... but willing to try Personal Capital. What is it and where can I get it ? Is it "secure" ?

www.personalcapital.com

It is free.

You sign up.. and enter login credentials for the financial institutions you want to include.

I would also suggest looking through youtube for personal capital tutorials.

You need to be comfortable providing your login credentials. That is the key.

Security is in the eye of the beholder... but I am satisfied with it.

 

[athena53]

I've been working with spreadsheet software since the days of MultiCalc, VisiPlan and Lotus 1-2-3. The three that I've built that I've used the longest are:


1. Tracking my assets monthly, since 1991. It really helps put market drops into perspective a few years after the fact.


2. My version of a checkbook register- it includes the bank balance, of course, plus additional reserves for out-of-pocket medical, home repair, upcoming taxes, amounts I've charged on my credit cards, etc. and the difference between the bank balance and the reserves is what I have to spend. It's complicated but it works the way my brain works.


3. A simple long-term cash flow projection by year, with variable interest, inflation and investment return rates. I update each year with actual results. This gives me an idea of whether I'm on track- not as fancy as a Monte Carlo simulation but a decent approximation, which I think really got me started thinking seriously about retirement when I first put it together about 15 years ago.


I've added many others, including one that I used to update my portfolio IRR monthly.

 

[Souschef]

I have one spreadsheet just to calculate RMD's every year. I downloaded the factors from the IRS website, which makes it an easy calculation
I then have a column just for where the money will go, and when it gets added up it makes sure I do not under draw.
I also have another one with all our accounts with a column as to who the beneficiary is. I then add them up to make sure the beneficiaries get equal shares (approx)

 

[Throwdownmyaceinthehole]

I download expenses from the bank every month and assign a category. Just takes a few minutes, then I put in a pivot table so I can get each months expenses. Then end of year I do a pie chart. Its a total waste of time as our expenses have been stable for years. But the info is fun to look at each month - it's our State of the Union report!

 

[athena53]

I download expenses from the bank every month and assign a category. Just takes a few minutes, then I put in a pivot table so I can get each months expenses. Then end of year I do a pie chart. Its a total waste of time as our expenses have been stable for years. But the info is fun to look at each month - it's our State of the Union report!

Oh, yeah- I started that 3 years ago. Very enlightening. I've done a great job cutting back by eliminating cable and going with Netflix and MagicJack, changing thermostat settings, etc. Then I spend all the savings on travel!

 

[Elbata]

OP here. Thank you all so much for your comments.

Have been studying Google Spreadsheets the last two days. And it's been a lot of fun. And an eye-opener to say the least.

I think though, I'm going to head over to Personal Capital and check it out.

I created two spreadsheets--an Investment and a Monthly Expense. The investment sheet was relatively simple, and the expense sheet much more complicated.

Looking over my expense spreadsheet, I marvelled at how little I spend. Wife and I are pretty much glued to home--DW is with her mom everyday visiting her in assisted living, and dear MIL's dog is now with us, so we just don't spend much money.

 

[FIREd]

I have many spreadsheets (love spending time tweaking them too). The main ones are:

Net worth
Asset allocation tracking
Checkbook register
Sinking fund management
Portfolio management and passive income tracking
Budget

I don't track our expenses anymore - our checking account balance tells me all I need to know about our spending.

 

[Danmar]

I love spreadsheets and have dozens for such things as:

Main cash flow budgets, current year plus next 2 years
Check register
Workouts, including heart rates, body weight, activity, duration, etc
Detailed expense tracking for our house in Arizona
Cost basis for all my individual investments. Some go back 20 years
Net worth but haven’t updated in years as I can do it in my head.
IRR calculations although my brockeage system now gives me this
Various one off efforts to analyse various financial issues

I would be lost without them.