Spreadsheets

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Spreadsheets

Post by abuss368 »

Bogleheads -

Curious how many Bogleheads created or use a spreadsheet to review and monitor their portfolios? Years ago we had a spreadsheet that I built with many tabs and information. Ultimately I figured out it was not worth it or needed. Now we use a very simple two tab spreadsheet that involves minimal input or data entry. In fact the fund values link to an online quote and update automatically.

One tab is the overall portfolio and compares to target asset allocation.
The other tab shows the total activity for the year - beginning, contributions, dividends, market gains and losses, and ending. In some respects this is available on Vanguard’s website under performance.

I admire Taylor (who hopefully sees this thread) and stopped tracking his portfolio with spreadsheets! Trying to move in that direction.

Best.
John C. Bogle: “Simplicity is the master key to financial success."
User avatar
DartThrower
Posts: 953
Joined: Wed Mar 11, 2009 4:10 pm
Location: Philadelphia

Re: Spreadsheets

Post by DartThrower »

I don't really need a spreadsheet in my situation. Since I have had all Vanguard funds the past 20 years I feel comfortable just using Account Balances and Portfolio Watch. Due to market returns, aggressive 403(b) savings and inflation the nominal value of my portfolio has increased 7 fold over the 20 year span. This has enabled me to retire this year at age 59. No financial advisor. No actively managed funds. No spreadsheet.

Possibly a spreadsheet would have given me some kind of enhanced insights such as better tax management so I do see the value in it for those so inclined. I invest in a tax efficient manner based on the advice on this site and elsewhere, but I don't do much tax loss harvesting etc.
Our patience will achieve more than our force. -Edmund Burke
livesoft
Posts: 73523
Joined: Thu Mar 01, 2007 8:00 pm

Re: Spreadsheets

Post by livesoft »

I don't use a spreadsheet, but I wanted to say that I tax-loss harvest without worries because brokerage accounts have all the information needed to show losses that need harvesting without use of any spreadsheets.
Wiki This signature message sponsored by sscritic: Learn to fish.
dcabler
Posts: 1580
Joined: Wed Feb 19, 2014 11:30 am

Re: Spreadsheets

Post by dcabler »

I use a spreadsheet. Not necessarily because I need to, but because I like to.

What's in it?
1) A tab with an annual view. Each account I have has its own column and I show the amount in each one for each year (been doing this since 2004). The current year is "live" and I update it probably once a week or so. Also contains charts of growth for the current year, and overall growth since I've been tracking. At year's end, I copy and paste the tab to create a new one and "freeze" all calculations for the old one.
2) A tab that tracks asset allocation and automatically computes whether I need to rebalance or not. Once a year or so I run my portfolio through M* X-ray and I run a factor regression using porfoliovisualizer and I update that as well. I also estimate the total $ that go towards e/r
3) A tab with a current backtest for all of my accounts.
4) A tab for my ibonds and TIPs funds. I've built a pseudo-ladder (a la BobK) and I use this one to calculate percentage of tips holding in a long term tips fund, intermediate TIPs fund, etc. based on current duration.
5) A tab to calculate funding ratio

Also, I have the SMF Excel add-in which I use to grab some information I find useful for analysis. Mostly I live in tabs described in 1 and 2 above.

Cheers
aristotelian
Posts: 8260
Joined: Wed Jan 11, 2017 8:05 pm

Re: Spreadsheets

Post by aristotelian »

I have 13 separate accounts and found that I needed a spreadsheet to pull them all together. I used to use Personal Capital but did not like the phone calls from salesmen or giving over my login ingo. I use Google Sheets with =GOOGLEFINANCE commands to pull market prices in real time. I only need to update it when new shares are purchased.
livesoft wrote: Tue Sep 17, 2019 6:11 am I don't use a spreadsheet, but I wanted to say that I tax-loss harvest without worries because brokerage accounts have all the information needed to show losses that need harvesting without use of any spreadsheets.
I have a tab with a list of acceptable harvesting partners in each category. Otherwise I find myself doing the same research over and over. I also have a space on the spreadsheet for "RECENT LOSS PURCHASES-DO NOT BUY" so I don't get confused and end up with a wash sale.
Dantes
Posts: 333
Joined: Wed Feb 25, 2015 6:38 pm

