subscribe to the RSS Feed

Sunday, April 30, 2017

Tracking Investing

Posted by Tim Stobbs on January 7, 2011

Investing for me has always been a weird relationship.  At some points I love doing it and others I don’t care about it at all. Up until recently my choice of investments were more or less irrelevant to my plan since my savings far out paced my investment returns. Let’s face it when you only have $50,000 in investments a 1% difference in your return is only $500/year which pales in comparison to saving about 50% of your family income.  Yet as I start to close in on paying off my mortgage I will in the next few years have a larger cash flow going to investments which will soon accumulate in a large enough investment balance where my investment choices will start mattering a lot more.

So in preparation for that fact I’m thinking about developing a system to go with my net worth updates that would allow me to more easily track what contributions I’ve made in a given year.  That would then allow me to actually track my pure investment return rather that the current contributions and investments growth.  Currently using my existing statements isn’t an easy process since my investments are spread out over multiple companies.  Yet with a simple spreadsheet I do think I can manage to track their growth and contributions.

Also I am concerned about tracking enough information to make such a sheet useful, but not making it too much work to update.  Otherwise it will turn into a  chore and I will likely avoid doing it and thus ruin the entire purpose of the spreadsheet.

Yet I don’t think I’m the first person to do this so I am wondering if how you track your investments.  Do you just use your quarterly or monthly statements, a program or just a simple excel sheet?  If you have a spreadsheet feel free to email or link to a copy in your comment. I think I should be able to find something that would be a useful template to build what I want without having to spend much time doing it.

Comments

