What all do you track on your spreadsheet?

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
radiowave
Posts: 3352
Joined: Thu Apr 30, 2015 5:01 pm

Re: What all do you track on your spreadsheet?

Post by radiowave »

I follow similar models as above. I have a worksheet for each calendar year that summarizes the portfolio across multiple institutions, retirement accounts, etc. and includes basic info about cash on hand, asset allocation, etc. Then I have a summary worksheet that pulls the summary values for each calendar year worksheet in its own separate column and YTD for current year, I have SWR calculations for fixed percentages, summary tax info and summary expenses. This feeds a separate retirement worksheet that looks ahead and includes income flows like SS, pensions, RMD and I can simulate different scenarios for inflation, return rates, etc. I have a separate income worksheet that follows monthly pensions and SS (in future) to keep track that everything is getting deposited. I update the current year worksheet every month or so, just to keep track of what is happening to the portfolio and examine the summary worksheet to see if I need to rebalance. At the end of the calendar year, I finish the years entry, then create a new worksheet for next calendar year and adjust the summary worksheet as needed.

Yeah, it sounds like a lot of work, but I find it only takes about 15-20 min per month to look up the brokerages and banks online and enter the new numbers for the portfolio and I generate activity on all institution websites.
Bogleheads Wiki: https://www.bogleheads.org/wiki/Main_Page
User avatar
vanbogle59
Posts: 1314
Joined: Wed Mar 10, 2021 7:30 pm

Re: What all do you track on your spreadsheet?

Post by vanbogle59 »

I have a workbook. It has about 20 tabs.
All but 2 have some past date as part of the title (e.g. budget 2005, Stocks 1999, socsec 2015).
I keep those around to remind myself where I came from. Oh how silly (and complicated!) I used to be.

The other 2 have everything I currently care about:
1) My passwords: about 50 of them, written in a secret code that only I can understand (I hope!)
1a?) including my pw to a firecalc simulation that I like to play with (does that count as another spreadsheet? :oops: )

2) My account balances:
2a) Vanguard IRAs (3 funds)
2b) Ally IRA (cds)
2c) current very small pension and 401K (I don't even really know where it is, just Megacorp's website :( )
2d) coinbase (fun!)

I occasionally add notes to 2 (how/when to rebalance, ATHs or some tax event from this year, nothing special)
User avatar
CommitmentDevice
Posts: 274
Joined: Tue Apr 02, 2019 3:25 am

Re: What all do you track on your spreadsheet?

Post by CommitmentDevice »

I track assets and liabilities monthly. I manually grab the account balances from Mint.
Image

My sheet does some basic analysis on the balances.
Image

I use this sheet for rebalancing. I rebalance monthly through purchasing. I get the current market value of my holdings from my brokerage. The sheet then tells me where to buy to move back towards my asset allocation.
Image
Hannibal Barca
Posts: 145
Joined: Sun Apr 11, 2021 12:22 pm

Re: What all do you track on your spreadsheet?

Post by Hannibal Barca »

What I track in my main workbook:

-Tab for investment options I've vetted as good to use in each of my different accounts. This makes things easier when I'm looking to rebalance. For example there are only 4 passive funds with good expense ratios in my 401k. I also have a list of my favorite Vanguard funds (VOO, VBR, VEA, VWO, VGK, VPL, VNQ).
-Balance sheet tab - Includes individual positions in different rows, summed-up to show my cash, taxable accounts, tax-advantaged accounts, tax-exempt accounts, real assets (house, car), liabilities (CCs, mortgage), etc. This tab also calculates my net worth (equity on the balance sheet) and after-tax net worth
-After-tax Asset allocation tab, which I use to understand my portfolio mix vs my strategic and tactical asset allocations. It also calculates how many incremental dollars I need to add / subtract to hit the tactical asset allocation
-Net Worth chart tab (shows both pre-tax and after-tax net worth lines)
-Tab with individual savings contributions I've made over time (so every 401k contribution from my payroll, employer match, other contributions I've made to taxable accounts, etc.)
-Tab summing those savings and comparing that to my savings targets (e.g., x% of my base salary, y% of my post-tax bonus, the surplus or deficit to that aggregate target)
-Tab with investment portfolio roll-forward (shows build of BOP balance, inflows, outflows, market action, EOP balance by year) and some related metrics like % market action / avg balance, implied alpha, etc.
-Tabs for paychecks from my employers over the years. This feeds into the savings contributions tab, and the savings target tab

What I don't need to track:

-Budget - I have a high level budget, and every 4-6 months I pull all my CCs' transactions and make sure spending is in-line, but it's never that surprising. I just don't spend a lot of money.
-Forecasting future value - I know my portfolio value, so it's not hard to type that into a generic investment calculator online, assume a real ROR and a time horizon, and get my future value. Also I can get to the same place using rule of 72 and powers of 2. I'm sure you could monte carlo it and what not, but the more money you have, the more risk you can accept and the less that sort of testing matters
-Retirement spending - I don't really want to destroy the pile of money I will spend a lifetime growing, so I only plan to take 2% out a year in retirement. 2% x future value gets me my retirement spending power. Whether I actually need that much money is another story, I'm pretty cheap.
User avatar
riverant
Posts: 1073
Joined: Tue May 04, 2021 6:51 am

Re: What all do you track on your spreadsheet?

Post by riverant »

I use quicken to aggregate all my transactions, account balances, and cash flows. I then export all transactions and various report information (asset allocation, account balances, asset valuations, investment balances) into a PowerBI report that displays monthly and YTD information going back to 2016 or so.

I don’t budget per se, but keep an eye on monthly net income, spend by category to make sure outliers make sense, and verify our asset allocation. It’s fun to see the net worth line increase as it clearly shows the benefit of compound returns (and an awesome bull run).

In excel, I also have a lifetime financial planning sheet that estimates outcome based on retirement dates and various assumptions.
Mako52
Posts: 174
Joined: Mon Jul 30, 2018 11:07 am

Re: What all do you track on your spreadsheet?

Post by Mako52 »