Re: Spreadsheets

Post by Dantes »

I have lots of spreadsheets - for investments, for projections, for transactions, for tax liability, for expenses. I update them frequently.

I fulfilled my quota of ignoring portfolio performance a few years ago.
Dandy
Posts: 6385
Joined: Sun Apr 25, 2010 7:42 pm

Re: Spreadsheets

Post by Dandy »

It's funny how some habits linger well beyond their actual critical need.

I keep 2 speadsheets:
1. Investments/Savings - While I have them all on VG's website and update it for assets outside VG I feel the need monthly to enter they contents in a spreadsheet and have it make some automatic calc's that I like. e.g. overall and for each type of account the equity/fixed income allocation, parsing out the fixed income into several categories: no loss of principal, short term bonds, intermediate bonds. This later calc is because I try to keep enough no loss and short term fixed income equal to my draw down dollars until age 90. It takes about 1 hour a month and it is somewhat helpful in tracking any need for rebalancing -- I rarely make any decisions - it is more of a comfort ritual. Also, I have tried to limit looking at my investments frequently so this planned access helps me do that. I used to access my account several times a week.

2. I usually run all expenditures through my checkbook. I set up a spreadsheet to track monthly expenses to about 25 categories e.g. each insurance, each credit card, charity/gifts, utilities, etc. I want to keep an idea of our expense trends to make sure there isn't a subtle expense creep. It has been somewhat helpful since monthly expenses can vary widely. Tracking not only the expenses for that month but the ytd average is useful. I guess being a life time frugal spender makes a difference since there has been very little expense creep. But, knowing this has made it easier to gift some early inheritance to my children and to up our charity contributions - and to spend without guilt. :oops:

A couple of hours a month on keeping a pulse on investments and expenses probably isn't needed but I'll continue at least for now.
3-20Characters
Posts: 717
Joined: Tue Jun 19, 2018 2:20 pm

Re: Spreadsheets

Post by 3-20Characters »

If you’re going to just mimic what’s on your accounts pages at Vanguard, Fido, etc, a spreadsheet is probably not worth the effort. Most brokerages allow you to add outside accounts and there are third party aggregators as well. If however, you’re going to use that data to do other customization in the way of calculations, projections, historical, what if analysis, and even borrow from many of the great spreadsheets created by other bogleheads, then a spreadsheet is the only way to go.

In my spreadsheet, I have many bits and pieces of information, pie charts, and long term what if analysis which I would hate to have to recreate every time I want to run some scenario. I have expenses for many years, projected SS, look ups from the VPW table, etc, etc. I update my spreadsheet about once a month (dividend distributions and bank balances) and the rest pretty much runs on its own. It’s a valuable tool for me to see our finances at the 35k ft. view while being able to zoom in on detail as well.

I realize that some people think this is all waste of time and maybe it is. Different strokes.
livesoft
Posts: 73523
Joined: Thu Mar 01, 2007 8:00 pm

Re: Spreadsheets

Post by livesoft »

3-20Characters wrote: Tue Sep 17, 2019 6:47 amDifferent strokes.
Medical advice is not condoned on this forum. :)
Wiki This signature message sponsored by sscritic: Learn to fish.
User avatar
Rowan Oak
Posts: 710
Joined: Mon May 09, 2016 2:11 pm
Location: Yoknapatawpha

Re: Spreadsheets

Post by Rowan Oak »

No spreadsheet.
I sometimes use a calculator to figure stock/bond % to know if I need to rebalance. Vanguard does this for you, but not without delay.
When I see a loss in taxable I will usually harvest the loss.
Very rarely look at or think about performance, because I use The Three-Fund Portfolio
and it is what it is.
“If you can get good at destroying your own wrong ideas, that is a great gift.” – Charlie Munger
User avatar
Cyclesafe
Posts: 1183
Joined: Wed Dec 31, 2014 1:03 pm

Re: Spreadsheets