9 Responses to “Tracking Investing”
  1. Robert says:

    First, the benefit to holding assets all over the place is rarely worth the cost imposed by complexity. I’d suggest a maximum of two locations (1 is better) unless there’s a compelling reason.

    Second, investment deposits and income belong on the income statement, not the net worth statement. I prepare each of these quarterly, but neither of them give me my investment return.

    Because I keep all my assets together (at work), it’s easy to produce a single performance number. Also, we have software that does it for us. I believe you will needto track each deposit and withdrawal, interest and dividend payment and use the modified Dietz method. This is probably doable in excel, but I think http://www.google.ca/finance will also do it for you, with a chart, but with only a simple (not compound) rate of return.

  2. deegee says:

    Tracking my investments over the years has been a patchwork for a variety of reasons.

    (1) My retirement accounts produced statements every quarter, while my non-retirement accounts produced statements every month or every quarter.

    (2) Within my non-retirement accounts, there was additional variation of when the I received statements over the years. Sometimes, it was at the end of the month end of the quarter, the middle of the quarter, and the middle of the month. This made matching up these snapshots rather tough. I would often use the automated phone systems (in the pre-online days) to get account balances.

    (3) Counting my local bank, I have non-retirement accounts with 3 institutions, so I need a spreadsheet to put all the data together.

    (4) Figuring out a combined rate of return for my retirement+non-retirement accounts does not seem relevant because I have significantly different portfolios and investment objectives for them.

    (5) I have at many times over the years used the dividends from one fund deposited into another fund. I did not always want to do the extra work to figure out what my rate of return would have been had I always reinvested the dividends into the same fund they came from.

    (6) For funds which I have invested or redeemed shares in the middle of the month, calculating a rate of return for that month is only an approximation because the dividends are based on an average daily share balance which is often tough to calculate.

    Based on these obstacles, I have multiple spreadsheets which calculate my rate of return based on only the dividends and cap gains generated from within the fund. One main spreadsheet which uses all the combined data from the non-retirement funds does do various rates of return, even if approximate in spots.

    On the upside, once I had these spreadsheets working, simply copying the line above into the new line I create would copy the formulas, too. I have to make sure the annual formulas are correct and are not by a row or two if they are an aggregate of monthly amounts.

    The end of each month is when I do most of the work, but I do have to keep on stealth mid-month activity (i.e. cap gains distributions) and for mid-month snapshots based on my bank account statement. Still, after all these years it is fairly routine.

  3. George says:

    Yes, I have a few tracking schemes:

    1) I enter the totals from my monthly brokerage statements into a spreadsheet that compares the grand total to the S&P500 index, taking into account deposits & withdrawls. This is akin to a net worth statement, but it only includes investments directly under my control and gives me a benchmark to measure against.

    2) I track dividends & yield for my taxable account, updated at least monthly and whenever I execute a trade. This spreadsheet tells me which months have how much income, total income YTD, and calculates the stop loss that I use as a safety valve for trading stocks. My target yield is 6.5% (or higher) and I want to beat my mortgage which is 3.85%.

    3) Annually, I track dividends & yield for all accounts, including my wife’s, and compare that to the government’s poverty threshold for 2 adults… produces a neat chart that shows how the _income_ is building, gaining momentum. Why the poverty threshold? Because with an income at twice the poverty threshold, sociologists have found families are “comfortable” with few concerns even if they have wants.

    4) Annual totals for all retirement accounts, including the ones not directly under my control (pension), are tracked in another spreadsheet. The totals are run out into the future with a consistent estimated return and I measure how well the estimates match reality. Yes, that means there’s a new column each year for the latest estimate.

    5) Not related to investments, but an interesting exercise, I annually track my earned income and compare it to the government statistics for a person of my age/education (preferably the median, but that’s not always available). It’s fun to leap ahead of the median curve, but amazing how quickly one can fall behind, too! Fairly recently, I crossed an age threshold (again) and am back on the wrong side of the median…

  4. Patrick says:

    I use Quicken Home and Business. There are a variety of free programs on the internet such as Mint.com. However something about putting personal info into cyberspace that makes me uneasy – so I paid for Quicken and keep the info on my hard drive. It tracks lifetime performance of every investment automatically updates stock, mutual funds etc daily and charts my net worth progress.
    For me using a spreadsheet would be a tonne of work just to save the $75 the program cost – my time is better spent improving my investment choices :)

  5. Goal Hunter says:

    To track investment performance just list:

    1) Cash flows in & out (deposits and withdrawals) since last update
    2) Change in value EXCLUDE flows in & out.

    It’s that easy. You won’t get an “exact” annualized continuously compounded rate of return, but you will get a period over period comparison which is great. Do the same thing quarterly and annually too.

    Example:

    Jan 31: Starting value $1000
    Feb 28: Deposited $500 during Feb, value is $1,750
    Feb value EXCL flows is $1,250 so you made a $250/$1000 gain = 25% during Feb. If value is $1,250 then EXCL flows you lost $250 or -25% performance.

    Uncomplicated and tells you exactly what you want to know -> how well your investment decisions were.

    Group all your investments as a bundle to produce just one figure.

  6. I’m mostly in the stock market, and use google finance to track mine — it gives a nice chart of where my moneys at, breaks out each stocks +/-, and shows me my overall gains/losses. Its simple, and all linked up to google — I highly recommend.

  7. What I do is I use Bloomberg app to track my stocks. It tells me instantaneously how much I’m up or down. I have it on my iPhone.

    I also, once a month, do a net worth tally and in it I include individual stocks. It’s nice to look back a few months to see what the market value was a month or two ago.

  8. Retiredat44 says:

    I think keeping track of the investment returns does give you a better idea of what is going on as well as a big sense of satisfaction! I use a very simple but pretty stupid way to do it in a spreadsheet. Here is an example:
    Contribution # of Years Annual Rate of Return: 1.0905
    2000 7100 11 $18,413.69
    2001 6935 10 $16,493.13
    2002 7740 9 $16,879.98
    2003 7740 8 $15,479.12
    2004 7740 7 $14,194.52
    2005 7740 6 $13,016.52
    2006 7740 5 $11,936.29
    2007 7740 4 $10,945.70
    2008 7740 3 $10,037.32
    2009 7740 2 $9,204.33
    2010 6480 1 $7,066.44 $143,667.05
    Total Contribution Current Value Gain/Loss %
    82435.00 143902.00 74.5642

    At each row, you put in the annual contributions to the account and the number of years the money has been in the account. You then plug in an ‘artificial’ rate of return (here shown as 1.0905, that is approximately 9%), and then use a formula of =annual contribution*(rate of return^years in the account) for the compounded result of each year’s contribution, the sum of which should be close to the currently value of your account.

    You can expand this to set a target rate as well as tracking your actual performance.

    Have fun!

  9. Goal Hunter says:

    Nice spreadsheet Retiredat44!

    Couple of suggestions. There is a formula called IRR which will give you the actual rate.

    Second, you might be getting better over time. Maybe at the beginning you didn’t optimize your strategy and got 3% but now you are rocking it at 15%. 10 years is a very long time. You could have a third column called ROI from year X.

home | top