How to backtest dynamic asset allocation

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Tue Mar 05, 2019 7:50 pm

Greetings, all!

As I've reported elsewhere, my current retirement strategy is to attach a particular asset allocation (say 75/25) to a particular target balance (say $1 million) and increase the proportion of fixed income as portfolio value approaches and exceeds the target balance. For instance, at $500K, the asset allocation would be 87/13; at $2 million, it would be 50/50. Thus for the accumulation phase.

Then, in retirement, my plan is to spend my fixed holdings first, resulting in an increased proportion of equities that will eventually reach 100 percent.

Is there any (free) way available to model and backtest this strategy? The problem is that I know my asset allocation right now, and I know what it would be at the end of a hypothetical 30-year retirement, but in between, my asset allocation would change depending on how my portfolio did. All the spreadsheets, etc., that I'm aware of online ask me to estimate what my asset allocation would be at retirement. I have no idea!

Anybody have any suggestions?

balbrec2
Posts: 160
Joined: Mon Nov 13, 2017 3:03 pm

Re: How to backtest dynamic asset allocation

Post by balbrec2 » Thu Mar 07, 2019 11:23 am

I know of know way to do what you ask although others may.
Even if you find a way, remember, back testing will only show you how
something did. You can't extrapolate that into the future. Anything can happen!

GAAP
Posts: 802
Joined: Fri Apr 08, 2016 12:41 pm

Re: How to backtest dynamic asset allocation

Post by GAAP » Thu Mar 07, 2019 11:38 am

I think you'll have to build your own model.

