Page 1 of 1

Deluxe Version B of Calculators: More thresholds

Posted: Sun Feb 22, 2004 2:19 pm
by JWR1945
Deluxe Version B: More thresholds

I have consistently found that the best choice for the lowest threshold is 100% stocks. For the highest threshold, it is 0% stocks. That leaves us with only one threshold to vary and two allocations.

With this change, you get two additional thresholds (for a total of four) and three allocations to vary. The allocation below the lowest threshold is always 100% stocks. The allocation above the highest threshold is always 0% stocks. This totals five allocations, three of which you can vary.

Doing it this way allows me to leave most of the user interface unchanged.

These are my preliminary changes.

Copy an earlier version of the switching calculator that includes three allocation levels. Such versions include calculations on row 2550. I chose to copy the JanSz-Chips Deluxe V1.0A version.

1) I changed cell E1 from V1.0A to V1.0B: JanSz-Chips Deluxe V1.0B.
2) I cut cell C19 and pasted it on cell A19: PE Ratio Criteria for Switch:.
3) I wrote '= Lowest Threshold into cell C19. [The apostrophe is important. If you copy and paste, it may be unnecessary. If you get a #NAME? error, you need to put it back in.]
4) I wrote Low = into cell E19. I clicked the checkmark. I clicked Format, then Cells, then Alignment, then Right. Finally, I clicked the checkmark. This is a two step process.
5) I replaced the contents of cell G19 with Middle =. I clicked the checkmark. I clicked Format, then Cells, then Alignment and then Right. Finally, I clicked the checkmark.
6) Format cells B19 and H19 to be the same as for F19 and I19. This is done by clicking on the cell, then clicking Format, then Cells, then Number, then Custom and finally on the choice just below 0.0% and 0.000%. Be sure that your selection is beneath the entry with three zeros after the decimal point. That line begins with _(*#,##0.0_);. [If you are in doubt, click cell F19 or I19, Format, Cells and Number to see its selection.] Finally, choose Font and then click Bold and Color (and click the blue square). Then, click OK.

This puts the thresholds into cells $B$19, $F$19, $H$19 and $I$19, from lowest to highest. The allocations, from lowest to highest, are in cells $B$20, $F$20 and $I$20. The stock allocation is 100% when P/E10 is less than or equal to the lowest threshold (in cell $B$19). The stock allocation is 0% when P/E10 is greater than the highest threshold (in cell $I$20). Excel reads the thresholds from left to right.

Special instructions for stocks and commercial Paper

When I first attempted to make my changes, I found that I had used too many arguments. Most likely, the number of arguments was right but the parentheses were wrong. In any event, I cut the middle portion out of my modified code and pasted it into cell C2552. To get to row 2552, press function key F5 and type a2552. Then write Intermediate Logic into cell A2552. Click cell C2552. Write =IF($H$19<B186,IF($I$19<B186,C177,+(C177*(1-$I$20)+$I$20*C2550)),+(C177*(1-$F$20)+$F$20*C2550)) and then click the checkmark.

Format cell C2552. Click cell C2552, click Format, click Cells, click number and click the word number (in a different location) as the category. I chose to use 2 decimal places and parentheses for a negative number.

Place the cursor over the right hand corner of cell C2552 to locate the fill handle. When the fat plus sign changes to narrow cross hairs, click the (left) mouse button and hold it down. Move the mouse to the right until you reach cell EK2552. This drags the formula (with adjustments according to relative locations) throughout the entire row. The cells to be changed will be highlighted (white on black instead of black on white). Release your mouse button and then click anywhere. I clicked cell EK2552. You can drag the formula in several steps, if you wish.

I wrote this in cell C182: =IF($B$19<B186,IF($F$19<B186,C2552,+(C177*(1-$B$20)+$B$20*C2550)),C2550)

I formatted cell C182 (as before with cell C2552) and I used the fill handle to drag the formula across the entire row to cell EK182. [That is, I placed the cursor over the right hand corner. I pressed down on my mouse button when the fat plus sign changed to narrow cross hairs. I moved my mouse to the right until I reached cell EK182. I lifted my mouse button. Then I clicked on cell EK182. I could have clicked anywhere.]

Special instructions for stocks and TIPS

Copy an earlier version of the switching calculator that includes three allocation levels. Such versions include calculations on row 2550. I chose to copy the JanSz-Chips Deluxe V2.0A version.

Repeat the first series of changes except to change the version from V2.0A to V2.0B.

As before, I cut the middle portion out of my modified code and pasted it into cell C2552. To get to row 2552, press function key F5 and type a2552. Then write Intermediate Logic into cell A2552. Click cell C2552. Write =IF($H$19<B186,IF($I$19<B186,C180,+(C180*(1-$I$20)+$I$20*C2550)),+(C180*(1-$F$20)+$F$20*C2550)) and then click the checkmark.

[TIPS nominal interest rates are on row 180. Commercial paper interest rates are on row 177.]

Format cell C2552. Click cell C2552, click Format, click Cells, click number and click the word number (in a different location) as the category. I chose to use 2 decimal places and parentheses for a negative number.

Place the cursor over the right hand corner of cell C2552 to locate the fill handle. When the fat plus sign changes to narrow cross hairs, click the (left) mouse button and hold it down. Move the mouse to the right until you reach cell EK2552. This drags the formula (with adjustments according to relative locations) throughout the entire row. The cells to be changed will be highlighted (white on black instead of black on white). Release your mouse button and then click anywhere. I clicked cell EK2552. You can drag the formula in several steps, if you wish.

I wrote this in cell C182: =IF($B$19<B186,IF($F$19<B186,C2552,+(C180*(1-$B$20)+$B$20*C2550)),C2550)

I formatted cell C182 (as before with cell C2552) and I used the fill handle to drag the formula across the entire row to cell EK182. [That is, I placed the cursor over the right hand corner. I pressed down on my mouse button when the fat plus sign changed to narrow cross hairs. I moved my mouse to the right until I reached cell EK182. I lifted my mouse button. Then I clicked on cell EK182. I could have clicked anywhere.]

Special instructions for stocks and ibonds

Copy an earlier version of the switching calculator that includes three allocation levels. Such versions include calculations on row 2550. I chose to copy the JanSz-Chips Deluxe V3.0A version.

Repeat the first series of changes except to change the version from V3.0A to V3.0B.

As before, I cut the middle portion out of my modified code and pasted it into cell C2552. To get to row 2552, press function key F5 and type a2552. Then write Intermediate Logic into cell A2552. Click cell C2552. Write =IF($H$19<B186,IF($I$19<B186,C181,+(C181*(1-$I$20)+$I$20*C2550)),+(C181*(1-$F$20)+$F$20*C2550)) and then click the checkmark.

[The ibonds nominal interest rates are on row 181. Commercial paper interest rates are on row 177. TIPS nominal interest rates are on row 180.]

Format cell C2552. Click cell C2552, click Format, click Cells, click number and click the word number (in a different location) as the category. I chose to use 2 decimal places and parentheses for a negative number.

Place the cursor over the right hand corner of cell C2552 to locate the fill handle. When the fat plus sign changes to narrow cross hairs, click the (left) mouse button and hold it down. Move the mouse to the right until you reach cell EK2552. This drags the formula (with adjustments according to relative locations) throughout the entire row. The cells to be changed will be highlighted (white on black instead of black on white). Release your mouse button and then click anywhere. I clicked cell EK2552. You can drag the formula in several steps, if you wish.

I wrote this in cell C182: =IF($B$19<B186,IF($F$19<B186,C2552,+(C181*(1-$B$20)+$B$20*C2550)),C2550)

I formatted cell C182 (as before with cell C2552) and I used the fill handle to drag the formula across the entire row to cell EK182. [That is, I placed the cursor over the right hand corner. I pressed down on my mouse button when the fat plus sign changed to narrow cross hairs. I moved my mouse to the right until I reached cell EK182. I lifted my mouse button. Then I clicked on cell EK182. I could have clicked anywhere.]

Final Remarks

I have checked out the commercial paper version of these new calculators. Being able to refer to the data summary percentages (the survivability line in the Calculation Results Matrix (Nominal) box) helps this checkout immensely. I have checked out all of the thresholds and allocations (two thresholds and three allocations at a time). I used my previous two threshold, three allocation (Deluxe) version for making comparisons.

It takes just a little bit of time to get used to entering new conditions. Remember that there is always a 100% stock allocation when the threshold is below the lowest and a 0% stock allocation when the threshold is above the highest.

At first glance it appears that having two additional stock allocations reveals the granularity of the data. That is, some results change abruptly based on a single threshold. Special care may be needed in presenting results.

It may be worthwhile to introduce memory into switching decisions. Doing so would not be too difficult. It might be appropriate to include logic not to change allocations before a specified period of time, perhaps two or three years.

Have fun.

John R.