Spreadsheets to Track PP

[TripleB]

I've been in the process of building a high-speed spreadsheet to track my PP. I'm looking for ideas on features to build in.

Here's how I have it so far:

First Tab: Overview
1) Displays the total portfolio value
2) Displays the value per asset
3) Displays the ratio of asset to total to 1 decimal place (i.e. what percentage is the asset at relative to the whole)
4) Displays the % of overall assets that are tax-sheltered (i.e. in IRAs, annuities, I-Bonds, physical gold coins, etc)
5) Displays the % of overall assets that are creditor-protected (i.e. IRAs/Annuities/401k)
6) Displays a "Test Field" where I may add prospective new contributions to a certain asset, and it displays a change in the asset ratios (i.e. if I contribute $5k to Bonds for my 2011 IRA, it displays the new % ratios of each asset)
7) Monthly withdrawal capability based on 2%, 3% and 4% withdrawal rates (i.e. to let me see if I am financially independent if my portfolio holdings are enough to sustain my monthly living expenses)

Second Through Fifth Tabs: One Tab Per Asset
1) Displays the Total of that asset
2) Displays the total tax-sheltered from that asset
3) Displays the total creditor-sheltered from that asset
4) Displays some specific breakdowns for that asset (i.e. in Cash, what percent are I-Bonds, what percent are Physical T-Bills, etc)

Of course Tab 1 links to values from Tabs 2 through 5 so I just input the data once.

Sixth Tab: Projections
1) Displays a monthly timeline for the next 5 years
2) Displays estimated portfolio contributions per month
3) Displays estimated total portfolio value per month
4) Field to enter estimated annual growth rate
5) Sensitivity analysis to show effects of deviations from the estimated growth rate


Seventh Tab+: Historical Data
I haven't built this yet, but I may have it pull in data from the Overview page. I.e. on the first of the month I may manually paste the portfolio totals, and the percentage per asset into one sheet. I can then compare this to my projections, as well as calculate my individual portfolio growth rate

I'll probably build a few graphs from this data as well for a quick image summary.

I've been hesitant to track my portfolio growth because it's not actionable. Regardless of whether the PP did +30% last year, or -30%, it doesn't affect how I will invest next year. Since I can't act on the information, it's wasteful to calculate and analyze. Thus, I may skip on that specific calculation, but I do like looking at deviations from actual portfolio to projected portfolio because that is actionable. I may use that to estimate how much longer I need to work to sustain financial independence, which is my overall goal.

My personality is to make things a little complex, so while most people can get away with a one page spreadsheet that has 9 cells on it (nominal value per asset, total assets, ratio per asset), I like to play around a little more as part of my hobby. Any ideas on what else to build in, if nothing more than to practice playing with Excel? Maybe I'll add in some If/Then statements to make the ratios appear RED if it's <15% or >35%

[Jake]

Very interesting, thanks for sharing! My spreadsheet is similar but not as sophisticated- I just have the basic ratios and some fields for calculating the impact of rebalancing requirements. You have given me a lot of ideas

Do you have a variable portfolio? I have a tab which tracks the variable value and then some fields on my overview tab to show the ratios between variable and permanent etc.

[Jimbo]

To save you some work, try starting from this spreadsheet.  You won't need the section using foreign currency conversion.  Instead create 4 sections for the equity, debt, cash and gold. Add a section below the totals  for whatever benchmark you want to use.  It has the benefit of showing what you have on separate tabs, all in one place.  With emphasis on historical monthly performance. 
Discussion http://www.retailinvestor.org/tracking.html
Spreadsheet http://www.retailinvestor.org/Portfolio.xls

[mkchiu]

Here are some interesting spreadsheets.
http://www.flexibleretirementplanner.co ... /tools.htm

[hoost]

I have been building out a portfolio tracking spreadsheet for my personal use, and decided to see if I could make it intuitive enough for others to take advantage of.  I won't say it's perfect, but if you're familiar with google docs/excel, you should be able to manipulate it and make it work for you.

https://docs.google.com/spreadsheet/ccc ... JaHc#gid=8

I'll try to add in some comments to the various cells to address any questions that might arise.  Anyone is welcome to copy it and use/modify as you see fit.  If you have questions about how something works, please ask and I'll explain.  Suggestions for improvements are welcome as well.

---

There are currently 8 tabs.

The first is for rebalancing scenarios.  It pulls in the totals for each of the 4 PP categories and calculates the percentages.  There is an area on the right side to test out different additions/subtractions.  The target %'s can also be changed.

The second is a balance sheet.  It's designed to summarize your portfolio to a more standard accounting format.  To keep a historical record, what I've been doing is taking a snapshot of the Current column at the beginning of each quarter, and pasting it to the right of the Current.  Start by inserting a new column in between B and C.  Then copy column C and Paste Special >> Values ONLY.  This will avoid pulling the formulas.

