Portfolio Returns in Excel: Two Approaches

While writing my status report for the end of 2019, I discovered that I did not know the actual returns of our investment portfolio. This was a situation that could not endure much longer, and today I am pleased to report that the ever-more-sophisticated spreadsheet is now able to compute such figures.

Readers may be familiar with the two distinct approaches to calculating the returns of a portfolio:

Choosing one of these is necessary when the portfolio is subject to inflows/outflows of capital throughout the investing period (without flows, the return can be trivially calculated given start and end balances). Much has been written about the differences between the two, and the situations where one may be more applicable than the other.

For me, since I want to be able to compare my portfolio against public indices, I believe the time-weighted approach is best. This involves creating an initial pool of imaginary “units” representing portions of the total, adding or removing units when a cash flow occurs (e.g. regular savings deposit, withdrawal), and keeping track of the per-unit price. The per-unit price ought to change in response to actual rises/falls in the underlying investments, not affected by the flows. Additionally, the final result should not be affected by the timing of the flows (this is what distinguishes it from the other approach). Essentially, I will be measuring my choice of asset allocation.

A practicality

But beware! Between the conception and the creation there is a source of potential error: how can we know the exact total balance at the time of each cash flow? Do we have to check all the sub-accounts at exactly the time a cash flow is executed? Or what if we invest at various points throughout the month, but only know the balance at the end?

After thinking about this for a while, I have come to the conclusion that there is no practical way to solve this issue perfectly, but that with a bit of mitigation it is unlikely to ever have a serious impact. The simple mitigation is to check balances at the end of the month and to arrange flows to occur soon afterwards, for example of the 1st of the month (this is convenient because I can perform manual transfers while still logged in after checking balances). Assuming that the flow amounts are a small fraction of the total portfolio value, and that there are no unusually large portfolio value changes near flow events, the end figure should marry with reality pretty closely.

I suspect that others have done the same thinking, but in my reading I did not come across anyone who brought up this point explicitly.

The implementation

Since I have all the numbers in the spreadsheet anyway, I chose to incorporate both approaches. This will be an interesting opportunity to compare the two, and to notice if the cash flow timings do indeed make a significant impact. At the very least, it should provide a redundant check to help detect Excel errors.

You can find many examples of the formulas required to track unitized returns, so I will not repeat them here. I think it’s more useful to show how I personally implemented these calculations alongside an existing sheet that was previously used simply to track the monthly portfolio balance. This is designed to be scalable across many months/years via copy+paste, with low ongoing maintenance effort.

Here’s the relevant part of the sheet:

Figures changed for privacy
  • This covers Aug-Dec 2019. The value in H35 is the total at the end of July. Column I is August and column M is December. I always have a free column of either side of a year (this is important for the XIRR computation below).
  • Row 35 gives the total at the end of each month (this is actually calculated as a sum of figures above, but could be entered manually).
  • Row 38 gives the net cash flow in/out of the portfolio (this is also calculated from other figures, but could be entered manually). Remember, we assume these happen on or close to the 1st.
  • Bonus tip: the verification and check cells are a system I use to ensure that these historic computed values never change unexpectedly in the future.
  • Rows 42-49 show the unitized computation. The cumulative AER row is the most interesting, with the bold value in column M giving the end-of-year value. I used the number of cumulative months row (47) to give an AER with the formula =(1+M48)^(12/M47)-1.
  • Rows 53-58 show the XIRR computation. All examples I saw online had a vertical list of flows, but, well, it works this way too!

Conclusion

With the dummy figures in the above example, the two approaches yield results that differ by only 0.01%. My actual results were 1.89% unitized and 1.78% cash-weighted, with a larger delta due to more volatile monthly returns and flows. As a side note: it looks like one factor in the low performance is the fact that much of our cash holdings receive annual interest falling outside this Aug-Dec period. I will continue to track these returns and they will certainly feature in the next status update.


So, how did you implement return tracking for your portfolio? Does it look like the above, or do you have a different method?

The Beginning

After my first post last week where I gave an end-of-2019 status report, it is now time to do first things first, and introduce myself.