Post by Cyclesafe »

My spreadsheet bridges annual personal balance sheets with personal annual income statements. I enter by hand only the topline categories of assets - takes 5 minutes max. Algorithms for taxes are valid within my narrow income range. I have year end results for the past 10 years and I project out to age 100.

The value of this is that I fully appreciate how little is known and how a change in assumptions often has unanticipated effects elsewhere in the model. But endless tinkering has resulted in what I would like to think is a sensitivity as to which factors have the greatest impact on me.
Last edited by Cyclesafe on Tue Sep 17, 2019 7:19 am, edited 1 time in total.
"Plans are useless; planning is indispensable.” (Dwight Eisenhower) | "Man plans, God laughs" (Yiddish proverb)
Jack FFR1846
Posts: 12806
Joined: Tue Dec 31, 2013 7:05 am
Location: 26 miles, 385 yards west of Copley Square

Re: Spreadsheets

Post by Jack FFR1846 »

One excel spread sheet with one tab. It was my first step in getting my finances in order. Before the spread sheet, I didn't know what old accounts I had nor how much. I spent a full year taking mailed statements to build the spread sheet.

Mine contains all of my and my wife's accounts, ER, % of total, cost. Another section breaks into asset allocation percentages, adds non-retirement money (savings, assets, etc) rolling up to a net worth number. Besides my total of assets, I've got boxes showing 4%, 3% and 2% numbers, intended for withdrawal amounts. In another section, I have broken down social security amounts by year I start taking them, and my wife, and the small pension that I'll receive along with the one time chance for a lump sum payment, which is what I intend to take.

Nothing is linked to anything. I don't hand my login info or passwords to anyone. The spread sheet resides on a flash drive.
Bogle: Smart Beta is stupid
Northster
Posts: 198
Joined: Wed Oct 28, 2009 3:30 pm

Re: Spreadsheets

Post by Northster »

Everyone's situation is different. For me, with assets spread over 8 accounts I need a spreadsheet to track and adjust asset allocation. Nothing complicated but essential for me.
jebmke
Posts: 11664
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: Spreadsheets

Post by jebmke »

I have all our assets at Vanguard so I can see everything when I go in at the end of the month to sell something to pay for my bills coming due in the next month.

I do keep a separate spreadsheet in Google Sheets that breaks down my bond holdings into a 3x3 -- Issuer (UST, Muni, Corporate) x Term (Short, Medium Long). I update this once a year.
When you discover that you are riding a dead horse, the best strategy is to dismount.
ARoseByAnyOtherName
Posts: 1000
Joined: Wed Apr 26, 2017 12:03 am

Re: Spreadsheets

Post by ARoseByAnyOtherName »

I have a simple spreadsheet that just lists all our checking, savings, mortgage, and investment accounts along with their balances (and liabilities in the case of our mortgage). I pay off credit cards once a week so there’s no need to list those. This provides a high level picture of all our assets, and I periodically add a new row which provides tracking over time.

It doesn’t list holdings within each account. When I need to dive in to the performance of individual accounts or holdings I just log in to that account.

For me this provides the right balance of simplicity and high level overview. As I posted elsewhere I’m investigating use of an aggregator to hopefully replace this over time, but this spreadsheet works well.
User avatar
geniekid
Posts: 27
Joined: Tue May 07, 2019 8:37 am

Re: Spreadsheets

Post by geniekid »

I have a spreadsheet with one tab for asset allocation/rebalancing, one tab for monthly cash flow, and one tab for comparing CDs vs I-bonds (surprisingly close when my tax bracket and expected future tax bracket are taken into account).

The spreadsheet mostly came about because I wanted one place to view my assets and got tired of Mint.com always trying to sell me things.
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

DartThrower wrote: Tue Sep 17, 2019 5:59 am No financial advisor. No actively managed funds. No spreadsheet.
Simplicity! A nice place to be at.
John C. Bogle: “Simplicity is the master key to financial success."
NoblesvilleIN
Posts: 126
Joined: Sat Mar 24, 2018 6:04 pm
Location: Noblesville IN