Every quarter for the last 20 years I have listed all of my assets and liabilities in rows and the quarter-ending values in each column. The column entries also include credit score and S&P value. The assets are listed as individual account numbers, not grouped.

At the bottom of the spreadsheet the rows are aggregated into the following categories:

Total cash
Total 529 plans
Total taxable retirement
Total non-taxable retirement
Total taxable brokerage
Home Equity
Total Net Worth

Other tabs include past mortgage summaries (amount/rate, average monthly interest, for example), annual and updated budgets, annual income by year with Federal and state income taxes.
JoeQ
Posts: 66
Joined: Mon Apr 05, 2021 10:57 am

Re: What all do you track on your spreadsheet?

Post by JoeQ »

Jaymover wrote: Sat Aug 07, 2021 8:28 pm I don't think there is one size fits all approach but I think developing your own spreadsheet and then keeping it updated helps with familiarising yourself with your financial position (for better or worse).

For me a figure that determines your overall asset allocation minus emergency and fun fund is a good figure for me to keep an eye on. It is an indicator of the amount of risk you are taking at the time of asset review.

However I was recently listening to a podcast and they say many investors get it wrong focusing on their net wealth and are better off focusing on cash flow and liquidity. For instance its all very well owning a $10million dollar mansion but if it costs $200K a year to own and your income is $60K and you are not in a position to ever sell it the $10million is a bit meaningless. I have to listen to that podcast again to try and get a grip on the concept as it is counter intuitiveeand contrary to many wealth maximisation spruikers talk about.
I'd love to listen to that podcast as well. Can you share please?
75% Total US Stock | 25% Total International Stock
User avatar
Meg77
Posts: 2835
Joined: Fri May 22, 2009 1:09 pm
Location: Dallas, TX

Re: What all do you track on your spreadsheet?

Post by Meg77 »

I have a spreadsheet that has grown in complexity over the years. I started it when I got married in 2014, so there are tabs for each year since then on which I track our monthly net worth. Then there is an "Annual progress" tab that looks the same as those but shows the year end net worth for each year (and projections/goals going out 10 years).

Then I have an asset allocation tab. I get most of this data from personal capital but add my investment RE and a couple of things that don't sync with PC to get the real AA. I also have columns where I can play with what to buy and see how it would change AA over.

Then there's a cash flow tab showing the next 6-12 months of flows in and out of our sole joint checking account. I estimate net pay, bonuses, investment income and gifts on the income side and project spending (credit card payments), mortgage, investments made, and periodic taxes and gifts paid on the outgo side.

The income tab shows my income and DH's each year since marriage. Base salary, bonus, RSUs, 401k match and any other reimbursements are tallied here to show our total income. A chart next to that shows my medicare and ss earnings going back to my first job in 2002 which I get from the government website every few years.

Then I have a Rentals tab with a section for each of my 4 (previously 6) rentals summarizing the rent and expenses for each one each year. This info matches what I report on my tax return. I have additional columns showing the actual cash flow though - the net income less the amount of mortgage principal paid that year (which populates from the net worth tab that year).

Then a taxes tab that summarizes our taxable income each year and on which I try to predict our tax burden in current/future years so I can adjust withholding periodically. This has gotten really hard to do now that we have so many K1s and RE syndications I can't control the sale of. So I ignore this a lot more these days and just try to overwithhold.

Then I have a Budget tab that shows a 5 year average of our spending - which I get from Mint and update every few months or once a year. I also have a global cash flow analysis I did once showing all rental income and expenses combined with personal. It's not really perfect but is interesting to see. That one shows what I consider to be my gross savings rate because it breaks out interest paid as a separate expense from principal repaid on all my mortgages.

Then another rental tab showing a simple overview of the rentals and basic/projected income and expenses, CAP rates, NOI, assumed appreciation, assumed equity, and so on. On my net worth tabs I value it all at cost so this lets me see what I actually think they are worth.

A debt tab I don't look at much any more shows my 4 remaining mortgages, LTV, net equity (if I sold), the balance, the rate and effective tax rate, and my payments broken down to P and I.

A "retirement" tab predicts my total income if I were to retire today, or in any of the last 8 years since I track it. I assume 4% SWR on investments and retirement (I split these up as "investments" are actual liquidity I can tap any time), and add RE cash flows. I can manipulate the returns I assume on each different type of asset to make projections going through 2044. It's a rough estimate but fun to see how my future income grows with every year I keep working and saving. If I retire this year our retirement income could be $150K. Milestone! If I work and save like I am through 2024 it should hit $200K.

A Timeline tab shows a 10 year timeline across the top and then a rough and simpler net worth chart underneath along with our salaries, life events and financial notes (sold this property, paid off that debt, got a big gift, etc). I also have a pie chart showing a breakdown of our assets and a bar chart showing the growth of various asset classes over time.

An Estate Plan tab shows each individual account, credit card, and investment, the legal owner, who we want as beneficiary, whether we consider it separate or joint property. It's the beginning of the creation of a will and estate plan which has been in the works for years and left undone...

An insurance tab I don't keep up with shows all our insurance policies, deductibles, and dwelling/liability coverages.

A RE Purchase tab calculates projected returns on a RE purchase with slots I can edit for down payment amount, rate, amortization, etc. It's not very sophisticated but gives me a template to do the manual calculations when I'm considering an investment.

