What can you homegrown financial management spreadsheet do?

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Ron Scott
Posts: 772
Joined: Tue Apr 05, 2016 5:38 am

What can you homegrown financial management spreadsheet do?

Post by Ron Scott » Fri Sep 14, 2018 7:44 am

I know many BHs use external software or online services to update and analyze their finances, but many others like me rely on homegrown spreadsheets. This thread is directed at them.

It would be interesting to know what facility you've built.

I'll start. I have a few connected spreadsheets, all very straightforward, nothing fancy. This system can:

1. Easily accept updates to $values (by asset by account), that I do about monthly
2. Track changes to asset allocation (stocks, bonds, cash, real estate) and asset location (taxable, IRA, Roth) and compares actual vs. a plan
3. Create a Net Worth statement suitable for inclusion in the family "letter" and for estate purposes
4. Track changes to total Net Worth with and without inflation (it maintains a history from my retirement date)

I don't live on a budget so there's nothing there I monitor.

You?

Jack FFR1846
Posts: 7628
Joined: Tue Dec 31, 2013 7:05 am

Re: What can you homegrown financial management spreadsheet do?

Post by Jack FFR1846 » Fri Sep 14, 2018 7:55 am

Mine is built in Excel with no links and stored on a flash drive.

Contains each house (fidelity, Schwab, Vanguard, etc) with each fund listed. Color coded blue for equity, purple for bond and red for international equity. Black for US Savings bonds. Each fund moving across has amount, % of total, ER, $ translation of ER. Both mine and my wife's accounts are listed.

Totals include overall total, total of costs, %ER including US Savings Bonds and %ER without US Savings Bonds.

Next section is asset allocation showing % equity, bond in totals with and without US Savings Bonds included. Separate section breaks equity into US and International.

Net worth section takes the total number, adds guesstimate of house and cars value.

I have a second spreadsheet where I break out each year and include expected major life events (expenses or income) by year. Every year includes investments, cash, Savings Bonds, expenses broken by major, balance, expected gains on investments, percentage (can be changed each year) for growth of investments. At the bottom, totals for gains or shortfalls for each year. If shortfall, savings bond balance for next year is debited. When a year concludes, I overwrite the predicted numbers with knowns and the following years all trigger from the last known so will update.
Bogle: Smart Beta is stupid

2015
Posts: 1830
Joined: Mon Feb 10, 2014 2:32 pm

Re: What can you homegrown financial management spreadsheet do?

Post by 2015 » Fri Sep 14, 2018 8:21 am

I have spreadsheets but I don't want to waste the few precious moments on this earth with my nose buried way up there.

1) About 5 spreadsheets, all in one workbook, each on it's own separate tab, nothing linked.
2) One spreadsheet serves as checkbook, another documents transfer amounts savings/mm monthly to checkbook, another tracks my VG accounts, another my net worth, and a last breaks down AA but this one exists for AA inputs into FIDO's retirement income planner (or whatever they're calling it now).
3) As most of my accounts are with VG, and any others are manually input into VG, I don't use anything to track performance, ER's, etc., as it's all there in one convenient place.

The benefit of simplicity just keeps on giving.

diy60
Posts: 167
Joined: Wed Sep 07, 2016 6:54 pm

Re: What can you homegrown financial management spreadsheet do?

Post by diy60 » Fri Sep 14, 2018 8:36 am

Retired so I decided to learn some VBA, built one Excel workbook with multiple worksheets (tabs). The workbook pulls in account data from several downloaded csv-formatted account files. No manual entries and no looking up ticker values since all info is already in the downloaded csv-formatted account files. Various macros buttons to toggle between holding categories like income only, equity only, and total picture, and a few others, all broken down by target AA, account owners, and account types. I recognize my portfolio and approach is too complex, but just something to challenge the brain for right now. I will definitely simplify my finances and approach as I age further.

scrabbler1
Posts: 2207
Joined: Fri Nov 20, 2009 2:39 pm

Re: What can you homegrown financial management spreadsheet do?

Post by scrabbler1 » Fri Sep 14, 2018 8:46 am

I have several spreadsheets. Some are for individual mutual funds which include each transaction the way a checkbook register would. Others are summary spreadsheets which show totals of fund values. I have a spreadsheet which is a checkbook register and does some other related things. And I have a retirement spreadsheet which has short-term and longer-term budgets, still handy even as I have been retired for nearly 10 years. None of these are linked to others although a few have multiple tabs to connect very similar data.

