Spreadsheet for Tracking the Global Market Portfolio

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
aj76er
Posts: 539
Joined: Tue Dec 01, 2015 11:34 pm
Location: Portland, OR

Spreadsheet for Tracking the Global Market Portfolio

Post by aj76er » Sun Aug 13, 2017 5:38 pm

Hi All,

I've been reading William Sharpe's RISMAT series on what he calls the WBS (World Bond-Stock) Portfolio. It consists of the following 4 Vanguard funds held at market proportions:

VBTLX: Vanguard Total Bond Market Index Fund Admiral Shares
VTABX: Vanguard Total International Bond Index Admiral Shares
VTSAX: Vanguard Total Stock Market Index Admiral Shares
VFWAX: Vanguard FTSE All-World ex-U.S. Admiral Shares

In RISMAT-7, he demonstrates the math behind initializing and rebalancing the portfolio using a MATLAB program. The details can be found on page 23.
See the following link for more detail:

https://web.stanford.edu/~wfsharpe/RISMAT/RISMAT-7.pdf

I decided to put together a spreadsheet that follows his formulas and tracks the WBS in realtime using the GOOGLEFINANCE function. Note that the market cap numbers still have to be entered manually every quarter from the respective index supplier's fact sheets. These are relatively easy to find if you do a google search for "<index name> fact sheet" (e.g. "CRSP U.S. index fact sheet").

Here's a shareable link to the google doc:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Hopefully anyone interested in this portfolio will find this spreadsheet useful.
"Buy-and-hold, long-term, all-market-index strategies, implemented at rock-bottom cost, are the surest of all routes to the accumulation of wealth" - John C. Bogle

User avatar
CyberBob
Posts: 3185
Joined: Tue Feb 20, 2007 2:53 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by CyberBob » Mon Aug 14, 2017 9:21 am

That's a great idea that we should keep up-to-date on Sharpe's allocation. Maybe we should add it to the wiki.

It looks like your numbers from the spreadsheet are as follows:
CRSP U.S: $25,089,806.00
FTSE All-World ex U.S.: $20,585,441.00
Citi: USBIG: $18,860,820.00
Citi: WorldBIG - USBIG: $18,373,240.00

But the numbers I get are a bit different. CRSP is the same. But the FTSE number from the 'All-World' index isn't what the Vanguard fund actually follows. The index there is actually the 'Global All-Cap ex-US'. All-World and Global sure sound like they should be the same thing, but All-World actually doesn't include small-caps, but Global does. The number I get for Global ex-US, from the factsheet on this page, is $23,056,597.

I also got a Citi numbers from this page that seem to be slightly different too (possibly from a transposed 6 and 0 in the middle of the USBIG number).
USBIG $18,806,820
WorldBIG-USBIG $18,427,240 ($37,234,060-$18,806,820)

So total of all four ($25,089,806+$23,056,597+$18,806,820+$18,427,240) = $85,380,463

Giving these percentages (rounded to nearest whole number):
29% US stock
27% ex-US stock
22% US bond
22% ex-US bond

User avatar
aj76er
Posts: 539
Joined: Tue Dec 01, 2015 11:34 pm
Location: Portland, OR

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by aj76er » Mon Aug 14, 2017 2:52 pm

^ Thanks for checking the market cap numbers.

Another possible source of these could be from the vanguard website thru the fund's "Portfolio & Management" page.

On the VFWAX page, it lists $30.9 billion for the "FTSE All-World ex US Index" as of 06/30/2017
On the VTIAX page, it lists $24.0 billion for the "FTSE Global All Cap ex US Index" as of 06/30/2017