I also have a "split" tab I worked on when DH and I separated in 2020 to figure out how we'd divide our assets. It's basically our net worth chart at beginning and at present (I haven't updated it since 2019 values though) with adjustments for separate property I comingled at various times and with various assumed return rates. There are also adjustments for how our retirement accounts have grown differently due to different AAs (and his higher match).
"An investment in knowledge pays the best interest." - Benjamin Franklin
Jablean
Posts: 872
Joined: Sat Jun 02, 2018 2:38 pm

Re: What all do you track on your spreadsheet?

Post by Jablean »

My workbook has lots of worksheets in it but I don't do a lot of linking back and forth. My main sheet has tracked my cash, fund, net worth since 2000. I just keep year end on all but the last 2 years. It's about 400 rows tall but some of those are empty nowadays as I've moved investments from three firms all to Fidelity. I track my mix there by doing SumIfs for types of funds and the bottom part has asset mix graphs tied to those sums and my net worth graph. I used to have to open each firm and my bank up on the web to get the numbers but nowadays I can pull it all up in Fidelity's Full View. I plan my RMDs from my inherited stretch IRAs and I make notes such as what year we bought a car, or changed jobs.

Another sheet tracks where we are for the ACA cliff. We are both 1099 so this looks at my monthly income (I have estimated income in the unfilled months) estimated taxes and tracks IRA, Roth, SEP, HSA contributions and Conversions for the year. It has all my notes, important links, and instructions such as putting the ACA money in my DH account next year instead of mine. It's not very tall but is wider as I copy the 12 or so columns every year over to the right. I match my numbers against TurboTax and figure out things like LTCapGains is against Taxable Income and not the Adjusted Gross Income that I watch for the ACA cliff.

I've got a kid in college so I track expenditures in one sheet and in another I've got my 5 year college payment plan so that I know when to use the AOTC credit or LLC and which year not to use the Coverdell or 529 because the scholarship covers enough that I have to watch what pays for what or I won't get the AOTC credit. I did not overfund the 529 but plug back in some of what I pull out to get my state tax credit.

DH is a year away from Medicare so I have several tabs where I make notes on SS and one where I do a 20 year expense trial run where I estimate my total expenses and then figure out where the money needs to come from to pay for it. Makes my trust the 25x needed advice a bit more.

I used to track credit cards when we had some high balances that had to be slowly paid off and I did a budget if we had a job loss etc but I don't track expenses (I don't think I ever had the patience) If I really wanted to then Fidelity can build me a budget based on my monthly expenses it pulls from my bank. I just go to the bank and it has a place where it shows me the balance of my spending account for the last 6 months. As long as that is staying fairly steady (I put a predetermined amount into it every month) then I figure we are spending within our limits.

I track some of my other life things there too that aren't financial. It really is the source of anything that might get updated once a year or more. I make a copy and rename it now and then so that hopefully it doesn't crash. I'm still using Office 2007. Nothing fancy in my formulas besides sums.
GeoMetry
Posts: 243
Joined: Mon Jan 31, 2011 7:26 pm

Re: What all do you track on your spreadsheet?

Post by GeoMetry »

I have a column in my spreadsheet that tells me how much tax is imbedded in my accounts based upon my multiyear forward looking tax plan, currently about $200k. So when the value of the investments in my tax deferred accounts go up so does the amount in the imbedded tax column.
User avatar
max12377
Posts: 294
Joined: Tue Jul 15, 2008 4:02 pm

Re: What all do you track on your spreadsheet?

Post by max12377 »

I don’t use a spreadsheet at all for my portfolio. I just put all my investments into vanguard’s tool and let it calculate my aa. There is a place to put outside investments so I use that for the non vanguard stuff. Other than the total number, the only thing I really care about is my aa and overall portfolio expense ratio. It’s basically a 3 funder and I rarely fuss with it. Once in a while I updated the number for the stable value but other than that it is really simple to manage.

I use mint to keep track of my spending. Been doing that for years so I have nice fancy charts I can play with.

When I left a job not too long ago I did work up a spreadsheet to figure out all my monthly expenses and expected outflow just in case I wanted to retire. It reminded me of some yearly subscriptions I had left on autopilot & I cleared out all the nonessential bs. It was basically a monthly budget that I developed. I multiplied the monthly budget by 12 to get my yearly budget. Then figured out I could live on a sub 3 percent withdrawal rate and it made me happy.

Then I went back to work. It was a really nice 6 month practice retirement and it made me to look at everything closely so I’d be better prepared for the real one.
Arpanet
Posts: 54
Joined: Fri Aug 03, 2018 12:24 pm

Re: What all do you track on your spreadsheet?

Post by Arpanet »

vanbogle59 wrote: Mon Aug 09, 2021 1:57 pm 1) My passwords: about 50 of them, written in a secret code that only I can understand (I hope!)
:shock: :shock: :shock:

I am begging you to get something like 1password. It will make your life much easier day-to-day and protect you from unlikely but catastrophic risks.
Susan1963
Posts: 15
Joined: Wed Aug 29, 2018 1:52 pm

Re: What all do you track on your spreadsheet?

Post by Susan1963 »

It's really a personal decision.
We keep 2 spreadsheets.
One is the "Road to Retirement" and is a net worth statement and includes our passive income (pensions and dividends).
The other is a spending spreadsheet that we have maintained for over a decade, with about 30 categories, so that we can see where the money is going and estimate which expenses - and what amounts - are likely to disappear/be reduced in the future.
User avatar
vanbogle59
Posts: 1314
Joined: Wed Mar 10, 2021 7:30 pm

Re: What all do you track on your spreadsheet?

Post by vanbogle59 »

Arpanet wrote: Wed Aug 11, 2021 11:58 am
vanbogle59 wrote: Mon Aug 09, 2021 1:57 pm 1) My passwords: about 50 of them, written in a secret code that only I can understand (I hope!)
:shock: :shock: :shock:

