0

Let's say I invest x amount at the beginning of each year. After N years, my total investments are worth y.

How do I calculate the CAGR?

As a bonus, assume that my only tool is Google Sheets.

Improve this question

1 Answer 1

Reset to default
1

CAGR is not very significant when you have inflows or outflows, since those aren't really "growth", but the formula to do so would be:

(X*N/Y)^(1/N) - 1

If you want to get a better picture of how the actual investments did, there are two common ways to do that: Time-Weighted Return (TWR) and Money-Weighted Return (MWR). MWR is essentially IRR: at what constant interest rate could I invest/borrow the cashflows and end up with the same ending value (y). It's commonly used to see how well an investor makes specific decisions on what to invest in and when, since investing money before high growth period and removing it before low growth periods can skew the results.

Since you always invest at the beginning of each period and aren't "timing" your investments, TWR is easier (and maybe more informative). With TWR you calculate the growth of each period between cash flows, multiply them all together, and take the Nth root. Subtracting 1 from that gives you the average growth of each investment period, disregarding cash flows).

In google sheets your formula for each year would be something like

A = Beginning Balance of year N
B = Inflow (`x`) at beginning of year N
C = Ending balance of year N including inflow
 
     (Cn/(An+Bn))
Improve this answer
6
  • IIR function ended up being what I was looking for. For two simple simulated strategies, the IIR appears to be the same when they end with the same total profit in the end. Apparently TWR doesn't have this property.
    – relatively_random
    Aug 9, 2022 at 11:46
  • Just a comment specific to Google Sheets. To use IRR this way, represent cash inflows as a sequence of negative numbers, one for each year. Then append the final value of the investment as a positive number. To append, use array syntax, with semicolons to join columns and commas to join rows, e.g. =IRR({A$1:A15;B15}) or =IRR({A$1:D1,D2}).
    – relatively_random
    Aug 9, 2022 at 11:57
  • 1
    Typically it's done the other way, with inflows as positive numbers and the final value (akin to "cashing out") a negative "outflow". But, you end up with the same IRR either way.
    – D Stanley
    Aug 9, 2022 at 13:41
  • For the IRR vs TWR - it depends on the timing of the cashflows. If you have more in your investment account during larger growth periods, TWR will account for that, but it may skew IRR somewhat. Meaning it makes you look like a better investor just because you had more invested in "good times".
    – D Stanley
    Aug 9, 2022 at 13:43
  • 1
    That's correct - TWR is a measure of how well your investment choices performed. IRR takes the timing into account as well. It's not clear to me what you're comparing IRR to to know if it's appropriate or not, but it is a reflection of your investment performance overall.
    – D Stanley
    Aug 9, 2022 at 16:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .