Annual Tracking Spreadsheet Review?

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
Topic Author
JoeRetire
Posts: 4812
Joined: Tue Jan 16, 2018 2:44 pm

Annual Tracking Spreadsheet Review?

Post by JoeRetire » Tue Dec 31, 2019 6:00 am

I maintain a spreadsheet with virtually all my "numbers" in it.

I keep track of each IRA, each investment outside of an IRA, current Social Security benefit estimates, home equity, etc, etc. I also track our expenses during the year by category. I have tabs for projecting net income and expenses going forward. Basically, it's our financial life on a spreadsheet. I updated this spreadsheet periodically throughout the year (once or twice per month).

At the end of each year, I add some year-end balances, numbers and notes, file it away, and start a new spreadsheet for the upcoming year. The end of the year process provides a convenient time to reflect on the year and look ahead.

Anyone else do it this way? Any interesting variations?
Very Stable Genius

User avatar
RickBoglehead
Posts: 5608
Joined: Wed Feb 14, 2018 9:10 am
Location: In a house

Re: Annual Tracking Spreadsheet Review?

Post by RickBoglehead » Tue Dec 31, 2019 6:08 am

Quicken does it for me.
Avid user of forums on variety of interests-financial, home brewing, F-150, PHEV, home repair, etc. Enjoy learning & passing on knowledge. It's PRINCIPAL, not PRINCIPLE. I ADVISE you to seek ADVICE.

livesoft
Posts: 69764
Joined: Thu Mar 01, 2007 8:00 pm

Re: Annual Tracking Spreadsheet Review?

Post by livesoft » Tue Dec 31, 2019 6:11 am

MS Money does it for me, but yes, I make a backup each year for sure.
Wiki This signature message sponsored by sscritic: Learn to fish.

daheld
Posts: 786
Joined: Wed Sep 13, 2017 8:14 am
Location: Midwest US

Re: Annual Tracking Spreadsheet Review?

Post by daheld » Tue Dec 31, 2019 8:18 am

I don't track investments in a spreadsheet as I can easily log in to check balances and performance. We do use a fancy Excel spreadsheet my wife created to track income and expenses. It is more time consuming than Mint and similar programs, but as I've said here before I am uneasy giving all my login/banking info to a single entity. I understand the security features of such programs, but it seems an unwise risk.

acegolfer
Posts: 1676
Joined: Tue Aug 25, 2009 9:40 am

Re: Annual Tracking Spreadsheet Review?

Post by acegolfer » Tue Dec 31, 2019 8:29 am

My spreadsheet tracks every $ including investments without me entering any values.

acegolfer
Posts: 1676
Joined: Tue Aug 25, 2009 9:40 am

Re: Annual Tracking Spreadsheet Review?

Post by acegolfer » Tue Dec 31, 2019 8:34 am

daheld wrote:
Tue Dec 31, 2019 8:18 am
but as I've said here before I am uneasy giving all my login/banking info to a single entity. I understand the security features of such programs, but it seems an unwise risk.
This is why I don't use Quicken any more.

OTOH, Mint doesn't store passwords for major banks. It continues to work, even if I didn't tell my new passwords.
https://help.mint.com/Accounts-and-Tran ... ctions.htm

Silk McCue
Posts: 3983
Joined: Thu Feb 25, 2016 7:11 pm

Re: Annual Tracking Spreadsheet Review?

Post by Silk McCue » Tue Dec 31, 2019 8:51 am

acegolfer wrote:
Tue Dec 31, 2019 8:34 am
daheld wrote:
Tue Dec 31, 2019 8:18 am
but as I've said here before I am uneasy giving all my login/banking info to a single entity. I understand the security features of such programs, but it seems an unwise risk.
This is why I don't use Quicken any more.

OTOH, Mint doesn't store passwords for major banks. It continues to work, even if I didn't tell my new passwords.
https://help.mint.com/Accounts-and-Tran ... ctions.htm
If you don't want Quicken to store your passwords for your accounts don't put them in the password vault. Problem solved.

Cheers

acegolfer
Posts: 1676
Joined: Tue Aug 25, 2009 9:40 am

Re: Annual Tracking Spreadsheet Review?

Post by acegolfer » Tue Dec 31, 2019 8:54 am

Silk McCue wrote:
Tue Dec 31, 2019 8:51 am
acegolfer wrote:
Tue Dec 31, 2019 8:34 am
daheld wrote:
Tue Dec 31, 2019 8:18 am
but as I've said here before I am uneasy giving all my login/banking info to a single entity. I understand the security features of such programs, but it seems an unwise risk.
This is why I don't use Quicken any more.

OTOH, Mint doesn't store passwords for major banks. It continues to work, even if I didn't tell my new passwords.
https://help.mint.com/Accounts-and-Tran ... ctions.htm
If you don't want Quicken to store your passwords for your accounts don't put them in the password vault. Problem solved.

Cheers
Not sure whether that was a sarcasm or not. Then how to auto update without giving the password?

Silk McCue
Posts: 3983
Joined: Thu Feb 25, 2016 7:11 pm

Re: Annual Tracking Spreadsheet Review?

Post by Silk McCue » Tue Dec 31, 2019 9:00 am

acegolfer wrote:
Tue Dec 31, 2019 8:54 am
...

Not sure whether that was a sarcasm or not. Then how to auto update without giving the password?
No sarcasm. You can't auto-update but you can use a tool that keeps all of your finances in one location. There are many folks here that log into each individual account and download transactions to place into a home built Excel spreadsheet. Using Quicken without storing passwords would provide, for most non techies, a superior solution.

Cheers

kaudrey
Posts: 1026
Joined: Fri Nov 22, 2013 2:40 pm

Re: Annual Tracking Spreadsheet Review?

Post by kaudrey » Tue Dec 31, 2019 9:02 am

I do something somewhat similar.

My spreadsheet has various tabs:

Net Worth (updated quarterly)
Portfolio - which I use to see our overall asset allocation percentage, also quarterly)
Expenses (has only about 8 large categories, many things just get dumped into "living expenses" line)
Savings - tracking all savings
Taxes - where I put things we'll need to do our taxes at the end of the year
Retirement - this is the "pulling it all together" sheet; it pulls in the info from the other sheets, and then projects expenses, income, assets going forward until we are 100. There are a lot of formulas in this one! It's my favorite....

acegolfer
Posts: 1676
Joined: Tue Aug 25, 2009 9:40 am

Re: Annual Tracking Spreadsheet Review?

Post by acegolfer » Tue Dec 31, 2019 9:09 am

Silk McCue wrote:
Tue Dec 31, 2019 9:00 am
acegolfer wrote:
Tue Dec 31, 2019 8:54 am
...

Not sure whether that was a sarcasm or not. Then how to auto update without giving the password?
No sarcasm. You can't auto-update but you can use a tool that keeps all of your finances in one location. There are many folks here that log into each individual account and download transactions to place into a home built Excel spreadsheet. Using Quicken without storing passwords would provide, for most non techies, a superior solution.

Cheers
Yes, Downloaded CSV + spreadsheet is safer. My spreadsheet script automates this process.

NoblesvilleIN
Posts: 91
Joined: Sat Mar 24, 2018 6:04 pm
Location: Noblesville IN

Re: Annual Tracking Spreadsheet Review?

Post by NoblesvilleIN » Tue Dec 31, 2019 9:13 am

I have two spreadsheets, one that is updated on January 1st of each year that tracks EOY values for investments, savings, and debt and one that tracks the individual investments and is updated as dividends and interest are posted or money is transferred out.

The annual spreadsheet has a row for each investment account (taxable, IRA, 401k), bank account, and debt with a column for each year going back more than 15 years. The idea behind this spreadsheet was to watch our assets grow (most years) and debt decrease (most years). The top half has the investments and savings and the bottom half has the debt. There are totals for each section along with the increase/decrease from the prior year in dollars and percent. I use it as a motivation tool. This year I am adding a row for withdrawals from taxable (I retired last Dec. 31st) and next year I'll add a row for withdrawals from the IRA (we will start drawing from it in 2020).

The tracking spreadsheet has a row for each individual investment (stock, bond, CD, mutual fund) within in an account (taxable, my IRA, DW IRA) with a column for purchase date, purchase price, commission, # shares, initial annual dividend/interest/distribution rate, estimated annual income at purchase, current annual dividend/interest/distribution rate, estimated annual income (current year). On January 1st, I add a column for the new year and move the current year over. This way I can see the income growth by year for each individual investment. There is a tab for sold/matured investments with the reason it was sold so that I can keep myself honest - most years nothing is sold. I also have tabs tracking the HSA, withdrawals, tax info for quarterly payments, SSA income (used to project future SSA income) and a tab that tracked an old employee stock purchase plan.

