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!
[TABLE="width: 1439"]
<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>[TR]
[TD="class: xl63, width: 124"]Investment Model[/TD]
[TD="class: xl64, width: 89, align: center"]Income[/TD]
[TD="width: 97"][/TD]
[TD="width: 92"][/TD]
[TD="class: xl65, width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 99"][/TD]
[TD="width: 105"][/TD]
[TD="width: 116"][/TD]
[TD="width: 161"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Equity[/TD]
[TD="class: xl67, align: center"]20%[/TD]
[TD][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]Fixed Income[/TD]
[TD="class: xl67, align: center"]80%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl69"]Initial Investment[/TD]
[TD="class: xl70, align: center"] $10,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl72, align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl73, colspan: 2, align: center"]Index Returns[/TD]
[TD="class: xl73, colspan: 2, align: center"]Portfolio Returns[/TD]
[TD="class: xl73, colspan: 3, align: center"]Balnces[/TD]
[TD="class: xl73, colspan: 2, align: center"]No Rebalancing[/TD]
[TD="class: xl73, colspan: 2, align: center"]Differences[/TD]
[/TR]
[TR]
[TD="class: xl71"]Year[/TD]
[TD="class: xl74"]S&P 500[/TD]
[TD="class: xl75"]BBgBarc Us Agg[/TD]
[TD="class: xl76"]Equity Return[/TD]
[TD="class: xl75"]Fixed Income Return[/TD]
[TD="class: xl76"]Equity Balance[/TD]
[TD="class: xl75"]Fixed Income Balance[/TD]
[TD="class: xl77"]Total Balance[/TD]
[TD="class: xl76"]Equity %[/TD]
[TD="class: xl75"]Fixed Income %[/TD]
[TD="class: xl76"]Equity Difference[/TD]
[TD="class: xl75"]Fixed Income Difference[/TD]
[/TR]
[TR]
[TD="class: xl78"]1975[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl81"] $ - [/TD]
[TD="class: xl82"] $ - [/TD]
[TD="class: xl83"] $ 2,000.00 [/TD]
[TD="class: xl84"] $ 8,000.00 [/TD]
[TD="class: xl68"] $ 10,000.00 [/TD]
[TD="class: xl85, align: right"]20.00%[/TD]
[TD="class: xl86, align: right"]80.00%[/TD]
[TD="class: xl85, align: right"]0.00%[/TD]
[TD="class: xl86, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1976[/TD]
[TD="class: xl87, align: right"]21.45%[/TD]
[TD="class: xl88, align: right"]14.50%[/TD]
[TD="class: xl83"] $ 429.04 [/TD]
[TD="class: xl84"] $ 1,159.73 [/TD]
[TD="class: xl83"] $ 2,429.04 [/TD]
[TD="class: xl84"] $ 9,159.73 [/TD]
[TD="class: xl68"] $ 11,588.77 [/TD]
[TD="class: xl85, align: right"]20.96%[/TD]
[TD="class: xl86, align: right"]79.04%[/TD]
[TD="class: xl85, align: right"]0.96%[/TD]
[TD="class: xl86, align: right"]-0.96%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1977[/TD]
[TD="class: xl87, align: right"]-7.43%[/TD]
[TD="class: xl88, align: right"]2.99%[/TD]
[TD="class: xl83"] $ (180.39)[/TD]
[TD="class: xl84"] $ 273.98 [/TD]
[TD="class: xl83"] $ 2,248.65 [/TD]
[TD="class: xl84"] $ 9,433.71 [/TD]
[TD="class: xl68"] $ 11,682.36 [/TD]
[TD="class: xl85, align: right"]19.25%[/TD]
[TD="class: xl86, align: right"]80.75%[/TD]
[TD="class: xl85, align: right"]-0.75%[/TD]
[TD="class: xl86, align: right"]0.75%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1978[/TD]
[TD="class: xl87, align: right"]6.36%[/TD]
[TD="class: xl88, align: right"]1.38%[/TD]
[TD="class: xl83"] $ 143.10 [/TD]
[TD="class: xl84"] $ 130.57 [/TD]
[TD="class: xl83"] $ 2,391.76 [/TD]
[TD="class: xl84"] $ 9,564.27 [/TD]
[TD="class: xl68"] $ 11,956.03 [/TD]
[TD="class: xl85, align: right"]20.00%[/TD]
[TD="class: xl86, align: right"]80.00%[/TD]
[TD="class: xl85, align: right"]0.00%[/TD]
[TD="class: xl86, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1979[/TD]
[TD="class: xl87, align: right"]17.07%[/TD]
[TD="class: xl88, align: right"]1.91%[/TD]
[TD="class: xl83"] $ 408.19 [/TD]
[TD="class: xl84"] $ 182.76 [/TD]
[TD="class: xl83"] $ 2,799.94 [/TD]
[TD="class: xl84"] $ 9,747.04 [/TD]
[TD="class: xl68"] $ 12,546.98 [/TD]
[TD="class: xl85, align: right"]22.32%[/TD]
[TD="class: xl86, align: right"]77.68%[/TD]
[TD="class: xl85, align: right"]2.32%[/TD]
[TD="class: xl86, align: right"]-2.32%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1980[/TD]
[TD="class: xl87, align: right"]28.14%[/TD]
[TD="class: xl88, align: right"]2.67%[/TD]
[TD="class: xl83"] $ 788.02 [/TD]
[TD="class: xl84"] $ 260.17 [/TD]
[TD="class: xl83"] $ 3,587.97 [/TD]
[TD="class: xl84"] $ 10,007.20 [/TD]
[TD="class: xl68"] $ 13,595.17 [/TD]
[TD="class: xl85, align: right"]26.39%[/TD]
[TD="class: xl86, align: right"]73.61%[/TD]
[TD="class: xl85, align: right"]6.39%[/TD]
[TD="class: xl86, align: right"]-6.39%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1981[/TD]
[TD="class: xl87, align: right"]-5.05%[/TD]
[TD="class: xl88, align: right"]6.06%[/TD]
[TD="class: xl83"] $ (181.13)[/TD]
[TD="class: xl84"] $ 606.54 [/TD]
[TD="class: xl83"] $ 3,406.83 [/TD]
[TD="class: xl84"] $ 10,613.75 [/TD]
[TD="class: xl68"] $ 14,020.58 [/TD]
[TD="class: xl85, align: right"]24.30%[/TD]
[TD="class: xl86, align: right"]75.70%[/TD]
[TD="class: xl85, align: right"]4.30%[/TD]
[TD="class: xl86, align: right"]-4.30%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1982[/TD]
[TD="class: xl87, align: right"]19.51%[/TD]
[TD="class: xl88, align: right"]28.23%[/TD]
[TD="class: xl83"] $ 664.76 [/TD]
[TD="class: xl84"] $ 2,996.54 [/TD]
[TD="class: xl83"] $ 4,071.59 [/TD]
[TD="class: xl84"] $ 13,610.29 [/TD]
[TD="class: xl68"] $ 17,681.88 [/TD]
[TD="class: xl85, align: right"]23.03%[/TD]
[TD="class: xl86, align: right"]76.97%[/TD]
[TD="class: xl85, align: right"]3.03%[/TD]
[TD="class: xl86, align: right"]-3.03%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1983[/TD]
[TD="class: xl87, align: right"]20.34%[/TD]
[TD="class: xl88, align: right"]8.03%[/TD]
[TD="class: xl83"] $ 828.13 [/TD]
[TD="class: xl84"] $ 1,092.52 [/TD]
[TD="class: xl83"] $ 4,899.72 [/TD]
[TD="class: xl84"] $ 14,702.81 [/TD]
[TD="class: xl68"] $ 19,602.53 [/TD]
[TD="class: xl85, align: right"]25.00%[/TD]
[TD="class: xl86, align: right"]75.00%[/TD]
[TD="class: xl85, align: right"]5.00%[/TD]
[TD="class: xl86, align: right"]-5.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1984[/TD]
[TD="class: xl87, align: right"]6.08%[/TD]
[TD="class: xl88, align: right"]14.10%[/TD]
[TD="class: xl83"] $ 298.13 [/TD]
[TD="class: xl84"] $ 2,073.69 [/TD]
[TD="class: xl83"] $ 5,197.86 [/TD]
[TD="class: xl84"] $ 16,776.50 [/TD]
[TD="class: xl68"] $ 21,974.36 [/TD]
[TD="class: xl85, align: right"]23.65%[/TD]
[TD="class: xl86, align: right"]76.35%[/TD]
[TD="class: xl85, align: right"]3.65%[/TD]
[TD="class: xl86, align: right"]-3.65%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1985[/TD]
[TD="class: xl87, align: right"]27.56%[/TD]
[TD="class: xl88, align: right"]19.97%[/TD]
[TD="class: xl83"] $ 1,432.32 [/TD]
[TD="class: xl84"] $ 3,350.15 [/TD]
[TD="class: xl83"] $ 6,630.18 [/TD]
[TD="class: xl84"] $ 20,126.64 [/TD]
[TD="class: xl68"] $ 26,756.82 [/TD]
[TD="class: xl85, align: right"]24.78%[/TD]
[TD="class: xl86, align: right"]75.22%[/TD]
[TD="class: xl85, align: right"]4.78%[/TD]
[TD="class: xl86, align: right"]-4.78%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1986[/TD]
[TD="class: xl87, align: right"]17.11%[/TD]
[TD="class: xl88, align: right"]14.21%[/TD]
[TD="class: xl83"] $ 1,134.65 [/TD]
[TD="class: xl84"] $ 2,859.22 [/TD]
[TD="class: xl83"] $ 7,764.83 [/TD]
[TD="class: xl84"] $ 22,985.86 [/TD]
[TD="class: xl68"] $ 30,750.69 [/TD]
[TD="class: xl85, align: right"]25.25%[/TD]
[TD="class: xl86, align: right"]74.75%[/TD]
[TD="class: xl85, align: right"]5.25%[/TD]
[TD="class: xl86, align: right"]-5.25%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1987[/TD]
[TD="class: xl87, align: right"]5.12%[/TD]
[TD="class: xl88, align: right"]2.72%[/TD]
[TD="class: xl83"] $ 397.36 [/TD]
[TD="class: xl84"] $ 624.78 [/TD]
[TD="class: xl83"] $ 8,162.19 [/TD]
[TD="class: xl84"] $ 23,610.65 [/TD]
[TD="class: xl68"] $ 31,772.84 [/TD]
[TD="class: xl85, align: right"]25.69%[/TD]
[TD="class: xl86, align: right"]74.31%[/TD]
[TD="class: xl85, align: right"]5.69%[/TD]
[TD="class: xl86, align: right"]-5.69%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1988[/TD]
[TD="class: xl87, align: right"]15.37%[/TD]
[TD="class: xl88, align: right"]7.59%[/TD]
[TD="class: xl83"] $ 1,254.13 [/TD]
[TD="class: xl84"] $ 1,792.14 [/TD]
[TD="class: xl83"] $ 9,416.32 [/TD]
[TD="class: xl84"] $ 25,402.79 [/TD]
[TD="class: xl68"] $ 34,819.11 [/TD]
[TD="class: xl85, align: right"]27.04%[/TD]
[TD="class: xl86, align: right"]72.96%[/TD]
[TD="class: xl85, align: right"]7.04%[/TD]
[TD="class: xl86, align: right"]-7.04%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1989[/TD]
[TD="class: xl87, align: right"]27.53%[/TD]
[TD="class: xl88, align: right"]13.57%[/TD]
[TD="class: xl83"] $ 2,591.86 [/TD]
[TD="class: xl84"] $ 3,446.21 [/TD]
[TD="class: xl83"] $ 12,008.18 [/TD]
[TD="class: xl84"] $ 28,849.00 [/TD]
[TD="class: xl68"] $ 40,857.18 [/TD]
[TD="class: xl85, align: right"]29.39%[/TD]
[TD="class: xl86, align: right"]70.61%[/TD]
[TD="class: xl85, align: right"]9.39%[/TD]
[TD="class: xl86, align: right"]-9.39%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1990[/TD]
[TD="class: xl87, align: right"]-3.15%[/TD]
[TD="class: xl88, align: right"]8.58%[/TD]
[TD="class: xl83"] $ (378.67)[/TD]
[TD="class: xl84"] $ 2,475.61 [/TD]
[TD="class: xl83"] $ 11,629.51 [/TD]
[TD="class: xl84"] $ 31,324.61 [/TD]
[TD="class: xl68"] $ 42,954.12 [/TD]
[TD="class: xl85, align: right"]27.07%[/TD]
[TD="class: xl86, align: right"]72.93%[/TD]
[TD="class: xl85, align: right"]7.07%[/TD]
[TD="class: xl86, align: right"]-7.07%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1991[/TD]
[TD="class: xl87, align: right"]26.59%[/TD]
[TD="class: xl88, align: right"]14.84%[/TD]
[TD="class: xl83"] $ 3,092.76 [/TD]
[TD="class: xl84"] $ 4,650.05 [/TD]
[TD="class: xl83"] $ 14,722.27 [/TD]
[TD="class: xl84"] $ 35,974.66 [/TD]
[TD="class: xl68"] $ 50,696.93 [/TD]
[TD="class: xl85, align: right"]29.04%[/TD]
[TD="class: xl86, align: right"]70.96%[/TD]
[TD="class: xl85, align: right"]9.04%[/TD]
[TD="class: xl86, align: right"]-9.04%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1992[/TD]
[TD="class: xl87, align: right"]7.34%[/TD]
[TD="class: xl88, align: right"]7.14%[/TD]
[TD="class: xl83"] $ 1,081.07 [/TD]
[TD="class: xl84"] $ 2,568.95 [/TD]
[TD="class: xl83"] $ 15,803.34 [/TD]
[TD="class: xl84"] $ 38,543.61 [/TD]
[TD="class: xl68"] $ 54,346.95 [/TD]
[TD="class: xl85, align: right"]29.08%[/TD]
[TD="class: xl86, align: right"]70.92%[/TD]
[TD="class: xl85, align: right"]9.08%[/TD]
[TD="class: xl86, align: right"]-9.08%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1993[/TD]
[TD="class: xl87, align: right"]9.60%[/TD]
[TD="class: xl88, align: right"]9.30%[/TD]
[TD="class: xl83"] $ 1,517.52 [/TD]
[TD="class: xl84"] $ 3,585.60 [/TD]
[TD="class: xl83"] $ 17,320.86 [/TD]
[TD="class: xl84"] $ 42,129.21 [/TD]
[TD="class: xl68"] $ 59,450.07 [/TD]
[TD="class: xl85, align: right"]29.14%[/TD]
[TD="class: xl86, align: right"]70.86%[/TD]
[TD="class: xl85, align: right"]9.14%[/TD]
[TD="class: xl86, align: right"]-9.14%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1994[/TD]
[TD="class: xl87, align: right"]1.31%[/TD]
[TD="class: xl88, align: right"]-2.96%[/TD]
[TD="class: xl83"] $ 227.22 [/TD]
[TD="class: xl84"] $ (1,246.82)[/TD]
[TD="class: xl83"] $ 17,548.08 [/TD]
[TD="class: xl84"] $ 40,882.39 [/TD]
[TD="class: xl68"] $ 58,430.47 [/TD]
[TD="class: xl85, align: right"]30.03%[/TD]
[TD="class: xl86, align: right"]69.97%[/TD]
[TD="class: xl85, align: right"]10.03%[/TD]
[TD="class: xl86, align: right"]-10.03%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1995[/TD]
[TD="class: xl87, align: right"]31.90%[/TD]
[TD="class: xl88, align: right"]16.95%[/TD]
[TD="class: xl83"] $ 5,598.17 [/TD]
[TD="class: xl84"] $ 6,930.43 [/TD]
[TD="class: xl83"] $ 23,146.25 [/TD]
[TD="class: xl84"] $ 47,812.82 [/TD]
[TD="class: xl68"] $ 70,959.08 [/TD]
[TD="class: xl85, align: right"]32.62%[/TD]
[TD="class: xl86, align: right"]67.38%[/TD]
[TD="class: xl85, align: right"]12.62%[/TD]
[TD="class: xl86, align: right"]-12.62%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1996[/TD]
[TD="class: xl87, align: right"]20.67%[/TD]
[TD="class: xl88, align: right"]3.57%[/TD]
[TD="class: xl83"] $ 4,784.11 [/TD]
[TD="class: xl84"] $ 1,705.11 [/TD]
[TD="class: xl83"] $ 27,930.36 [/TD]
[TD="class: xl84"] $ 49,517.94 [/TD]
[TD="class: xl68"] $ 77,448.30 [/TD]
[TD="class: xl85, align: right"]36.06%[/TD]
[TD="class: xl86, align: right"]63.94%[/TD]
[TD="class: xl85, align: right"]16.06%[/TD]
[TD="class: xl86, align: right"]-16.06%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1997[/TD]
[TD="class: xl87, align: right"]28.79%[/TD]
[TD="class: xl88, align: right"]9.22%[/TD]
[TD="class: xl83"] $ 8,041.36 [/TD]
[TD="class: xl84"] $ 4,563.54 [/TD]
[TD="class: xl83"] $ 35,971.71 [/TD]
[TD="class: xl84"] $ 54,081.48 [/TD]
[TD="class: xl68"] $ 90,053.20 [/TD]
[TD="class: xl85, align: right"]39.94%[/TD]
[TD="class: xl86, align: right"]60.06%[/TD]
[TD="class: xl85, align: right"]19.94%[/TD]
[TD="class: xl86, align: right"]-19.94%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1998[/TD]
[TD="class: xl87, align: right"]25.14%[/TD]
[TD="class: xl88, align: right"]8.33%[/TD]
[TD="class: xl83"] $ 9,042.21 [/TD]
[TD="class: xl84"] $ 4,504.85 [/TD]
[TD="class: xl83"] $ 45,013.93 [/TD]
[TD="class: xl84"] $ 58,586.34 [/TD]
[TD="class: xl68"] $ 103,600.26 [/TD]
[TD="class: xl85, align: right"]43.45%[/TD]
[TD="class: xl86, align: right"]56.55%[/TD]
[TD="class: xl85, align: right"]23.45%[/TD]
[TD="class: xl86, align: right"]-23.45%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1999[/TD]
[TD="class: xl87, align: right"]19.10%[/TD]
[TD="class: xl88, align: right"]-0.82%[/TD]
[TD="class: xl83"] $ 8,596.02 [/TD]
[TD="class: xl84"] $ (483.17)[/TD]
[TD="class: xl83"] $ 53,609.95 [/TD]
[TD="class: xl84"] $ 58,103.17 [/TD]
[TD="class: xl68"] $ 111,713.12 [/TD]
[TD="class: xl85, align: right"]47.99%[/TD]
[TD="class: xl86, align: right"]52.01%[/TD]
[TD="class: xl85, align: right"]27.99%[/TD]
[TD="class: xl86, align: right"]-27.99%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2000[/TD]
[TD="class: xl87, align: right"]-9.55%[/TD]
[TD="class: xl88, align: right"]11.00%[/TD]
[TD="class: xl83"] $ (5,117.52)[/TD]
[TD="class: xl84"] $ 6,390.44 [/TD]
[TD="class: xl83"] $ 48,492.42 [/TD]
[TD="class: xl84"] $ 64,493.61 [/TD]
[TD="class: xl68"] $ 112,986.04 [/TD]
[TD="class: xl85, align: right"]42.92%[/TD]
[TD="class: xl86, align: right"]57.08%[/TD]
[TD="class: xl85, align: right"]22.92%[/TD]
[TD="class: xl86, align: right"]-22.92%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2001[/TD]
[TD="class: xl87, align: right"]-12.65%[/TD]
[TD="class: xl88, align: right"]8.11%[/TD]
[TD="class: xl83"] $ (6,136.08)[/TD]
[TD="class: xl84"] $ 5,227.78 [/TD]
[TD="class: xl83"] $ 42,356.35 [/TD]
[TD="class: xl84"] $ 69,721.39 [/TD]
[TD="class: xl68"] $ 112,077.74 [/TD]
[TD="class: xl85, align: right"]37.79%[/TD]
[TD="class: xl86, align: right"]62.21%[/TD]
[TD="class: xl85, align: right"]17.79%[/TD]
[TD="class: xl86, align: right"]-17.79%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2002[/TD]
[TD="class: xl87, align: right"]-24.98%[/TD]
[TD="class: xl88, align: right"]9.76%[/TD]
[TD="class: xl83, align: center"]##########[/TD]
[TD="class: xl84"] $ 6,806.62 [/TD]
[TD="class: xl83"] $ 31,777.82 [/TD]
[TD="class: xl84"] $ 76,528.01 [/TD]
[TD="class: xl68"] $ 108,305.83 [/TD]
[TD="class: xl85, align: right"]29.34%[/TD]
[TD="class: xl86, align: right"]70.66%[/TD]
[TD="class: xl85, align: right"]9.34%[/TD]
[TD="class: xl86, align: right"]-9.34%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2003[/TD]
[TD="class: xl87, align: right"]25.22%[/TD]
[TD="class: xl88, align: right"]4.02%[/TD]
[TD="class: xl83"] $ 8,014.16 [/TD]
[TD="class: xl84"] $ 3,078.30 [/TD]
[TD="class: xl83"] $ 39,791.98 [/TD]
[TD="class: xl84"] $ 79,606.31 [/TD]
[TD="class: xl68"] $ 119,398.29 [/TD]
[TD="class: xl85, align: right"]33.33%[/TD]
[TD="class: xl86, align: right"]66.67%[/TD]
[TD="class: xl85, align: right"]13.33%[/TD]
[TD="class: xl86, align: right"]-13.33%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2004[/TD]
[TD="class: xl87, align: right"]10.33%[/TD]
[TD="class: xl88, align: right"]4.25%[/TD]
[TD="class: xl83"] $ 4,110.39 [/TD]
[TD="class: xl84"] $ 3,381.12 [/TD]
[TD="class: xl83"] $ 43,902.37 [/TD]
[TD="class: xl84"] $ 82,987.43 [/TD]
[TD="class: xl68"] $ 126,889.80 [/TD]
[TD="class: xl85, align: right"]34.60%[/TD]
[TD="class: xl86, align: right"]65.40%[/TD]
[TD="class: xl85, align: right"]14.60%[/TD]
[TD="class: xl86, align: right"]-14.60%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2005[/TD]
[TD="class: xl87, align: right"]4.80%[/TD]
[TD="class: xl88, align: right"]2.40%[/TD]
[TD="class: xl83"] $ 2,105.18 [/TD]
[TD="class: xl84"] $ 1,991.29 [/TD]
[TD="class: xl83"] $ 46,007.55 [/TD]
[TD="class: xl84"] $ 84,978.72 [/TD]
[TD="class: xl68"] $ 130,986.27 [/TD]
[TD="class: xl85, align: right"]35.12%[/TD]
[TD="class: xl86, align: right"]64.88%[/TD]
[TD="class: xl85, align: right"]15.12%[/TD]
[TD="class: xl86, align: right"]-15.12%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2006[/TD]
[TD="class: xl87, align: right"]14.66%[/TD]
[TD="class: xl88, align: right"]4.24%[/TD]
[TD="class: xl83"] $ 6,746.77 [/TD]
[TD="class: xl84"] $ 3,605.21 [/TD]
[TD="class: xl83"] $ 52,754.32 [/TD]
[TD="class: xl84"] $ 88,583.93 [/TD]
[TD="class: xl68"] $ 141,338.25 [/TD]
[TD="class: xl85, align: right"]37.32%[/TD]
[TD="class: xl86, align: right"]62.68%[/TD]
[TD="class: xl85, align: right"]17.32%[/TD]
[TD="class: xl86, align: right"]-17.32%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2007[/TD]
[TD="class: xl87, align: right"]5.35%[/TD]
[TD="class: xl88, align: right"]6.73%[/TD]
[TD="class: xl83"] $ 2,821.49 [/TD]
[TD="class: xl84"] $ 5,965.83 [/TD]
[TD="class: xl83"] $ 55,575.82 [/TD]
[TD="class: xl84"] $ 94,549.76 [/TD]
[TD="class: xl68"] $ 150,125.58 [/TD]
[TD="class: xl85, align: right"]37.02%[/TD]
[TD="class: xl86, align: right"]62.98%[/TD]
[TD="class: xl85, align: right"]17.02%[/TD]
[TD="class: xl86, align: right"]-17.02%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2008[/TD]
[TD="class: xl87, align: right"]-46.20%[/TD]
[TD="class: xl88, align: right"]5.11%[/TD]
[TD="class: xl83, align: center"]##########[/TD]
[TD="class: xl84"] $ 4,829.18 [/TD]
[TD="class: xl83"] $ 29,899.73 [/TD]
[TD="class: xl84"] $ 99,378.94 [/TD]
[TD="class: xl68"] $ 129,278.67 [/TD]
[TD="class: xl85, align: right"]23.13%[/TD]
[TD="class: xl86, align: right"]76.87%[/TD]
[TD="class: xl85, align: right"]3.13%[/TD]
[TD="class: xl86, align: right"]-3.13%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2009[/TD]
[TD="class: xl87, align: right"]23.48%[/TD]
[TD="class: xl88, align: right"]5.76%[/TD]
[TD="class: xl83"] $ 7,020.20 [/TD]
[TD="class: xl84"] $ 5,725.43 [/TD]
[TD="class: xl83"] $ 36,919.93 [/TD]
[TD="class: xl84"] $ 105,104.37 [/TD]
[TD="class: xl68"] $ 142,024.30 [/TD]
[TD="class: xl85, align: right"]26.00%[/TD]
[TD="class: xl86, align: right"]74.00%[/TD]
[TD="class: xl85, align: right"]6.00%[/TD]
[TD="class: xl86, align: right"]-6.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2010[/TD]
[TD="class: xl87, align: right"]14.03%[/TD]
[TD="class: xl88, align: right"]6.34%[/TD]
[TD="class: xl83"] $ 5,180.34 [/TD]
[TD="class: xl84"] $ 6,660.08 [/TD]
[TD="class: xl83"] $ 42,100.27 [/TD]
[TD="class: xl84"] $ 111,764.46 [/TD]
[TD="class: xl68"] $ 153,864.72 [/TD]
[TD="class: xl85, align: right"]27.36%[/TD]
[TD="class: xl86, align: right"]72.64%[/TD]
[TD="class: xl85, align: right"]7.36%[/TD]
[TD="class: xl86, align: right"]-7.36%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2011[/TD]
[TD="class: xl87, align: right"]2.09%[/TD]
[TD="class: xl88, align: right"]7.55%[/TD]
[TD="class: xl83"] $ 879.81 [/TD]
[TD="class: xl84"] $ 8,437.69 [/TD]
[TD="class: xl83"] $ 42,980.08 [/TD]
[TD="class: xl84"] $ 120,202.15 [/TD]
[TD="class: xl68"] $ 163,182.22 [/TD]
[TD="class: xl85, align: right"]26.34%[/TD]
[TD="class: xl86, align: right"]73.66%[/TD]
[TD="class: xl85, align: right"]6.34%[/TD]
[TD="class: xl86, align: right"]-6.34%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2012[/TD]
[TD="class: xl87, align: right"]14.85%[/TD]
[TD="class: xl88, align: right"]4.13%[/TD]
[TD="class: xl83"] $ 6,380.40 [/TD]
[TD="class: xl84"] $ 4,962.46 [/TD]
[TD="class: xl83"] $ 49,360.47 [/TD]
[TD="class: xl84"] $ 125,164.60 [/TD]
[TD="class: xl68"] $ 174,525.08 [/TD]
[TD="class: xl85, align: right"]28.28%[/TD]
[TD="class: xl86, align: right"]71.72%[/TD]
[TD="class: xl85, align: right"]8.28%[/TD]
[TD="class: xl86, align: right"]-8.28%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2013[/TD]
[TD="class: xl87, align: right"]28.06%[/TD]
[TD="class: xl88, align: right"]-2.04%[/TD]
[TD="class: xl83"] $ 13,848.96 [/TD]
[TD="class: xl84"] $ (2,558.93)[/TD]
[TD="class: xl83"] $ 63,209.43 [/TD]
[TD="class: xl84"] $ 122,605.67 [/TD]
[TD="class: xl68"] $ 185,815.10 [/TD]
[TD="class: xl85, align: right"]34.02%[/TD]
[TD="class: xl86, align: right"]65.98%[/TD]
[TD="class: xl85, align: right"]14.02%[/TD]
[TD="class: xl86, align: right"]-14.02%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2014[/TD]
[TD="class: xl87, align: right"]12.83%[/TD]
[TD="class: xl88, align: right"]5.79%[/TD]
[TD="class: xl83"] $ 8,109.29 [/TD]
[TD="class: xl84"] $ 7,104.41 [/TD]
[TD="class: xl83"] $ 71,318.72 [/TD]
[TD="class: xl84"] $ 129,710.08 [/TD]
[TD="class: xl68"] $ 201,028.80 [/TD]
[TD="class: xl85, align: right"]35.48%[/TD]
[TD="class: xl86, align: right"]64.52%[/TD]
[TD="class: xl85, align: right"]15.48%[/TD]
[TD="class: xl86, align: right"]-15.48%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2015[/TD]
[TD="class: xl87, align: right"]1.37%[/TD]
[TD="class: xl88, align: right"]0.55%[/TD]
[TD="class: xl83"] $ 980.17 [/TD]
[TD="class: xl84"] $ 711.39 [/TD]
[TD="class: xl83"] $ 72,298.88 [/TD]
[TD="class: xl84"] $ 130,421.47 [/TD]
[TD="class: xl68"] $ 202,720.36 [/TD]
[TD="class: xl85, align: right"]35.66%[/TD]
[TD="class: xl86, align: right"]64.34%[/TD]
[TD="class: xl85, align: right"]15.66%[/TD]
[TD="class: xl86, align: right"]-15.66%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2016[/TD]
[TD="class: xl87, align: right"]11.30%[/TD]
[TD="class: xl88, align: right"]2.61%[/TD]
[TD="class: xl83"] $ 8,167.66 [/TD]
[TD="class: xl84"] $ 3,407.96 [/TD]
[TD="class: xl83"] $ 80,466.54 [/TD]
[TD="class: xl84"] $ 133,829.44 [/TD]
[TD="class: xl68"] $ 214,295.98 [/TD]
[TD="class: xl85, align: right"]37.55%[/TD]
[TD="class: xl86, align: right"]62.45%[/TD]
[TD="class: xl85, align: right"]17.55%[/TD]
[TD="class: xl86, align: right"]-17.55%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2017[/TD]
[TD="class: xl89, align: right"]19.75%[/TD]
[TD="class: xl90, align: right"]3.48%[/TD]
[TD="class: xl83"] $ 15,889.67 [/TD]
[TD="class: xl84"] $ 4,658.01 [/TD]
[TD="class: xl83"] $ 96,356.22 [/TD]
[TD="class: xl91"] $ 138,487.44 [/TD]
[TD="class: xl68"] $ 234,843.66 [/TD]
[TD="class: xl92, align: right"]41.03%[/TD]
[TD="class: xl93, align: right"]58.97%[/TD]
[TD="class: xl92, align: right"]21.03%[/TD]
[TD="class: xl93, align: right"]-21.03%[/TD]
[/TR]
</tbody>[/TABLE]
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!
[TABLE="width: 1439"]
<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>[TR]
[TD="class: xl63, width: 124"]Investment Model[/TD]
[TD="class: xl64, width: 89, align: center"]Income[/TD]
[TD="width: 97"][/TD]
[TD="width: 92"][/TD]
[TD="class: xl65, width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 139"][/TD]
[TD="width: 99"][/TD]
[TD="width: 105"][/TD]
[TD="width: 116"][/TD]
[TD="width: 161"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Equity[/TD]
[TD="class: xl67, align: center"]20%[/TD]
[TD][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]Fixed Income[/TD]
[TD="class: xl67, align: center"]80%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl69"]Initial Investment[/TD]
[TD="class: xl70, align: center"] $10,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl72, align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl73, colspan: 2, align: center"]Index Returns[/TD]
[TD="class: xl73, colspan: 2, align: center"]Portfolio Returns[/TD]
[TD="class: xl73, colspan: 3, align: center"]Balnces[/TD]
[TD="class: xl73, colspan: 2, align: center"]No Rebalancing[/TD]
[TD="class: xl73, colspan: 2, align: center"]Differences[/TD]
[/TR]
[TR]
[TD="class: xl71"]Year[/TD]
[TD="class: xl74"]S&P 500[/TD]
[TD="class: xl75"]BBgBarc Us Agg[/TD]
[TD="class: xl76"]Equity Return[/TD]
[TD="class: xl75"]Fixed Income Return[/TD]
[TD="class: xl76"]Equity Balance[/TD]
[TD="class: xl75"]Fixed Income Balance[/TD]
[TD="class: xl77"]Total Balance[/TD]
[TD="class: xl76"]Equity %[/TD]
[TD="class: xl75"]Fixed Income %[/TD]
[TD="class: xl76"]Equity Difference[/TD]
[TD="class: xl75"]Fixed Income Difference[/TD]
[/TR]
[TR]
[TD="class: xl78"]1975[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl81"] $ - [/TD]
[TD="class: xl82"] $ - [/TD]
[TD="class: xl83"] $ 2,000.00 [/TD]
[TD="class: xl84"] $ 8,000.00 [/TD]
[TD="class: xl68"] $ 10,000.00 [/TD]
[TD="class: xl85, align: right"]20.00%[/TD]
[TD="class: xl86, align: right"]80.00%[/TD]
[TD="class: xl85, align: right"]0.00%[/TD]
[TD="class: xl86, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1976[/TD]
[TD="class: xl87, align: right"]21.45%[/TD]
[TD="class: xl88, align: right"]14.50%[/TD]
[TD="class: xl83"] $ 429.04 [/TD]
[TD="class: xl84"] $ 1,159.73 [/TD]
[TD="class: xl83"] $ 2,429.04 [/TD]
[TD="class: xl84"] $ 9,159.73 [/TD]
[TD="class: xl68"] $ 11,588.77 [/TD]
[TD="class: xl85, align: right"]20.96%[/TD]
[TD="class: xl86, align: right"]79.04%[/TD]
[TD="class: xl85, align: right"]0.96%[/TD]
[TD="class: xl86, align: right"]-0.96%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1977[/TD]
[TD="class: xl87, align: right"]-7.43%[/TD]
[TD="class: xl88, align: right"]2.99%[/TD]
[TD="class: xl83"] $ (180.39)[/TD]
[TD="class: xl84"] $ 273.98 [/TD]
[TD="class: xl83"] $ 2,248.65 [/TD]
[TD="class: xl84"] $ 9,433.71 [/TD]
[TD="class: xl68"] $ 11,682.36 [/TD]
[TD="class: xl85, align: right"]19.25%[/TD]
[TD="class: xl86, align: right"]80.75%[/TD]
[TD="class: xl85, align: right"]-0.75%[/TD]
[TD="class: xl86, align: right"]0.75%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1978[/TD]
[TD="class: xl87, align: right"]6.36%[/TD]
[TD="class: xl88, align: right"]1.38%[/TD]
[TD="class: xl83"] $ 143.10 [/TD]
[TD="class: xl84"] $ 130.57 [/TD]
[TD="class: xl83"] $ 2,391.76 [/TD]
[TD="class: xl84"] $ 9,564.27 [/TD]
[TD="class: xl68"] $ 11,956.03 [/TD]
[TD="class: xl85, align: right"]20.00%[/TD]
[TD="class: xl86, align: right"]80.00%[/TD]
[TD="class: xl85, align: right"]0.00%[/TD]
[TD="class: xl86, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1979[/TD]
[TD="class: xl87, align: right"]17.07%[/TD]
[TD="class: xl88, align: right"]1.91%[/TD]
[TD="class: xl83"] $ 408.19 [/TD]
[TD="class: xl84"] $ 182.76 [/TD]
[TD="class: xl83"] $ 2,799.94 [/TD]
[TD="class: xl84"] $ 9,747.04 [/TD]
[TD="class: xl68"] $ 12,546.98 [/TD]
[TD="class: xl85, align: right"]22.32%[/TD]
[TD="class: xl86, align: right"]77.68%[/TD]
[TD="class: xl85, align: right"]2.32%[/TD]
[TD="class: xl86, align: right"]-2.32%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1980[/TD]
[TD="class: xl87, align: right"]28.14%[/TD]
[TD="class: xl88, align: right"]2.67%[/TD]
[TD="class: xl83"] $ 788.02 [/TD]
[TD="class: xl84"] $ 260.17 [/TD]
[TD="class: xl83"] $ 3,587.97 [/TD]
[TD="class: xl84"] $ 10,007.20 [/TD]
[TD="class: xl68"] $ 13,595.17 [/TD]
[TD="class: xl85, align: right"]26.39%[/TD]
[TD="class: xl86, align: right"]73.61%[/TD]
[TD="class: xl85, align: right"]6.39%[/TD]
[TD="class: xl86, align: right"]-6.39%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1981[/TD]
[TD="class: xl87, align: right"]-5.05%[/TD]
[TD="class: xl88, align: right"]6.06%[/TD]
[TD="class: xl83"] $ (181.13)[/TD]
[TD="class: xl84"] $ 606.54 [/TD]
[TD="class: xl83"] $ 3,406.83 [/TD]
[TD="class: xl84"] $ 10,613.75 [/TD]
[TD="class: xl68"] $ 14,020.58 [/TD]
[TD="class: xl85, align: right"]24.30%[/TD]
[TD="class: xl86, align: right"]75.70%[/TD]
[TD="class: xl85, align: right"]4.30%[/TD]
[TD="class: xl86, align: right"]-4.30%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1982[/TD]
[TD="class: xl87, align: right"]19.51%[/TD]
[TD="class: xl88, align: right"]28.23%[/TD]
[TD="class: xl83"] $ 664.76 [/TD]
[TD="class: xl84"] $ 2,996.54 [/TD]
[TD="class: xl83"] $ 4,071.59 [/TD]
[TD="class: xl84"] $ 13,610.29 [/TD]
[TD="class: xl68"] $ 17,681.88 [/TD]
[TD="class: xl85, align: right"]23.03%[/TD]
[TD="class: xl86, align: right"]76.97%[/TD]
[TD="class: xl85, align: right"]3.03%[/TD]
[TD="class: xl86, align: right"]-3.03%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1983[/TD]
[TD="class: xl87, align: right"]20.34%[/TD]
[TD="class: xl88, align: right"]8.03%[/TD]
[TD="class: xl83"] $ 828.13 [/TD]
[TD="class: xl84"] $ 1,092.52 [/TD]
[TD="class: xl83"] $ 4,899.72 [/TD]
[TD="class: xl84"] $ 14,702.81 [/TD]
[TD="class: xl68"] $ 19,602.53 [/TD]
[TD="class: xl85, align: right"]25.00%[/TD]
[TD="class: xl86, align: right"]75.00%[/TD]
[TD="class: xl85, align: right"]5.00%[/TD]
[TD="class: xl86, align: right"]-5.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1984[/TD]
[TD="class: xl87, align: right"]6.08%[/TD]
[TD="class: xl88, align: right"]14.10%[/TD]
[TD="class: xl83"] $ 298.13 [/TD]
[TD="class: xl84"] $ 2,073.69 [/TD]
[TD="class: xl83"] $ 5,197.86 [/TD]
[TD="class: xl84"] $ 16,776.50 [/TD]
[TD="class: xl68"] $ 21,974.36 [/TD]
[TD="class: xl85, align: right"]23.65%[/TD]
[TD="class: xl86, align: right"]76.35%[/TD]
[TD="class: xl85, align: right"]3.65%[/TD]
[TD="class: xl86, align: right"]-3.65%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1985[/TD]
[TD="class: xl87, align: right"]27.56%[/TD]
[TD="class: xl88, align: right"]19.97%[/TD]
[TD="class: xl83"] $ 1,432.32 [/TD]
[TD="class: xl84"] $ 3,350.15 [/TD]
[TD="class: xl83"] $ 6,630.18 [/TD]
[TD="class: xl84"] $ 20,126.64 [/TD]
[TD="class: xl68"] $ 26,756.82 [/TD]
[TD="class: xl85, align: right"]24.78%[/TD]
[TD="class: xl86, align: right"]75.22%[/TD]
[TD="class: xl85, align: right"]4.78%[/TD]
[TD="class: xl86, align: right"]-4.78%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1986[/TD]
[TD="class: xl87, align: right"]17.11%[/TD]
[TD="class: xl88, align: right"]14.21%[/TD]
[TD="class: xl83"] $ 1,134.65 [/TD]
[TD="class: xl84"] $ 2,859.22 [/TD]
[TD="class: xl83"] $ 7,764.83 [/TD]
[TD="class: xl84"] $ 22,985.86 [/TD]
[TD="class: xl68"] $ 30,750.69 [/TD]
[TD="class: xl85, align: right"]25.25%[/TD]
[TD="class: xl86, align: right"]74.75%[/TD]
[TD="class: xl85, align: right"]5.25%[/TD]
[TD="class: xl86, align: right"]-5.25%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1987[/TD]
[TD="class: xl87, align: right"]5.12%[/TD]
[TD="class: xl88, align: right"]2.72%[/TD]
[TD="class: xl83"] $ 397.36 [/TD]
[TD="class: xl84"] $ 624.78 [/TD]
[TD="class: xl83"] $ 8,162.19 [/TD]
[TD="class: xl84"] $ 23,610.65 [/TD]
[TD="class: xl68"] $ 31,772.84 [/TD]
[TD="class: xl85, align: right"]25.69%[/TD]
[TD="class: xl86, align: right"]74.31%[/TD]
[TD="class: xl85, align: right"]5.69%[/TD]
[TD="class: xl86, align: right"]-5.69%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1988[/TD]
[TD="class: xl87, align: right"]15.37%[/TD]
[TD="class: xl88, align: right"]7.59%[/TD]
[TD="class: xl83"] $ 1,254.13 [/TD]
[TD="class: xl84"] $ 1,792.14 [/TD]
[TD="class: xl83"] $ 9,416.32 [/TD]
[TD="class: xl84"] $ 25,402.79 [/TD]
[TD="class: xl68"] $ 34,819.11 [/TD]
[TD="class: xl85, align: right"]27.04%[/TD]
[TD="class: xl86, align: right"]72.96%[/TD]
[TD="class: xl85, align: right"]7.04%[/TD]
[TD="class: xl86, align: right"]-7.04%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1989[/TD]
[TD="class: xl87, align: right"]27.53%[/TD]
[TD="class: xl88, align: right"]13.57%[/TD]
[TD="class: xl83"] $ 2,591.86 [/TD]
[TD="class: xl84"] $ 3,446.21 [/TD]
[TD="class: xl83"] $ 12,008.18 [/TD]
[TD="class: xl84"] $ 28,849.00 [/TD]
[TD="class: xl68"] $ 40,857.18 [/TD]
[TD="class: xl85, align: right"]29.39%[/TD]
[TD="class: xl86, align: right"]70.61%[/TD]
[TD="class: xl85, align: right"]9.39%[/TD]
[TD="class: xl86, align: right"]-9.39%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1990[/TD]
[TD="class: xl87, align: right"]-3.15%[/TD]
[TD="class: xl88, align: right"]8.58%[/TD]
[TD="class: xl83"] $ (378.67)[/TD]
[TD="class: xl84"] $ 2,475.61 [/TD]
[TD="class: xl83"] $ 11,629.51 [/TD]
[TD="class: xl84"] $ 31,324.61 [/TD]
[TD="class: xl68"] $ 42,954.12 [/TD]
[TD="class: xl85, align: right"]27.07%[/TD]
[TD="class: xl86, align: right"]72.93%[/TD]
[TD="class: xl85, align: right"]7.07%[/TD]
[TD="class: xl86, align: right"]-7.07%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1991[/TD]
[TD="class: xl87, align: right"]26.59%[/TD]
[TD="class: xl88, align: right"]14.84%[/TD]
[TD="class: xl83"] $ 3,092.76 [/TD]
[TD="class: xl84"] $ 4,650.05 [/TD]
[TD="class: xl83"] $ 14,722.27 [/TD]
[TD="class: xl84"] $ 35,974.66 [/TD]
[TD="class: xl68"] $ 50,696.93 [/TD]
[TD="class: xl85, align: right"]29.04%[/TD]
[TD="class: xl86, align: right"]70.96%[/TD]
[TD="class: xl85, align: right"]9.04%[/TD]
[TD="class: xl86, align: right"]-9.04%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1992[/TD]
[TD="class: xl87, align: right"]7.34%[/TD]
[TD="class: xl88, align: right"]7.14%[/TD]
[TD="class: xl83"] $ 1,081.07 [/TD]
[TD="class: xl84"] $ 2,568.95 [/TD]
[TD="class: xl83"] $ 15,803.34 [/TD]
[TD="class: xl84"] $ 38,543.61 [/TD]
[TD="class: xl68"] $ 54,346.95 [/TD]
[TD="class: xl85, align: right"]29.08%[/TD]
[TD="class: xl86, align: right"]70.92%[/TD]
[TD="class: xl85, align: right"]9.08%[/TD]
[TD="class: xl86, align: right"]-9.08%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1993[/TD]
[TD="class: xl87, align: right"]9.60%[/TD]
[TD="class: xl88, align: right"]9.30%[/TD]
[TD="class: xl83"] $ 1,517.52 [/TD]
[TD="class: xl84"] $ 3,585.60 [/TD]
[TD="class: xl83"] $ 17,320.86 [/TD]
[TD="class: xl84"] $ 42,129.21 [/TD]
[TD="class: xl68"] $ 59,450.07 [/TD]
[TD="class: xl85, align: right"]29.14%[/TD]
[TD="class: xl86, align: right"]70.86%[/TD]
[TD="class: xl85, align: right"]9.14%[/TD]
[TD="class: xl86, align: right"]-9.14%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1994[/TD]
[TD="class: xl87, align: right"]1.31%[/TD]
[TD="class: xl88, align: right"]-2.96%[/TD]
[TD="class: xl83"] $ 227.22 [/TD]
[TD="class: xl84"] $ (1,246.82)[/TD]
[TD="class: xl83"] $ 17,548.08 [/TD]
[TD="class: xl84"] $ 40,882.39 [/TD]
[TD="class: xl68"] $ 58,430.47 [/TD]
[TD="class: xl85, align: right"]30.03%[/TD]
[TD="class: xl86, align: right"]69.97%[/TD]
[TD="class: xl85, align: right"]10.03%[/TD]
[TD="class: xl86, align: right"]-10.03%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1995[/TD]
[TD="class: xl87, align: right"]31.90%[/TD]
[TD="class: xl88, align: right"]16.95%[/TD]
[TD="class: xl83"] $ 5,598.17 [/TD]
[TD="class: xl84"] $ 6,930.43 [/TD]
[TD="class: xl83"] $ 23,146.25 [/TD]
[TD="class: xl84"] $ 47,812.82 [/TD]
[TD="class: xl68"] $ 70,959.08 [/TD]
[TD="class: xl85, align: right"]32.62%[/TD]
[TD="class: xl86, align: right"]67.38%[/TD]
[TD="class: xl85, align: right"]12.62%[/TD]
[TD="class: xl86, align: right"]-12.62%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1996[/TD]
[TD="class: xl87, align: right"]20.67%[/TD]
[TD="class: xl88, align: right"]3.57%[/TD]
[TD="class: xl83"] $ 4,784.11 [/TD]
[TD="class: xl84"] $ 1,705.11 [/TD]
[TD="class: xl83"] $ 27,930.36 [/TD]
[TD="class: xl84"] $ 49,517.94 [/TD]
[TD="class: xl68"] $ 77,448.30 [/TD]
[TD="class: xl85, align: right"]36.06%[/TD]
[TD="class: xl86, align: right"]63.94%[/TD]
[TD="class: xl85, align: right"]16.06%[/TD]
[TD="class: xl86, align: right"]-16.06%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1997[/TD]
[TD="class: xl87, align: right"]28.79%[/TD]
[TD="class: xl88, align: right"]9.22%[/TD]
[TD="class: xl83"] $ 8,041.36 [/TD]
[TD="class: xl84"] $ 4,563.54 [/TD]
[TD="class: xl83"] $ 35,971.71 [/TD]
[TD="class: xl84"] $ 54,081.48 [/TD]
[TD="class: xl68"] $ 90,053.20 [/TD]
[TD="class: xl85, align: right"]39.94%[/TD]
[TD="class: xl86, align: right"]60.06%[/TD]
[TD="class: xl85, align: right"]19.94%[/TD]
[TD="class: xl86, align: right"]-19.94%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1998[/TD]
[TD="class: xl87, align: right"]25.14%[/TD]
[TD="class: xl88, align: right"]8.33%[/TD]
[TD="class: xl83"] $ 9,042.21 [/TD]
[TD="class: xl84"] $ 4,504.85 [/TD]
[TD="class: xl83"] $ 45,013.93 [/TD]
[TD="class: xl84"] $ 58,586.34 [/TD]
[TD="class: xl68"] $ 103,600.26 [/TD]
[TD="class: xl85, align: right"]43.45%[/TD]
[TD="class: xl86, align: right"]56.55%[/TD]
[TD="class: xl85, align: right"]23.45%[/TD]
[TD="class: xl86, align: right"]-23.45%[/TD]
[/TR]
[TR]
[TD="class: xl78"]1999[/TD]
[TD="class: xl87, align: right"]19.10%[/TD]
[TD="class: xl88, align: right"]-0.82%[/TD]
[TD="class: xl83"] $ 8,596.02 [/TD]
[TD="class: xl84"] $ (483.17)[/TD]
[TD="class: xl83"] $ 53,609.95 [/TD]
[TD="class: xl84"] $ 58,103.17 [/TD]
[TD="class: xl68"] $ 111,713.12 [/TD]
[TD="class: xl85, align: right"]47.99%[/TD]
[TD="class: xl86, align: right"]52.01%[/TD]
[TD="class: xl85, align: right"]27.99%[/TD]
[TD="class: xl86, align: right"]-27.99%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2000[/TD]
[TD="class: xl87, align: right"]-9.55%[/TD]
[TD="class: xl88, align: right"]11.00%[/TD]
[TD="class: xl83"] $ (5,117.52)[/TD]
[TD="class: xl84"] $ 6,390.44 [/TD]
[TD="class: xl83"] $ 48,492.42 [/TD]
[TD="class: xl84"] $ 64,493.61 [/TD]
[TD="class: xl68"] $ 112,986.04 [/TD]
[TD="class: xl85, align: right"]42.92%[/TD]
[TD="class: xl86, align: right"]57.08%[/TD]
[TD="class: xl85, align: right"]22.92%[/TD]
[TD="class: xl86, align: right"]-22.92%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2001[/TD]
[TD="class: xl87, align: right"]-12.65%[/TD]
[TD="class: xl88, align: right"]8.11%[/TD]
[TD="class: xl83"] $ (6,136.08)[/TD]
[TD="class: xl84"] $ 5,227.78 [/TD]
[TD="class: xl83"] $ 42,356.35 [/TD]
[TD="class: xl84"] $ 69,721.39 [/TD]
[TD="class: xl68"] $ 112,077.74 [/TD]
[TD="class: xl85, align: right"]37.79%[/TD]
[TD="class: xl86, align: right"]62.21%[/TD]
[TD="class: xl85, align: right"]17.79%[/TD]
[TD="class: xl86, align: right"]-17.79%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2002[/TD]
[TD="class: xl87, align: right"]-24.98%[/TD]
[TD="class: xl88, align: right"]9.76%[/TD]
[TD="class: xl83, align: center"]##########[/TD]
[TD="class: xl84"] $ 6,806.62 [/TD]
[TD="class: xl83"] $ 31,777.82 [/TD]
[TD="class: xl84"] $ 76,528.01 [/TD]
[TD="class: xl68"] $ 108,305.83 [/TD]
[TD="class: xl85, align: right"]29.34%[/TD]
[TD="class: xl86, align: right"]70.66%[/TD]
[TD="class: xl85, align: right"]9.34%[/TD]
[TD="class: xl86, align: right"]-9.34%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2003[/TD]
[TD="class: xl87, align: right"]25.22%[/TD]
[TD="class: xl88, align: right"]4.02%[/TD]
[TD="class: xl83"] $ 8,014.16 [/TD]
[TD="class: xl84"] $ 3,078.30 [/TD]
[TD="class: xl83"] $ 39,791.98 [/TD]
[TD="class: xl84"] $ 79,606.31 [/TD]
[TD="class: xl68"] $ 119,398.29 [/TD]
[TD="class: xl85, align: right"]33.33%[/TD]
[TD="class: xl86, align: right"]66.67%[/TD]
[TD="class: xl85, align: right"]13.33%[/TD]
[TD="class: xl86, align: right"]-13.33%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2004[/TD]
[TD="class: xl87, align: right"]10.33%[/TD]
[TD="class: xl88, align: right"]4.25%[/TD]
[TD="class: xl83"] $ 4,110.39 [/TD]
[TD="class: xl84"] $ 3,381.12 [/TD]
[TD="class: xl83"] $ 43,902.37 [/TD]
[TD="class: xl84"] $ 82,987.43 [/TD]
[TD="class: xl68"] $ 126,889.80 [/TD]
[TD="class: xl85, align: right"]34.60%[/TD]
[TD="class: xl86, align: right"]65.40%[/TD]
[TD="class: xl85, align: right"]14.60%[/TD]
[TD="class: xl86, align: right"]-14.60%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2005[/TD]
[TD="class: xl87, align: right"]4.80%[/TD]
[TD="class: xl88, align: right"]2.40%[/TD]
[TD="class: xl83"] $ 2,105.18 [/TD]
[TD="class: xl84"] $ 1,991.29 [/TD]
[TD="class: xl83"] $ 46,007.55 [/TD]
[TD="class: xl84"] $ 84,978.72 [/TD]
[TD="class: xl68"] $ 130,986.27 [/TD]
[TD="class: xl85, align: right"]35.12%[/TD]
[TD="class: xl86, align: right"]64.88%[/TD]
[TD="class: xl85, align: right"]15.12%[/TD]
[TD="class: xl86, align: right"]-15.12%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2006[/TD]
[TD="class: xl87, align: right"]14.66%[/TD]
[TD="class: xl88, align: right"]4.24%[/TD]
[TD="class: xl83"] $ 6,746.77 [/TD]
[TD="class: xl84"] $ 3,605.21 [/TD]
[TD="class: xl83"] $ 52,754.32 [/TD]
[TD="class: xl84"] $ 88,583.93 [/TD]
[TD="class: xl68"] $ 141,338.25 [/TD]
[TD="class: xl85, align: right"]37.32%[/TD]
[TD="class: xl86, align: right"]62.68%[/TD]
[TD="class: xl85, align: right"]17.32%[/TD]
[TD="class: xl86, align: right"]-17.32%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2007[/TD]
[TD="class: xl87, align: right"]5.35%[/TD]
[TD="class: xl88, align: right"]6.73%[/TD]
[TD="class: xl83"] $ 2,821.49 [/TD]
[TD="class: xl84"] $ 5,965.83 [/TD]
[TD="class: xl83"] $ 55,575.82 [/TD]
[TD="class: xl84"] $ 94,549.76 [/TD]
[TD="class: xl68"] $ 150,125.58 [/TD]
[TD="class: xl85, align: right"]37.02%[/TD]
[TD="class: xl86, align: right"]62.98%[/TD]
[TD="class: xl85, align: right"]17.02%[/TD]
[TD="class: xl86, align: right"]-17.02%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2008[/TD]
[TD="class: xl87, align: right"]-46.20%[/TD]
[TD="class: xl88, align: right"]5.11%[/TD]
[TD="class: xl83, align: center"]##########[/TD]
[TD="class: xl84"] $ 4,829.18 [/TD]
[TD="class: xl83"] $ 29,899.73 [/TD]
[TD="class: xl84"] $ 99,378.94 [/TD]
[TD="class: xl68"] $ 129,278.67 [/TD]
[TD="class: xl85, align: right"]23.13%[/TD]
[TD="class: xl86, align: right"]76.87%[/TD]
[TD="class: xl85, align: right"]3.13%[/TD]
[TD="class: xl86, align: right"]-3.13%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2009[/TD]
[TD="class: xl87, align: right"]23.48%[/TD]
[TD="class: xl88, align: right"]5.76%[/TD]
[TD="class: xl83"] $ 7,020.20 [/TD]
[TD="class: xl84"] $ 5,725.43 [/TD]
[TD="class: xl83"] $ 36,919.93 [/TD]
[TD="class: xl84"] $ 105,104.37 [/TD]
[TD="class: xl68"] $ 142,024.30 [/TD]
[TD="class: xl85, align: right"]26.00%[/TD]
[TD="class: xl86, align: right"]74.00%[/TD]
[TD="class: xl85, align: right"]6.00%[/TD]
[TD="class: xl86, align: right"]-6.00%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2010[/TD]
[TD="class: xl87, align: right"]14.03%[/TD]
[TD="class: xl88, align: right"]6.34%[/TD]
[TD="class: xl83"] $ 5,180.34 [/TD]
[TD="class: xl84"] $ 6,660.08 [/TD]
[TD="class: xl83"] $ 42,100.27 [/TD]
[TD="class: xl84"] $ 111,764.46 [/TD]
[TD="class: xl68"] $ 153,864.72 [/TD]
[TD="class: xl85, align: right"]27.36%[/TD]
[TD="class: xl86, align: right"]72.64%[/TD]
[TD="class: xl85, align: right"]7.36%[/TD]
[TD="class: xl86, align: right"]-7.36%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2011[/TD]
[TD="class: xl87, align: right"]2.09%[/TD]
[TD="class: xl88, align: right"]7.55%[/TD]
[TD="class: xl83"] $ 879.81 [/TD]
[TD="class: xl84"] $ 8,437.69 [/TD]
[TD="class: xl83"] $ 42,980.08 [/TD]
[TD="class: xl84"] $ 120,202.15 [/TD]
[TD="class: xl68"] $ 163,182.22 [/TD]
[TD="class: xl85, align: right"]26.34%[/TD]
[TD="class: xl86, align: right"]73.66%[/TD]
[TD="class: xl85, align: right"]6.34%[/TD]
[TD="class: xl86, align: right"]-6.34%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2012[/TD]
[TD="class: xl87, align: right"]14.85%[/TD]
[TD="class: xl88, align: right"]4.13%[/TD]
[TD="class: xl83"] $ 6,380.40 [/TD]
[TD="class: xl84"] $ 4,962.46 [/TD]
[TD="class: xl83"] $ 49,360.47 [/TD]
[TD="class: xl84"] $ 125,164.60 [/TD]
[TD="class: xl68"] $ 174,525.08 [/TD]
[TD="class: xl85, align: right"]28.28%[/TD]
[TD="class: xl86, align: right"]71.72%[/TD]
[TD="class: xl85, align: right"]8.28%[/TD]
[TD="class: xl86, align: right"]-8.28%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2013[/TD]
[TD="class: xl87, align: right"]28.06%[/TD]
[TD="class: xl88, align: right"]-2.04%[/TD]
[TD="class: xl83"] $ 13,848.96 [/TD]
[TD="class: xl84"] $ (2,558.93)[/TD]
[TD="class: xl83"] $ 63,209.43 [/TD]
[TD="class: xl84"] $ 122,605.67 [/TD]
[TD="class: xl68"] $ 185,815.10 [/TD]
[TD="class: xl85, align: right"]34.02%[/TD]
[TD="class: xl86, align: right"]65.98%[/TD]
[TD="class: xl85, align: right"]14.02%[/TD]
[TD="class: xl86, align: right"]-14.02%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2014[/TD]
[TD="class: xl87, align: right"]12.83%[/TD]
[TD="class: xl88, align: right"]5.79%[/TD]
[TD="class: xl83"] $ 8,109.29 [/TD]
[TD="class: xl84"] $ 7,104.41 [/TD]
[TD="class: xl83"] $ 71,318.72 [/TD]
[TD="class: xl84"] $ 129,710.08 [/TD]
[TD="class: xl68"] $ 201,028.80 [/TD]
[TD="class: xl85, align: right"]35.48%[/TD]
[TD="class: xl86, align: right"]64.52%[/TD]
[TD="class: xl85, align: right"]15.48%[/TD]
[TD="class: xl86, align: right"]-15.48%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2015[/TD]
[TD="class: xl87, align: right"]1.37%[/TD]
[TD="class: xl88, align: right"]0.55%[/TD]
[TD="class: xl83"] $ 980.17 [/TD]
[TD="class: xl84"] $ 711.39 [/TD]
[TD="class: xl83"] $ 72,298.88 [/TD]
[TD="class: xl84"] $ 130,421.47 [/TD]
[TD="class: xl68"] $ 202,720.36 [/TD]
[TD="class: xl85, align: right"]35.66%[/TD]
[TD="class: xl86, align: right"]64.34%[/TD]
[TD="class: xl85, align: right"]15.66%[/TD]
[TD="class: xl86, align: right"]-15.66%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2016[/TD]
[TD="class: xl87, align: right"]11.30%[/TD]
[TD="class: xl88, align: right"]2.61%[/TD]
[TD="class: xl83"] $ 8,167.66 [/TD]
[TD="class: xl84"] $ 3,407.96 [/TD]
[TD="class: xl83"] $ 80,466.54 [/TD]
[TD="class: xl84"] $ 133,829.44 [/TD]
[TD="class: xl68"] $ 214,295.98 [/TD]
[TD="class: xl85, align: right"]37.55%[/TD]
[TD="class: xl86, align: right"]62.45%[/TD]
[TD="class: xl85, align: right"]17.55%[/TD]
[TD="class: xl86, align: right"]-17.55%[/TD]
[/TR]
[TR]
[TD="class: xl78"]2017[/TD]
[TD="class: xl89, align: right"]19.75%[/TD]
[TD="class: xl90, align: right"]3.48%[/TD]
[TD="class: xl83"] $ 15,889.67 [/TD]
[TD="class: xl84"] $ 4,658.01 [/TD]
[TD="class: xl83"] $ 96,356.22 [/TD]
[TD="class: xl91"] $ 138,487.44 [/TD]
[TD="class: xl68"] $ 234,843.66 [/TD]
[TD="class: xl92, align: right"]41.03%[/TD]
[TD="class: xl93, align: right"]58.97%[/TD]
[TD="class: xl92, align: right"]21.03%[/TD]
[TD="class: xl93, align: right"]-21.03%[/TD]
[/TR]
</tbody>[/TABLE]
