
Economics Economics Forum  Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance 
 LinkBack  Thread Tools  Display Modes 
December 14th, 2013, 09:49 AM  #1 
Newbie Joined: Dec 2013 Posts: 1 Thanks: 0  Can't get my head around this asset allocation formula
I have a small spreadsheet to figure out asset allocation for a retirement account. There are a total of ten index funds and I have assigned a percentage of the total value of all funds that each fund should be. For example: Total is $1,000 Fund 1 = 10% (or $100) Fund 2 = 5% (or $50) Fund 3 = 7.5% (or $75) etc. Once a year, I will rebalance all funds to match the percentages by selling funds that hold too much and redistributing to the funds that have too little. This is very simple to calculate; just total everything up and multiply by the target percentage. However, on a monthly basis I want to add money to the funds and keep the balances as close as possible to the target percentages but (and this is where I'm having trouble) without selling any of the funds that are over the target percentages. For example: Current total is $1,000 I'm going to add $500 Fund 1 goal is 10%; current is actually 5% (it's 5% under) Fund 2 goal is 5%; current is actually 4% (it's 1% under) Fund 3 goal is 7.5%; current is actually 9% (it's 1.5% over) In this case, I would want a formula that tells me how to divvy up the $500 contribution so that after the contribution were made, the percentage Fund 1 and Fund 2 were under are as equal as possible (e.g. both 2.3% under). In most cases, I would need to ignore funds that were over their target percentage (since I'm not selling), but if a contribution were big enough and that fund was just a little over the target, it might need a portion of the contribution as well. Any ideas on how to solve this? And I apologize if my explanation is unclear. Thanks!!! 
December 15th, 2013, 01:53 AM  #2 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 14,597 Thanks: 1038  Re: Can't get my head around this asset allocation formula Code: 100 10 50 5 +100 150 10 50 5 40 4 +35 75 5 75 7.5 90 9 +36 126 8.4 775 77.5 820 82 +329 1149 76.6 ===================================================== 1000 100 1000 100 +500 1500 100 COLUMN 1,2 : theoretical setup COLUMN 3,4 : actual at purchase date COLUMN 5 : 500 purchase breakdown COLUMN 6,7: after purchase NOTES: 1st 2 invs't back to original percentages 500  135 = 365 left: 3rd invs't gets 9/91 * 365 = 36 : the 9:82 percentages used 4th invs't gets 82/91 * 365 = 329 The percentages of 3rd and 4th drop after the purchase, but NOT the size of each... Am I "seeing" this mess :P properly ? 

Tags 
allocation, asset, formula, head 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Allocation (not pro rata) problem  kureka  Number Theory  0  March 26th, 2012 01:01 AM 
Hello everyone, I am in over my head.  cr12678  New Users  3  December 11th, 2011 11:01 AM 
PB268, Head Bear at PolarBearGames  PB268  New Users  4  October 1st, 2010 10:05 AM 
Number allocation  Mond  Number Theory  5  July 1st, 2009 05:53 AM 
I'm scratching my head  Larry Rudnicki  Algebra  1  January 15th, 2008 10:54 PM 