Spreadsheet In-Depth: The Monthly Overview

One of the more important sheets in our Excel workbook is the Monthly Overview. This is used to take details of income, cash flow and savings from several other sheets in order to produce a few crucial figures each month, to help us track where we are financially.

Monthly is also the right frequency to motivate us about where we might end up in the future, and to hold us accountable to the longer-term. We are able to think through how everyday decisions/events over the weeks have led to the end-of-month result. With yearly figures, this immediacy just isn’t there (any more than monthly is too much hassle).

Figures changed for privacy

Historic statistics

The first row tracks our investment portfolio total, mirrored from the savings sheet.

The next few rows are dedicated to how much we actually save in each month. This is a computed as a combination of direct savings from our current account and pension contributions. Derived from this (and our income sheet) are the two savings rates: gross savings and net savings. The gross figure looks at savings as a proportion of total pre-tax compensation, whereas the net figure takes after-tax income.

I think the gross one is more honest in the sense that it cannot be so easily “gamed” via sacrificing take-home income to pension contributions. However, blindly maximising this rate also has its problems, since we do not want to contribute disproportionate amounts to pensions at our age.

We also track the absolute amount of cash-equivalent savings, to see our “emergency savings”, most of which can be withdrawn at short notice. Lastly, the asset allocation is calculated from the savings sheet and is presented in a single cell for brevity.

Future-looking statistics

The final bit is a simple percentage tracking how far we are from financial independence. This is a straightforward calculation from the monthly portfolio totals, and there is actually a fair bit more nuance to this (pension vs. non-pension pots, non-constant expenditure in retirement, etc.) But it serves its purpose well as a rough yardstick and motivator.


That’s it! We used to have many more rows here tracking all sorts of things (one of these was the square-feet equivalent of monthly savings in our local property market!) But in the end I believe it’s better to keep things simple.

Do you have a monthly overview sheet? What do you track?

The Inflation-Proof Asset

We live in an era of historically low inflation rates. The chart below shows the periods of eye-watering inflation that have occurred within living memory — during these times the price of goods shot upwards and bank balances everywhere cowered in real terms. Shillings may have disappeared in 1971, but it was in the next decade that the purchasing power of cash was truly decimated. Was this a feature of a past politics and economy? Or will we see a 21st century comeback of these conditions?

Source: Office for National Statistics

One defence against inflation is Index-Linked Gilts, which yield returns linked to RPI (shown above). Alternatively, we could try to identify equity sectors that held up well in past inflationary environments. Or we could look to more exotic (read: dangerous) investments.

But I can give you one “investment” that will never lose against an inflation metric: the basket of goods used to calculate it. We simply buy the stuff, stick it in the larder and add a new asset class to our spreadsheet! We won’t tell Marie Kondo. Of course, it won’t actually be practical to store certain perishables, and we’ll likely find that a Government employee has fiddled with the basket composition by quarter-end, but I think the idea is worth entertaining for a second.

Photo by Alex Block on Unsplash

Assuming that we could buy and store goods today to be consumed over the next few decades, there is one large issue still outstanding: the money used to buy a can of beans in 2020 could instead have been invested in productive assets and would have likely waxed over time until it could buy several of the cans decades later. Because over history the beast of inflation has time and time again been slain by the power of productive capitalism. And this is what makes this strategy unappealing to implement to any great extent.

But there are a couple of scenarios where I think one can make a case for this sort of thinking:

  1. Jane has no idea what will happen to property prices over her retirement. She is currently a private renter, but has enough capital to become a cash buyer if it makes sense. She is ambivalent to the possible lifestyle advantages of being a homeowner, and the idea of house-as-investment does not seem fully convincing. But what Jane really values is peace of mind, and by purchasing a house now, she removes the future risk of price changes in the property/rental market. When she goes to sleep in her new home, she knows that every day she wakes, she will still own it, regardless of any economic turmoil. It’s hers.
  2. Joe is always replacing low-quality tools that wear out or break. Plastic casings and under-specified fastenings buckle under his expectations of how they should perform for him. This attrition is largely mitigated by fact that these tools are all great bargains when bought. But one day, Joe decides to stem this steady cashflow by splashing out on a set of very high-quality, durable tools that are built for a lifetime. He doesn’t know which route would have worked out better financially in the long-term. But now he owns a collection of physical tools instead of numbers in a bank account that promise a possible future conversion to real usable things.

What do you think? Have you tempered your asset allocation with an old-fashioned dose of physical stuff?

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?