Math question: how to combine two return series?
Math question: how to combine two return series?
Let's say you have two return series (to take an example, the 1926-2013 returns of the S&P500, and the 1926-2013 returns of 10yrs Treasury Bonds).
Say you have a given asset allocation (to take an example, 75% stocks, 25% bonds).
Is there a formula of sorts to figure out the combined returns (assuming constant AA, rebalancing every year in other words), just based on some aggregate characteristics of the two series (e.g. geometrical mean, correlation factor, etc)?
Just adding the two (AA-weighted) geo-means of the two series isn't quite right. In my example, this 'misses' ~ 0.3% of the combined returns... Which I know is due to the low correlation of the two series, but how to translate this observation in an easy to use formula in Excel or equivalent?
Say you have a given asset allocation (to take an example, 75% stocks, 25% bonds).
Is there a formula of sorts to figure out the combined returns (assuming constant AA, rebalancing every year in other words), just based on some aggregate characteristics of the two series (e.g. geometrical mean, correlation factor, etc)?
Just adding the two (AA-weighted) geo-means of the two series isn't quite right. In my example, this 'misses' ~ 0.3% of the combined returns... Which I know is due to the low correlation of the two series, but how to translate this observation in an easy to use formula in Excel or equivalent?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: Math question: how to combine two return series?
Go back to the source numbers. In other words, use the weighted average of the arithmetic means. Then, combine the StdDevs (I don't remember if it's the weighted average, again, or something else).
Finally:
GeoMean = ArithMean - (StdDev^2)/2
That last formula is a good approximation.
Finally:
GeoMean = ArithMean - (StdDev^2)/2
That last formula is a good approximation.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Re: Math question: how to combine two return series?
If you want to do that, you also want the co-variance of the two returns in question. That will change your math a lot.
Re: Math question: how to combine two return series?
Well, this is indeed the key question! Where co-variance should come into play - as lee1026 pointed out. But how?longinvest wrote:Then, combine the StdDevs (I don't remember if it's the weighted average, again, or something else).
Re: Math question: how to combine two return series?
If you get a combined return series using weighted average return for each year, can't you then just take the stdev of the combined (portfolio) return series?
Re: Math question: how to combine two return series?
I'm not entirely sure I get what form your starting data is in, or what you're trying to find. Your initial post talks only about returns but later it seems you want covariance. In any case, if you are looking for the steps in calculating a covariance matrix among other things, this series of 2 videos might have what you're looking for:
Risk-Return: Spreadsheet Modeling Part 1
By Pat Obi
http://www.youtube.com/watch?v=ELjsYnb-t7E
Risk-Return: Spreadsheet Modeling Part 2
By Pat Obi
http://www.youtube.com/watch?v=yIKnGQYEWrw
You might start with the Part 2 first, watch the first few minutes and see if this is what your looking for. If so, watch video 1 then 2 and it goes through all the steps for a weighted 3 stock portfolio, which you could adapt to your case.
If your beginning data is in some form other than monthly cumulative price, you may need to apply scale factors along the way that match the time interval of the data. As far as annual rebalancing, that can get messy and there are multiple ways to do that. The videos don't cover that, so you'd have to go back and work that in after you get the non-rebalanced version working. Hope this helps.
Risk-Return: Spreadsheet Modeling Part 1
By Pat Obi
http://www.youtube.com/watch?v=ELjsYnb-t7E
Risk-Return: Spreadsheet Modeling Part 2
By Pat Obi
http://www.youtube.com/watch?v=yIKnGQYEWrw
You might start with the Part 2 first, watch the first few minutes and see if this is what your looking for. If so, watch video 1 then 2 and it goes through all the steps for a weighted 3 stock portfolio, which you could adapt to your case.
If your beginning data is in some form other than monthly cumulative price, you may need to apply scale factors along the way that match the time interval of the data. As far as annual rebalancing, that can get messy and there are multiple ways to do that. The videos don't cover that, so you'd have to go back and work that in after you get the non-rebalanced version working. Hope this helps.
Re: Math question: how to combine two return series?
This seems like the easiest way.blueleaf wrote:If you get a combined return series using weighted average return for each year, can't you then just take the stdev of the combined (portfolio) return series?
Re: Math question: how to combine two return series?
Thanks, Tamales. Around 3:00 of this video (Part 2), there is indeed an equation that shows how to calculate the std deviation of two weighted series based on aggregate characteristics of the series (the individual series std deviations and the covariance, as I suspected).Tamales wrote:You might start with the Part 2 first, watch the first few minutes and see if this is what your looking for. If so, watch video 1 then 2 and it goes through all the steps for a weighted 3 stock portfolio, which you could adapt to your case.
I actually had found the same answer by other means, Wikipedia, of course, as the entry on std deviation does discuss how to make such a combo of two series, and provides a similar formula: http://en.wikipedia.org/wiki/Standard_d ... deviations
To summarize in Excel lingo, the combined std-deviation is:
SQRT ( POWER(W1*SD1, 2) + POWER(W2*SD2, 2) + 2*W1*W2*COVARIANCE.S(Series1, Series2) )
- Wi being the AA weights (e.g. 75% series1, 25% series2)
- SDi being the std deviation of the two series
- Beware, don't use COVAR, do use COVARIANCE.S, this isn't exactly the same thing
And I checked, and it works. Cool.
Re: Math question: how to combine two return series?
Yes.blueleaf wrote:If you get a combined return series using weighted average return for each year, can't you then just take the stdev of the combined (portfolio) return series?
The portfolio return each period will just be 0.75(stock return i) + 0.25(bond return i). You now have a return series for the portfolio, so you can calculate means return, standard deviation of returns, skewness, kurtosis, and so on. Go wild!
Simplify the complicated side; don't complify the simplicated side.
Re: Math question: how to combine two return series?
You guys are way too practical... Yes, of course, and this is exactly how I'm testing the math above.magician wrote:Yes.blueleaf wrote:If you get a combined return series using weighted average return for each year, can't you then just take the stdev of the combined (portfolio) return series?
The portfolio return each period will just be 0.75(stock return i) + 0.25(bond return i). You now have a return series for the portfolio, so you can calculate means return, standard deviation of returns, skewness, kurtosis, and so on. Go wild!
Ok, so we have a proper way to compute combined std deviations now (see my previous post). Which provides a decent way to compute geometrical means by using the approximation "GeoMean ~= ArithMean - (StdDev^2)/2". But well, this last formula isn't rigorously exact.
Pondering about this last formula, I stumbled upon an interesting paper about such approximate formula, and suggesting more complicated formulas to go from geometrical mean to arithmetic mean, and the reverse way around (I'd suggest you jump straight to the conclusion!): http://www.cdiadvisors.com/papers/CDIAr ... 110814.pdf.
I checked with my stock/bonds return series, and yes, A4 does seem a tad better, although not always, and well, in truth, the delta is really small... And that's probably enough math for tonight!
(if somebody has a more straightforward way to figure out combined geomeans than going through arithmetic means and std deviations, please do speak up).
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: Math question: how to combine two return series?
Me, I just the the weighted average of the geo means, knowing that the result underestimates, somewhat, the the correct geo mean.
As I use the result as a crude estimate, precision is not important to me. It all depends on what you intend to use it for.
As I use the result as a crude estimate, precision is not important to me. It all depends on what you intend to use it for.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Re: Math question: how to combine two return series?
Sure, excel has a built-in GEOMEAN() function (to do it in one cell you enter it as an array formula with ctrl+shft+enter. The video below goes thru the different methods). Or you can use PRODUCT() and SUMPRODUCT() to do it manually. Here are a couple videos:siamond wrote: (if somebody has a more straightforward way to figure out combined geomeans than going through arithmetic means and std deviations, please do speak up).
Excel Finance Class 96: Comparing Geometric Mean with Arithmetic Mean For Average Stock Returns
http://www.youtube.com/watch?v=q9xnth9cKi4
Excel Finance Class 97: Using Geometric Mean & Arithmetic Mean to Estimate Future Returns
http://www.youtube.com/watch?v=NYiu_aA-dck
and the spreadsheet for these: http://people.highline.edu/mgirvin/YouT ... 3Ch10.xlsx
and a powerpoint: http://people.highline.edu/mgirvin/YouT ... 3Ch10.pptx
Re: Math question: how to combine two return series?
For sure. I've been using this one for quite a while. But GEOMEAN only works for ONE series of returns. It doesn't allow to compute the return of two weighted series combined. That is, unless you're practical as previously suggested and do the line-by-line combo yourself, and then use GEOMEAN on the resulting combined series, of course.Tamales wrote:Sure, excel has a built-in GEOMEAN() function (to do it in one cell you enter it as an array formula with ctrl+shft+enter. The video below goes thru the different methods). Or you can use PRODUCT() and SUMPRODUCT() to do it manually.siamond wrote: (if somebody has a more straightforward way to figure out combined geomeans than going through arithmetic means and std deviations, please do speak up).
But this wasn't my question, I was trying to figure out the combined GEOMEAN of two series just based on aggregate characteristics of individual series (simply adding the geomeans together can really be quite off the mark; the method we discussed in the past few posts does get much closer, but is a tad contorted and regrettably implies to know the arithmetic averages).
To better explain what I have in mind... Say you have some historical characteristics (e.g. std-deviation of US stocks, std-deviation of US-bonds; covariance between the two), and (leap of faith) you're thinking that such characteristics might stay reasonably true in the future on the long run. Then say you have some expected returns forecasts on those two series (hence geomeans) coming from a well known author you respect (or a forecasting model you believe in). Is there a magic formula that would approximate the combined return of weighted combination of both series? Something than would miss the mark significantly less than simply adding the geomeans? Of course, one might say garbage in, garbage out, who cares, but hey... it's a math question, not a judgment on the inputs...
Re: Math question: how to combine two return series?
I'm not 100% sure about this so take it with a grain of salt, but I think you can take a simple average of the portfolio-weighted values if you use natural log returns instead of simple returns, but only with a 2-component portfolio. Once you go to 3 or more assets in the portfolio the covariance matrix approach is the way to go. I truly hope I'm not wasting your time with this but here's a different video which I think covers what you're after:siamond wrote:I was trying to figure out the combined GEOMEAN of two series just based on aggregate characteristics of individual series (simply adding the geomeans together can really be quite off the mark; the method we discussed in the past few posts does get much closer, but is a tad contorted and regrettably implies to know the arithmetic averages).
http://www.youtube.com/watch?v=bsFZ9umZ ... Kl8W18Jisw
My apologies if I'm still missing what you're after.