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?