At the bottom it calculates your retained earnings as Total Equity - Contributed Capital.  Contributed capital is calculated by taking the contributed capital from the previous quarter (whatever is in column C) and adding it to a calculated value on the next tab.

The third is the Equity/Returns tab.  This tab is used to compute your personal returns from the portfolio.  To me, this is the most important tab, because if I can't track how well I'm doing I won't know whether or not I need to make an adjustment.  The xirr function is used to calculate returns.  Every time money is added to the portfolio it needs to be input here. Insert a new row above the Current Result row.  Put in the date, amount of money contributed, and what account it went to (so you remember).

If you want to keep a YTD, QTD, etc. you have to add in a withdrawal followed by a contribution on the same day.  An example is for 12/31/2011.  At the top of the page, the total return computes the return for all of the cash flows.  The YTD return needs to be modified to encompass only the dates you want to compute returns for.  For instance, if I wanted to do 2012, I would start the formula with the positive cash flow on 12/31/2011 and go through the current result.

The fourth tab is to track stock.  If I have two of the same holdings in different accounts, I break it down as you can see in VTSAX and sum the shares.  The Balance Sheet uses a lookup function and calculates the total value by multiplying total shares by market price.  If you change the ticker symbol on the stock tab, you need to go to the balance sheet and change it there also.  You can also insert more rows, etc. ...just use the other cells as a reference to get the formulas to work.

The fifth tab is for gold.  ETF quotes are pulled from google finance.  Coins are pulled from Colorado Gold.  At the bottom you can see the full table from Colorado Gold.  If you need to include a different type of coin, you can look up the row,column in that table and modify the formulas.  Individual coin holdings are totaled in Column E and summed in the Total Bullion cell, which feeds the Balance Sheet.  The ETF tickers use a lookup table.

The sixth tab is for bonds.  The spreadsheet uses to the cusip to look up the 3rd party price from fidelity.  Description, account, maturity are entered by hand; you could get rid of them or leave them blank if you want.  TLT is done with the same logic as stock.  Individual bond total follows the same logic as gold coins.

The seventh tab is for cash.  This should be self explanatory if you've seen the other 3 tabs.

The eighth tab is where I keep records.  If I roll over an ira, buy a gold coin, etc. I keep notes here for convenience.  That way when I do my taxes I have cost basis for each type of coin, records regarding rollovers, etc.

---

I hope that this is helpful to at least one person.  End of the day, it's helpful to me so I guess it doesn't matter if anyone else likes it or not, but hopefully someone gets some use out of it anyway...even if it's just to learn how to use a certain formula.

The key for me was being able to track returns over time.  That being said, I have not come up with a way to do historical returns.  I don't have any intention to build in that feature...I build the spreadsheet right as I started my PP, so I have no need to go back and reconstruct.

If you want to use this, I'd put in all of your holdings to the tabs 4-7; make sure everything shows up properly on the Balance Sheet, and then consider that your Contributed Capital.  What you would do is on the Equity/Returns sheet in the first row under the Date/Contribution/etc. put the current date, and insert a contribution equal to the total value of the portfolio, put a comment "Starting Value".  Then delete all the rows below except Current Result.  Make sure the Contributed Capital cell is pointed to that contribution; it would say something like =sum(B8).  This should be a good starting point.  When you add your next transaction, insert a row above the Current Result.  Then update the contributed capital formula.  (e.g. =sum(B8:B9) if you had contributions in rows 8 and 9.

Let me know if you have any questions or suggestions.

[jackely]

I made one with some of the same data but I included a section for "What If" calculations for re-balancing. I key in the amount to buy/sell of a certain asset and it makes all the adjustments for me. When I get it where I want it I can take that section and do all the trades to get back in balance.

[hoost]

Hi jackh,

I have something sort of like that on the first tab, but it's only broken down by asset class.  I gather from your statement that you have it broken down into individual shares? (I guess that's not a question).  I thought about making it more detailed, but this seems to be working okay for me for now; I'm not sure how I could make that work within my current framework.  It's an interesting idea; I'll give it some more thought.

[jackely]

Hi jackh,

I have something sort of like that on the first tab, but it's only broken down by asset class.  I gather from your statement that you have it broken down into individual shares? (I guess that's not a question).  I thought about making it more detailed, but this seems to be working okay for me for now; I'm not sure how I could make that work within my current framework.  It's an interesting idea; I'll give it some more thought.

Yes, I have it broken broken down into every individual asset I can buy or sell but I don't get down to the share/price level, just the $ amounts. I can do that calculation manual calculation easy enough.

[steve]

I just uploaded a sample portfolio tracker to google docs , I did not convert it so it is excel file that you would need to download in order to use.
This speadsheat is a simple modified version of what I use. It also has a built in GTU premium calculator that updates close to real time.

https://docs.google.com/open?id=0B8hEwP ... GgydVF3SlU

[hoost]

I spent some time messing around with my portfolio spreadsheet today and updated it to behave more a like a database.

