Calculating/tracking portfolio Growth

[doneat54]

I track our portfolio in a massive spreadsheet (I'm sure ALL have one too) and log into the 8 or so accounts (IRAs, 401Ks Roth's etc) we have and pull balances, and try to about once a month. Been FIRE'd a bit over 2.5 yrs, DW is still working and so far, we have not had to touch the portfolio.


Up until now, I never really tracked the growth of the accounts much (as a %), but have started to. Since it might be 80 days or 15 days or whatever days between when I pull balances, the interval growth as a percentage is pretty meaningless.



Had the thought to annualize each growth calc the other day and used this simple formula:

Calculate interval growth: (current balance-last balance)/last balance


Calculate interval days: Date of current balance check-Date of last balance check


Annualize interval growth: (interval growth*interval days)/365


It seems to work, and the growth numbers are better than I had thought. I can then get a rolling growth rate, average growth over last year, etc.


Do the same calc for each account individually to track them.


Sound math?


I got 7.724% growth since May 2018 on the whole portfolio, and that is with roughly a 40/60 AA. I can clear see lower growth in the accounts where I wend bond heavy vs the others.


Interested to hear how others track growth, if you do. I know there are sites that you can trust with your logins and they can do pulls of balances on a schedule for you, but with the 2 level authentications now, they never seemed to work that well, and I never really felt 100% comfortable with them.

 

[jazz4cash]

I never was too interested in tracking any more than quarterly. I’m not comfortable with any auto logon programs so I use Fidelity GPS and manually load spouse’s 2 accounts and maybe 8 credit union CDs. My main objective is to manage our AA.

 

[pb4uski]

I only track YTD and use XIRR.... all you need are beginning and ending balances and the dates and amounts of additions or withdrawals.

And these are "outside the fence" since I look at all our accounts combined... so if I move money from tIRA to taxable it is still inside the fence... only additions or withdrawals are those that go out of the retirement accounts combined.

Occasionally I might look at 3-years using the same XIRR approach.

For us everything is in Quicken, linked to the providers and updated periodically using One Step Update.

 

[OldShooter]

Whew! Too much work for me.

There are two kinds of return: time-weighted, and dollar-weighted. So the first question is which one you want. I think most prefer time-weighted.

Another problem arises when money is added to or taken out of an account. If you are just tracking account balances, you won't be able to tell whether a change was due to earnings or to funds flows.

I think what you want is probably given to you by the internal rate of return function (IRR) in Excel or as @pb4 suggests, XIRR. You might want to check it out.

We are passive investors, so we really have no reason to track growth per se. Once a year we look at the accounts and AA and make any changes. I do reconcile brokerage and bank statements with Quicken downloads so I get a look at account values every month. Quicken also has a function that claims to calculate IRR, but it gives enough goofy values that I do not trust it or use it. (Now that Quicken has gone to subscriptions I think Moneydance is considered to be an acceptable workalike. I am stuck in Quicken due to the years of data I have that will not import into Moneydance.)

 

[donheff]

I follow a simle approach. For the portfolio as a whole I divide the current balance by the end of year balance to identify the growth to date. In another cell I add the year to date withdrawals back into the current balance to arrive at a rough estimate of growth without reduction by expenses. I compare that to a target retirement fund that is close to my AA as a benchmark. The numbers are quite close. For each mutual fund in our portfolio I have a cell showing the Google function total return to date. I have thought about trying some of the more complex IRR formulas the engineers around here plug in but I am not a spreadsheet guru and don't like functions that leave me scratching my head as to what they really mean.

 

[doneat54]

Well thanks for the insights so far. For clarity, there have been only 2 withdrawls in the 18 or so month period that I am following, and I did add those back in to the account balance for the interval they were in, for the sake of getting a true growth for that interval.

I'll look into XIRR. Again, this is basically a group of unfunded accounts, so the only "additions" to it are the growth of the holdings. Some said you just need start and end balances and "dates of withdrawls and additions", but there really aren't any (except the two mentioned above). I'll google XIRR, might be an interesting comparison calc to mine.....

 

[doneat54]

I follow a simple approach. For the portfolio as a whole I divide the current balance by the end of year balance to identify the growth to date. ....

You mean "divide the current balance by the START of year balanc"? Or end of previous year balance?

 

[OldShooter]

... this is basically a group of unfunded accounts, so the only "additions" to it are the growth of the holdings. Some said you just need start and end balances and "dates of withdrawls and additions", but there really aren't any (except the two mentioned above). ...

Yahbut ... when you retire you will start drawing, so you might as well set up an analysis system that is robust enough to handle less-static accounts. Just MO.

 

[pb4uski]

This may work for your too... simple but effective. While I do XIRR I also do a parallel calculation using this moneychimp calculator... since my withdrawals are pretty constant it is usually within 1/10% of XIRR.

Investment Return Calculator: Measure your Portfolio's Performance

 

[donheff]

You mean "divide the current balance by the START of year balanc"? Or end of previous year balance?

Yes. I use the number I compute on Jan 1. I just call it EOY.

 

[doneat54]

This may work for your too... simple but effective. While I do XIRR I also do a parallel calculation using this moneychimp calculator... since my withdrawals are pretty constant it is usually within 1/10% of XIRR.

Investment Return Calculator: Measure your Portfolio's Performance

Cool. Plugged the numbers in and got 7.2%, so pretty close. Thanks

 

[scrabbler1]

I first calculate a monthly change by taking the end-of-month balance divided by the start-of-month balance, excluding any new money going in or money going out. I then multiply 12 consecutive monthly changes together to get a 12-month, or yearly change. It's not 100% clean, but it works well for me.

 

