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?

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?