I would have expected these to be closer :(.

At any rate, I think getting accurate and up-to-date market cap numbers is the trickiest part of the methodology.

I agree that it would be great to start a Wiki page documenting the methodology and possibly keeping it up-to-date with the latest market cap numbers.
"Buy-and-hold, long-term, all-market-index strategies, implemented at rock-bottom cost, are the surest of all routes to the accumulation of wealth" - John C. Bogle

Fclevz
Posts: 355
Joined: Fri Mar 30, 2007 11:28 am

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by Fclevz » Mon Aug 14, 2017 3:43 pm

aj76er wrote:
Mon Aug 14, 2017 2:52 pm
Another possible source of these could be from the vanguard website thru the fund's "Portfolio & Management" page.

On the VFWAX page, it lists $30.9 billion for the "FTSE All-World ex US Index" as of 06/30/2017
On the VTIAX page, it lists $24.0 billion for the "FTSE Global All Cap ex US Index" as of 06/30/2017

I would have expected these to be closer :(.
These numbers look to be the median market cap numbers for the stocks making up the index, rather than the total market cap of the entire index.

Cheers,
Fred

retiredjg
Posts: 32655
Joined: Thu Jan 10, 2008 12:56 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by retiredjg » Mon Aug 14, 2017 3:51 pm

aj76er wrote:
Sun Aug 13, 2017 5:38 pm
Hi All,

I've been reading William Sharpe's RISMAT series on what he calls the WBS (World Bond-Stock) Portfolio. It consists of the following 4 Vanguard funds held at market proportions:

VBTLX: Vanguard Total Bond Market Index Fund Admiral Shares
VTABX: Vanguard Total International Bond Index Admiral Shares
VTSAX: Vanguard Total Stock Market Index Admiral Shares
VFWAX: Vanguard FTSE All-World ex-U.S. Admiral Shares
Is it possible that what you are reading was published several years ago? The FTSE fund is no longer the most complete international fund available. I suspect he would have used the more complete international fund if it had been available then. In fact, the paper hints at that.


This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.

Fclevz
Posts: 355
Joined: Fri Mar 30, 2007 11:28 am

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by Fclevz » Mon Aug 14, 2017 3:54 pm

In case anyone hasn't seen it yet, here is a video where Sharpe talks about it https://www.youtube.com/watch?v=XsXOLZ9U7jI

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

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by longinvest » Mon Aug 14, 2017 3:56 pm

Fclevz wrote:
Mon Aug 14, 2017 3:54 pm
In case anyone hasn't seen it yet, here is a video where Sharpe talks about it https://www.youtube.com/watch?v=XsXOLZ9U7jI
And there's a thread about this video: viewtopic.php?f=10&t=207804
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

retiredjg
Posts: 32655
Joined: Thu Jan 10, 2008 12:56 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by retiredjg » Mon Aug 14, 2017 3:57 pm

aj76er wrote:
Sun Aug 13, 2017 5:38 pm
Hi All,

I've been reading William Sharpe's RISMAT series on what he calls the WBS (World Bond-Stock) Portfolio. It consists of the following 4 Vanguard funds held at market proportions:

VBTLX: Vanguard Total Bond Market Index Fund Admiral Shares
VTABX: Vanguard Total International Bond Index Admiral Shares
VTSAX: Vanguard Total Stock Market Index Admiral Shares
VFWAX: Vanguard FTSE All-World ex-U.S. Admiral Shares
Is it possible that what you are reading was published several years ago? The FTSE fund is no longer the most complete international fund available. I suspect he would have used the more complete international fund if it had been available then. In fact, the paper hints at that.

I only took a glance, but see if this is an updated version of what you have been reading.

https://web.stanford.edu/~wfsharpe/RISMAT/



This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.

User avatar
aj76er
Posts: 539
Joined: Tue Dec 01, 2015 11:34 pm
Location: Portland, OR

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by aj76er » Mon Aug 14, 2017 5:56 pm

Fclevz wrote:
Mon Aug 14, 2017 3:43 pm
These numbers look to be the median market cap numbers for the stocks making up the index, rather than the total market cap of the entire index.

Cheers,
Fred
Yep, that explains the discrepancy. Too bad, would have been an easy place to grab the data :)
"Buy-and-hold, long-term, all-market-index strategies, implemented at rock-bottom cost, are the surest of all routes to the accumulation of wealth" - John C. Bogle

User avatar
aj76er
Posts: 539
Joined: Tue Dec 01, 2015 11:34 pm
Location: Portland, OR

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by aj76er » Mon Aug 14, 2017 6:00 pm

retiredjg wrote:
Mon Aug 14, 2017 3:57 pm
This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.
yes, similar. however, I believe the TargetDate and LifeStrategy funds are fixed percentages (with TargetDate having a glide path). Sharpe's WBS portfolio is designed to float with the changing market weights of the 4 indexes. The rebalancing that occurs is only designed to ensure the 4 indexes are being followed faithfully; and not as a means to control risk vs return. Rather one is supposed to accept the market neutral risk-vs-return (which is unlike the TargetDate and LifeStrategy objectives).
"Buy-and-hold, long-term, all-market-index strategies, implemented at rock-bottom cost, are the surest of all routes to the accumulation of wealth" - John C. Bogle

User avatar
zonto
Posts: 98
Joined: Tue Aug 24, 2010 3:45 pm
Location: Boston, MA

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by zonto » Mon Aug 14, 2017 10:45 pm

retiredjg wrote:
Mon Aug 14, 2017 3:51 pm
. . . This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.
Closest to it is probably Vanguard LifeStrategy Moderate Growth Fund (VSMGX).

pascalwager
Posts: 1102
Joined: Mon Oct 31, 2011 8:36 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by pascalwager » Tue Aug 15, 2017 11:10 pm

The latest RISMAT update is May 2017, but Sharpe didn't update the Vanguard int'l stock fund to the newer global all-cap index version, which surprised me. Personally, I purchased the newer all-cap fund with no hesitation.

pascalwager
Posts: 1102
Joined: Mon Oct 31, 2011 8:36 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by pascalwager » Tue Aug 15, 2017 11:17 pm

rlovendale wrote:
Mon Aug 14, 2017 10:45 pm
retiredjg wrote:
Mon Aug 14, 2017 3:51 pm
. . . This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.
Closest to it is probably Vanguard LifeStrategy Moderate Growth Fund (VSMGX).
Yes, but the Target and LS Funds allocations are not market-weighted. You probably already know this, but some others may not.

retiredjg
Posts: 32655
Joined: Thu Jan 10, 2008 12:56 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by retiredjg » Wed Aug 16, 2017 6:34 am

pascalwager wrote:
Tue Aug 15, 2017 11:10 pm
The latest RISMAT update is May 2017, but Sharpe didn't update the Vanguard int'l stock fund to the newer global all-cap index version, which surprised me. Personally, I purchased the newer all-cap fund with no hesitation.
I could not find a date on the link I posted above, but it does contain Vanguard's Total International Index, not the FTSE fund. I have to admit I skimmed the document very quickly....

retiredjg
Posts: 32655
Joined: Thu Jan 10, 2008 12:56 pm

Re: Spreadsheet for Tracking the Global Market Portfolio

Post by retiredjg » Wed Aug 16, 2017 6:37 am

pascalwager wrote:
Tue Aug 15, 2017 11:17 pm
rlovendale wrote:
Mon Aug 14, 2017 10:45 pm
retiredjg wrote:
Mon Aug 14, 2017 3:51 pm
. . . This portfolio idea, with the more complete international fund, looks a lot like the Vanguard Target funds and LifeStrategy funds.
Closest to it is probably Vanguard LifeStrategy Moderate Growth Fund (VSMGX).
Yes, but the Target and LS Funds allocations are not market-weighted. You probably already know this, but some others may not.
You are correct, they are not market weighted but they are not too far away unless market weight has changed since I checked.

Post Reply