2

Let us say an investor invests $X per year for N years. Let us say the final portfolio value is $Y. In this scenario, how should I measure a metric that quantifies "average annualized return"?

I think CAGR is defined as CAGR = [($Y/$X)^(1/N) -1]*100. However, this does not account for the fact that the investor actually invested N * $X in total.

Does it make sense to modify CAGR as CAGR' = [($Y/(N*$X))^(1/N) -1]*100? This accounts for the entire invested capital. I find it to be intuitive in that it essentially says that "Hey if I invest (N*$X) over N years in an investment, I might receive a return that is as if all of my invested money compounded at the rate of CAGR' for N years." What other metrics could I use?

  • What, exactly is your goal/motive here? – JoeTaxpayer May 9 at 0:10
  • I am trying to figure out what is an appropriate and intuitive metric to measure my "average annualized return" or something that conveys the same. Considering the fact that the investor is contributing $X per year for N years, and has $Y in his portfolio at the end of N years, the plain CAGR doesn't give the complete picture. So what is appropriate to use in such a scenario? I don't think geomean or arithmetic mean of annual returns is the answer either. – akhil28288 May 9 at 2:23
1

NOTE: I am not a financial advisor but the question intrigued me and I have wanted to try to find something similar too. Your modification would imply that you took all the eventual $X and invested in the beginning. So the CAGR is bound to mislead with that. I was looking into different mathematical series to be able to express this but that can be left to a separate exercise.

I derived an approximation method and I have it attached and described as follows. Short answer approximation for your effective CAGR (new metric)- NewMetric_CAGR = [2 * ((Y/X) - N) / (N^2 + N)]

I couldn't write equations here but I am attaching my notebook method of approximating an effective CAGR when you have yearly investment of equal amounts for N years.

How yearly amounts would add up to final amount at N years Note equation (1) below. Substituting usual CAGR factors into new calculation to find how to derive new CAGR based on regular method

Alternatives: Another metric CAGR_eff_X = [(2(R+1)^N - 2N)/ (N^2 + N)] where R is the CAGR calculated with just $X as the investment for N years. If you don't want to base it against a CAGR that is already an approximation and that too based on an investment in lumpsum then simply from the derivation below This is because the $X that you invested was multiplied by the rate^N effectively. But the subsequent ones have had lesser indices. Now if it were regular investment made at the beginning and one-time (but not calculating as N times $X). Remember, you could calculate R by the usual way accounting for only $X (not N times $X). With modification it would be easy to treat and contrast it against one-time N times $X amount invested too. So again, CAGR_eff_X = [(2(R+1)^N - 2N)/ (N^2 + N)] Now if you want to contrast it with how it would be with N times $X invested in the 1st year in lumpsum the CAGR_eff_NX = [(2(R+1)^N - 2)/ (N + 1)] (I could put the completed equations separately if needed.)

I'd still not base it against a could-have-been CAGR and so I'll simply derive the rate from the eq (1) in my handwritten calculation as NewMetric_CAGR = [2 * ((Y/X) - N) / (N^2 + N)]

Note also: The mathematical approximations that higher powers are negligible (in my working sheet) works for fairly low numbers like under 20% CAGR as calculated as a lumpsum investment. For higher returns and shorter duration/years the approximations deviate. For longer duration and even with higher CAGR the approximations would work.

Sample calculations pasted below (Can't paste spreadsheet but easy to replicate sheet): Sample calculations showing conventional CAGR versus discussed metrics with the NewMetric_CAGR as the last column Part 2 of sample calculations

Explanation for negative rates: It may seem odd to see negative rates for NewMetric and even other CAGR_Eff calculations but it seems right. For ex, 2nd row where x=100,y=100,n=5, you are actually putting 100 every year and so in the absence of growth you'd at least have y=500 but it is 110 and so the loss. For the same row, though it is actually a profit (of merely 10) if you had invested only 100 at the beginning of 5 years. So CAGR continues to be positive so long as y>x but our NewMetric will be negative if y<nx.

For much bigger values of y, say y=5000 or 20000, the numbers will be positive throughout but NewMetric_CAGR will be less than CAGR and that makes sense (because you are investing NX (NewMetric) versus X (CAGR) so getting the same Y has to mean lower rate per year.

  • hmm, I think I understand what you are trying to do here. I think, my question was basically about solving for what you denote as 'R' in your equation 1. That itself seems like one measure of "average annualized rate of return". – akhil28288 May 9 at 2:41
  • Yeah I would simply use the NewMetric_CAGR then. I will attach a sheet where I plugged in sample values to see how the numbers look. – perennial_noob May 9 at 2:44
  • Updated with calculations to give more illustration. Note how NewMetric_CAGR is at best the same as CAGR but it is right. – perennial_noob May 9 at 3:07
0

If you invested X monthly with a constant monthly yield of R > 1 then after N months you would have around B = X * (R^N - 1) / (R - 1). Here, you know B, X and N and need to solve for R. It doesn't look like this has an easy closed form solution but we can approximate it efficiently. For instance:

X = $400
B = $60,000
N = 120

A monthly yield of R = 1.0 would give B = $48,000, so we know we have a higher monthly return. R = 1.01 gives ~$92,000, so the monthly yield is less than that. Splitting the difference at R = 1.005 gives ~$65,000, much closer. R = 1.0039 gets quite close to the right answer. The corresponding APY is 4.78%.

The number this method produces is the comparable constant rate a savings account would have had to provide over the investment period to give you the same return.

I glossed over some details here but the full derivation using the partial sum formula for the geometric series is not too difficult... and you'd get a more precise answer (this might be off by a month in some direction or other).

  • Note as R increases the fraction increases and tends toward R^(N-1). Therefore, R < (B/X)^(1/(N-1)) should be an upper bound. Indeed, we find this gives R < ~0.0043, which is already a fairly close figure. As R decreases the fraction decreases toward NR^(N-1), giving a lower bound of R > (B/NX)^(1/(N-1)). For our case, this tells us R > ~0.0019. So we know analytically before approximation that the APY must be between ~2.33% and ~5.27%. – Patrick87 May 9 at 4:59
0

There isn't a single, perfect answer. One common way to arrive at an "equivalent CAGR" is to use Internal Rate of Return. You can calculate this in Excel using the IRR function. Generally however, you are adding arbitrary amounts of monies at arbitrary times and this complicates the formula. Solving the general case is handled by the XIRR function where you can supply specific dates you have added or removed funds.

If you are looking to determine your investing performance versus a benchmark such as the S&P 500, you can track a shadow portfolio where the same amount of funds had purchased a SP500 ETF. Be aware that the accuracy of calculations will depend on whether you are tracking the dividends as well. The S&P 500 Index does include dividends so you can use it as a faux share price.

If you are trying to compare your performance to mutual funds, you would also want to try to approximate your Average Annual Return, since that is what you'd find in a fund prospectus.

0

If you invest $X each year for N years, and after each year your money is multiplied by r, then that's a geometric series: the ith investment will be worth S = $X*r^(N-i). If you're given S, X, and N, then you can calculate r.

If you're putting in a variable amount each year, you can create an spreadsheet table where Amount_year = Amount_(year-1)*(1+rate of return)+Investment_year. Then do a goal seek to make the final amount equal to your observed amount. One thing to keep in mind about goal seek, however, is that it only finds one answer, even if there's more than one solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.