Join Early Retirement Today
Reply
 
Thread Tools Display Modes
Spreadsheets
Old 11-22-2017, 09:33 AM   #1
Full time employment: Posting here.
 
Join Date: Dec 2012
Posts: 656
Spreadsheets

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.
Elbata is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 11-22-2017, 10:05 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
RunningBum's Avatar
 
Join Date: Jun 2007
Posts: 13,184
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
RunningBum is offline   Reply With Quote
Old 11-22-2017, 10:06 AM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,401
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.
Meadbh is offline   Reply With Quote
Old 11-22-2017, 10:49 AM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
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.
__________________
Idleness is fatal only to the mediocre -- Albert Camus
donheff is offline   Reply With Quote
Old 11-22-2017, 11:21 AM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
OldShooter's Avatar
 
Join Date: Mar 2017
Location: City
Posts: 10,308
Quote:
Originally Posted by Elbata View Post
... 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
OldShooter is offline   Reply With Quote
Old 11-22-2017, 11:34 AM   #6
Recycles dryer sheets
 
Join Date: Nov 2017
Posts: 85
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.
pn3069 is offline   Reply With Quote
Old 11-22-2017, 11:35 AM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 10,252
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.
LOL! is offline   Reply With Quote
Old 11-22-2017, 12:29 PM   #8
Thinks s/he gets paid by the post
 
Join Date: Nov 2006
Posts: 1,635
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.
PatrickA5 is offline   Reply With Quote
Old 11-22-2017, 12:41 PM   #9
Moderator
Jerry1's Avatar
 
Join Date: Nov 2014
Posts: 9,070
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!
Jerry1 is offline   Reply With Quote
Old 11-22-2017, 12:45 PM   #10
Full time employment: Posting here.
Lawrencewendall's Avatar
 
Join Date: Feb 2017
Location: Severn
Posts: 947
Quote:
Originally Posted by pn3069 View Post
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
Lawrencewendall is offline   Reply With Quote
Old 11-22-2017, 12:51 PM   #11
Thinks s/he gets paid by the post
 
Join Date: Aug 2013
Location: North
Posts: 4,023
Quote:
Originally Posted by pn3069 View Post
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.
__________________
Time > $$$ ~ 100% equities ~ FIRE @2031
kgtest is offline   Reply With Quote
Old 11-22-2017, 01:12 PM   #12
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Nov 2009
Posts: 6,679
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.
__________________
Retired in late 2008 at age 45. Cashed in company stock, bought a lot of shares in a big bond fund and am living nicely off its dividends. IRA, SS, and a pension await me at age 60 and later. No kids, no debts.

"I want my money working for me instead of me working for my money!"
scrabbler1 is offline   Reply With Quote
Old 11-22-2017, 02:22 PM   #13
Full time employment: Posting here.
 
Join Date: Jan 2008
Location: Flyover America
Posts: 679
Quote:
Originally Posted by pn3069 View Post
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
capjak is offline   Reply With Quote
Old 11-22-2017, 02:58 PM   #14
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Mar 2011
Posts: 8,332
After this forum, spreadsheets are my second biggest online time consumer!
__________________
Living well is the best revenge!
Retired @ 52 in 2005
marko is offline   Reply With Quote
Old 11-22-2017, 05:14 PM   #15
Recycles dryer sheets
 
Join Date: Feb 2011
Posts: 225
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.
PaddyMac is offline   Reply With Quote
Old 11-22-2017, 05:52 PM   #16
Recycles dryer sheets
 
Join Date: Nov 2017
Posts: 85
Quote:
Originally Posted by capjak View Post
+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 !
pn3069 is offline   Reply With Quote
Old 11-22-2017, 06:05 PM   #17
Recycles dryer sheets
retired1's Avatar
 
Join Date: Jun 2011
Posts: 297
Quote:
Originally Posted by pn3069 View Post
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" ?
retired1 is offline   Reply With Quote
Old 11-22-2017, 06:08 PM   #18
Recycles dryer sheets
 
Join Date: Nov 2017
Posts: 85
Quote:
Originally Posted by retired1 View Post
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.
pn3069 is offline   Reply With Quote
Old 11-22-2017, 06:09 PM   #19
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
athena53's Avatar
 
Join Date: May 2014
Posts: 7,306
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.
athena53 is offline   Reply With Quote
Old 11-22-2017, 06:44 PM   #20
Thinks s/he gets paid by the post
Souschef's Avatar
 
Join Date: Dec 2015
Location: Santa Paula
Posts: 4,067
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)
__________________
Retired Jan 2009 Have not looked back.
AA 60/35/5 considering SS and pensions a SP annuity
WR 2% with 2SS & 2 Pensions
Souschef is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
For those with retirement spreadsheets... Cbear1883 FIRE and Money 21 08-18-2007 10:20 AM
Member Spreadsheets Andy R Forum Admin 1 07-29-2007 05:26 PM
Google Spreadsheets Rich_by_the_Bay Other topics 30 08-03-2006 08:09 PM
Google Spreadsheets mickj Other topics 1 08-02-2006 11:42 AM
Spreadsheets Helen Young Dreamers 35 08-14-2005 03:32 PM

» Quick Links

 
All times are GMT -6. The time now is 02:37 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2024, vBulletin Solutions, Inc.