Announcement

Collapse
No announcement yet.

Show me your SHEETS!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Show me your SHEETS!

    All,

    First world problem. I have a number of assets spread among trusts, LLCs, asset classes, accounts, tax advantaged status(es), and married into some dynastic wealth that we may or may not see. Yeah... lucky guy.

    HOWEVER, I can't track it all accurately. I really only care about after tax & inflation returns on my investments while maintaining sufficient diversification. In practice, this is hard to do. How is everyone here tracking after tax/inflation returns on their various investments? For example, I've held a real estate syndication that has distributions. I can manually calculate my principle, distributions, est depreciation, and tax implications with an inflation correction (reverse discount, I suppose). But I want a spreadsheet to do this for me for everything. It seems daunting. Just cooking up the formulas for everything seems a big effort. I need a solution, preferably with a reasonable way to estimate tax impact for planning purposes.

    A direct comparison is important to me, as I can then factor in my own time and decide if the risk/reward is worthwhile. For example, compare the syndication effort (vetting, documents, review, etc) with VNQ after tax/inflation. To be clear, it doesn't need to be perfect, but within a reasonable error range.

    I've tried Quicken, it was too clunky and the latency got too onerous. Entering historical returns for the account was possible for some, but not for most. I've tracked my net worth over time with a Xcel sheet and I'm fairly facile with spreadsheets but don't want to invent the wheel here. Personal Capital is nicely automated for brokerage, but horrible for RE, inflation, and individual returns inclusive of reinvestments.

    Not interested in paying an accountant or professional advisor for this.

    Bottom line: HOW DO YOU TRACK YOUR INVESTMENTS?

    Thanks for any advice or contributions!

  • #2
    I have a pretty solid google spreadsheet for tracking my investments, which I update monthly. It includes a tax adjustment for pre-tax assets (assuming I’ll pay about 19% in effective federal and state taxes in retirement… may update that). I also include cost basis of my taxable accounts. That’s what I use to create an after-tax asset allocation so I know how I really should be rebalancing and directing new assets. RE syndications are hard though. What I do now is keep a separate tab and calculate PV using inflows and outflows as well as my expected return (knowing this could be inaccurate). I admit that I am still learning the tax aspect of RE private equity, but this at least helps me to estimate current balance for the purpose of updating asset allocation.

    Comment


    • #3
      PhysicianOnFIRE had some downloadable spreadsheets on his website. I tried some, then just went basic with my own excel sheets and if i miss a little accuracy due to tax implications, oh well.

      Comment


      • #4
        I use an Excel spreadsheet to keep track of everything. There are a number of them online that you can tailor to your own needs. Once you spend some time on the front end it makes everything pretty easy on the back end.

        Comment


        • #5
          Here is a sample of how I do it, if anyone wants to try.

          It is easiest to start with Columns J and K in the photo I’ve attached. Column J is the total number of shares (manually inputted). Column K is the current price. This can be automatically pulled by google. The formula for VTI is =GOOGLEFINANCE("VTI","price") (just substitute whatever ticker symbol you want).

          Next is column B, that is the gross asset value. It is Column J multiplied by Column K. In this case, formula is =J50*K50

          Then in columns D, F, and H manually put in the number of shares that are in pre-tax, taxable, and Roth accounts.
          Column C is the effective tax rate you estimate you’ll pay on 401k, IRA in retirement.
          Column F is the effective tax rate you think you’ll pay on capital gains in your taxable account.
          Column H is the aggregate cost basis for that ETF or mutual fund in your taxable account.

          Then column I does the work. Here is the formula - =D50*(1-C50)*K50+F50*K50-(F50*K50-H50)*E50+G50*K50




          Comment


          • #6
            I also just have an excel spreadsheet and log onto my accounts monthly to update.

            Have rows of assets then rows of debts. Leave room for expansion.

            =SUM(A1:A10) in A11 to add up assets
            =SUM(D1220) in D21 to add up debts

            =A11-D21 is net worth.

            New column every month and usually done in 10 minutes.

            Comment


            • #7
              OP - I'm surprised quicken isn't able to track all that data you're looking for in the RE and stocks and reinvestment -- are you using Home+Business. Excel/Sheets does nicely for things, but you have to make a lot of time for the tracking and carryovers, and reinvestments -- all that data crunching is done by Quicken and frankly what you're paying for that things like PC or any basic budget program doesn't accomplish.

              If you're really into nitty gritty Quickbooks is ledger heavy maybe what you're interested if Quicken isn't keeping up with the data inputs.

              Comment


              • #8
                I found it too hard to track real, after-tax returns so I don't. I just track them pre-tax and pre-inflation. The inflation adjustment is then easy to make. The tax one, not easy at all.
                Helping those who wear the white coat get a fair shake on Wall Street since 2011

                Comment


                • #9
                  Originally posted by StarTrekDoc View Post
                  OP - I'm surprised quicken isn't able to track all that data you're looking for in the RE and stocks and reinvestment -- are you using Home+Business. Excel/Sheets does nicely for things, but you have to make a lot of time for the tracking and carryovers, and reinvestments -- all that data crunching is done by Quicken and frankly what you're paying for that things like PC or any basic budget program doesn't accomplish.

                  If you're really into nitty gritty Quickbooks is ledger heavy maybe what you're interested if Quicken isn't keeping up with the data inputs.
                  Thanks! My issue with quicken was that once I had my data loaded in, the latency for navigating around the software was just too great. It was driving me nuts!

                  Comment


                  • #10
                    Originally posted by The White Coat Investor View Post
                    I found it too hard to track real, after-tax returns so I don't. I just track them pre-tax and pre-inflation. The inflation adjustment is then easy to make. The tax one, not easy at all.
                    Agree!

                    That's the sticking point. A REIT PE or index fund may have the same quoted return, but factor in depreciation and the whole tax picture shifts. Certainly enough to move the needle. Hence my dilemma.

                    How do you handle reinvestments/lots? Are you updating each one as a line item?

                    Comment


                    • #11
                      Originally posted by ThatGuy View Post

                      Thanks! My issue with quicken was that once I had my data loaded in, the latency for navigating around the software was just too great. It was driving me nuts!
                      Yeah; that's annoying and doesn't matter if stupid celeron vs i7 core CPU -- bloated software grind. It does work though and we have 25+years of data on it.

                      Comment

                      Working...
                      X