How to calculate CAGR in your portfolio with multiple years of cash contributions to your ISA

24th Mar '21 by wildshot

22 comments 2244 reads

Hi everyone, hope you are all safe and well?

I've a main stocks and shares ISA which I've been running since 2006 and I have detailed transaction and contribution records on spreadsheet from the start. I have the date and value of every cash deposit I've made into my ISA. Unfortunately/regrettably I never unitised my portfolio until this year. Hopefully I will keep the discipline to unitise going forward which should make things easier.

I was however trying to figure out how to calculate the performance of this ISA to calculate a CAGR (cumulative average growth rate) so I could understand my performance. From research online I've seen the XIRR calculation feature being suggested. I tried this in my Libre Office spreadsheet however this came out at a rather baffling clearly wrong % of just over 2%. I know this is wrong because had I invested all my capital on day 1 back in 2006 the CAGR would be 6.7% (per a quick calc here https://www.vertex42.com/Calculators/cagr-calculator.html). Of course my capital was deployed over many years and lumpy in contributions. I also know that between 2016 and 2019 the value of my portfolio doubled and only a small portion of this value change was due to contributions. Further in 2020 (without any contributions in the calendar year) my performance was just over 8%.

I'd appreciate if anyone can share their thoughts/experience on how to calculate CAGR with just the dates and amounts of contributions over 14 years and the value of the portfolio as of today or any specific month end?

Unlock with Email

Unlock with Google

Already have an account?
Login here

Alphaflight

24th Mar '21

This CARG calculator shows "the workings". That is to say, it shows the mathematics in action:

http://www.mrzeno.com/CAGR-Cal...

PLS1nve5t

here's one I prepared earlier....

https://docs.google.com/spread...

wildshot

Thanks for sharing.

I've compared how you've done it to how I did mine and it appears to be consistent. I'll keep thinking about it because my result just doesn't make sense. Maybe I'll try it in a different spreadsheet application like Google or numbers.

Thankfully with unitisation it'll be less of an issue for me in future.

Thanks for sharing and your input.

(Update - I've just reperformed the calculation in Numbers spreadsheet on my Mac and it has come out a much more respectable result >10% pa. I wonder if the problem was in my use of Libre Office?)

That sounds better - I use Excel and not had any problems - or at least none that I've spotted :-)

gus 1065

A few thoughts. It’s a useful exercise and lots of ways to skin the cat. Here’s how I’ve done it with my portfolio. Given the likely frequency of flows in and out of the portfolio over time, I think the Internal Rate of Return (IRR) calculation will give you a reasonably accurate measure of your performance over time. Depending on the level of accuracy you want and the extent of your records you may need to do a bit of work with the raw data to get a sensible outcome. I would suggest setting up a simple excel (or similar, anyone else still using Lotus 1,2,3?) spreadsheet.

In column 1/2 put in ascending order from your first data point the date; either annual, semi, monthly (probably not worth going any more granular than this) up to the present date. I’d recommend quarterly or possibly monthly depending on the frequency of your transactions. In the example below, I’ve done it on an annual basis to save space. In column 3 put in your ISA contributions against the relevant period as a negative number. In column 4 put in any dividends as and when received (an increase in portfolio value so positive). In column 5 put in the net P&L from any sales or other disposals. Gains are a positive. Losses are a negative. Carry on inputting the dividend/disposals data until the present period. Finally, as the last entry in column 6 put in the current value of the portfolio including any cash held as a positive.

Add all of columns 3 through 6 (not the period/date column) into a single column 7. At the top of the sum column type in the formula something like =IRR(<Range of the data in Column 6>, 0.1), where 0.1 is a plug figure of the estimated IRR expressed as a decimal, in this case 0.1 or 10%. (The exact formula will depend on what spreadsheet software your using). This should give you a result expressed as a decimal, e.g. 0.125, which expressed as a percentage would be 12.5%). This is your rate of return from your investment over its life.

Following spreadsheet is an example assuming regular ISA contributions, annual dividend income all retained in the ISA and some random P&Ls from disposals to and from the portfolio. Note, you only record “external” cash movements, not individual stock purchases/sales other than to record gains and losses which all get rolled up into the final portfolio value figure. In this example, the IRR is 12.6%, i.e. an annual return of 12.6% compound.

As a final check, in column 8 I have calculated the net present value of each of the cash flows in column 7 using the formula NPV = Cashflow/((1+IRR%)^n period), where IRR is the 12.6% expressed as a percentage and n period is the number of the period taken from the first column. If these NPVs are all added together they should be equal to 0 (which they are) since this is what the IRR represents (if you get my drift).

Hope this helps.

Gus.

* Thinking a bit more about the example above, I think I’m probably double counting the benefit from the dividends and the sale P&Ls. Unless these are taken out of the ISA when received they shouldn’t be recorded in the IRR . I suspect you may well reinvest/retain all of the income in the pooled ISA (I do) in which case you simply need to plot the ISA contributions in each year they are recorded and the end period portfolio and cash marked to market valuation and take the IRR from these aggregated flows to get your annualised return.

abtan

I don't understand how any of the examples suggested work unless you know the exact portfolio value on the day of cash addition or withdrawal.

Adding cash on Day 1 of the year vs Day 364 will make a material difference to any CAGR calculation if you're just using Day 1 and Day 365 as your portfolio valuation reference points.

Personally, I'm hoping stockopedia simply include Returns/CAGRs within their Portfolios.
It would certainly save me the time of working it out for myself.