This should make it easier to run various queries on my holdings.

I have already built in some queries to the spreadsheet (based on my current needs); if anyone has something more/different they'd like done with it, please let me know and I can incorporate it into the document and/or post an explanation (as I understand it) of how to implement what you have in mind.

Most of the instructions I posted previously should apply, however in this iteration, input all of your holdings into the "Holdings" tab.  All other data/reports are pulled from the holdings listed on that tab.

I find it a lot easier to visualize the portfolio in this format than what I had previously.

I plan to add another tab to break down the portfolio based on individual accounts/account types, but I haven't yet decided how I'm going to implement that yet.

I created a dummy copy in case anyone is interested in using it:  (***Edited 08-Apr-2013 to update link/spreadsheet***)

https://docs.google.com/spreadsheet/ccc ... sp=sharing

[Greg]

Since this is the stickied thread, I thought I'd post my template and steve's template in here for reference.

http://gyroscopicinvesting.com/forum/ht ... 021#p48021

[notsheigetz]

This is personalized for my own situation (Jack (me) and my Wife Nenette, aka (Nen) but the columns are easily configurable for any situation and they are all basically the same (not sophisticated enough for tax accounting - you have to think about that yourself).

Basically, I look at my Fidelity statement and enter the real values in CURRENT. The TARGET section is calculated automatically based on the PP goals and is read-only. The Buy+/Sell section is where I enter my what-if scenarios (Cash to Invest is carried over from the CURRENT section). Entering figures into the BUY+/SELL section I am able to see how it all balances out in the TARGET section and I can print out a page telling me exactly what transactions I need to do in each account to achieve the Target.

[Kriegsspiel]

snip

I ganked your spreadsheet man, thanks for all that work.  I was working on a spreadsheet but it was Barney-style compared to some of the majestic pieces you guys made.

[hoost]

snip

I ganked your spreadsheet man, thanks for all that work.  I was working on a spreadsheet but it was Barney-style compared to some of the majestic pieces you guys made.

No worries.  I'm glad someone else was able to make sense of/use it.  It makes the effort more worthwhile.  If you have any questions on tweaking it, let me know.

[Kriegsspiel]

Yea, the part that blew my mind was that you could link to websites to update the cells.  I never knew you could do that.  I don't understand how it updates my bonds... that's pretty much black wizardry.  I did figure out how to update the Colorado gold table since I guess they changed the rows since you made that table.  Other than that, I pretty much kept it as is, except for deleting out the stuff I don't use.  I'll probably have to recheck that original shared one if I need to add that stuff back in... I think I deleted one of your really long formulae. 

I think the only thing I might add to it is something to calculate the duration of the bond ladders.

[frugal]

Hello,

where can I see daily variation of HBPP?

I would like to feel the flutuactions day by day in a chart.

Thank you.

[Greg]

A quick way to do this would be make a Google Finance Page and put a hypothetical $2500 in each of IAU, SHY, TLT, and VTI.

[frugal]

yes GREAT

Can you only see ONE year chart?

Regards

[Greg]

Well you can either plot all 4 of the items and compare them against each other. This will at least show you percentage changes in the past year.

Or check the price one year ago and state that as your cost basis for each of the 4 items. That way you'll have the price appreciation of one year.

[frugal]

Yahoo Finance has longterm (+10years) charts for the similar My Portfolio?

Tks

[Xtal]

I just found this online: lovely way to track physical gold (and silver) coin purchases:

https://drive.google.com/previewtemplat ... ode=public#

[Kriegsspiel]

hoost, is there a website where I can link a cell in the gold portion to the gold spot price?  I had been using Colorado gold, and tried to link it to Kitco's site but messed something up.

[hoost]

hoost, is there a website where I can link a cell in the gold portion to the gold spot price?  I had been using Colorado gold, and tried to link it to Kitco's site but messed something up.

Here is the bid from APMEX:  =Index(ImportHTML("http://www.apmex.com","table",0),2,2)

I use colorado gold for the bid on coins.  This will give you the full table: =importhtml("http://www.coloradogold.com/selling.php",1,0)

To get an individual value from the table, use this (this is for 1 oz. gold eagle):  =index(importhtml("http://www.coloradogold.com/selling.php",1,0),2,2)

1 oz. Maple Leaf would be:  =index(importhtml("http://www.coloradogold.com/selling.php",1,0),8,2)

Is that what you're looking for?

[Kriegsspiel]

=index(importhtml("http://www.kitco.com/market/",1,0),5,6)

That's the one I'm using to get the gold spot price, just for a consistent measure across the different coins/dealers one might use.  It wasn't working for a while so I thought I broke the internet, but it's working now.

[Rezo]

Hi guys, just wanted to say thanks for putting up all the info for tracking so far and had a question. Fidelity recently updated it so that the old =IMPORTHTML for bond prices isn't pulling in the price anymore. Does anyone know another place to pull this info from and/or can write up a =IMPORTHTML formula please?