Re: Spreadsheets

Post by NoblesvilleIN »

I have two spreadsheets.

One is a single tab that gets updated on January 1st each year that tracks our progress. The top section has a row for each account (taxable, my IRA, her IRA, checking, saving, ESPP (when I was working), etc.). It lists the value of each account on 12/31/xx. The bottom section has a row for each debt (1st mortgage, HELC, car loan) - I have a generic vehicle loan row because that is a short term debt. Across the top is column for each year, going back to 12/31/2003. I have added rows over the years for new accounts (eg. when I opened my taxable account in 2011). I do not remove a row when an account is closed (eg. 1st mortgage paid off at the end of 2011). I total each section (investments/savings and debt) and compare it to the prior year - in both dollars and percentage. The goal is to see the investment/savings grow and the debt drop. I can look back and see that in 2007 my investments/savings dropped 9.65% and in 2008 they dropped an additional 31%. I can also see that I was back to 2006 values by sometime in 2011. I dropped more and took longer to recover because of restricted stock options that were worth a lot in 2006 but fell to almost worthless in 2007 because of a failed LBO (I was not an executive or even a manager). While seeing the investments/savings change was helpful, I got more value out of watching the debt change. Seeing the debt dropping served as a great motivator for us. It also showed that in 2015, our debt went up when we chose to remodel the bathroom using the HELC and to finance half of a new car with a vehicle loan. A few years ago, I added a row for the high and low point of our investments each year. The difference is used to highlight the volatility and provides a sanity check when the market goes crazy. Later in the day on January 1st, my wife rolls her eyes and sits down with me to go over the prior year. The eye roll has become a running joke with us as each year she tries to get more dramatic. She really is interested and we have discussions throughout the year. She also shouts "innocent spouse" as I do the taxes each March when I ask her for numbers such as what we paid in property tax the prior year. She pays the bills and I handle the investments, but we keep each other informed on a regular basis. This spreadsheet is a historical record of our progress. I wish I had thought to start it even earlier than we did.

The other spreadsheet would not be of interest to this forum because it tracks our individual investments and we are not 3-fund people. I use it to track and project our income from dividends and interest and it has many tabs (owned stocks, index funds & cd's; sold stocks and matured cd's; withdrawals - car purchase for cash in 2014, half of a car in 2015, carpet and new ZTR mower in 2017, regular withdrawals this year to supplement my wife's income after I retired; tax info for paying quarterly taxes; HSA distributions; SSA earnings - to estimate future Social Security).
Last edited by NoblesvilleIN on Tue Sep 17, 2019 10:21 am, edited 1 time in total.
Admiral
Posts: 3089
Joined: Mon Oct 27, 2014 12:35 pm

Re: Spreadsheets

Post by Admiral »

Do I need one? No.
Do I have one? Of course. :?

I only update it once per year with all investment holdings, shares, and share price. It calculates my AA so I can see if it has drifted. Of course, Vanguard does this as well, but, belt-and-suspenders...

I also have a spreadsheet that records and projects 12 mo cashflow, and one that tracks pension increases monthly. I don't track expenses except in very general terms (housing, credit cards, tuition, etc).

Is any of it truly necessary? Probably not. But I like data.

I use finance software for tracking/recording monthly expenses.
finfire
Posts: 207
Joined: Tue Aug 14, 2018 1:52 pm

Re: Spreadsheets

Post by finfire »

Nope. I dont' have any desire to duplicate what is already mostly provided by my financial providers. I don't have any desire to work on spreadsheets unless someone is paying me....even then it's a struggle.

I do use a spreadsheet to track my expenses. It's simple and maybe takes 10 mins/month to update.
Texanbybirth
Posts: 1398
Joined: Tue Apr 14, 2015 12:07 pm

Re: Spreadsheets

Post by Texanbybirth »

We have a monthly budget spreadsheet, but that's mainly for my wife to track expenses. We don't have a spreadsheet for investments. (We also don't have very many investments to keep track of. :P )

I get my spreadsheet modeling/tinkering/macro-ing fix at work. :twisted:
“The strong cannot be brave. Only the weak can be brave; and yet again, in practice, only those who can be brave can be trusted, in time of doubt, to be strong.“ - GK Chesterton
mptfan
Posts: 6259
Joined: Mon Mar 05, 2007 9:58 am

Re: Spreadsheets

Post by mptfan »

dcabler wrote: Tue Sep 17, 2019 6:27 am I use a spreadsheet. Not necessarily because I need to, but because I like to.
Me too, it's a hobby. Nobody needs a spreadsheet.
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Spreadsheets

Post by dbr »

mptfan wrote: Tue Sep 17, 2019 10:27 am
dcabler wrote: Tue Sep 17, 2019 6:27 am I use a spreadsheet. Not necessarily because I need to, but because I like to.
Me too, it's a hobby. Nobody needs a spreadsheet.
I find various spreadsheets to be a great convenience and an efficient way to organize and save information. Whether or not convenience and efficiency are something a person needs is a different question. There is always an issue whether the task needs to be undertaken at all.

I also do not find keeping data in spreadsheets to be onerous and I am reasonably effective at doing the work.
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

finfire wrote: Tue Sep 17, 2019 10:04 am Nope. I dont' have any desire to duplicate what is already mostly provided by my financial providers. I don't have any desire to work on spreadsheets unless someone is paying me....even then it's a struggle.
Very well said! Duplication as the information is already there.
John C. Bogle: “Simplicity is the master key to financial success."
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

Texanbybirth wrote: Tue Sep 17, 2019 10:11 am I get my spreadsheet modeling/tinkering/macro-ing fix at work. :twisted:
This is also a part of where I am coming from. I work with spreadsheets all day. So the last thing I want to do is work on them during the night and weekends.
Last edited by abuss368 on Tue Sep 17, 2019 12:32 pm, edited 1 time in total.
John C. Bogle: “Simplicity is the master key to financial success."
Texanbybirth
Posts: 1398
Joined: Tue Apr 14, 2015 12:07 pm

Re: Spreadsheets

Post by Texanbybirth »

abuss368 wrote: Tue Sep 17, 2019 11:22 am
Texanbybirth wrote: Tue Sep 17, 2019 10:11 am I get my spreadsheet modeling/tinkering/macro-ing fix at work. :twisted:
This is also a part of where I am coming from. I work with spreadsheets all day. So the last thing I want to is work on them during the night and weekends.
If I recall correctly, you’re a fellow accountant/CPA, so that makes perfect sense (cents). :moneybag :beer
“The strong cannot be brave. Only the weak can be brave; and yet again, in practice, only those who can be brave can be trusted, in time of doubt, to be strong.“ - GK Chesterton
EvelynTroy
Posts: 460
Joined: Sat Jun 07, 2008 8:35 am

Re: Spreadsheets

Post by EvelynTroy »

I don't have the skills or interest to learn how to create my own spreadsheets. I don't have a need for anything complicated. I use the brokerage account information it provides to track my returns.
I use this spreadsheet for rebalancing. It is in Google Sheets, it automatically updates. I eliminated the Rick Ferri and Personal Capital columns - they were provided for ideas. Seems to be all I need.

You can see it here with instructions to use - the workbook is two sheets. The author is a Personal Capital fan, but that has nothing to do with the spreadsheet that is provided.

https://www.doughroller.net/investing/a ... eadsheet/

Ev
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

Texanbybirth wrote: Tue Sep 17, 2019 11:29 am If I recall correctly, you’re a fellow accountant/CPA, so that makes perfect sense (cents). :moneybag :beer
:sharebeer
John C. Bogle: “Simplicity is the master key to financial success."
User avatar
changingtimes
Posts: 264
Joined: Mon Jul 24, 2017 9:28 am

Re: Spreadsheets

Post by changingtimes »

Hello, my name is ChangingTimes, and I have a spreadsheet problem.

Investments, balance tracking, forecasting, cost basis, stock sales, dividends, charitable donations, 529 donations, pension estimation, ss estimation, paycheck tracking, yearly income tax estimation, credit card lineup (to strategize rewards), plus a few other tabs, all in one Google spreadsheet.

It's all very interconnected, so that I can project, say, Roth conversions, and see how it would impact my taxes. Or change my paycheck withholding and see how that helps.

It also makes me look terribly busy at work. :sharebeer
User avatar
NearlyRetired
Posts: 185
Joined: Fri Aug 09, 2019 12:56 pm
Location: United Kingdom

Re: Spreadsheets

Post by NearlyRetired »

mptfan wrote: Tue Sep 17, 2019 10:27 am
dcabler wrote: Tue Sep 17, 2019 6:27 am I use a spreadsheet. Not necessarily because I need to, but because I like to.
Me too, it's a hobby. Nobody needs a spreadsheet.
+1
To err is to be human, to really mess up, use a computer
esteen
Posts: 102
Joined: Thu May 23, 2019 12:31 am

Re: Spreadsheets

Post by esteen »

I use a spreadsheet. It helps me track things so I would say it's helpful, though not absolutely necessary.

I have an instructions/explanations tab for DW, a net worth (balance sheet) tab, an expenses tab (actual, budget, and projected in retirement), a savings plan tab, a housing tab (mortgage amortization tables, projected net sales proceeds for when we are planning a move), and a "resources" tab for commonly used links and informational tidbits related to my financial situation I want to have centrally located.

I like keeping my various financial data in one spreadsheet because it makes it simpler to view the overall impacts of a life decision (say an increase/decrease in savings rates or new expense).
Steadfast
Posts: 236
Joined: Wed Nov 06, 2013 3:44 pm

Re: Spreadsheets

Post by Steadfast »

I manage a family portfolio - currently 10 separate accounts across 5 institutions - so I use a Google Sheet to aggregate all of the financial data into a single portfolio view.

I programmed pie and bar charts to help visualize asset allocations, track total net worth over time, etc. I also programmed a rebalancing tool on it. All very simple but helpful.

I update it at least monthly, mostly because I enjoy the process. I make it as simple and intuitive as possible, so that DW, whom has access to it, can interpret or use it as needed.
We don't see things as they are, we see things as we are.
User avatar
5th_Dimension
Posts: 169
Joined: Mon Jul 11, 2016 1:05 am

Re: Spreadsheets

Post by 5th_Dimension »

My spreadsheet is like the Winchester Mystery House. I keep adding things to it just because I think of something else that might be cool to keep track of. Someone looking at it from the outside however might think me a bit obsessive :happy
Pick up a penny and soon you'll have many.
User avatar
LilyFleur
Posts: 1576
Joined: Fri Mar 02, 2018 10:36 pm

Re: Spreadsheets

Post by LilyFleur »

I'm not obsessed with spreadsheets--but with savings in several funds held in two institutions (taxable and tax deferred), I needed a spreadsheet to calculate my current asset allocation. One Googlesheet does it for me. I have the tax brackets represented in that spreadsheet so that when I add in a paycheck from my part-time job, or royalties, or dividends from my taxable account, I can see where I'm at for the year, in terms of tax strategy as well as estimated taxes.

I don't track expenses. I put most everything on a 2% back credit card, and I look at the spending summary and review the expenses before I pay the bill. I'm retired. I have lived frugally for so many years that I don't have to write down every expense. Been there, done that. Living frugally is now a habit, with pockets of fun spending which usually involves time with friends, family, or my wonderful BH boyfriend :mrgreen:
Last edited by LilyFleur on Tue Sep 17, 2019 5:26 pm, edited 1 time in total.
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

5th_Dimension wrote: Tue Sep 17, 2019 5:00 pm My spreadsheet is like the Winchester Mystery House. I keep adding things to it just because I think of something else that might be cool to keep track of. Someone looking at it from the outside however might think me a bit obsessive :happy
I hear you as I went through that phase. Trying to move towards simplicity and using what is available on Vanguard’s website and statements.
John C. Bogle: “Simplicity is the master key to financial success."
lostdog
Posts: 3344
Joined: Thu Feb 04, 2016 2:15 pm

Re: Spreadsheets

Post by lostdog »

abuss368 wrote: Tue Sep 17, 2019 9:38 am
DartThrower wrote: Tue Sep 17, 2019 5:59 am No financial advisor. No actively managed funds. No spreadsheet.
Simplicity! A nice place to be at.
No spreadsheet for the portfolio. All portfolio info is up at Vanguard.

We use google sheets for check register. I share it with my wife and she can see it in her google drive.

1 checking account
1 brokerage and IRA accounts at Vanguard.

Simple.
Kobinator
Posts: 8
Joined: Thu Feb 01, 2018 8:33 pm

Re: Spreadsheets

Post by Kobinator »

Like others have mentioned, I use a Google Sheet to track investments (3-fund across 403b, 401k, IRAs, and taxable). Googlefinance provides daily nav updates, and a Google App script checks the sheet around noon everyday and emails me if the allocation drifts beyond +/- 2.5% bands.

The sheet also has a section that calculates the rebalancing transactions needed such that the overall PF rebalances while keeping the 403b (mine) and 401k (DW's) internal allocations equivalent. We're fortunate that each has similar 3-fund-compatible options with comparable expense ratios.

I update the holdings quarterly with data from a GnuCash report (captures regular contributions in our standard allocations and reinvested dividends). I use GnuCash for all household accounting (updated quarterly), and I share a yearly report with DW to share progress on goals and to prompt conversation about any desired changes. The report is mainly charts; a spreadsheet would probably kill the (already dull) mood, haha.
User avatar
cashboy
Posts: 582
Joined: Tue Sep 11, 2018 5:03 pm
Location: USA

Re: Spreadsheets

Post by cashboy »

i use 3 excel workbooks.

(1) expenses
several spreadsheets in that workbook - ex: by type (house, car) and in total

(2) positions/assets/investments
many spreadsheets in that workbook - ex: history, trending, daily changes in value, yearly expense ratios, charts, IPS, goals, etc

(3) retirement planning
many spreadsheets in that workbook

i like working with spreadsheets so for me it is fun (a hobby) rather than a chore. it is also my nature to have all the details at hand.
Three-Fund Portfolio: FSPSX - FXAIX - FXNAX (with slight tilt of CDs - CASH - Canned Beans - Rice - Bottled Water)
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

lostdog wrote: Tue Sep 17, 2019 5:57 pm
abuss368 wrote: Tue Sep 17, 2019 9:38 am
DartThrower wrote: Tue Sep 17, 2019 5:59 am No financial advisor. No actively managed funds. No spreadsheet.
Simplicity! A nice place to be at.
No spreadsheet for the portfolio. All portfolio info is up at Vanguard.

We use google sheets for check register. I share it with my wife and she can see it in her google drive.

1 checking account
1 brokerage and IRA accounts at Vanguard.

Simple.
That is a nice place to be in.
John C. Bogle: “Simplicity is the master key to financial success."
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

Does it bother anyone that when logging into Vanguard and using the variety of portfolio tools and analysis features that only the last 10 years of data are available? The data from inception is no longer available. In some respects if something is that far in the rear view mirror that probably begs the question of how relevant is it any longer?
John C. Bogle: “Simplicity is the master key to financial success."
dbr
Posts: 33842
Joined: Sun Mar 04, 2007 9:50 am

Re: Spreadsheets

Post by dbr »

abuss368 wrote: Tue Sep 17, 2019 8:00 pm Does it bother anyone that when logging into Vanguard and using the variety of portfolio tools and analysis features that only the last 10 years of data are available? The data from inception is no longer available. In some respects if something is that far in the rear view mirror that probably begs the question of how relevant is it any longer?
The spreadsheet I have now has data back about twenty years. The parts of the spreadsheet that hold basis information go back farther than that. (The spreadsheet is not that old, but the data loaded in it goes back.)

Relevance I don't know, but my information is not captive to the whims of a mutual fund peddler somewhere.

On the other hand it would be the rare individual who has complete financial records back to birth or something except someone very young.
ARoseByAnyOtherName
Posts: 1000
Joined: Wed Apr 26, 2017 12:03 am

Re: Spreadsheets

Post by ARoseByAnyOtherName »

finfire wrote: Tue Sep 17, 2019 10:04 am Nope. I dont' have any desire to duplicate what is already mostly provided by my financial providers.
How do you see your overall net worth (assuming you have assets at multiple providers, which it sounds like you do?)
User avatar
Random Musings
Posts: 5837
Joined: Thu Feb 22, 2007 4:24 pm
Location: Pennsylvania

Re: Spreadsheets

Post by Random Musings »

Spreadsheet is on one tab and consolidates non Vanguard holdings I have. Reminds me when I need to rebalance. Basically, I look at percentages of equities, bonds, REiTs and cash. Equities are broken out into domestic/internatonal as well as capitalization. Bonds are broken out into real/nominal as well as overall duration - include cash portion in that calc. Of course, it shows what the portfolio weighted avg ER is. Use Vanguard and M* data, good enough for my needs.

RM
I figure the odds be fifty-fifty I just might have something to say. FZ
User avatar
grabiner
Advisory Board
Posts: 28259
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: Spreadsheets

Post by grabiner »

I use a spreadsheet to manage my asset allocation, because I have funds which span multiple asset classes (Total Stock Market, for example, is 40% LG, 40% LV, 10% SG, 10% SV by my definition), and I also tax-adjust values ($6900 in a Roth IRA is worth as much as $10,000 in an employer plan which I expect to have taxed at 31%). When I need to make a new contribution, or do my annual rebalance, I update the spreadsheet to see which asset classes are underweighted and should receive new money.
Wiki David Grabiner
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

grabiner wrote: Tue Sep 17, 2019 8:38 pm When I need to make a new contribution, or do my annual rebalance, I update the spreadsheet to see which asset classes are underweighted and should receive new money.
This is what we do as well. I have noticed even without the aid of a spreadsheet with only a few funds it takes minutes to identify the funds underweight.
John C. Bogle: “Simplicity is the master key to financial success."
mindboggling
Posts: 472
Joined: Sat Sep 06, 2014 6:35 pm
Location: Upstate NY

Re: Spreadsheets

Post by mindboggling »

66 y/o, retired. No spreadsheets here. I use the accounting program Gnucash to track income and expenses. I don't track investment results. Almost everything is at Vanguard so I just use their website. My investments are very simple.
In broken mathematics, We estimate our prize, --Emily Dickinson
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

Texanbybirth wrote: Tue Sep 17, 2019 10:11 am I get my spreadsheet modeling/tinkering/macro-ing fix at work. :twisted:
Looking back over the day it involved building and maintaining spreadsheets all day! Further motivates me to want to pull the plug on any investment spreadsheets at home.

Getting my head around the fact that any information that is needed is available on Vanguard's website and statements.
John C. Bogle: “Simplicity is the master key to financial success."
User avatar
grabiner
Advisory Board
Posts: 28259
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: Spreadsheets

Post by grabiner »

I also use a spreadsheet to keep track of tax lots. I have non-covered shares, so Vanguard won't track them properly, and the 1099-B reported gain will not match what I need to enter on my tax form. In addition, I have had Vanguard use the wrong lot with donations of ETF shares (donating from the oldest lot, rather than from the highest-cost lot), so having my own spreadsheet as backup allows me to confirm that the numbers check.
Wiki David Grabiner
Tdubs
Posts: 1117
Joined: Tue Apr 24, 2018 7:50 pm

Re: Spreadsheets

Post by Tdubs »

I used the Bogleheads Returns Spreadsheet that can be found on the wiki at https://www.bogleheads.org/wiki/Calcula ... al_returns
User avatar
Topic Author
abuss368
Posts: 22151
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: Spreadsheets

Post by abuss368 »

Tdubs wrote: Tue Sep 17, 2019 9:19 pm I used the Bogleheads Returns Spreadsheet that can be found on the wiki at https://www.bogleheads.org/wiki/Calcula ... al_returns
I would agree that the wiki has nice samples for anyone who has a need.
John C. Bogle: “Simplicity is the master key to financial success."
Post Reply