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.
Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It only takes a minute to sign up.Sign up to join this community
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))