Like the many other bloggers in the FIRE community, I have decided to adopt a somewhat unconventional lifestyle in order to achieve financial independence at an early age. I do not like the phrase early retirement. Right now, Mrs. Fireman and I are both around 30, and with our current habits and cash flow, we might achieve this sometime around our mid-forties. For us this means amassing enough sensibly-invested net worth to sustain a modest lifestyle a withdrawal rate of 2.5-3.0% (which currently seems to be a conservative figure).

Unlike the friends, family and colleagues around us there will be no flashy cars, prestigious accomodation or luxury holidays along the way. But there will also be no sleeping in the back of cars or sitting down at dinner to bowls of rice. Others choose these paths. What we’re after, however, is the via media of eschewing attractive but ultimately barren consumerism, while not allowing Excel to become the main fixation of our lives.

Of course, anybody starting a blog such as this finds personal finance an interesting topic, and I am no exception. It is also a hobby, complete with a community, regularly scheduled activities (primarily updating spreadsheets) and a metric for progress. There is also an element of seeing things as they really are, for example the simple relationship between saving rates and retirement age , or the surprisingly high cost of small regular expenses. Is this journey for fun or profit? Let’s see whether it can be both.

Where we are today

Helping us on our journey is my full-time job in a STEM field, where I expect to earn c.£100k this calendar year (gross — it’s much less net!) Mrs Fireman will earn c.£20k for a non-STEM job. We spent around £40k on all expenditure last year (including £12k in rent, which is pretty good for our area, where we must stay for my job). There is certainly scope to reduce this (including the controversial option of becoming home owners). We also transferred c.£50k to our savings/investment portfolio (maxing both ISA allowances, and contributing to a pension). Net worth stands at c.£115k.

Acording to projections, 2020 is shaping up to be roughly similar.

Where we go from here

In the short-to-medium term, I think there are a few high-impact areas we can focus on:

  1. Increasing my total compensation by performing well at work — even small percentage-wise improvements here could have a big effect on the savings bottom line. This might look obvious (working until the job is done), or non-obvious (putting in the effort to get along socially with my colleagues). I think this is an area that deserves regular and careful attention, lest I become complacent.
  2. Deciding whether it makes sense to contribute more into pension wrappers. This has the huge attraction of “undoing” a lot of income tax for the surplus left after filling the ISAs. But any money in such wrappers is locked away for a long time (I might be 70 before I can access it), excessively vulnerable to political tax law whims and subject to unknown future tax rates. Since I believe we might be financially independent long before we can access thse, there is a trade-off in how much to contribute. I think this trade-off can best be modelled as two distinct “retirement” periods (where the first one ends at pension access age). But this makes the calculations more complex than than simply drawing down a single pot. This is the topic of a future post.
  3. Thinking more about asset allocation. I have read a lot on this topic, but am not at all certain on the right answer. In the medium-to-long term I can imagine settling on somewhere between 60-80% equities, based on all the information absorbed from books and blog posts. Although I accept intellectually the mantra of not trying to time markets, some part inside me warns that it’s a bad time to jack up equity exposure.
  4. Investigating novel ways to make money: there are several tentative ideas in this area which might appear in future updates. I have set myself the (ambitious) goal of generating £3000 in non-job/non-investment income in 2020. The absence of any real plans might be mitigated to some degree by this public yardstick. We will see.

Over the next months and years I will be providing more information about our progress, both the hard financials and other more lofty insights. Some of the blogs I linked to above have inspired me in many ways, and my humble aspiration is that my effort might do likewise for those find themselves reading this account.

2019 End-of-Year Status

This is my first status update describing our progress towards financial independence in 2019. It is also my first public post on this blog, and the first time I’ve tried to summarize our financial situation in a text format. The main motivation for doing this is to slowly build up a summary prose account of our journey that is comprehensible in a way that figures in a spreadsheet never can be.

I intend to post with more information about our situation, goals, etc. at some point. Of course, this background context would have made perfect sense as the first post. But I think there’s value in jumping in with the meat of it, especially since we have just passed the end of 2019 and the numbers are in.

This first post will use an update skeleton created by the author of retirementinvestingtoday.com (which is a large part of what inspired me to start this). This skeleton breaks each post into three parts: save hard, invest wisely and retire early. The figures have been taken from a comprehensive spreadsheet we’ve maintained all year.

Save Hard