toocold
Posts: 21
Joined: Sun Jul 23, 2017 9:17 am

Re: Annual Tracking Spreadsheet Review?

Post by toocold » Tue Dec 31, 2019 9:23 am

I have a spreadsheet that I have updated for the past 15 years. It's updated on a quarterly basis and it tracks things like net worth, asset allocations, passive income coverage, and a "wealth" factor. There were 6 quarters that I missed - 2008/2009 -- that was just too painful :)

Olemiss540
Posts: 1194
Joined: Fri Aug 18, 2017 8:46 pm

Re: Annual Tracking Spreadsheet Review?

Post by Olemiss540 » Tue Dec 31, 2019 10:26 am

Yes, I also do the same wrt an end of years spreadsheet update to finalize all investment values, spending/savings totals (and savings rate percentage), and NW/Investment projections.

I also finalize my tax "model" in a basic spreadsheet with income/deductions and projected tax burden as well as taxes paid to get an idea of where I sit before April.

Both of these spreadsheets are updated regularly but the tax spreadsheet usually doesn't get rolling until October so projections are close.
I hold index funds because I do not overestimate my ability to pick stocks OR stock pickers.

planetmike
Posts: 28
Joined: Thu Mar 28, 2019 8:00 am

Re: Annual Tracking Spreadsheet Review?

Post by planetmike » Thu Jan 02, 2020 4:53 pm

I have a handful of spreadsheets that are updated monthly. Net worth, asset allocation, details of stock purchases. Balance the credit card and bank accounts in the financial software (iBank, now called Banktivity).

User avatar
Harry Livermore
Posts: 165
Joined: Thu Apr 04, 2019 5:32 am

Re: Annual Tracking Spreadsheet Review?

Post by Harry Livermore » Thu Jan 02, 2020 7:27 pm

I have two spreadsheets.
The first one is 4 pages, and shows a "snapshot" of net worth, expected retirement income and portfolio draw, basis and profit/loss. It analyzes asset allocation in several ways. It lists all assets and debt. My LLC's equipment, cash, and debt are on it as well, but I break out the net worth both with and without the business value. It lists HSA and FSA accounts, life and disability insurance protection. I generally enter share prices weekly, on Saturday morning, while I enjoy a cup of coffee. I update basis as statements come in (auto-investments and divvy reinvestment) I even track basis of IRAs and Roths, just for benchmarking. When I make mortgage payments, or pay down business debt, I update those lines. Since it's a "living document" and gets updated weekly, I occasionally make a PDF of it with the date. It's fun to look back through time at various iterations and see both the growth of net worth and the refinement of the document.
The second is a family budget and is much more complex. I have 8 categories of expenses that sum to the first page of the spreadsheet, with as many as 30 or 40 individual lines per category. The first page of the spreadsheet lists all income sources and savings, totals it, and subtracts the expenses. I try to get it to balance. On the individual pages, there are columns for each expense (monthly, quarterly, annually) and two of the three columns are formulas based on the third column. If an expense is a one-time annual event, the quarterly expense is that number divided by 4, and the monthly is that number divided by 12. If it's a monthly expense, the quarterly number is the monthly multiplied by 3, and the annual is multiplied by 12. This way I only have to change one number and the others update. The cell with the changeable number is bolded for easy reference. Having it set up this way allows me to see broad categories in monthly, quarterly, and annual views. I have a fourth column which is an "actual", that I fill in after the year is over. There is another column for notes. Expenses are also color coded based on who typically takes care of the bill: me, wife, or joint (yes, it's all one pot of money but we divide up stuff... yes we are weird) Sometime in February, I have enough information to do the budget actual, and then my wife and I go on a "date" where we reserve a quiet room at the library and spread the whole thing out on a table, 9 pages of legal sized paper, and try to see where we can do better at being frugal next year. Some date. Oy. We don't really enjoy the process but feel it's important, and it has helped us control expenses. We'll jot down notes and ideas ("call the insurance company and raise deductibles", "use more grocery store coupons", etc.) and pick the easiest things to implement.
Anyway, that's how it's done in the Livermore household!
Cheers

Barsoom
Posts: 248
Joined: Thu Dec 06, 2018 9:40 am

Re: Annual Tracking Spreadsheet Review?

Post by Barsoom » Thu Jan 02, 2020 7:31 pm

JoeRetire wrote:
Tue Dec 31, 2019 6:00 am
Anyone else do it this way? Any interesting variations?
You might want to check out this thread:

Retiree Portfolio Model.

-B

Jablean
Posts: 494
Joined: Sat Jun 02, 2018 2:38 pm

Re: Annual Tracking Spreadsheet Review?

Post by Jablean » Thu Jan 02, 2020 8:01 pm

I have a workbook with around 30 sheets in it, most of which I don't use but at one time or another in the last 20 years played with numbers on. I've looked at loan options, prepay the mortgage or college, which medical insurance to get when we first started ACA, house improvements, credit card tracking, medical test tracking, budget estimates early on and on years with big job changes. It's usually open on my desktop.

My two important sheets are the 200 line monetary/stock/assets (lots of totals and subtotals taking up most of the lines) which I do quarterly for the current year and then keep just the year end since 2000. I have each account with associated funds marked, starting last year, with a C or S or I or B etc which I have Excel look for and add up automatically for allocation which I total below and create a chart from. I inherited funds 2 years ago so I track those for RMDs and starting to look forward to retirement so looking at totals in current taxed RMDs, future taxed RMDs and I'll pull a couple of colums to the side to look at SS now and then. I've got a column that looks at growth for the last year. I used to have a linked spreadsheet where I tracked each fund in/out growth but haven't felt the need in the last 15 years or so. I track my net (no house value) and chart various points in sheet such as when my cash was finally more what I owed.

My other sheet is prepping for taxes and staying off the ACA cliff. I estimate income and replace with actual (sole props so never consistent) plug in capgains, HSA contributions, Inherited RMDs, SEP, estimated business expenses - anything that effects my ACA MAGI and I'm getting better at matching it up to tax forms so I've got better estimated taxes to pay each quarter.

HG01
Posts: 7
Joined: Thu Aug 11, 2016 2:23 pm

Re: Annual Tracking Spreadsheet Review?

Post by HG01 » Thu Jan 02, 2020 8:11 pm

Anyone willing to share the spread sheet template / tracker that works for you?

Thanks in advance.

User avatar
Taylor Larimore
Advisory Board
Posts: 29052
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

Re: Annual Tracking Spreadsheet Review?

Post by Taylor Larimore » Thu Jan 02, 2020 8:24 pm

JoeRetire:

I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom: "We ignore the real diamonds of simplicity, seeking instead the illusory rhinestones of complexity."
"Simplicity is the master key to financial success." -- Jack Bogle

JD2775
Posts: 397
Joined: Thu Jul 09, 2015 10:47 pm

Re: Annual Tracking Spreadsheet Review?

Post by JD2775 » Thu Jan 02, 2020 8:50 pm

You guys are way more sophisticated than I am.
I have a spreadsheet that I update monthly with my investment/saving account balances and overall net worth, that’s it. Plus a fancy formula cell that notes the difference in dollars from the previous month and if it’s positive it highlights the number green, otherwise red ;)
Last edited by JD2775 on Thu Jan 02, 2020 8:51 pm, edited 2 times in total.

User avatar
Harry Livermore
Posts: 165
Joined: Thu Apr 04, 2019 5:32 am

Re: Annual Tracking Spreadsheet Review?

Post by Harry Livermore » Thu Jan 02, 2020 8:50 pm

HG01 wrote:
Thu Jan 02, 2020 8:11 pm
Anyone willing to share the spread sheet template / tracker that works for you?

Thanks in advance.
Sent you a PM.
Cheers

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Thu Jan 02, 2020 8:56 pm

Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
JoeRetire:

I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom: "We ignore the real diamonds of simplicity, seeking instead the illusory rhinestones of complexity."
I am getting there Taylor! The massive spreadsheet (with multiple tabs and analysis) is long gone. A one page spreadsheet is left with minimal if anything needed. Now that we have streamlined to Jack Bogle's Two Fund Portfolio (like you), I will have to look at everything because a spreadsheet may no longer be needed. There are no longer 6 plus funds to rebalance and worry about!!!

P.S. My wife is so much happier!
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Thu Jan 02, 2020 9:05 pm

RickBoglehead wrote:
Tue Dec 31, 2019 6:08 am
Quicken does it for me.
We used Quicken for a decade and stopped cold over 11 years ago. Never had a need and did not see any value any longer. We were also tired of the upgrade fees with no improvements.

Ultimately things were streamlined and are very easy so that our Vanguard statements and online accounts provide more information than we need.
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

Tamales
Posts: 1439
Joined: Sat Jul 05, 2014 10:47 am