Fingers crossed

The XIRR function in Excel (and other spreadsheets), takes into account the date when deposits or withdrawals are made - it sort of scales them against the given time frame.

e.g. you make a 10% of portfolio sized deposit half way through the year, then that will account for very very approx 5% of the total gain / loss for the year.

You can get more accuracy by shortening the time frame (quarterly, monthly, weekly or even daily) but who cares if you made 0.00001% profit yesterday ? - the yearly time frame is good enough and the one most often used in willy waving competitions :-)

https://corporatefinanceinstit...

Effortless Cool

Hi Gus,

I was just reading your example and thinking you were double-counting and then found you had spotted it yourself. The XIRR calculation really is as simple as recording all cashflows into the fund (cash plus investments) as dated negatives, all cashflows out as dated positives and the current value of the fund as a terminal positive.

Hi abtan,

I record the exact date of each cash flow and that is accurately reflected in the XIRR calculation. The valuation of the portfolio on the date of the cashflow has no effect on the IRR, the only portfolio valuation that matters is the current one.

"The valuation of the portfolio on the date of the cashflow has no effect on the IRR"

I'm afraid that I don't really understand this.

If you monitor a portfolio over say 4 years, I imagine it would make a huge difference to the IRR depending on whether you made one big cash deposit on day 10 or day 1000 of that 4 year period.

Or maybe I'm just misunderstanding what is trying to be achieved

Before 2020 I would actually have agreed with you.
And then April 2020 happened.

I made a cash deposit on April 1st and another one on April 10th.
For ease in my spreadsheets I noted that I had deposited it all on April 1st.

But I made so much profit between those 10 days, that I literally knocked a few % off my April and Year End returns by not calculating things correctly.

I didn't even think such a discrepancy was possible, until I went back and (roughly) worked it out again.

So yes, in most cases just picking dates close to each other will suffice, but when the markets go crazy, it can make a real difference to working out your true returns.

We do seem to be at cross purposes.

I say: "The valuation of the portfolio on the date of the cashflow has no effect on the IRR".

You say: "If you monitor a portfolio over say 4 years, I imagine it would make a huge difference to the IRR depending on whether you made one big cash deposit on day 10 or day 1000 of that 4 year period".

Both statements are correct. As I have said, it is the date of the cash flow (and the amount, obviously) that needs to be used in the calculation.

Are you taking the year end as the tax year end ? If so then pulling your 10th April deposit back into the 1st of April i.e. the previous year will have that effect.

For your monthly April figure the backdating of your 10th April deposit will "dilute" the gains.

Here's a back of a fag packet simulation it shows backdating your 10th April payment to the 1st costs a 10% reduction in return for the month....

Assumes starting from 100k, and adding 10k at either the 1st of April or 10th April and in both cases ending the month on 120K. I hope you can see that XIRR takes into account the date of deposit or withdrawal and the return it generates over that period.

25th Mar '21

Thanks for continuing the discussion.

I guess what I don't understand is what happens if there are wild swings in-between cashflow movements.

For example, let's say the I lose £90k in January, but then top up my account by £100k on Feb 1st:

The XIRR returns 0.52%, but my in-year returns are completely different.

Anyway, it's interesting see that formula so thanks for sharing.

For reference I use the time-weighted return. I don't really like it, but my understanding is that this is what the industry uses, so I need it as a point of comparison.

Cheers

In that example it looks like you lost 90k in the first month, then made 91k in the remainder of the year, so roughly about break even ? half a percent sounds about right.....

Sean Reilly

29th Mar '21

Hi mate, what do you mean when you refer to "Thankfully with unitisation it'll be less of an issue for me in future." ? Thanks

TheGob

30th Mar '21

This is is a straight forward calculation and best done avoiding functions like XIRR etc.

Set out the date and amount deposited. Then add a column to calculate the number of days from deposit until now. In Excel this is =TODAY()-[cell ref with deposit date]. You will now have how long each deposit is invested.

In the next column we are going to work out what each deposit is now worth. At the top of the next column, enter a per cent age interest rate, it can be any number at this stage. Against each deposit you now want the formula in this column to read =[cell ref of deposit] *POWER(1 + [cell ref of interest rate with $ before letter and number],[cell ref with number of days]).

Now sum this column. Below it enter your current valuation. Calculate the difference between the valuation and the total of your interest adjusted deposits.

Now goal seek setting this difference to zero with your cell adjusting being the interest rate. This will now be your daily rate of return to convert to an annualised just use

=POWER(1 + i, 365)

I'll try and post an example spreadsheet later.

I think you've just described XIRR the long handed hard way ;-)

Hi Sean, by unitisation I will (like unit trusts do) have a typical unit value. Then say if the unit value at the start of the year is £1, then at the end of the year is £1.05 then I know that I've made 5% return in the year. Unitisation helps you adjust for cash inflows and outflows as your unit value reflects this.

There is a detailed explanation on it here Lemon Fool discussion of unitisation

Unitisation is surely only useful if other parties also have cash inflows/outflows to/from the same pot? If you are just calculating your own returns on your own funds, then it would just seem to add a needless layer of complication.

How to calculate CAGR in your portfolio with multiple years of cash contributions to your ISA

Unlock the rest of this article with a 14 day trial

About the Author

Wildshot

22 comments

Unlock the metrics for all these stocks and more...

Unlock the metrics for all these stocks and more...