<< Back to all Blogs
Login or Create your own free blog
Layout:
Home > Fun with Spreadsheets and Pivot Tables
 

Fun with Spreadsheets and Pivot Tables

April 12th, 2015 at 06:33 pm

I just spent the majority of this weekend working on a comprehensive financial spreadsheet tracking every single investment transaction I've made in 2015.

(I know, I know, I have NO life.)

I'm using GoogleFinance functions that dynamically update share price and market values, and I am having a blast using pivot tables to break out the data in a variety of ways. Behold the plots I've done so far --

Asset Allocation pie chart (excluding stock options) --



Asset Location bar chart showing asset classes in various tax buckets --



Which accounts and funds my investments have gone --



Cumulative timeline of this year's 401(k), IRA, and brokerage contributions --



And now I know that I'm $24,869.60 short on my target allocation for international equities. And I can also recall random Vanguard fund tickers with disturbing familiarity, heh.

2 Responses to “Fun with Spreadsheets and Pivot Tables”

  1. AnotherReader Says:

    You might like the tracking software that Personal Capital uses. It does all this and downloads the information from your banks, mutual funds, brokers, etc. It also tracks debt. I highly recommend it.

    And get those bonds out of your taxable accounts! Put them in your tax deferred/tax free accounts to avoid paying taxes needlessly.

  2. amberfocus Says:

    I tried Personal Capital a few years ago, but was supremely skeeved out when they called my personal phone to prospect. I was actually so uncomfortable that I deleted my account. I just checked back, and they still require a phone number to sign up. Eh. I'm quite skittish of wealth chasers.

    I totally get what you're saying about the location of the bonds, but I'm juggling competing considerations. Namely, I need those bonds (and their interest/dividend payments) to be accessible way before traditional retirement age of 59-1/2 (I'd like to be retired by 35 or 40) -- and that means they've got to be in taxable accounts, which will get drawn down first.

    What I might do instead is split the allocation to be proportional within each tax bucket (so 10% for now) to protect them from taxes for the time being, and then up the allocation only in the taxable bucket when I move to 25% bonds in early retirement. That might be a reasonable compromise?

Leave a Reply

(Note: If you were logged in, we could automatically fill in these fields for you.)
*
Will not be published.
   

* Please spell out the number 9.  [ Why? ]

vB Code: You can use these tags: [b] [i] [u] [url] [email]