[audreyh1]

Since we withdraw at the beginning of the year and make no additions during the year, our YTD growth is a simple current value/start of year value -1 calculation which is included in my portfolio spreadsheet.

 

[SecondCor521]

The XIRR function in Excel or similar programs is really pretty easy to learn to use, FWIW.

 

[jkern]

I use an Excel spreadsheet and update quarterly. It takes about 15 minutes each time.

 

[RobLJ]

For us everything is in Quicken, linked to the providers and updated periodically using One Step Update.

I also track with Quicken & OneStep. I do have to do the internet download for Wells Fargo checking, for which they want to charge for automated Quicken downloads (for some reason Savings downloads with OneStep). The data goes back to 1995, when I started using a computer finance program which I imported into Microsoft Money, then imported into Quicken after MSMoney folded.

 

[Cobra9777]

I use several different methods...

We have most of our accounts at Fidelity and they provide tracking and performance figures for any grouping of accounts and timeframe along with some basic benchmarks. They only calculate this at the end of each month and IIRC it takes a couple weeks to post it. So this is not a good option if I want an answer quickly or mid-month. It's also a bit of a black box; I'm not really sure how it's calculated, although if I searched, I'm sure I could find an explanation. The number sometimes deviates from my other methods though not by a huge amount.

For the most accurate answer, I use XIRR in Excel. This is a bit more time-consuming as I have to download some transactions with dates from Fidelity. I trust this answer the most and it can be run at any time and converted to YTD if desired.

Moneychimp is a quicker version that never varies much from the XIRR calculation. But you still need to look up your additions and withdrawals.

Another spreadsheet method I use often is to look up the YTD total return (in Morningstar) for each of our 12 ETFs. Putting these into Excel takes a few minutes and it's not as accurate as other methods if we've made changes in the portfolio during the year. But it's always extremely close. Plus this is the only method that allows analysis by holding or categories of holdings so I can determine how the various parts of the portfolio are performing relative to the total. The other methods just provide a single answer for the total portfolio. I also use these figures to construct various simple benchmarks, like a 50/20/30 mix of VTI/VXUS/BND.

 

[kgtest]

I wrote a script a year or two ago that auto-logged onto acnts and snagged the shares # from each of our 4 investment sites...

Then I just pasted the output file into Google Sheets in a quick one cut/paste maneuver. So I could run this script every quarter to "report on my dividends" and get my total shares.

Now I just sort of do it willy nilly EOM so i can report back to ER and update my own monthly spreadsheets...stopped with script because browsers updated, MFA is an issue now, and I am lazy and don't want to re-script lol.

I should though. Maybe over xmas when I get downtime.

I basically get a realtime look if I want using Google Sheets. But I simplified my investing to 3 firms and 3 sites to login to for DW and me.

Vanguard (Broker, IRA, Roth) and Schwab(401k) and Melife(403b)

Investing is as hard as you make it. I haven't done much of anything past year...just changed 401k contribution towards the end of the year, that's about it. Haven't triggered a rebalance yet in 2 years of investing minimizing txn costs.

Login to 4 accounts, paste into sheets, find my old ER post and quote it to update. My contributions are automatically factored in my sheet to give me YTD. Looks like I'm bouncing around 29.5 today.

 

[NanoSour]

I've been tracking returns using the OpenOffice XIRR function. I combine retirement accounts (IRA's/401K/403b) and non-retirement balances and subtract our monthly "paycheck". After 6 years we're at 7.4% annually using a 40/35/20 Vanguard 3-Fund portfolio. Cash is running around 5%.

BTW, based on SSA COLA adjustments of ~1.6% annually, I'm tracking a 5.8% real return over the past 6 years. I'm happy with that and hope it continues.

 

[Rianne]

VG does a 10yr, 5yr, 3yr, 1yr and YTD for you. But, that's the only company we've dealt with. I'm sure Fidelity does the same.

 

[perez99]

I only track YTD and use XIRR.... all you need are beginning and ending balances and the dates and amounts of additions or withdrawals.
....
For us everything is in Quicken, linked to the providers and updated periodically using One Step Update.

I started using Quicken myself in the last few months. Make it much easier to understand our spending.
Can you comment on what reports you use? It seems you are exporting the data to Excel.
Thanks

 

[Montecfo]

I calculate returns once annually. I do not find I need to do it more often. I focus more on AA in the interim and my stock selections.

I use quicken but just for tracking expenses, not for investment returns.

 

[COcheesehead]

I track three numbers:
Portfolio
Networth
FIRE Pool - consists of everything I will use as a source of funds in retirement so it includes a real estate holding and a present value of social security.

I calculate year over year growth because to me that is all that matters. The formula's are all in the spreadsheet and cells are set to copy, so I only have to input numbers into one in order to update them all.

If I am really concerned about market growth Fido calculates that for me.

 

[KarenC]

I use the BogleheadsReturns spreadsheet. (You don’t have to be a Boglehead to use it. ;-) )


https://www.bogleheads.org/wiki/Calculating_personal_returns

 

[pb4uski]

I started using Quicken myself in the last few months. Make it much easier to understand our spending.
Can you comment on what reports you use? It seems you are exporting the data to Excel.
Thanks

I use a Portfolio Value report subtotaled by account and then copy to clipboard (Ctrl-C) and then paste into an Excel workbook that I keep on a sheet called Data. Then other sheets reference to this Data sheet to calculate YTD return, look at AA and compare to target AA, etc.