Re: Annual Tracking Spreadsheet Review?

Post by Tamales » Fri Jan 03, 2020 9:07 am

I do something similar, except I wait until taxes are completed for the year since that provides some inputs to the process.

There are certainly scenarios where this sort of exercise doesn't have much value--such as having such a large financial cushion that these sorts of details aren't meaningful, or if you're already well into retirement where the statistical uncertainty about your 'final' outcome has become very small. But outside of those scenarios, blanket assertions that such a process has no value aren't really helpful. Individuals have different "needs" for data (in terms of both scale and scope), to reach their comfort level. Do what's right for you, not what's right for someone else.

Blake7
Posts: 202
Joined: Fri Mar 30, 2018 2:52 pm
Location: USA

Re: Annual Tracking Spreadsheet Review?

Post by Blake7 » Fri Jan 03, 2020 10:07 am

My spreadsheet has the following tabs, which share certain data amongst themselves.

Investments:
A breakout in $ and % of all of our investments (401k, 457, individual Roths, checking). It shows institutional ratios, pre-tax vs. taxable, investment types (stocks, cash), total assets, etc.

Expenses:
Calculates monthly and annual living expenses. Also calculates disposable and discretionary income using data from Taxes tab.

Net Worth:
A standard assets and liabilities worksheet. Data is pulled in from several fields under the investments tab.

Taxes:
Calculates marginal and effective federal and state tax rates and remaining space within tax brackets. Useful for decisions such as Roth conversions.

Home Cost:
Tracks home maintenance and repair costs. Total is divided by # years of home ownership and included under the Expenses tab.

Nothing fancy (basic MDAS), and didn’t take long to put together. :happy

MikeG62
Posts: 2360
Joined: Tue Nov 15, 2016 3:20 pm
Location: New Jersey

Re: Annual Tracking Spreadsheet Review?

Post by MikeG62 » Fri Jan 03, 2020 10:12 am

JoeRetire wrote:
Tue Dec 31, 2019 6:00 am

...Anyone else do it this way? Any interesting variations?
I use two different excel workbooks.

The first (the one I use throughout the year) is a multi-tabbed excel spreadsheet which tracks our spending.

The first tab is an electronic checkbook register (created by me which replaces a paper checkbook register). The second tab summarizes our monthly credit card spending into the categories (about a dozen of them) I want to track - for all of the CC's used that month. The third tab pulls in the checkbook data and credit card spending, summarizes them by month and compares the spending to budget (for both the month and YTD).

It takes time each month, but not an inordinate amount of time. The checkbook register is easiest - it's essentially like using a paper register, but much easier to reconcile to the bank balance (actually, I reconcile in real time periodically throughout the month, and as a result, I no longer reconcile to our monthly bank statement). Reviewing our CC statements and categorizing the spend into the buckets I want to track takes a bit more time. However, for the CC's we use the most, I pull the data into another tab where I have some logic built in to automatically categorize the spend. This information is then pulled into the second tab which summarizes the spend from all of our cards. Since I am retired I don't mind doing it. Plus, it keeps me close to our spending and avoids the need for periodic spending reviews (as the reviews now happen in real time and is ongoing).

The second workbook tracks our financial assets (including annual roll-forward). This is updated only annually.

Three years ago, I added/built-in a number of charts and graphs - or I should say I pulled data from these workbooks/spreadsheets into several pie, bar and line graphs. This has made the annual process of reviewing our spending with my DW “a lot” easier. She is much more receptive to looking at charts and graphs than what otherwise seems to her like a blizzard of numbers in a spreadsheet.

Here is a brief description of those charts and graphs:

1. Annual % Withdrawal Rate by year (Bar charts) with columns for SWR% guideline (4%), what our withdrawals would be if we were using VPW, our Target WD Rate, and our Actual WD Rate (computed using both our beginning and ending portfolio values)

2. Annual $ Withdrawal Amount by year (Bar charts) with columns showing amount we would withdraw if following the SWR guideline, VPW, our Target Spend amount (using our Target WD Rate) and our Actual Spend amount.

3. Spending for the year (Pie Chart) by category of Spend – this one really hit home with my DW!

4. Spending by Category and delta budget by month (Bar Chart) - this one is intended to highlight the budget variances.

5. Travel & Entertainment Spend for the year (Pie Charts) – one by Category of Spend (airfare, hotel, meals, car service, and other spend) and the second by Trip

