This article is composed by Bogleheads® forum member digarei
THIRTEEN (13) persons met July 9 2016 in mid-town Sacramento to see a demonstration of a spreadsheet that could be a useful tool in planning one’s financial future, and to learn how simple it is to calculate investor returns. Members of this chapter have a habit of getting together once a month, almost always on the Second Saturday.
The presentation and subjects were selected by members in January 2016; they are part of a series of meetings (“Plan to Invest I/II/III”) begun last month and lasting through September.
Two new members attended for the first time: an attorney and an IT consultant. Welcome J and A!
Following introductions, the coordinator inflicted small misery upon those gathered with a reading from ‘The Devil’s Financial Dictionary‘, a scripture of sorts. Authored by Jason Zweig and published in the fall of 2015, the book would appear to be a pocket-size dictionary of investing & financial terms. But many of the entries are deliciously satirical, the definitions true to the marrow. This material you won’t find in Investopedia.
Though not uniformly comedic, there are any number of in-group jokes for nerdy investors. It may be read aloud to good effect. However, whether recited in the stentorian manner that was so prevalent a hundred years ago—and now all but vanished except on the periphery of urban shopping malls, or in the undulating voice of a Ginsberg or Burroughs, you’re sure to find yourself with fewer friends than at the outset. This cannot be avoided.
Diversify: (verb ) To own a variety of investments with countervailing risks and returns, making your portfolio safer; most investors, however, di-worse-ify instead, making their portfolios more dangerous by buying lots of whatever has been going up lately. If all of your holdings go up together, they have high CORRELATION and will also go down together. The word “diversify” comes from the Latin diversicare, to make different; to be diversified, you must own assets that sometimes make you feel good and sometimes make you feel bad. – Jason Zweig
Two questions were posed in the invitation to this meeting:
a. Can you write a financial plan using Microsoft Excel?
b. What kinds of information would you include in it?
(Let’s find out if anyone knows…?)
Develop a Long Term Financial Plan using an Excel spreadsheet
Speaking today will be chapter member Bruce; he presented a spreadsheet which he had created to assist a family member sort out their financial situation. Using real estate as an example, Bruce explained that even someone with millions of dollars in assets could still go hungry if they weren’t able to trade their assets for food in a timely manner.
The takeaway is that cash flow is at least as important as one’s net worth.
The planning tool ¹ that Bruce developed therefore emphasizes cash availability and net worth, from the current year and for the duration of one’s investing horizon.
Critical data, including all types of income and expenses are calculated and then listed summarily on the second tab of the workbook, which serves as a dashboard in this application. The spreadsheet is comprised of six (6) worksheets (aka “tabs”); the second one is of central importance.
A. Month/Year Budget (“Spending Plan”)
B. Income Timeline (main sheet, see below)
C. Fed and State Income Tax Tables [MFJ] (“Tax % Calc”)
D. Trad IRA Balances & Required Minimum
Distributions (“IRA Val and MRD”)
E. Investment Policy Statement
F. Asset Allocation – Funds/accounts
CF 1012×730 Cash Flow spreadsheet [Income Timeline]
Income Timeline features:
• Income & Expense Projection worksheet (extrapolated 25+ years)
• Almost qualifies as a one-page spreadsheet (40 rows); easy to read
• Annual Projections – Investments in taxable & deferred accounts: Stocks/bonds/cash; Real Estate, Savings, Debt/Mortgage, Net Worth
Bruce responded to questions as he explained the cash flow tracking and its constituent variables.
How to Track Personal Investor Returns using IRR
Next, we turned to a discussion of another useful spreadsheet tool, admittedly one that has a much narrower purpose. The best, clearest and simplest demonstration of XIRR (a robust iterative version of the Internal Rate of Return function) is surely the one that White Coat Investor posted several years ago on his website. His article explains how it works. It’s very easy to determine your personal performance by taking your recent statements, transcribing your account balances and transactions (shares sold–or purchased with money you deposited from outside the account) along with dates. No need to account for reinvested dividends.
An annotated version of WCI’s sample spreadsheet follows.
WCI 848×730 XIRR spreadsheet
How to Calculate Your Return: The Excel XIRR Function
How to Improve Earnings in a Down or Sideways Economy
This conversation came near the end of the meeting and for that reason received short shrift. There was acknowledgment of several on-line banks that offer competitive rates on either CDs or savings accounts or both. Bankrate.com is a website you should be familiar with.
The remainder of the time was spent in a free-ranging discussion on a number of topics. Very enjoyable. At the meeting’s conclusion, FIVE (5) of us went to lunch at the Rubicon Brewing Co.
¹ The cash flow planning tool is a good example to emulate when designing your own spreadsheet—or just update this one by plugging in your own numbers. Used as organizing concept, it is comprehensive enough that the addition of some instructions, a data entry tab, additional formulas to pull in information from other worksheets (such as calculating annual income tax from the tax tables, income and filing status) and validation/error checking would render this a very useful application—a single page console to monitor your wealth and the progress being made toward your financial goals.
1.) This topic was postponed to September’s meeting: Rebalance your Portfolio in 10 minutes with a #2 Pencil
2.) The following files are available to meeting attendees and core members, upon request. They were attached to an email to members sent on 7-10-16.
1 – Cashflow_Bruce_2016_July.pptx
2 – Model_Financial_Plan_Bruce_2016July.xlsx
3 – XIRR_WCI_2011.pdf
4 – XIRR_Example_WCI.xlsx
5 – BogleheadsReturns_BHs_longinvest_2016Feb.xlsx
6 – ROR-stuff_gummy.xlsx
3). Audio for meeting distributed for the first time. A link to the audio recording of the July 2016 meeting was sent to meeting participants on 5/18/2017.