I am begging you to get something like 1password. It will make your life much easier day-to-day and protect you from unlikely but catastrophic risks.
Well, at risk of helping the hacker who steals my spreadsheet :o ....
I do have "something like 1 password" :beer
But not literally 1 password. (I don't want a breach at one source to be a breach for all.)
Nor do I have 1 ID.
Stoic9
Posts: 369
Joined: Mon Dec 10, 2018 6:14 am

Re: What all do you track on your spreadsheet?

Post by Stoic9 »

Retired 4 years last week. I track calendar monthly/yearly and retirement year.

Investments: Tax deferred/taxable/Roth

Real estate: rentals

Total net worth: zero debt

Pretty easy as with the exception of the market slight downturn in 2018 everything goes up.
wfrobinette
Posts: 1879
Joined: Fri Feb 20, 2015 2:14 pm

Re: What all do you track on your spreadsheet?

Post by wfrobinette »

the-worst-investor wrote: Sat Aug 07, 2021 7:55 pm I have been working on an Excel spreadsheet to track our investments and was curious what, if anything, other Bogleheads include on their spreadsheets. I did use Personal Capital to aggregate all of our accounts, but I got a little wary of putting in my passwords and logins to a third party aggregator so I decided to make my own spreadsheet to keep track. Currently, I am trying to keep the sheet simple. I only have names and ticker symbols, shares held, expense ratio, current value, and a notes section for notes about the holding.

How complex is your spreadsheet, if you even have one? If not, how do you prefer to track your investment holdings across various accounts?
KISS

I have 1 workbook that I look at once or twice a year. Asset Allocation targets vs Asset allocation actuals, yearly totals(value contributions) by type of account (tIRA, rIRA, cash)

At the end of the day, it comes down to actionable information. If I can't act on the data point I don't track it. If the data is tracked at the same grain somewhere else it stays there(tax lots, number of shares, expense ratios, etc.).
jerrysmith
Posts: 107
Joined: Sat Feb 16, 2019 1:20 pm

Re: What all do you track on your spreadsheet?

Post by jerrysmith »

I use YNAB for budgeting but I track our NW in a spreadsheet. We don't own individual securities so it's pretty basic. IRA/Pension1/Pension2/403B/457-1/457-2/Mortgage/Student Loans/Cash/Home value.
tibbitts
Posts: 23716
Joined: Tue Feb 27, 2007 5:50 pm

Re: What all do you track on your spreadsheet?

Post by tibbitts »

I don't use a spreadsheet at all. I've used the Vanguard Yodlee service since... whatever it was before Yodlee (which was better in a way because you could characterize outside investments manually vs. the software choosing "other.")
an_asker
Posts: 4903
Joined: Thu Jun 27, 2013 2:15 pm

Re: What all do you track on your spreadsheet?

Post by an_asker »

the-worst-investor wrote: Sat Aug 07, 2021 7:55 pm I have been working on an Excel spreadsheet to track our investments and was curious what, if anything, other Bogleheads include on their spreadsheets. I did use Personal Capital to aggregate all of our accounts, but I got a little wary of putting in my passwords and logins to a third party aggregator so I decided to make my own spreadsheet to keep track. Currently, I am trying to keep the sheet simple. I only have names and ticker symbols, shares held, expense ratio, current value, and a notes section for notes about the holding.

How complex is your spreadsheet, if you even have one? If not, how do you prefer to track your investment holdings across various accounts?
Same here. Have essentially the same fields.

I do have it broken down - separate tabs - by Taxable, tax deferred and tax free (aka Roth). I update monthly.
an_asker
Posts: 4903
Joined: Thu Jun 27, 2013 2:15 pm

Re: What all do you track on your spreadsheet?

Post by an_asker »

Meg77 wrote: Tue Aug 10, 2021 10:13 am I have a spreadsheet that has grown in complexity over the years. I started it when I got married in 2014, so there are tabs for each year since then on which I track our monthly net worth. Then there is an "Annual progress" tab that looks the same as those but shows the year end net worth for each year (and projections/goals going out 10 years).

[...]

I also have a "split" tab I worked on when DH and I separated in 2020 to figure out how we'd divide our assets. It's basically our net worth chart at beginning and at present (I haven't updated it since 2019 values though) with adjustments for separate property I comingled at various times and with various assumed return rates. There are also adjustments for how our retirement accounts have grown differently due to different AAs (and his higher match).
Wow Meg! Those are all different spreadsheets on my laptop :-)

And even then, all of my spreadsheets don't cover everything yours does!!
Count of Notre Dame
Posts: 448
Joined: Fri Oct 11, 2013 1:08 pm

Re: What all do you track on your spreadsheet?

Post by Count of Notre Dame »

Stopped tracking my spending years ago, since all that matters is a steady net increase in my net worth! I keep a balance sheet going of my assets and liabilities, just the account values. Update it on a monthly basis.
Money_Badger
Posts: 465
Joined: Thu Jul 02, 2020 11:05 pm
Location: Raleigh NC

Re: What all do you track on your spreadsheet?

Post by Money_Badger »

My s/s has grown and grown over the years. It currently has the following tabs:

1. Total Retirement Assets (which aggregates automatically from other tabs). It also goes back 7 years and is updated monthly.
2. Mortgage (including history and projected)
3. Asset Allocation (aggregates automatically from other tabs ... with a slight monthly tweak)
4. Individual Retirement Assets (most of which pulls price quotes directly so no updates are required). A few I have to update manually, which I do once a month. I added this tab 10 months ago to track the performance of individual assets month to month.

Probably overkill, but it works for me.
Golf maniac
Posts: 1359
Joined: Wed Dec 27, 2017 1:02 pm
Location: Florida

Re: What all do you track on your spreadsheet?

Post by Golf maniac »

I have an Libre Spreadsheet with probably 10 to 15 different worksheets. They include a detailed budget that we update every week showing all expenses and sources of income (pension, investments, wife’s SS). There is also a higher level annual budget worksheet we use to determine future expenses out 5 years and sources we will use to fund the expenses. Very simply project a 3% inflation.

I have three investment worksheets, one shows if I start to take SS at 62, one at FRA, and one at 70. These simply assume a 4% return on investments and a 3% inflation for expenses with withdrawals each year. This has proven to be very conservative. The plan is to wait until 70 so the survivor benefit will be as high as possible. I have a SS worksheet that actually has all my earnings loaded in to check against SSA.

I have a worksheet that has the cost of our home and all the upgrades we have done to have a good idea of how much we have invested. It also tracks taxes paid each year but doesn’t track minor maintenance.

We have about 4 or 5 worksheets that show how my wife will fare if I die at various ages. Given my pension will be cut in half and she picks up my SS and loses hers, we wanted to make sure she can live comfortably if I die. We have term life insurance to help until I am 76, at that point she should be fine. If she dies first obviously I will be fine from a financial perspective. Finally, there is an RMD worksheet that I need to update for the changes in the law. Obviously I am a finance major and love all the detail and running various scenarios.
Investing86
Posts: 14
Joined: Fri Jan 20, 2017 1:39 pm

Re: What all do you track on your spreadsheet?

Post by Investing86 »

I update a spreadsheet monthly with this info

Column A - Savings/Checking
Column B - Brokerage Account
Column C - 401K
Column D - Roth IRA
Column E - Home Equity (I just do how much principal I've paid off, I don't account for any increase in home value, as I am not selling anytime soon and I don't know how realistic the Zillow estimates are)

Those listed above are the main columns. Then I have a couple of just summary columns.
Column F - Cash on Hand (Just adding Column A + B together)
Column G - Total (Adding Columns A-E)
Column H - Savings % of Total Cash - Column A divided by Column F
Column I - Projected monthly pension payment if I retire today (I just pull this from my employer 401k page)
jdb
Posts: 1759
Joined: Wed Dec 05, 2012 7:21 pm

Re: What all do you track on your spreadsheet?

Post by jdb »

furikake wrote: Sun Aug 08, 2021 2:35 pm I don't have an investment spreadsheet, am I in trouble?
If you’re in trouble so am I. All investments at Vanguard, plus some individual stocks. Once every few weeks I do a quick drive by Vanguard review, though do check individual stocks more often out of curiosity. End of each year I do a Vanguard rebalance and tax loss harvest, if applicable. And save a lot of time for things I enjoy more, like mowing my lawn. Good luck.
Jaymover
Posts: 214
Joined: Wed May 12, 2021 8:19 pm

Re: What all do you track on your spreadsheet?

Post by Jaymover »

JoeQ wrote: Tue Aug 10, 2021 9:39 am
Jaymover wrote: Sat Aug 07, 2021 8:28 pm I don't think there is one size fits all approach but I think developing your own spreadsheet and then keeping it updated helps with familiarising yourself with your financial position (for better or worse).

For me a figure that determines your overall asset allocation minus emergency and fun fund is a good figure for me to keep an eye on. It is an indicator of the amount of risk you are taking at the time of asset review.

However I was recently listening to a podcast and they say many investors get it wrong focusing on their net wealth and are better off focusing on cash flow and liquidity. For instance its all very well owning a $10million dollar mansion but if it costs $200K a year to own and your income is $60K and you are not in a position to ever sell it the $10million is a bit meaningless. I have to listen to that podcast again to try and get a grip on the concept as it is counter intuitiveeand contrary to many wealth maximisation spruikers talk about.
I'd love to listen to that podcast as well. Can you share please?
https://www.morningstar.com.au/learn/ar ... ass/206215

" I guess you lose some you win some, as long as the outcome is income" . That one.

Although for Australian investors, one of the hosts is from the US I think so still talking about a global investor context. This is a really good, well informed series of podcasts if you start from the begining
JoeQ
Posts: 66
Joined: Mon Apr 05, 2021 10:57 am

Re: What all do you track on your spreadsheet?

Post by JoeQ »

Jaymover wrote: Mon Aug 16, 2021 7:05 pm
JoeQ wrote: Tue Aug 10, 2021 9:39 am
Jaymover wrote: Sat Aug 07, 2021 8:28 pm I don't think there is one size fits all approach but I think developing your own spreadsheet and then keeping it updated helps with familiarising yourself with your financial position (for better or worse).

For me a figure that determines your overall asset allocation minus emergency and fun fund is a good figure for me to keep an eye on. It is an indicator of the amount of risk you are taking at the time of asset review.

However I was recently listening to a podcast and they say many investors get it wrong focusing on their net wealth and are better off focusing on cash flow and liquidity. For instance its all very well owning a $10million dollar mansion but if it costs $200K a year to own and your income is $60K and you are not in a position to ever sell it the $10million is a bit meaningless. I have to listen to that podcast again to try and get a grip on the concept as it is counter intuitiveeand contrary to many wealth maximisation spruikers talk about.
I'd love to listen to that podcast as well. Can you share please?
https://www.morningstar.com.au/learn/ar ... ass/206215

" I guess you lose some you win some, as long as the outcome is income" . That one.

Although for Australian investors, one of the hosts is from the US I think so still talking about a global investor context. This is a really good, well informed series of podcasts if you start from the begining
Thank you. For anyone else interested, it's actually the episode before that one, called "Cash rules everything around me".
75% Total US Stock | 25% Total International Stock
chris319
Posts: 1659
Joined: Thu Jan 28, 2021 5:04 pm

Re: What all do you track on your spreadsheet?

Post by chris319 »

I have a Google spreadsheet to track my portfolio every day. Prices are updated automatically.

For permanent files such as bank and tax records I use Excel spreadsheets which are stored on my local HD. I'm just not comfortable keeping these "permanent" records in the cloud for fear that they could some day become unavailable for whatever reason.

I also back up (clone) my HD's regularly. Cloning gives me a bootable backup and this is oh-so-handy in the event of a mishap.
Financial decisions based on emotion often turn out to be bad decisions.
Fat Tails
Posts: 519
Joined: Wed Oct 30, 2019 12:47 am
Location: New Mexico

Re: What all do you track on your spreadsheet?

Post by Fat Tails »

It started as a spreadsheet with just 2 tabs, one for value of financial assets organized by taxable status with the total compared to our goal for entering retirement, and the second tab is a chart with value and date as the axes. I update every 6 months.

Once in retirement, I added a tab with annual projection of accounts’ growth, income (including Roth conversions)) and taxes (and tax bracket) and required minimum distributions starting at age 72. I update this annually to determine the amount of Roth conversions we will do for the current year and an estimate for the future years.

Then I added a 3rd tab to compare Roth conversions vs no Roth conversions to determine the breakeven time.

It has been very eyeopening on the impact of Roth conversions on RMDs.

Cheers
“Doing well with money has little to do with how smart you are and a lot to do with how you behave.” - Morgan Housel
corpgator
Posts: 217
Joined: Wed Jul 22, 2015 12:00 pm

Re: What all do you track on your spreadsheet?

Post by corpgator »

I use the bogleheads return tracker to see my returns over time. In another spreadsheet I have a summary sheet that has all the various investment accounts that I'm slowly consolidating split between fixed and equity, but cash is the only fixed I have right now since I'm buying a house. I'm a churner, so I also have a sheet with all my bank accounts, most of them empty waiting to be closed because of bank account bonuses, another sheet for credit cards and their balances, a sheet to track bank account bonuses, a sheet for comparing job salaries for when I'm switching jobs, a sheet for looking at house prices and the various scenarios to see if it's a good idea to buy or rent or which house I would be better off buying, a sheet for tracking roth IRA basis, a sheet listed our current gift cards, and finally a sheet for total wealth that shows all of our assets.
Don Rico
Posts: 1
Joined: Thu Aug 26, 2021 7:14 pm

Re: What all do you track on your spreadsheet?

Post by Don Rico »

Post by Broken Man 1999 » Mon Aug 09, 2021 1:37 pm
"On the Vanguard site I have set up proxies for our Transamerica VA, so it is tracked/updated automatically."

Since the Transamerica migration from Vanguard VA, How does one accomplish this?
I contacted TA but there is no way to get a good spreadsheet like VG had of the VA, share, price, quantity, value for each VA folder.
So how did you set up the TA VA proxies to access this on the VG Balances & Holdings pages? thx.
L84SUPR
Posts: 343
Joined: Sat Feb 01, 2014 12:28 pm

Re: What all do you track on your spreadsheet?

Post by L84SUPR »

Input
The balance and ER for each fund in each account. The percentage distribution of each fund as US stocks, Intl stocks, and bonds.

Output
The spreadsheet calculates total dollars, percent stocks, percent bonds, percent of stocks in US, percent of stocks in Intl, and weighted ER.
1/3rd VTWAX, 1/3rd Wellington, 1/3rd G Fund | All models are wrong. Some are useful.
Impatience
Posts: 724
Joined: Thu Jul 23, 2020 3:15 pm

Re: What all do you track on your spreadsheet?

Post by Impatience »

I just track a summary of each account’s value. So net worth. Do it once per pay check so every 2 weeks. It’s not that interesting to look at but I feel it keeps me disciplined.
scrabbler1
Posts: 2798
Joined: Fri Nov 20, 2009 1:39 pm

Re: What all do you track on your spreadsheet?

Post by scrabbler1 »

I have several spreadsheets.

For each mutual fund in my taxable account, I have a spreadsheet where I record each transaction. I also use it to determine cost basis when I make a sale. The rollover IRA spreadsheet has both mutual funds together and a tab for my overall portfolio including taxable.

I have a checkbook register spreadsheet where I record each transaction in the checking account. It is also linked to a skeleton version of my income tax returns. I can play with that part of it to do what-if scenarios which is useful for determining estimated taxes.

Another spreadsheet keeps monthly balances of my overall taxable account.

Another spreadsheet is my early retirement spreadsheet which includes a projected checkbook register, or cash inflows and outflows. It has a section for projecting overall expenses and income in my initial early retirement years.

Another spreadsheet shows my income and categorized expenses by year. This was also handy when planning my early retirement.

It's a lot of spreadsheets, but in my career I created and played with lots of spreadsheets, so it's second nature to by now.

When I was working, I had a spreadsheet for my 401k and company stock holdings.
Reamus294
Posts: 265
Joined: Wed Jul 24, 2019 8:54 am

Re: What all do you track on your spreadsheet?

Post by Reamus294 »

I have a spreadsheet that I use for expected budget, asset allocation, my FI number (expected expenses and income needed), expected retirement contributions/interest to make sure I am on the right path to get to the FI, basic upcoming tax calculations and a mortgage amortization when I had a mortgage.

I also keep a net worth number but may stop since it doesn’t serve much of a purpose for me. I used to do it to make myself feel better when we were paying a lot on the mortgage so I could see that number go up. I only use it to dream about moving to a hut on the beach now.

My asset allocation sheet is what I use to rebalance about quarterly which gets confusing with multiple accounts that can’t be combined yet. I have the mutual funds broken down by percentages of large/mid/small caps too to make sure I am within my desired bands.

I recently added info to my FI number tab to show how the expected retirement income will change with pension or SS which gets confusing. I’ll probably use this in retirement to make sure my balance is on the right path over a smoothing period.
SGM
Posts: 3341
Joined: Wed Mar 23, 2011 4:46 am

Re: What all do you track on your spreadsheet?

Post by SGM »

Yearly changes in the value of my portfolio.

Projected income streams to assess taxation changes each year.

I don't calculate net worth. Although I have a notional idea of the value of the real estate I own.

We do not budget. We spend what we need.
User avatar
enad
Posts: 1581
Joined: Fri Aug 12, 2022 2:50 pm

Re: What all do you track on your spreadsheet?

Post by enad »

I use one spreadsheet per retirement account e.g. Roth, Traditional, Rollover, etc ...
In each spreadsheet I have:

1) prices tab that links to a master spreadsheet were I past prices into from the web
2) a tab for each exchange traded fund in the account
3) a summary tab that pulls information from other tabs and lists: (a) Name of ETF, (b) Ticker Symbol, shares owned, price/share, value, return, basis, basis date, years held
4) another tab that I use to build the initial pie (S&D) and rebuilds it based with actual shares. I have included 5/25 balance bands to help me rebalance and uses color coded words BUY (green) or SELL (red) along with the number of shares.