Over the calendar year, we added £48,660 to our investments, including pension contributions. This was primarily generated from our day jobs, but a significant £10,000 chunk was a gift from relatives. This gift is a one-off and so we must be careful to not extrapolate using this anomaly.

I am very happy with this figure. The gross savings rate was 42%, calculated as the above newly invested amount divided by household total compensation (including employer pension contributions). The main driver for this high rate was earning more: I increased my compensation through negotiation and received a sizeable bonus.

Another driver of the rate was spending less. Mrs Fireman and I are naturally frugal spenders and this year was no exception. We went on a few low-cost holidays at sensible times and avoided large purchases. There is certainly room to look at further optimizing expenses, but alas there is no extravagence to be easily curtailed.

I improved the spreadsheet half-way through the year and the previous figures are not directly comparable…

One category that looks high on our expenses sheet is accommodation: we spend an average of £1400 per month on housing costs, including rent, bills, council tax and furniture. Cutting this down could have a large impact in the long-term, but changes here depend on many other factors, including my estimation of the local property market. So for now this expense is expected to stay reasonably constant for the foreseeable future.

Save hard score: Pass. We are doing well, with possible scope to improve next year.

Invest Wisely

The way my finance spreadsheet is currently set up does not allow me to easily calculate investment returns through e.g. unitization or the internal rate of return. This is because I track the balances of all accounts at the end of every month, but not new deposits. This means that the true returns and new money is mingled together. I hope to improve the system to track this in 2020.

There has been a slow shift towards equities throughout the year to end with an asset allocation:

  • 51% equities (with UK bias)
  • 18% fixed income
  • 28% cash equivalent
  • 3% other (e.g. RateSetter)

I have not yet determined the ideal allocation for our circumstances, so there has been no formal rebalancing process. This is another next action to take at some point, but to be honest I have been putting this off: my reason tells me we should be aiming for somewhere up to 80% equities, but my intuition after looking at recent market valuations makes me very wary of going that far. I accept that we cannot hope to time the market, but it is more difficult to back up that belief with cold, hard cash (or rather, shares).

Our investments are tax-optimized in the following way:

  • 74% ISA wrappers (we actually hold nearly every possible ISA sub-type)
  • 20% pension wrappers
  • 7% accounts subject to tax

We maxed out our ISA allowances this year and so had to add money to taxable accounts. It is not clear whether it would be better to increase pension contributions to avoid this situation — one reason against this is the minimum age restriction of 55 years on withdrawals (we are nowhere close to this and may need access to the capital/returns sooner).

Tax efficiency score: Pass. A high 94% of our net worth is in tax-free wrappers.

We do not yet have high enough amounts with investment platforms to warrant switching to ETFs/a flat-fee structure, but we have still ended the year on a respectable TER (total expense ratio) of 0.28%. This is perfectly acceptable to me for the moment.

Minimize expenses score: Pass

Retire Early

I have no intention of actually retiring early (the RI part of FIRE) in the traditional sense. Rather, I’m in this for financial independence (the FI part). This means no longer needing to labour for others in order to fund a lifestyle of consumerism, but instead being free to pursue things like my own business, meaningful work and other projects.

But these both start on the same day, when we wake up one morning and our investment accounts have breached some limit that we have calculated is enough to sustain us.

Unfortunately, I have not spent a lot of time calculating this with any precision. If I look at the popular table here and plug in a very approximate “net savings rate” of 65% (I do not precisely calculate the rate based on net income, as the tool requires), then we end up with a timeline of 10.5 years. Of course, that table is based on a safe withdrawal rate (SWR) of 4%, which I am convinced is dangerously high (I would feel much more comfortable with something around 3.2%).

One blocker to tracking this is that I think it’s important to incorporate projected future changes into the number instead of simply extrapolating from a savings rate (although that method is powerfully motivating). An example of a possible future change is how long Mrs Fireman continues to work for — something like long-term maternity leave could really affect the forecast.

I hope that, given the above approximate figure of 10.5 years, when I get around to actually working this out and tracking it within our spreadsheet it comes out to something under 12. Time will tell.

Retire early score: Fail. This is the good bit and I am not tracking it properly.

That’s it for the status update — I intend to settle into a routine of doing this every quarter or half-year. I may also post other insights or developments if I think they’re interesting! Feedback is much appreciated.


So, what happened with your finances in 2019?