TwstdSista
Posts: 983
Joined: Thu Nov 16, 2017 4:03 am

Re: What can you homegrown financial management spreadsheet do?

Post by TwstdSista » Fri Sep 14, 2018 9:07 am

I have an end of month balance spread sheet that I complete every month. It tracks our personal cash, business cash, home and vehicle values, retirement assets, allocations for both retirement and overall assets and percentages off target from our desired AA, overall net worth, increase/decrease for both the month and the year of our net worth, and increase/decrease for both the month and the year of our personal cash.

Nothing is linked, I manually input the numbers every month. Then I print it out, leave it for the husband to look at (he really only cares about the net worth number) and eventually file it away in a binder.

retiringwhen
Posts: 398
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: What can you homegrown financial management spreadsheet do?

Post by retiringwhen » Fri Sep 14, 2018 9:14 am

Gosh, I have about 10 spreadsheets. The problem is I find them a good source of amusement/distraction and don't always have a plan for them, they are more investigative.

One tracks my AA vs. plan and analyzes how to make those adjustments while minimizing consequences (mostly taxes), this is as close as I have to one that is authoritative. It also got out of hand with too much detail on AA details. I need to pair it back to essentially just comparing my actual investments to the a basic 3-fund portfolio with the complication that I use approximated 3-fund model since some of the investments can't be structured as true total market funds.

Another is an adaptation of ERN's safe withdrawal calculator to figure out when I can throw in the towel.
I have done others as well, but I find ERN's is easiest to use and manipulate and the results are well within bounds of just about any other tool out there while realizing his approach is conservative.

I recently started using the BogleheadsReturns spreadsheet to look at long-term returns. I really like the spreadsheet and the approach only requires about 15 minutes a month and will provide us with solid historical data going forward. I wish I started this years ago.