I would probably start with the Schiller data set (http://www.econ.yale.edu/~shiller/data.htm) and have some fun with spreadsheets. The annual data would be a lot easier to work with and probably sufficient for your needs.
“Adapt what is useful, reject what is useless, and add what is specifically your own.” ― Bruce Lee

User avatar
willthrill81
Posts: 8715
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

Re: How to backtest dynamic asset allocation

Post by willthrill81 » Thu Mar 07, 2019 11:46 am

Amadis_of_Gaul wrote:
Tue Mar 05, 2019 7:50 pm
Greetings, all!

As I've reported elsewhere, my current retirement strategy is to attach a particular asset allocation (say 75/25) to a particular target balance (say $1 million) and increase the proportion of fixed income as portfolio value approaches and exceeds the target balance. For instance, at $500K, the asset allocation would be 87/13; at $2 million, it would be 50/50. Thus for the accumulation phase.

Then, in retirement, my plan is to spend my fixed holdings first, resulting in an increased proportion of equities that will eventually reach 100 percent.

Is there any (free) way available to model and backtest this strategy? The problem is that I know my asset allocation right now, and I know what it would be at the end of a hypothetical 30-year retirement, but in between, my asset allocation would change depending on how my portfolio did. All the spreadsheets, etc., that I'm aware of online ask me to estimate what my asset allocation would be at retirement. I have no idea!

Anybody have any suggestions?
I've never seen a bucket strategy like this outperform a traditional AA approach over a long-term period, whether it's in the accumulation or decumulation phase.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings

gmaynardkrebs
Posts: 1384
Joined: Sun Feb 10, 2008 11:48 am

Re: How to backtest dynamic asset allocation

Post by gmaynardkrebs » Thu Mar 07, 2019 12:16 pm

Would a low cost Life-Strategy fund dated 2050 get you pretty close anyway? Your bond component will be much higher at the end of 30 years. Or, perhaps, just a balanced index fund? To be honest, I don't follow what you have in mind.

dcabler
Posts: 847
Joined: Wed Feb 19, 2014 11:30 am

Re: How to backtest dynamic asset allocation

Post by dcabler » Thu Mar 07, 2019 12:50 pm

GAAP wrote:
Thu Mar 07, 2019 11:38 am
I think you'll have to build your own model.

I would probably start with the Schiller data set (http://www.econ.yale.edu/~shiller/data.htm) and have some fun with spreadsheets. The annual data would be a lot easier to work with and probably sufficient for your needs.
Agree that you'll need to probably create your own model. Check out the Simba's Spreadsheet on this forum for lots of data for many different asset types. https://www.bogleheads.org/wiki/Simba%2 ... preadsheet

Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

Re: How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Thu Mar 07, 2019 2:14 pm

dcabler wrote:
Thu Mar 07, 2019 12:50 pm

Agree that you'll need to probably create your own model. Check out the Simba's Spreadsheet on this forum for lots of data for many different asset types. https://www.bogleheads.org/wiki/Simba%2 ... preadsheet
Thanks! That's what I was afraid of. The data are certainly out there, but even though I can conceptualize what I'm doing, I have no idea how to make Excel do that. Oh, well. We'll see whether I care enough to learn.

Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

Re: How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Thu Mar 07, 2019 2:31 pm

willthrill81 wrote:
Thu Mar 07, 2019 11:46 am

I've never seen a bucket strategy like this outperform a traditional AA approach over a long-term period, whether it's in the accumulation or decumulation phase.
Consider the summary charts on pp.73-37 in the McClung book _Living off Your Money_. You can get the first three chapters for free here:

http://livingoffyourmoney.com/wp-conten ... apters.pdf

McClung found that comparing a constant allocation strategy to a bonds-first strategy yielded higher safe withdrawal rates for the latter when backtested using US, UK, and Japanese data. This proved to be true, in fact, for all the data sets he considered except Shiller's dataset of large-cap US stocks. On the basis of the evidence we have, reverse glidepaths make for a more comfortable retirement. Will this hold true in the future? Maybe. However, it has held true in the past, and it will continue to hold true for as long as the long-term trend of the stock market is upward.

Concerning a glidepath during the accumulation phase, I think it's likely that my returns from a glidepath strategy will be lower than they would have been from a straight 100-percent equity portfolio. However, I view a glidepath as sequence-of-returns-risk insurance. If the market collapses the day after retirement, a portfolio with a 70/30 bond allocation has historically yielded a higher absolute-dollar withdrawal rate than a 100/0 portfolio.

In theory, I could run a 100/0 portfolio until I retire, then adopt a 70/30 portfolio on retirement day. However, that strikes me as very market-timing-y. Such a dramatic change in asset allocation could prove either very beneficial or very not. I don't feel like rolling the dice.

dcabler
Posts: 847
Joined: Wed Feb 19, 2014 11:30 am

Re: How to backtest dynamic asset allocation

Post by dcabler » Thu Mar 07, 2019 2:49 pm

Amadis_of_Gaul wrote:
Thu Mar 07, 2019 2:14 pm
dcabler wrote:
Thu Mar 07, 2019 12:50 pm

Agree that you'll need to probably create your own model. Check out the Simba's Spreadsheet on this forum for lots of data for many different asset types. https://www.bogleheads.org/wiki/Simba%2 ... preadsheet
Thanks! That's what I was afraid of. The data are certainly out there, but even though I can conceptualize what I'm doing, I have no idea how to make Excel do that. Oh, well. We'll see whether I care enough to learn.
Warning - once you become an Excel jockey you'll wonder how you ever lived without it. :D

GrowthSeeker
Posts: 425
Joined: Tue May 15, 2018 10:14 pm

Re: How to backtest dynamic asset allocation

Post by GrowthSeeker » Thu Mar 07, 2019 2:58 pm

I also think you’ll have to build your own spreadsheet. If you don’t have Excel, LibreOffice Calc is free.
Do you have an exact formula for how the % stocks (in the AA) relates to the balance?
Are you enough of a spreadsheet guru to figure out how to structure the spreadsheet?
If not, say where you’re stuck and help is on the way.
Just because you're paranoid doesn't mean they're NOT out to get you.

Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

Re: How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Thu Mar 07, 2019 7:46 pm

GrowthSeeker wrote:
Thu Mar 07, 2019 2:58 pm
I also think you’ll have to build your own spreadsheet. If you don’t have Excel, LibreOffice Calc is free.
Do you have an exact formula for how the % stocks (in the AA) relates to the balance?
Are you enough of a spreadsheet guru to figure out how to structure the spreadsheet?
If not, say where you’re stuck and help is on the way.
I appreciate your generosity (and I suspect many others on here would be equally generous with help), and I do have Excel. I even use a Cro-Magnon level Excel spreadsheet to calculate my asset allocation.

However, I think doing something like this would require me to _learn_ Excel, which I have never had to do before because i work in an utterly non-technical field. I admit that the challenge intrigues me, but that would be a lot of work to satisfy idle curiosity! I was hoping somebody else had done the work so I could plug in my numbers and look at the pretty graphs, but alas, no. :(

GrowthSeeker
Posts: 425
Joined: Tue May 15, 2018 10:14 pm

Re: How to backtest dynamic asset allocation

Post by GrowthSeeker » Thu Mar 07, 2019 7:55 pm

Well, here is a formula [Edit: for the accumulation phase] to match your percent stocks as a function of portfolio value (Balance):

Percent Stock = 100 - Balance / 40000
Just because you're paranoid doesn't mean they're NOT out to get you.

Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

Re: How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Fri Mar 08, 2019 9:30 am

GrowthSeeker wrote:
Thu Mar 07, 2019 7:55 pm
Well, here is a formula [Edit: for the accumulation phase] to match your percent stocks as a function of portfolio value (Balance):

Percent Stock = 100 - Balance / 40000
That certainly makes sense! However, I'm envisioning something a little more complex. We have all these glorious datasets we can use for backtesting, right? I would like to somehow use those datasets in an iterative way.

To put things another way, given X starting year and month, how does my balance and asset allocation change year by year? For instance, let's say that my goal is $1 million in savings, with a target asset allocation of 75/25. I start investing in January 1936 with a 100/0 allocation. I invest $100K, and the market doubles my money (not that it actually did that in 1936, of course), leaving me with $200K. I'm now 20 percent of the way to my savings goal, leaving me with a new allocation of 95/5 in January 1937. How does that new allocation fare (with additional deposits) through 1937? What will the new allocation be in 1938?

Fast-forward to 1966, when I begin retirement. I have saved $2 million, with a resultant asset allocation of 50/50. Assuming that I draw down my fixed holdings first, what is the highest withdrawal rate that will keep me from running out of money by 1996?

Really, though, I guess I don't need a whole backtesting spreadsheet. As long as I run the numbers for problem cohorts like 1929, 1966, and 2000, that should tell me what I need to know. The whole spreadsheet would be cool, though!

GrowthSeeker
Posts: 425
Joined: Tue May 15, 2018 10:14 pm

Re: How to backtest dynamic asset allocation

Post by GrowthSeeker » Fri Mar 08, 2019 9:58 am

Amadis_of_Gaul wrote:
Fri Mar 08, 2019 9:30 am
<snip>
Really, though, I guess I don't need a whole backtesting spreadsheet. As long as I run the numbers for problem cohorts like 1929, 1966, and 2000, that should tell me what I need to know. The whole spreadsheet would be cool, though!
The big spreadsheet isn't going to be that hard for you, you just have to learn a little at a time. Start small, take baby steps, get one thing working and move on to the next thing. Being proficient in Excel is a very nice skill to have.

I envision a sheet where one column is the Year (starting at whatever year you like).
A column is your Age.
A column is your annual additions to the portfolio, what you would save each year; or alternatively one column for annual income and another for annual expenses.
A column for Social Security income.

A column for what stocks did (percent change) that year.
A column for what bonds did that year.
- [for these two you could either use real data you have downloaded from the web], or
- [just put in an average number just to see how the numbers look and to at least get the spreadsheet working], or
- [you could do a Monte Carlo type where you have a mean and a S.D. and use a built in Excel function to randomize; if you do this, you'll get a different number each time Excel recalculates so it might be harder to make sense of it, and I wouldn't start this way until you're more Excel-savvy and have the spreadsheet working with simpler numbers]

And of course, a Balance Column where you'd have an entry one row above the first year as a starting balance. Then the balance for the other rows would be a formula looking at the prior year's balance, the percent stock, the stock return, the bond return, the income and the expenses.

A column for percent stock would look at the prior year's Balance.

In general, if something is complicated to figure out, you can either make it a user defined function (which is more elegant and easy if you're a programmer, provided you can figure out how to make even a simple function in Excel which was more of a hurdle for me than I thought it would be) OR you can just use several columns to do intermediate steps and get the same answers, which is what I usually do.

(note that all this rebalancing, if in a taxable account, will lead to a lot of capital gains tax most likely)
Last edited by GrowthSeeker on Fri Mar 08, 2019 10:01 am, edited 1 time in total.
Just because you're paranoid doesn't mean they're NOT out to get you.

livesoft
Posts: 65503
Joined: Thu Mar 01, 2007 8:00 pm

Re: How to backtest dynamic asset allocation

Post by livesoft » Fri Mar 08, 2019 10:01 am

Big ERN mentions a freely available toolkit in his series of safe withdrawal rate articles:
https://earlyretirementnow.com/2016/12/ ... t-1-intro/
Wiki This signature message sponsored by sscritic: Learn to fish.

Topic Author
Amadis_of_Gaul
Posts: 47
Joined: Tue Dec 18, 2018 5:57 pm

Re: How to backtest dynamic asset allocation

Post by Amadis_of_Gaul » Sat Mar 09, 2019 5:38 pm

GrowthSeeker wrote:
Fri Mar 08, 2019 9:58 am

The big spreadsheet isn't going to be that hard for you, you just have to learn a little at a time. Start small, take baby steps, get one thing working and move on to the next thing. Being proficient in Excel is a very nice skill to have.

I envision a sheet where one column is the Year (starting at whatever year you like).
A column is your Age.
A column is your annual additions to the portfolio, what you would save each year; or alternatively one column for annual income and another for annual expenses.
A column for Social Security income.

A column for what stocks did (percent change) that year.
A column for what bonds did that year.
- [for these two you could either use real data you have downloaded from the web], or
- [just put in an average number just to see how the numbers look and to at least get the spreadsheet working], or
- [you could do a Monte Carlo type where you have a mean and a S.D. and use a built in Excel function to randomize; if you do this, you'll get a different number each time Excel recalculates so it might be harder to make sense of it, and I wouldn't start this way until you're more Excel-savvy and have the spreadsheet working with simpler numbers]

And of course, a Balance Column where you'd have an entry one row above the first year as a starting balance. Then the balance for the other rows would be a formula looking at the prior year's balance, the percent stock, the stock return, the bond return, the income and the expenses.

A column for percent stock would look at the prior year's Balance.

In general, if something is complicated to figure out, you can either make it a user defined function (which is more elegant and easy if you're a programmer, provided you can figure out how to make even a simple function in Excel which was more of a hurdle for me than I thought it would be) OR you can just use several columns to do intermediate steps and get the same answers, which is what I usually do.

(note that all this rebalancing, if in a taxable account, will lead to a lot of capital gains tax most likely)
That does seem doable!

I do have some taxable savings, but I have enough in tax-deferred that I can do all of my rebalancing there and just let the equities ride in taxable. Whether or not I benefit from my scheme, Uncle Sam won't!

international001
Posts: 690
Joined: Thu Feb 15, 2018 7:31 pm

Re: How to backtest dynamic asset allocation

Post by international001 » Sat Mar 09, 2019 6:40 pm

GAAP wrote:
Thu Mar 07, 2019 11:38 am
I think you'll have to build your own model.

I would probably start with the Schiller data set (http://www.econ.yale.edu/~shiller/data.htm) and have some fun with spreadsheets. The annual data would be a lot easier to work with and probably sufficient for your needs.
Great data set.

But how do you compute real return (w/o taxes?).
If I do '=(B1784-B1772+SUM(C1773:C1784))/B1772' to see the return for year 2018 (before inflation), I don't get a reasonable value.

It's also interesting that CAPE10 is calculated w/o having into account dividends. Why?

GrowthSeeker
Posts: 425
Joined: Tue May 15, 2018 10:14 pm

Re: How to backtest dynamic asset allocation

Post by GrowthSeeker » Sun Mar 10, 2019 6:36 am

international001 wrote:
Sat Mar 09, 2019 6:40 pm
GAAP wrote:
Thu Mar 07, 2019 11:38 am
I think you'll have to build your own model.

I would probably start with the Schiller data set (http://www.econ.yale.edu/~shiller/data.htm) and have some fun with spreadsheets. The annual data would be a lot easier to work with and probably sufficient for your needs.
Great data set.

But how do you compute real return (w/o taxes?).
If I do '=(B1784-B1772+SUM(C1773:C1784))/B1772' to see the return for year 2018 (before inflation), I don't get a reasonable value.

It's also interesting that CAPE10 is calculated w/o having into account dividends. Why?
It looks like the dividends in column D are given as annualized values. For example the Dec, 2018 value is 53.75 for the dividend and the S&P value is 2567.31; dividing gives you 0.0209 or about 2%. That has to be for the whole year. So substitute the word AVERAGE in place of SUM.

And Earnings are just earnings (per share): what the company took in as income minus expenses. PE ratio is just price per share divided by earnings per share. Dividends are not part of PE so they are not part of a smoothed PE. Ah, but now I think I see your point. If a company stopped paying dividends and just let that money grow their share price at a faster rate, the PE would be higher. So maybe dividends should be taken into account along with price and earnings to make a different ratio that combines PE and div yield. (maybe this already exists)
Just because you're paranoid doesn't mean they're NOT out to get you.

international001
Posts: 690
Joined: Thu Feb 15, 2018 7:31 pm

Re: How to backtest dynamic asset allocation

Post by international001 » Fri Mar 15, 2019 7:46 am

GrowthSeeker wrote:
Sun Mar 10, 2019 6:36 am
international001 wrote:
Sat Mar 09, 2019 6:40 pm
GAAP wrote:
Thu Mar 07, 2019 11:38 am
I think you'll have to build your own model.

I would probably start with the Schiller data set (http://www.econ.yale.edu/~shiller/data.htm) and have some fun with spreadsheets. The annual data would be a lot easier to work with and probably sufficient for your needs.
Great data set.

But how do you compute real return (w/o taxes?).
If I do '=(B1784-B1772+SUM(C1773:C1784))/B1772' to see the return for year 2018 (before inflation), I don't get a reasonable value.

It's also interesting that CAPE10 is calculated w/o having into account dividends. Why?
It looks like the dividends in column D are given as annualized values. For example the Dec, 2018 value is 53.75 for the dividend and the S&P value is 2567.31; dividing gives you 0.0209 or about 2%. That has to be for the whole year. So substitute the word AVERAGE in place of SUM.

And Earnings are just earnings (per share): what the company took in as income minus expenses. PE ratio is just price per share divided by earnings per share. Dividends are not part of PE so they are not part of a smoothed PE. Ah, but now I think I see your point. If a company stopped paying dividends and just let that money grow their share price at a faster rate, the PE would be higher. So maybe dividends should be taken into account along with price and earnings to make a different ratio that combines PE and div yield. (maybe this already exists)

Thanks.. Your assumption about the dividends being annualized makes sense. It would seem same for earnings, since average of all months are being used to calculate PE10. I guess this just means multiplied by 12 the monthly dividends/earnings

IMO, if CAP10 doesn't account for dividends, it would seem a shortcoming of the metric. Larry considers it as an adjustment to CAPE10 (http://buckinghamadvisor.com/cape-10-ra ... f-context/)

Full disclosure. I don't hold a Nobel Prize

Post Reply