Lazy Rebalancing Formula for Excel/Google Sheets

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
EggCarton
Posts: 10
Joined: Sat Sep 01, 2018 11:03 am

Lazy Rebalancing Formula for Excel/Google Sheets

Post by EggCarton » Fri Sep 14, 2018 9:12 am

I currently do my best to re-balance our portfolio by purchasing under-weighted assets when I make new contributions. I keep an asset allocation spreadsheet which tells me how +/- our current allocation is. However, I can't figure out an Excel formula to calculate new contribution purchases. I am aware of the "Optimal lazy portfolio rebalancing calculator" ( http://optimalrebalancing.tk/? ) and find it very useful, but I would also like to not have to visit the site every time I put more money in the market.
Does anyone have a formula that I can plug into Excel to determine new contribution allocation?

Silk McCue
Posts: 1127
Joined: Thu Feb 25, 2016 7:11 pm

Re: Lazy Rebalancing Formula for Excel/Google Sheets

Post by Silk McCue » Fri Sep 14, 2018 9:40 pm

I analyzed how the tool was making its calculations while watching The Flash tonight on Netflix. It uses a percentage weighted approach on the funds that are under their target percentage. Too much of a paIn to provide instructions on how to create a spreadsheet to do that. Besides someone already spent a good amount of time in developing this tool.

What I can provide you is the approach to modify the format in which you store the data in your spreadsheet so that the first four columns match the tools expected inputs. You can then simply cut and paste that into the tool, update the contribution amount you want and then have it Recalculate the results. Once you are set up and tested in will be a one minute exercise each time you want to run it.

Instructions.

Place your current values into a spreadsheet such that they can be copied into the tool without typing. There are only 4 columns required (Asset Name, Target, Value and Share Price) - and the headings are just for show and aren’t really needed. Format the data as shown in the example. Then Copy and Paste the data into the online tool you are currently using rather than typing it in, overwriting the sample data. Now, change the Amount to Contribute field in the tool to your contribution amount. Then, press Recalculate and your results will appear in the results section of the tool. You can even cut and paste the data below and it will work.

ASSET NAME TARGET VALUE SHARE PRICE
Bond fund 20% 16500.00
TIPS fund 10% 6500.00
Domestic Stock ETF 40% 43500.00 68.75
International Stock ETF 30% 33500.00 48.80

Cheers

EggCarton
Posts: 10
Joined: Sat Sep 01, 2018 11:03 am

Re: Lazy Rebalancing Formula for Excel/Google Sheets

Post by EggCarton » Sat Sep 15, 2018 6:10 am

Thanks Silk. You cut my calculation time in half moving me much closer towards optimal laziness!
As an aside, because of your response, I found your post about the Achieve IRA CD. I thought it was a good fit for my dad and he is now interested in pursuing it. So thanks for all of your help.

Silk McCue
Posts: 1127
Joined: Thu Feb 25, 2016 7:11 pm

Re: Lazy Rebalancing Formula for Excel/Google Sheets

Post by Silk McCue » Sat Sep 15, 2018 6:55 am

EggCarton wrote:
Sat Sep 15, 2018 6:10 am
Thanks Silk. You cut my calculation time in half moving me much closer towards optimal laziness!
As an aside, because of your response, I found your post about the Achieve IRA CD. I thought it was a good fit for my dad and he is now interested in pursuing it. So thanks for all of your help.
You are certainly welcome. I'm glad that you found the Achieva post. It is an excellent offering. I'm not certain what post you came across as I have had a few different conversations in threads but there is at least one that documents my steps along the way which would be very helpful.

I just paused to find the reference and here it is.

viewtopic.php?f=10&t=252530&p=4066789&h ... a#p4043711

Cheers

Post Reply