I have one that tracks Cost Basis for all my taxable and tax-deferred accounts. It is partly used to help with efficient TLH activities. I also created this as a backup to untrustworthy sources from my employer, brokerages etc.... It also helps with tracing history of RMDs from an Inherited IRA (those turn out to be a pain, I can see why folks with small Inherited IRA's just take the distribution.)

I have two different styles of Tax Planning spread-sheets to help calculate estimated taxes and the efficacy of doing back-door Roth conversions.

I track weekly money market yields on Vanguard MM funds as part of my long-term arbitrage on tax rates for emergency funds. Probably more "fun" than useful.

The most useful, but total hassle is my medical expense tracking account (we have some serious family health issues that make us use a lot of medical services). This is used to ensure I am billed and pay what is correct, that claims are processed properly and reimbursements from our FSA and HSA are done efficiently. I truly hate the task and this spreadsheet keeps all the details in one place and let's me keep a detailed and executive summary of the financial state of the expenses. The upside is I have a very very good idea of what our long-term health care costs will be when planning for any gap years between employer subsidized insurance and Medicare.

I probably have more I am not thinking of.

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

Re: What can you homegrown financial management spreadsheet do?

Post by aristotelian » Fri Sep 14, 2018 9:26 am

I built my own self-updating spreadsheet to replace Personal Capital. Tracks real time portfolio market value as well as asset allocation.

I have a separate month to month income/expense spreadsheet to track savings rate and spending in major budget categories.
Last edited by aristotelian on Fri Sep 14, 2018 9:27 am, edited 1 time in total.

User avatar
midareff
Posts: 5712
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: What can you homegrown financial management spreadsheet do?

Post by midareff » Fri Sep 14, 2018 9:27 am

Ron Scott wrote:
Fri Sep 14, 2018 7:44 am
I know many BHs use external software or online services to update and analyze their finances, but many others like me rely on homegrown spreadsheets. This thread is directed at them.

It would be interesting to know what facility you've built.

I'll start. I have a few connected spreadsheets, all very straightforward, nothing fancy. This system can:

1. Easily accept updates to $values (by asset by account), that I do about monthly
2. Track changes to asset allocation (stocks, bonds, cash, real estate) and asset location (taxable, IRA, Roth) and compares actual vs. a plan
3. Create a Net Worth statement suitable for inclusion in the family "letter" and for estate purposes
4. Track changes to total Net Worth with and without inflation (it maintains a history from my retirement date)

I don't live on a budget so there's nothing there I monitor.

You?
Let me start at the beginning..... my portfolio is in M* so when I export data, at whatever interval I desire, I have all the custom data I want. From there I open my excel book which imports all the M* updated numbers. I track all you do and more except I am not interested in a net worth statement.
I track assigned AA by item and by account (fund cash, etc., IRA, Roth, taxable) vs. actual dynamic allocation and the difference in dollars and percentage. I track distributions, yield, SEC, CPI-U, real returns, WR, prior 12 month dividend yield, annualized expected distribution by SEC yield and prior 12 month distribution (that goes to an excel budget book) and of course performance for the prior 12 months and YTD.

In this excel book is a page for every fund detailing every transaction; buy, sell, distribution, etc. Main sheet imports number of shares from each page. I use a separate page to track bond funds performance individually, as a whole and against the performance of the VG TBM.

I have a page for a tracking withdrawals from accounts.. IRA, Roth, etc., by distribution and by sales (I'm retired with RMD and such) and that data is forwarded to calculate overall WR by by year, vs. prior three year portfolio average, etc.

I have a page which I use to benchmark portfolio performance at various intervals.. 3 mos, 12 mos 3 yrs, 5yrs, etc., against the same current AA percentages of the S&P 500 as represented by VFIAX and Barclay's Agg as represented by TBM.

I have a page to track accounts with expected drawdowns, inflation and long term anticipated gains to estimate future balances.

I have a page wherein I list all my investment rules.

I have another excel book for budgets, projected income by source vs expected expenditures. This is very handy for me to use in calculating expected free cash to book future cruises which require payments far in advance of the trip.

Let's talk about upkeep time.... I'd say about 10 - 15 minutes a month to update distributions with how many shares reinvested, how many $ sent to bank and monthly draws both on the individual funds page and in M*.

Update time from booting computer to login to password manager to login in to M* ... maybe one minute total with another 20 seconds to download the two exports I need from M*. All books now update all data on opening, instantly, including all charts and graphs.

FWIW, I also track about 30 key data points monthly which takes another 3 or 4 minutes once a month. I probably track more data than I could ever use but it just doesn't take that much time. Overall, I spent 46 years working to accumulate it so a half hour a month to keep updates and look at it doesn't matter much. Also FWIW, I have every investment transaction in excel from December 1997 to today...... not that it's worth keeping all that but it doesn't take much drive space.
Last edited by midareff on Fri Sep 14, 2018 9:32 am, edited 1 time in total.

dbapaddy
Posts: 17
Joined: Tue Aug 14, 2018 1:52 pm

Re: What can you homegrown financial management spreadsheet do?

Post by dbapaddy » Fri Sep 14, 2018 9:29 am

KISS...

Mine has ability to accept my balances and compute my NW...

spend about 10 minutes/year on it and it tells me everything I need to know at a glance...

simple is underrated.

donheff
Posts: 5
Joined: Thu Jan 25, 2018 1:19 pm

Re: What can you homegrown financial management spreadsheet do?

Post by donheff » Fri Sep 14, 2018 9:47 am

I like to keep real time track of our holdings in several accounts but I am uncomfortable letting sites have access to my account logins. So I maintain a simple Google Sheet that tracks each holding with columns for shares, prices (Google function that returns end of day price). total, year to date return. I also use it to track AA, several withdrawal rates, withdrawal spending history and the like. I update the share values every so often to account for distributions. It is simple and meets my needs.

engineerartist
Posts: 28
Joined: Tue Dec 02, 2014 1:55 pm
Contact:

Re: What can you homegrown financial management spreadsheet do?

Post by engineerartist » Fri Sep 14, 2018 12:10 pm

Ron Scott wrote:
Fri Sep 14, 2018 7:44 am
I know many BHs use external software or online services to update and analyze their finances, but many others like me rely on homegrown spreadsheets. This thread is directed at them.

It would be interesting to know what facility you've built.
I have two "sheets" with several tabs on each sheet.
The main sheet is used to track both my taxable portfolio and my Rollover IRA.
One tab is used to create a monthly Net Worth statement (keeps DW informed and secure).
Another tab tracks taxes (prior years, current and future year projections).
Tabs for Taxable and Rollover IRA portfolios showing cost, market values and monthly cash flows for each investment as well as all transactions.

My other sheet is used to track expenses. Although we do not keep a budget, I download all transactions from our bank and credit cards and categorize them to keep track of our spending.

Like others responding, I periodically download csv files of transactions to update these sheets.
Yes, this work does take some time, but I prefer not to trust any aggregator have the passwords to our accounts.

Although I do not have any accounting credentials, I am a spreadsheet geek and have been using them personally and for business since VisiCalc.
Retired - dividend growth investor

Monster99
Posts: 93
Joined: Tue Aug 29, 2017 10:28 am

Re: What can you homegrown financial management spreadsheet do?

Post by Monster99 » Fri Sep 14, 2018 12:15 pm

I use MSMoney (sunset edition) and pull in quotes using the hleOfxQuotes downloader (runs under Java) works fine for both checkbook and Investment tracking, I use Excel for my budget and allocation models.

bondsr4me
Posts: 900
Joined: Fri Oct 18, 2013 7:08 am

Re: What can you homegrown financial management spreadsheet do?

Post by bondsr4me » Fri Sep 14, 2018 12:32 pm

I do a few, but nothing too exciting.

I use Excel and Numbers just to keep up with technology.

I even keep a spreadsheet of my blood pressure for my doctor; I use Numbers for this.

I like doing the spreadsheets but nothing too complicated anymore.

When I was working for a company, I did a LIFO inventory calculation which was pretty involved.
As the years went by, the more complicated it got....glad I don't do that anymore.

I also had to do a consolidating income statement of the many subsidiaries that was fun and challenging.

Those were the days!

n00b
Posts: 67
Joined: Sun Feb 03, 2013 8:36 pm

Re: What can you homegrown financial management spreadsheet do?

Post by n00b » Fri Sep 14, 2018 1:11 pm

I built an Excel workbook that essentially models and graphs our projected inflation-adjusted net-worth and income for longer than we are expected to live.

One of my favorite features is the Dashboard page with displays the graphs and allows modeling of inflation, wages, pension, retirement plan contributions, investment return, retirement date, SS shortfall, etc. (actually about 2-dozen inputs).

The rest of the workbook imports from Quicken and allows asset allocation reporting and adjustment modeling, calculates expected SS payout, includes projected retirement payouts from work, projects expected pension, calculates RMDs, etc.

I have shared it with a couple coworkers who were preparing to retire and were not going to build something like that from the ground up. Both found it helpful. I'd like to share it a little more freely but as it stands, it includes a bunch of personal information (duh).

It needs to be extended to allow glide-path modeling.

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

Re: What can you homegrown financial management spreadsheet do?

Post by MikeG62 » Fri Sep 14, 2018 5:24 pm

I maintain a two multi-tabbed Excel spreadsheet.

One file contains several tabs. The first is an electronic checkbook register (created by me which replaces our paper checkbook register). Much better than a paper register as I can search for any entry quickly and efficiently and can also reconcile my checking account in real time and very quickly.

The second tab summarizes our monthly credit card spending in the categories (about 8 of them) I want to track for all CC's used that month (usually about ten or twelve of them). I have made some process improvements over the last year. Main improvement is that I now download the transactions from our main 2% cash back card (which contains spending on all sorts of things - basically all spending other than travel, groceries, restaurants and gasoline) into one of the tabs of that excel file. I then categorize the spending in the columns from left to right (about 20 different categories - although many end up in the "misc" or "all other" category of my summary CC spending tab). This has reduced the time I used to spend printing the CC statement and adding spend by category manually.

The third tab pulls in the checkbook data and credit card spending and summarizes them by month, including comparison to budget.

The fourth tracks our planned trips for the year and the estimated cost of each trip. I use this partly to ensure we have a plan for our travel and entertainment budget (which is a considerable amount and % of our overall spend). Managing our travel calendar is quite a bit of effort (as we are taking about 7-9 trips each year - sleeping outside our home about two months each year) and I find this tab to be quite useful in helping me manage and control it (making sure we are not over or under spending our budget).

The second file is used to track our investment portfolio (at a somewhat high level). That file includes tabs summarizing our assets (equities vs bonds vs cash), a rollforward of the year over year change in our portfolio across all of our accounts for the year, and a tab which shows the trended growth of our portfolio over time (going back as far as the late 1990’s) - both in $’s as well as % terms.

This year, I added a bunch of pie charts, bar charts and graphs (pulling data from those files) and used those when I sat with my wife to do a review of our 2017 spend. This helped tremendously to keep her attention/interest in the topic (and opened her eyes to some things) - certainly much more so than showing her spreadsheets with a blizzard of numbers, which had proven to be ineffective previously. The charts and graphs include the following:

Annual % Withdrawal Rate by year (Bar charts) with columns for SWR% guideline (4%), our Target WD Rate, and our Actual WD Rate (computed using both our beginning and ending portfolio values)

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

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

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

Travel & Entertainment Spend for the year (Pie Charts) – one by Category of Spend (airfare, hotel, meals, car service/taxi/uber/lyft/rental and other spend) and the second by Trip

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

Annual portfolio investment % gain/loss (Line Graph) and $ gain/loss (Bar Chart)
Real Knowledge Comes Only From Experience

User avatar
bertilak
Posts: 6053
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: What can you homegrown financial management spreadsheet do?

Post by bertilak » Fri Sep 14, 2018 5:45 pm

n00b wrote:
Fri Sep 14, 2018 1:11 pm
The rest of the workbook imports from Quicken ...
Can you expand on how you import from Quicken? I get the raw data for my spreadsheet by downloading a CSV file from Vanguard but there is one asset not in Vanguard, thus requiring manually data entry. The data is in Quicken so perhaps I can improve my process.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker, the Cowboy Poet

User avatar
randomizer
Posts: 1438
Joined: Sun Jul 06, 2014 3:46 pm

Re: What can you homegrown financial management spreadsheet do?

Post by randomizer » Fri Sep 14, 2018 5:48 pm

Rebalancing, net worth tracking, budgeting, tracking spending, projecting glide path, mortgage tracking, showing AA alternatives, calculating returns, tracking tax obligations/deductions etc and a few other things.
87.5:12.5, EM tilt — HODL the course!

dknightd
Posts: 701
Joined: Wed Mar 07, 2018 11:57 am

Re: What can you homegrown financial management spreadsheet do?

Post by dknightd » Fri Sep 14, 2018 6:05 pm

Over the years I've made many different spreadsheets. None of them particular useful to anybody but me. I used to do taxes in excel. But now I'd rather pay for dedicated tax software. And decide if I should refinance, or take out a loan of any kind. Nowadays I download a spreadsheet that seems to address what I'm interested in at the time, and adapt it to my needs. When I retire, and have more time, I might create a spreadsheet that summarizes things from other spreadsheets. Before I do that I'll search boggles and other places. There are so many good spreadsheets out there that are way better than I could create. I think the only spreadsheet I still use that I created many years ago is one to track my savings bonds. I only keep it because it has my bond numbers in it.

MotoTrojan
Posts: 2105
Joined: Wed Feb 01, 2017 8:39 pm

Re: What can you homegrown financial management spreadsheet do?

Post by MotoTrojan » Fri Sep 14, 2018 6:37 pm

Latest fun feature is an estimated value of index mutual funds (using equivalent ETFs) during the trading day. After a new daily price posts it updates to that.

n00b
Posts: 67
Joined: Sun Feb 03, 2013 8:36 pm

Re: What can you homegrown financial management spreadsheet do?

Post by n00b » Fri Sep 14, 2018 6:41 pm

bertilak wrote:
Fri Sep 14, 2018 5:45 pm
n00b wrote:
Fri Sep 14, 2018 1:11 pm
The rest of the workbook imports from Quicken ...
Can you expand on how you import from Quicken? I get the raw data for my spreadsheet by downloading a CSV file from Vanguard but there is one asset not in Vanguard, thus requiring manually data entry. The data is in Quicken so perhaps I can improve my process.
Sure.

I created and saved a custom "Portfolio Value" export which is then exportable in "Excel compatible format". This is a tab-delimited .txt file and can be imported to Excel via a "Data/From Text" connection.

User avatar
bertilak
Posts: 6053
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: What can you homegrown financial management spreadsheet do?

Post by bertilak » Fri Sep 14, 2018 6:56 pm

n00b wrote:
Fri Sep 14, 2018 6:41 pm
bertilak wrote:
Fri Sep 14, 2018 5:45 pm
n00b wrote:
Fri Sep 14, 2018 1:11 pm
The rest of the workbook imports from Quicken ...
Can you expand on how you import from Quicken? I get the raw data for my spreadsheet by downloading a CSV file from Vanguard but there is one asset not in Vanguard, thus requiring manually data entry. The data is in Quicken so perhaps I can improve my process.
Sure.

I created and saved a custom "Portfolio Value" export which is then exportable in "Excel compatible format". This is a tab-delimited .txt file and can be imported to Excel via a "Data/From Text" connection.
Thanks! I'll check it out.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker, the Cowboy Poet

Post Reply