Hi guys,
I've run into a mental roadblock trying to figure this out. I thought it was simple, but it was not as simple as I thought.
In the table below, I can easily calculate the return of the portfolio and break it down into equity and fixed income allocation. However, through time, this deviates from my target allocation of 20% equity and 80% fixed income, which is why I would like to "rebalance" every year.
I need a new column(s) that will basically "adjust" my equity and fixed income balances every year to keep my allocation at 20/80, which means that the returns will have to be calculated off the new adjusted numbers. You can see that throughout time, the percentages change and I actually end up with a portfolio of 41/59 which is VERY off target !!
What type of formula do I need?? Or do I need to use solver, VBA code, or something else to perform all the required calculations so that every year my portfolio is at 20/80?
Thanks for your help!
<colgroup><col width="124" style="width: 93pt;"><col width="89" style="width: 67pt;"><col width="97" style="width: 73pt;"><col width="92" style="width: 69pt;"><col width="139" span="4" style="width: 104pt;"><col width="99" style="width: 74pt;"><col width="105" style="width: 79pt;"><col width="116" style="width: 87pt;"><col width="161" style="width: 121pt;"></colgroup><tbody>
</tbody>
I've run into a mental roadblock trying to figure this out. I thought it was simple, but it was not as simple as I thought.
In the table below, I can easily calculate the return of the portfolio and break it down into equity and fixed income allocation. However, through time, this deviates from my target allocation of 20% equity and 80% fixed income, which is why I would like to "rebalance" every year.
I need a new column(s) that will basically "adjust" my equity and fixed income balances every year to keep my allocation at 20/80, which means that the returns will have to be calculated off the new adjusted numbers. You can see that throughout time, the percentages change and I actually end up with a portfolio of 41/59 which is VERY off target !!
What type of formula do I need?? Or do I need to use solver, VBA code, or something else to perform all the required calculations so that every year my portfolio is at 20/80?
Thanks for your help!
| Investment Model | Income | ||||||||||
| Equity | 20% | ||||||||||
| Fixed Income | 80% | ||||||||||
| Initial Investment | $10,000.00 | ||||||||||
| Index Returns | Portfolio Returns | Balnces | No Rebalancing | Differences | |||||||
| Year | S&P 500 | BBgBarc Us Agg | Equity Return | Fixed Income Return | Equity Balance | Fixed Income Balance | Total Balance | Equity % | Fixed Income % | Equity Difference | Fixed Income Difference |
| 1975 | $ - | $ - | $ 2,000.00 | $ 8,000.00 | $ 10,000.00 | 20.00% | 80.00% | 0.00% | 0.00% | ||
| 1976 | 21.45% | 14.50% | $ 429.04 | $ 1,159.73 | $ 2,429.04 | $ 9,159.73 | $ 11,588.77 | 20.96% | 79.04% | 0.96% | -0.96% |
| 1977 | -7.43% | 2.99% | $ (180.39) | $ 273.98 | $ 2,248.65 | $ 9,433.71 | $ 11,682.36 | 19.25% | 80.75% | -0.75% | 0.75% |
| 1978 | 6.36% | 1.38% | $ 143.10 | $ 130.57 | $ 2,391.76 | $ 9,564.27 | $ 11,956.03 | 20.00% | 80.00% | 0.00% | 0.00% |
| 1979 | 17.07% | 1.91% | $ 408.19 | $ 182.76 | $ 2,799.94 | $ 9,747.04 | $ 12,546.98 | 22.32% | 77.68% | 2.32% | -2.32% |
| 1980 | 28.14% | 2.67% | $ 788.02 | $ 260.17 | $ 3,587.97 | $ 10,007.20 | $ 13,595.17 | 26.39% | 73.61% | 6.39% | -6.39% |
| 1981 | -5.05% | 6.06% | $ (181.13) | $ 606.54 | $ 3,406.83 | $ 10,613.75 | $ 14,020.58 | 24.30% | 75.70% | 4.30% | -4.30% |
| 1982 | 19.51% | 28.23% | $ 664.76 | $ 2,996.54 | $ 4,071.59 | $ 13,610.29 | $ 17,681.88 | 23.03% | 76.97% | 3.03% | -3.03% |
| 1983 | 20.34% | 8.03% | $ 828.13 | $ 1,092.52 | $ 4,899.72 | $ 14,702.81 | $ 19,602.53 | 25.00% | 75.00% | 5.00% | -5.00% |
| 1984 | 6.08% | 14.10% | $ 298.13 | $ 2,073.69 | $ 5,197.86 | $ 16,776.50 | $ 21,974.36 | 23.65% | 76.35% | 3.65% | -3.65% |
| 1985 | 27.56% | 19.97% | $ 1,432.32 | $ 3,350.15 | $ 6,630.18 | $ 20,126.64 | $ 26,756.82 | 24.78% | 75.22% | 4.78% | -4.78% |
| 1986 | 17.11% | 14.21% | $ 1,134.65 | $ 2,859.22 | $ 7,764.83 | $ 22,985.86 | $ 30,750.69 | 25.25% | 74.75% | 5.25% | -5.25% |
| 1987 | 5.12% | 2.72% | $ 397.36 | $ 624.78 | $ 8,162.19 | $ 23,610.65 | $ 31,772.84 | 25.69% | 74.31% | 5.69% | -5.69% |
| 1988 | 15.37% | 7.59% | $ 1,254.13 | $ 1,792.14 | $ 9,416.32 | $ 25,402.79 | $ 34,819.11 | 27.04% | 72.96% | 7.04% | -7.04% |
| 1989 | 27.53% | 13.57% | $ 2,591.86 | $ 3,446.21 | $ 12,008.18 | $ 28,849.00 | $ 40,857.18 | 29.39% | 70.61% | 9.39% | -9.39% |
| 1990 | -3.15% | 8.58% | $ (378.67) | $ 2,475.61 | $ 11,629.51 | $ 31,324.61 | $ 42,954.12 | 27.07% | 72.93% | 7.07% | -7.07% |
| 1991 | 26.59% | 14.84% | $ 3,092.76 | $ 4,650.05 | $ 14,722.27 | $ 35,974.66 | $ 50,696.93 | 29.04% | 70.96% | 9.04% | -9.04% |
| 1992 | 7.34% | 7.14% | $ 1,081.07 | $ 2,568.95 | $ 15,803.34 | $ 38,543.61 | $ 54,346.95 | 29.08% | 70.92% | 9.08% | -9.08% |
| 1993 | 9.60% | 9.30% | $ 1,517.52 | $ 3,585.60 | $ 17,320.86 | $ 42,129.21 | $ 59,450.07 | 29.14% | 70.86% | 9.14% | -9.14% |
| 1994 | 1.31% | -2.96% | $ 227.22 | $ (1,246.82) | $ 17,548.08 | $ 40,882.39 | $ 58,430.47 | 30.03% | 69.97% | 10.03% | -10.03% |
| 1995 | 31.90% | 16.95% | $ 5,598.17 | $ 6,930.43 | $ 23,146.25 | $ 47,812.82 | $ 70,959.08 | 32.62% | 67.38% | 12.62% | -12.62% |
| 1996 | 20.67% | 3.57% | $ 4,784.11 | $ 1,705.11 | $ 27,930.36 | $ 49,517.94 | $ 77,448.30 | 36.06% | 63.94% | 16.06% | -16.06% |
| 1997 | 28.79% | 9.22% | $ 8,041.36 | $ 4,563.54 | $ 35,971.71 | $ 54,081.48 | $ 90,053.20 | 39.94% | 60.06% | 19.94% | -19.94% |
| 1998 | 25.14% | 8.33% | $ 9,042.21 | $ 4,504.85 | $ 45,013.93 | $ 58,586.34 | $ 103,600.26 | 43.45% | 56.55% | 23.45% | -23.45% |
| 1999 | 19.10% | -0.82% | $ 8,596.02 | $ (483.17) | $ 53,609.95 | $ 58,103.17 | $ 111,713.12 | 47.99% | 52.01% | 27.99% | -27.99% |
| 2000 | -9.55% | 11.00% | $ (5,117.52) | $ 6,390.44 | $ 48,492.42 | $ 64,493.61 | $ 112,986.04 | 42.92% | 57.08% | 22.92% | -22.92% |
| 2001 | -12.65% | 8.11% | $ (6,136.08) | $ 5,227.78 | $ 42,356.35 | $ 69,721.39 | $ 112,077.74 | 37.79% | 62.21% | 17.79% | -17.79% |
| 2002 | -24.98% | 9.76% | ########## | $ 6,806.62 | $ 31,777.82 | $ 76,528.01 | $ 108,305.83 | 29.34% | 70.66% | 9.34% | -9.34% |
| 2003 | 25.22% | 4.02% | $ 8,014.16 | $ 3,078.30 | $ 39,791.98 | $ 79,606.31 | $ 119,398.29 | 33.33% | 66.67% | 13.33% | -13.33% |
| 2004 | 10.33% | 4.25% | $ 4,110.39 | $ 3,381.12 | $ 43,902.37 | $ 82,987.43 | $ 126,889.80 | 34.60% | 65.40% | 14.60% | -14.60% |
| 2005 | 4.80% | 2.40% | $ 2,105.18 | $ 1,991.29 | $ 46,007.55 | $ 84,978.72 | $ 130,986.27 | 35.12% | 64.88% | 15.12% | -15.12% |
| 2006 | 14.66% | 4.24% | $ 6,746.77 | $ 3,605.21 | $ 52,754.32 | $ 88,583.93 | $ 141,338.25 | 37.32% | 62.68% | 17.32% | -17.32% |
| 2007 | 5.35% | 6.73% | $ 2,821.49 | $ 5,965.83 | $ 55,575.82 | $ 94,549.76 | $ 150,125.58 | 37.02% | 62.98% | 17.02% | -17.02% |
| 2008 | -46.20% | 5.11% | ########## | $ 4,829.18 | $ 29,899.73 | $ 99,378.94 | $ 129,278.67 | 23.13% | 76.87% | 3.13% | -3.13% |
| 2009 | 23.48% | 5.76% | $ 7,020.20 | $ 5,725.43 | $ 36,919.93 | $ 105,104.37 | $ 142,024.30 | 26.00% | 74.00% | 6.00% | -6.00% |
| 2010 | 14.03% | 6.34% | $ 5,180.34 | $ 6,660.08 | $ 42,100.27 | $ 111,764.46 | $ 153,864.72 | 27.36% | 72.64% | 7.36% | -7.36% |
| 2011 | 2.09% | 7.55% | $ 879.81 | $ 8,437.69 | $ 42,980.08 | $ 120,202.15 | $ 163,182.22 | 26.34% | 73.66% | 6.34% | -6.34% |
| 2012 | 14.85% | 4.13% | $ 6,380.40 | $ 4,962.46 | $ 49,360.47 | $ 125,164.60 | $ 174,525.08 | 28.28% | 71.72% | 8.28% | -8.28% |
| 2013 | 28.06% | -2.04% | $ 13,848.96 | $ (2,558.93) | $ 63,209.43 | $ 122,605.67 | $ 185,815.10 | 34.02% | 65.98% | 14.02% | -14.02% |
| 2014 | 12.83% | 5.79% | $ 8,109.29 | $ 7,104.41 | $ 71,318.72 | $ 129,710.08 | $ 201,028.80 | 35.48% | 64.52% | 15.48% | -15.48% |
| 2015 | 1.37% | 0.55% | $ 980.17 | $ 711.39 | $ 72,298.88 | $ 130,421.47 | $ 202,720.36 | 35.66% | 64.34% | 15.66% | -15.66% |
| 2016 | 11.30% | 2.61% | $ 8,167.66 | $ 3,407.96 | $ 80,466.54 | $ 133,829.44 | $ 214,295.98 | 37.55% | 62.45% | 17.55% | -17.55% |
| 2017 | 19.75% | 3.48% | $ 15,889.67 | $ 4,658.01 | $ 96,356.22 | $ 138,487.44 | $ 234,843.66 | 41.03% | 58.97% | 21.03% | -21.03% |
<colgroup><col width="124" style="width: 93pt;"><col width="89" style="width: 67pt;"><col width="97" style="width: 73pt;"><col width="92" style="width: 69pt;"><col width="139" span="4" style="width: 104pt;"><col width="99" style="width: 74pt;"><col width="105" style="width: 79pt;"><col width="116" style="width: 87pt;"><col width="161" style="width: 121pt;"></colgroup><tbody>
</tbody>