I have a master spreadsheet that pulls information from all the other spreadsheets including the BUY/SELL signals (so I don't have to look at each individual one). It too rebuilds the allocation for my retirement accounts & for my wife's retirement accounts and then a combined one based on asset classes that gives me a final picture.

I didn't just start this one day, rather it evolved over 35 years. I used Quicken in the past but had to buy a new version every few years (that got old) and found that excel can do it all for me. Implementing 5/25 rule was a little tricky but has worked well for me.
What Goes Up Must come down -- David Clayton-Thomas (1968), BST
User avatar
Go Blue 99
Posts: 1119
Joined: Thu Oct 09, 2008 3:42 pm

Re: What all do you track on your spreadsheet?

Post by Go Blue 99 »

I do a monthly PnL which lists our income, as well as high level spending (categories of mortgage, utilities, credit card bill, daycare). My primary goal is to track our total monthly spending and how much we save per month. I also list monthly net worth.

In terms of our investments, I only use a spreadsheet to track rebalancing efforts.
Northster
Posts: 266
Joined: Wed Oct 28, 2009 3:30 pm

Re: What all do you track on your spreadsheet?

Post by Northster »

My main interest is seeing amounts invested by fund and then by category (equity vs. fixed) so I can make appropriate withdrawals or re- balancing. I find very useful the GoogleFinance function in Google sheets. By entering just my number of shares owned I can keep continuous tabs on the total value of my holdings for that fund, because the function calls up the latest price. No ongoing data entry needed unless there is a trade. Excel has a similar function.
User avatar
AnnetteLouisan
Posts: 7261
Joined: Sat Sep 18, 2021 10:16 pm
Location: New York, NY

Re: What all do you track on your spreadsheet?

Post by AnnetteLouisan »

I track my accounts monthly in a handwritten net worth spreadsheet that includes my banks, 401k, TSP, Treasury Direct, Taxable Brokerage, etc.

I keep a budget and monitor bills and expenses but don’t track them on a spreadsheet. I do keep a little handwritten list in the back of my daily planner of non food purchases for the year - mostly clothes, shoes and home appliances like a hair iron or air purifier. I so rarely buy anything besides food / grocery that I’m actually looking for something to buy. I don’t really need anything else that I can think of.

I keep track of my gifts, donations and tips that I give to make sure to meet or exceed that level each year. I keep track of accrued leave and sick days.

I used to track what I ate each day (diet/nutrition). Now I keep a list of what I do for my health each day (what you keep track of you get more of). I track on many days what was good that day.
Last edited by AnnetteLouisan on Sat Aug 13, 2022 3:28 pm, edited 5 times in total.
User avatar
FreddieFIRE
Posts: 826
Joined: Tue Aug 03, 2021 11:49 am

Re: What all do you track on your spreadsheet?

Post by FreddieFIRE »

I only have brokerage accounts with a single custodian and a small checking account at a local bank. Why would I need a spreadsheet to track investments? It's all right there online whenever I need it, along with a bazillion years of monthly and year end statements. I do have a comprehensive financial/tax planning spreadsheet, but that's all about planning and managing the future, not tracking current state to the nth degree.

I realize that some folks are not able to simplify to this extent. For the rest of you, what's your excuse? :twisted:
A house and a job. Once the American dream. Two things I'll never again have. Life is simple (and good).
User avatar
tangy
Posts: 179
Joined: Sat Jun 24, 2017 8:19 am
Location: a lively twirling planet located 93,000,000 miles from the Sun

Re: What all do you track on your spreadsheet?

Post by tangy »

Wow. Reading through all these makes mine look lame. Three columns:

I track retirement accounts only (IRAs, 401ks, HSAs), the funds, current dollar amt and one of two categories--- Equities or Bonds/Cash. I mainly use it to determine if my AA is out-of-whack. And I don't freak out if it is a bit lopsided. I add notes here and there for things I want to do in the future or to consider as time goes on. I have only one or two funds for each so it is rather simple and quick to update. Jack Bogle would be proud.

I find if I have too many columns to fill in, I won't do it. This is quick to do quarterly to see if some rebalancing should be done. I do not incorporate my spending accounts (money moves in/out too quickly). I also do not track my home value in my net worth--never understood the point. I have learned to keep it simple.
Who is John Galt ?
invest4
Posts: 1905
Joined: Wed Apr 24, 2019 2:19 am

Re: What all do you track on your spreadsheet?

Post by invest4 »

TAB1

* Summary of Investments: Similar to Morningstar tracker which is going away, but missing a few bits (Return YTD for example). Simple. I look at this regularly.

- Name of investment (including cash items)
- Ticker
- Current Price
- Shares Held
- Market Value
- % Weight

TAB 2

* Detailed Summary of Investments: To capture what and where monies are flowing in / out across the portfolio for all sources and types. You need to go into each account to get the desired level of detail, but we don't have THAT many accounts, so doesn't take too much time and is updated when I'm interested to do so (at least monthly).

- Account owner (myself or spouse)
- Account Source (Providers for 401k, Roth, HSA, etc.)
- Type of Account (Rollover IRA, 401k, 401k sweep, Roth, Roth sweep, HSA, Cash, etc.)
- Specific investment (VTI, BND, CASH, etc.)
- # of Shares
- Price
- Market Value

* Statistics of Interest: To capture the makeup of individual investments (ex: % Roth monies invested in VTI vs other for example) and monitor Asset Allocation elements. One time investment in setup and good to go.

- Totals by Source(each provider) and Type of Account (IRA/401k, Roth, etc.). Amount of monies in provider A (100K) with splits for type (50/50 between 401k and Roth for example.
- Amount and % of equity investments by account type (ex:% of monies in VTI are 60% in 401k / Rollover IRA, 34% Roth, 6% HSA)
- Amount and % stocks vs bonds (stocks 70%, bonds 30%)
- Amount and % US vs Intl for stocks and bonds (US 80%, Intl 20%)
GibsonL6s
Posts: 571
Joined: Tue Aug 29, 2017 12:17 pm

Re: What all do you track on your spreadsheet?

Post by GibsonL6s »

I have a spreadsheet that tracks my networth. I list all of my accounts and assets. I have a tab that lets me forecast future rates of return for the various accounts and I have a lookup table for the RMDs for my 401k, inherited IRA and regular IRA. I use this spreadsheet and add in social security and my annual forecasted spend, to see what retirement looks like. I can put in future contributions to the 401k or start withdrawals. Sort of a simple monte carlo.

I use the schwab site to get my asset allocation, as it was taking alot of time to input it into the spreadsheet. Schwab lets you combine accounts and give you the AA.
Californiastate
Posts: 1516
Joined: Thu Feb 04, 2021 10:52 am

Re: What all do you track on your spreadsheet?

Post by Californiastate »

I pulled the pin and picked up Pralana Gold. My spreadsheet is detailed but not to the level of PG.
Thick Planchet
Posts: 11
Joined: Wed Apr 24, 2019 11:06 pm
Location: U.S.A.

Re: What all do you track on your spreadsheet?

Post by Thick Planchet »

runner3081 wrote: Sat Aug 07, 2021 9:34 pm -Every penny we spend - monthly (broken down into categories)
-Our Budget/spending
-How our income is allocated, i.e., what percent to taxes, what percent to expenses, retirement, etc.
-Exact, calculated copy of my paycheck (line-by-line)
-Balance sheet showing all accounts and balances, updated quarterly
-FIRE Ratios including:
*****How many times expenses we have saved
*****Current expenses vs 4%, 3% and 2% withdrawal rate
*****Current expenses in addition to estimated ACA (healthcare premiums + max OOP) costs vs 4%, 3% and 2% withdrawal rate
-Emergency fund breakdown into tiers (Checking, savings, iBonds, etc.)
-Target eFund amount vs actual
-Gift Card Tracker (what store and how much left)
-Net Worth Tracker, including asset diversification % breakdown Cash/investments/real estate/Other assets
-Retirement funding sheet (tracks limit to 401K and 457B deposits) - Useful to adjust for pay changes, when vacation is cashed out, etc, to ensure match is maximized
-Estimated retirement income broken in to age brackets
What kind of spreadsheet do you use?
Ask for advice, but do what you think is best. -Greek Proverb
runner3081
Posts: 5993
Joined: Mon Aug 22, 2016 3:22 pm

Re: What all do you track on your spreadsheet?

Post by runner3081 »

Thick Planchet wrote: Tue Feb 20, 2024 6:56 pm
runner3081 wrote: Sat Aug 07, 2021 9:34 pm -Every penny we spend - monthly (broken down into categories)
-Our Budget/spending
-How our income is allocated, i.e., what percent to taxes, what percent to expenses, retirement, etc.
-Exact, calculated copy of my paycheck (line-by-line)
-Balance sheet showing all accounts and balances, updated quarterly
-FIRE Ratios including:
*****How many times expenses we have saved
*****Current expenses vs 4%, 3% and 2% withdrawal rate
*****Current expenses in addition to estimated ACA (healthcare premiums + max OOP) costs vs 4%, 3% and 2% withdrawal rate
-Emergency fund breakdown into tiers (Checking, savings, iBonds, etc.)
-Target eFund amount vs actual
-Gift Card Tracker (what store and how much left)
-Net Worth Tracker, including asset diversification % breakdown Cash/investments/real estate/Other assets
-Retirement funding sheet (tracks limit to 401K and 457B deposits) - Useful to adjust for pay changes, when vacation is cashed out, etc, to ensure match is maximized
-Estimated retirement income broken in to age brackets
What kind of spreadsheet do you use?
Google Sheets. One that I custom made.
MathWizard
Posts: 6560
Joined: Tue Jul 26, 2011 1:35 pm

Re: What all do you track on your spreadsheet?

Post by MathWizard »

I have a projected budget spreadsheet in Excel.

The rows are years (my age) the columns are Roth and tax deferred
balances, with an assumed real rate of return (fixed or variable depending on whether I am using Monte Carlo simulations or not).

The remaining columns are annual:
Income:
Roth withdrawals (negative for contributions via conversion)
Tax deferred withdrawals (with an if statement for RMDs)
Income from pension/annuities
Social Security income


taxable income (using state &federal tax tables and taxation of SS)

Outflow
Health care
taxes
Lumpy expenses
maintenance, car replacement LTC, change in housing

Number that is important to me:
disposable income = Income - Outflows

It is fairly busy spreadsheet developed over 15 years.
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: What all do you track on your spreadsheet?

Post by Raspberry-503 »

I've got a Big Spreadsheet of Everything with tabs for many different things. On the portfolio tracking I have a table of all our accounts along the top (taxable, my 401k, my Roth 401k, my IRA, my Roth IRA, DW's IRA, DW's Roth IRA, I think that it's :greedy ) and the funds along the side. We use VTI, VEA, VWO, BND and BNDX, but some accounts like my 401k don't offer those so I it's SP500 and we have couple TLH alternatives to some of those funds.
It sounds worse than it is, especially because Fidelity will export your current positions into a file I can import in Excel then automatically extract each cell I'm the table

This allows me to know how much I have in US Stock, Developed Stock, Emerging Stock, US bonds and Foreign Bonds both in each account and as a total. The spreadsheet compares the totals to my desired AA and shows me how off I am from my desired allocation (for that asset class, so $10k could be 1% off in VTI but 8% off in VWO, making this up of course).
If the deviation is out of the 20/5 bands, the spreadsheet highlights that this asset class needs rebalances.
It also of course shows me my overall AA
From the table the spreadsheet also shows my percentage of taxable, tax-free and tax-deferred

It makes it very easy to practice asset- location while still seeing your asset allocation across your accounts.

I also have a fancy macro that build a table every time the data changes so that I don't stacked plot of how my accounts evolve over time (very similar to the chart in Empower)
User avatar
YangtzeCruiser
Posts: 36
Joined: Sun Feb 15, 2015 6:26 am
Location: Minnesota

Re: What all do you track on your spreadsheet?

Post by YangtzeCruiser »

I have a multi-page annual spreadsheet that I snapshot monthly.

Page 1: Income by account. 12-month extent. Does not include 403(b)s, IRAs, Roths. Categories. Attempt to distinguish qualified vs. unqualified dividends, Tax-free vs. taxable interest, and long- and short-term capital gains. Includes a section wherein I estimate our Federal income tax.

Page 2: Net worth. Tracks all monies flowing through our checking and savings accounts, and end-of-month balances for all accounts including 403(b)s, IRAs, and Roths.

Page 3: Portfolios. End-of-month values of the individual holdings in each taxable account except the robo.12-month extent.

Page 4: Capital Gains/Losses. Cut and paste from Schwab Gain/Loss page

Page 5: Income projection: 12-month extent. Same format as Page 1, and used that data for prior months. For remainder of the year, uses Schwab's projections plus any known future income from T-Bills.

More complicated than need be? Perhaps - a consequence of owning too many individual stocks
Post Reply