Shown below are two of the five essential worksheets from the Kipling Tranche 2.2 spreadsheet. These sheets are posted to generate a discussion as to how an investor might use this new spreadsheet. The assumptions are set in the Main Menu and are duplicated in the first screenshot. For example, for this $100,000 sample portfolio I selected eight (8) for the number of Offset Portfolios. The period between offsets is 2 so we look back approximately two weeks for the tranche recommendations. Two (2) ETFs are the maximum number of assets to include in any one portfolio, except for a tie. Tranches 2 and 5 are examples where there was a tie so three ETFs are selected.
ROC1 looks back 60 trading days and the weight is set to 30%. For ROC2 the look-back period is 100 trading days with a weight of 50%. Volatility is set to 10 trading days with a weight of 20%. These assumptions match the “robust” findings from all the back-testing that took place last summer.
Tranche Recommendations: The following screenshot is no different than before. Users select their securities and number of shares in the Portfolio worksheet and those selections are linked to this tranche worksheet. Based on 10/28/2015 data, VNQ, PCY, BIV, and TLT are the ETFs of choice. The recommended number of shares is found in the Required column and the Difference column calculates the difference between the recommended number of shares and the shares currently held in the portfolio.
Position Size Worksheet: Below is the new worksheet HedgeHunter added to his earlier tranche worksheet. This addition came out of the discussion on how one might reduce or control portfolio risk that goes beyond selling when the price of the ETF drops below its 195-Day Exponential Moving Average (EMA), the performance drops below SHY or some other selling trigger.
The key decision for each investor is to fill in the salmon colored cells, particularly those found in the Stop Loss column. Based on the percentages I selected and the shares held in certain ETFs, this portfolio carries a risk of 3.9% until the next review.
I presume there will be questions so fire away. Since HedgeHunter programmed this Excel™ spreadsheet, he is the final authority as to what the various columns represent.