6. Portfolio Value at the end of each year (one in Line Graph and the other in Bar Chart format) - mine goes back to 1996.

7. Annual portfolio investment % gain/loss (Line Graph) and $ gain/loss (Bar Chart).

As a retired finance guy, this is all comes naturally to me.
Last edited by MikeG62 on Fri Jan 03, 2020 5:42 pm, edited 1 time in total.
Real Knowledge Comes Only From Experience

BBQ Nut
Posts: 157
Joined: Sun Jun 22, 2014 9:19 am

Re: Annual Tracking Spreadsheet Review?

Post by BBQ Nut » Fri Jan 03, 2020 10:19 am

Spreadsheet user here; several tabs tied together via links & formulas to track various things.

I never liked the on-line or pre-canned programs for investment/expense tracking as I'm very particular on how things are categorized or organized.

So, I just made up my own; easy peasy.

I update it once a month with snapshots of investments and budget tracking/expenses.

rich126
Posts: 1135
Joined: Thu Mar 01, 2018 4:56 pm

Re: Annual Tracking Spreadsheet Review?

Post by rich126 » Fri Jan 03, 2020 10:39 am

Even though I'm a numbers guy I've never tracked spending or followed a budget in my life. Not sure what will happen when I retire. I've always had money going directly to various investments and would rarely ever pull any money from those investments to spend on stuff (except maybe for a house).

For work I did have to submit a financial disclosure form each year so that gives me a little bit of info since 2012. I am trying to keep that up even though I don't need it in my current job.

My mother before getting married worked in a bank and was the type of person that would balance a checkbook and spend a bunch of time tracking down the difference of a penny or two. I glance at my cc and bank balances but have never balanced them. Just look for any weird purchases (and did catch a couple in the decades of doing so).

I guess I don't see the point of doing so and find it tedious. I think I once bought quicken or MS money decades ago and tried to do something with it and didn't see it worth my time.

User avatar
WoodSpinner
Posts: 1067
Joined: Mon Feb 27, 2017 1:15 pm

Re: Annual Tracking Spreadsheet Review?

Post by WoodSpinner » Fri Jan 03, 2020 11:09 am

OP,

Here goes .....

First I am a big Quicken user and have no issues with the subscription model. I use this to do the grunt work of tracking income, expenses, budget, investments, and net worth. I make use of the One Step Update daily to make sure there are no issues with credit card charges, bad checks etc. Check budgets quarterly. Haven’t convinced my wife to do a Finance date yet :shock:

I have 2 main spreadsheets though setup to handle functions that Quicken doesn’t do well.

Asset Rebalancing
I run a report in Quicken on my investments, cut and paste to the spreadsheet which then reformats and analyzes the information. I find the following activities to be key.

1. Identify assets that are not in the right account. For instance, cash is supposed to be manually reinvested after quarterly dividends/interest and should not linger in any account. Bonds only in the IRA, never Taxable or the Roth. Essentially mirrors the rules in my IPS.

2. I use an annual rebalancing event plus 5/25 bands. The spreadsheet can highlight any assets out of bounds and come up with a suggested rebalancing plan. I have done significant consolidation on accounts and assets so rebalancing is not as challenging as it once was.

Retirement Planning
This is the most important tool I have developed and I have been really pleased at how we’ll it’s been working for me. I am married, 60 and retired in 2018 and am still getting used to the decumulation phase and the shifts in taxes, income and expenses.

1. Current and projected account balances from now through end of retirement. This includes cash flow needs to fund expenses, Roth Conversions, RMDs etc.

2. Expense budget with regular and lumpy expenses through retirement.

3. Income budget from Pensions, SS through Retirement.

4. Tax engine to manage expected Federal and State Taxes. I have learned a lot working through these calculations and have been pleased to be within a few dollars of my projections when the final tax bill arrives.

5. Spending engine to look at expected spending using various approaches: 4% Rule, Time Value of Money (TVM) and Variable Portfolio Withdrawals (VPW). I don’t follow any one approach rigorously but find some comfort if the spending levels are under at least 2 of the approaches.

6. Roth Conversion Engine which helps me plan how much to convert each year and understand the impacts on AGI, Marginal/Effective Taxes, IRMAA, and NIIT. Key is being able to project what these metrics are likely to be through different stages of retirement. Lots of unknowns but somehow you have to make a decision.

7. Publishing engine which pulls information from various engines, develops a standard set of graphs, metrics, and formats a high level cash flow plan. This has become really valuable for reviewing with my wife since it’s much better organized and cleaner than looking at the analysis in the various engines.

