Math question: how to combine two return series?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
siamond
Posts: 4313
Joined: Mon May 28, 2012 5:50 am

Math question: how to combine two return series?

Post by siamond » Sat Aug 09, 2014 7:39 pm

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?

longinvest
Posts: 3109
Joined: Sat Aug 11, 2012 8:44 am

Re: Math question: how to combine two return series?

Post by longinvest » Sat Aug 09, 2014 10:37 pm

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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

lee1026
Posts: 393
Joined: Sat Jun 21, 2014 7:47 pm

Re: Math question: how to combine two return series?

Post by lee1026 » Sat Aug 09, 2014 10:44 pm

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.

User avatar
siamond
Posts: 4313
Joined: Mon May 28, 2012 5:50 am

Re: Math question: how to combine two return series?

Post by siamond » Sun Aug 10, 2014 9:29 am

longinvest wrote:Then, combine the StdDevs (I don't remember if it's the weighted average, again, or something else).
Well, this is indeed the key question! Where co-variance should come into play - as lee1026 pointed out. But how?

blueleaf
Posts: 129
Joined: Fri Dec 27, 2013 8:30 pm

Re: Math question: how to combine two return series?

Post by blueleaf » Sun Aug 10, 2014 11:38 am

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?

Tamales
Posts: 1311
Joined: Sat Jul 05, 2014 10:47 am

Re: Math question: how to combine two return series?

Post by Tamales » Sun Aug 10, 2014 11:38 am

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.

Tanelorn
Posts: 1552
Joined: Thu May 01, 2014 9:35 pm

Re: Math question: how to combine two return series?

Post by Tanelorn » Sun Aug 10, 2014 12:34 pm

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?
This seems like the easiest way.

User avatar
siamond
Posts: 4313
Joined: Mon May 28, 2012 5:50 am

Re: Math question: how to combine two return series?

Post by siamond » Sun Aug 10, 2014 4:25 pm

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.
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).

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.

User avatar
magician
Posts: 1568
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA
Contact:

Re: Math question: how to combine two return series?

Post by magician » Sun Aug 10, 2014 4:51 pm

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?
Yes.

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.

User avatar
siamond
Posts: 4313
Joined: Mon May 28, 2012 5:50 am

Re: Math question: how to combine two return series?

Post by siamond » Sun Aug 10, 2014 6:45 pm

magician wrote:
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?
Yes.

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!
You guys are way too practical... Yes, of course, and this is exactly how I'm testing the math above. :wink:

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! :mrgreen:

(if somebody has a more straightforward way to figure out combined geomeans than going through arithmetic means and std deviations, please do speak up).

longinvest
Posts: 3109
Joined: Sat Aug 11, 2012 8:44 am

Re: Math question: how to combine two return series?

Post by longinvest » Sun Aug 10, 2014 6:53 pm

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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

Tamales
Posts: 1311
Joined: Sat Jul 05, 2014 10:47 am

Re: Math question: how to combine two return series?

Post by Tamales » Sun Aug 10, 2014 8:32 pm

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).
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:

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

User avatar
siamond
Posts: 4313
Joined: Mon May 28, 2012 5:50 am

Re: Math question: how to combine two return series?

Post by siamond » Mon Aug 11, 2014 7:11 pm

Tamales wrote:
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).
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.
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.

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...

Tamales
Posts: 1311
Joined: Sat Jul 05, 2014 10:47 am

Re: Math question: how to combine two return series?

Post by Tamales » Mon Aug 11, 2014 11:29 pm

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).
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:
http://www.youtube.com/watch?v=bsFZ9umZ ... Kl8W18Jisw
My apologies if I'm still missing what you're after.

Post Reply