At some point I may discard or shift these tools as my needs change in retirement. At this point they are working very well and are giving me important insights and signals. The key is to automate what you can and focus on the insights and metrics you want to achieve. Make sure it’s adding value for your goals.

WoodSpinner

User avatar
corn18
Posts: 1601
Joined: Fri May 22, 2015 6:24 am

Re: Annual Tracking Spreadsheet Review?

Post by corn18 » Fri Jan 03, 2020 11:34 am

I am a spreadsheet guy. I have the usual budget (income and expenses) tab. Also have a savings tab. Not a lot of complex calculations. I do track spending in detail each month. Nothing fancy but it goes back to 1996 and is easy to maintain.

I have added some tabs over the past few years that are proving important:

1. Roth IRA tab. This tracks all my back door and mega back door activity. This is very useful for looking at taxes and penalties. I plan to do Roth conversions of my 401k until age 70 when SS kicks in, so this will serve to track that activity.

2. SS and life insurance tab. I use this to look at survivor income. We have plenty of money as long as I am alive. But with a 55% survivor pension and SS reduction, my death would reduce income considerably for my wife.

3. Net worth tab. Not really useful other than to see that owning cars sucks.

4. Retirement tab. This is the one that is my focus lately. Plan to retire in 2 years and wanted to model that. Started off pretty simple, but now it is very comprehensive. I have it laid out by year with income (pension and SS) and expenses. That part is fairly simple. But I can now model Roth conversions and I calculate taxes each year including a full accounting of SS benefits. Then I run scenarios for when I retire. I can also model my death to make sure my wife is OK if I die.
Don't do something, just stand there!

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Fri Jan 03, 2020 11:41 am

rich126 wrote:
Fri Jan 03, 2020 10:39 am
I think I once bought quicken or MS money decades ago and tried to do something with it and didn't see it worth my time.
That is what eventually happened to us. We used Quicken for almost a decade and stopped cold 11 years ago. The last few years of using Quicken felt like to value and insanity.

I am trying to get like Taylor and move to no spreadsheets and rely on what Vanguard has on the website!
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Fri Jan 03, 2020 11:42 am

Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
JoeRetire:

I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom: "We ignore the real diamonds of simplicity, seeking instead the illusory rhinestones of complexity."
I am going to get there Taylor! Free and simple! More time to relax, friends, and life!
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Sat Jan 04, 2020 3:56 pm

Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
JoeRetire:

I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom: "We ignore the real diamonds of simplicity, seeking instead the illusory rhinestones of complexity."
Taylor -

I did it! Our spreadsheet which we kept for many years....I clicked on the file and hit delete.

I did this after looking at the file and then logging into Vanguard and looking at both the online tools and analysis and our statement. Total Duplication.

Simplicity. It is a never ending but rewarding journey.
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

User avatar
Toons
Posts: 13439
Joined: Fri Nov 21, 2008 10:20 am
Location: Hills of Tennessee

Re: Annual Tracking Spreadsheet Review?

Post by Toons » Sat Jan 04, 2020 4:17 pm

Quicken
27 years worth of data.
:happy
"One does not accumulate but eliminate. It is not daily increase but daily decrease. The height of cultivation always runs to simplicity" –Bruce Lee

User avatar
celia
Posts: 10057
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: Annual Tracking Spreadsheet Review?

Post by celia » Sat Jan 04, 2020 4:26 pm

Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.
Taylor, This looks like you only have one checking account and one brokerage account. Don’t you have IRAs, 401Ks, and an HSA? Or maybe I should ask how many pages your Vanguard statement is (with multiple accounts possible).

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Sat Jan 04, 2020 7:57 pm

celia wrote:
Sat Jan 04, 2020 4:26 pm
Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.
Taylor, This looks like you only have one checking account and one brokerage account. Don’t you have IRAs, 401Ks, and an HSA? Or maybe I should ask how many pages your Vanguard statement is (with multiple accounts possible).
Can Vanguard provide one consolidated account statement for mutual fund accounts or must they be separate?
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

capran
Posts: 75
Joined: Thu Feb 18, 2016 10:45 am

Re: Annual Tracking Spreadsheet Review?

Post by capran » Sat Jan 04, 2020 8:45 pm

I use Excel and update between quarterly and monthly depending on our travels. I "save as" with a new date for each update so it's just updating values. It lists totals for Roth, IRA, brokerage account, checking and savings, as well as total cash assets. There's a space to record unusual budget items outside the regular budget, as well as current monthly/annual income. there's also a quarterly and annual box to track totals by those parameters. I keep it in a notebook and have done so since 1988, although I did thin out the older ones to keep just quarterly records. Once it's set up, takes just 5-10 minutes to check and update.

User avatar
Taylor Larimore
Advisory Board
Posts: 29052
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

Re: Annual Tracking Spreadsheet Review?

Post by Taylor Larimore » Sat Jan 04, 2020 9:26 pm

celia wrote:
Sat Jan 04, 2020 4:26 pm
Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.
Taylor, This looks like you only have one checking account and one brokerage account. Don’t you have IRAs, 401Ks, and an HSA? Or maybe I should ask how many pages your Vanguard statement is (with multiple accounts possible).
Celia:

I do not have a brokerage account or a 401K or HSA. Only an IRA and taxable account. Last year I received a I6-page IRA statement. My Total Market Stock Index Fund is in my new taxable trust account statement. I'm not sure if I will get the 2019 trust statement or if it goes only to my trustee. I concentrate on my year-end balances and how they compare with last year. If reasonable, I move on with my personal life (much of it spent here).

Be careful about mimicking someone else's portfolio who may have an entirely different personal and financial situation.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom:“Does this (three fund) portfolio seem overly simplistic, even amateurish? Get over it. Over the next few decades, the overwhelming majority of all professional investors will not be able to beat it.”
"Simplicity is the master key to financial success." -- Jack Bogle

nguy44
Posts: 245
Joined: Sun Jul 09, 2017 1:52 pm

Re: Annual Tracking Spreadsheet Review?

Post by nguy44 » Sat Jan 04, 2020 10:38 pm

I have used Quicken for close to 25 years to track spending and investments (will re-evaluate as I move closer to being forced to get a subscription, but that is a topic in another thread which I am following).

I supplement this with spreadsheets that use the date from Quicken and other sources for more detailed calculations and forecasting:
- Retirement plan (income, SS, cash flow, Roth conversions)
- 401K historical contributions and gains
- Yearly net worth and expense detailed category calculations

User avatar
abuss368
Posts: 18070
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: Annual Tracking Spreadsheet Review?

Post by abuss368 » Sun Jan 05, 2020 9:56 am

Taylor Larimore wrote:
Sat Jan 04, 2020 9:26 pm
celia wrote:
Sat Jan 04, 2020 4:26 pm
Taylor Larimore wrote:
Thu Jan 02, 2020 8:24 pm
I do not bother tracking my finances on a spreadsheet. My two mutual funds (S&P 500 Index and Total Bond Market) are with Vanguard. Vanguard's annual statement, plus my bank statements, provide more information than I need.
Taylor, This looks like you only have one checking account and one brokerage account. Don’t you have IRAs, 401Ks, and an HSA? Or maybe I should ask how many pages your Vanguard statement is (with multiple accounts possible).
Celia:

I do not have a brokerage account or a 401K or HSA. Only an IRA and taxable account. Last year I received a I6-page IRA statement. My Total Market Stock Index Fund is in my new taxable trust account statement. I'm not sure if I will get the 2019 trust statement or if it goes only to my trustee. I concentrate on my year-end balances and how they compare with last year. If reasonable, I move on with my personal life (much of it spent here).

Be careful about mimicking someone else's portfolio who may have an entirely different personal and financial situation.

Happy New Year!
Taylor
Jack Bogle's Words of Wisdom:“Does this (three fund) portfolio seem overly simplistic, even amateurish? Get over it. Over the next few decades, the overwhelming majority of all professional investors will not be able to beat it.”
Thanks Taylor and well said. No value in endlessly reviewing a portfolio on a spreadsheet when the information is available on Vanguard's website and the year end statement.
John C. Bogle - Two Fund Portfolio: Total Stock & Total Bond. "Simplicity is the master key to financial success." || Buy Total Stock until it hurts. Then find a way to buy even more!

JGoneRiding
Posts: 1874
Joined: Tue Jul 15, 2014 3:26 pm

Re: Annual Tracking Spreadsheet Review?

Post by JGoneRiding » Sun Jan 05, 2020 12:08 pm

I update the net worth sheet at year end.

I actually need yo get a better handle on where expenses are going but due to numbers of accounts quicken has been annoying. Going to try it again

Post Reply