HYP Top-up Unitisation
TheWeeMan has suggested a structure for adding unitisation to the "FTSE-HYP Tracking" sheet on the HYP Top-up Spreadsheet.
His proposal is documented here to allow further discussion. It could potentially be added as a permanent feature to the HYP Top-up spreadsheet if this appears to be popular. Until that time, it is quite easy to copy TheWeeMan's sheet to replace the existing "FTSE-HYP Tracking" sheet. Don't change the name of the sheet, this would cause the automatic update of the HYP Portfolio value and FTSE indices to fail.
Here are TheWeeMan's notes:
His proposal is documented here to allow further discussion. It could potentially be added as a permanent feature to the HYP Top-up spreadsheet if this appears to be popular. Until that time, it is quite easy to copy TheWeeMan's sheet to replace the existing "FTSE-HYP Tracking" sheet. Don't change the name of the sheet, this would cause the automatic update of the HYP Portfolio value and FTSE indices to fail.
Here are TheWeeMan's notes:
Day one I started out with an existing portfolio so I assigned a notional unit price of 100 pounds and then worked out how many units I had from there. Row one is the only "special" row where the calcs are done differently.
Every time I add money into the portfolio I add that value in pounds in the last completed row. Ideally I would have calculated yesterdays values or even todays, but I dont religiously do that as I figure sometimes I'll "win" sometimes I'll "lose" and over time it will come out in the wash - but if you wanted to be pedantic about it then the closer to the time the money really goes in you have a valuation the more accurate. New money entered in column F calculates a new value for the number of units on the next line ready for the next time you push the valuation button on the main portfolio page. Unit price is calculated once you have a valuation for that line. My goal was simplicity I just wanted to add new money and press calculate... The sheet attached is my real sheet with the values modified by a proportion - so while its not real data, its is approximately the right relative values and trends if you see what I mean. If you really do need the full sheet and real values let me know. Ideally I would have unitised the FTSE indices to get a more realistic relative value, but for now its been fine for me as it is. I've been toying with a new column for inputting the dividends as they come in to come up with a income units after reading about it on TMF, but decided for now I'm not that interested in it but it would be relatively easy to add using the same sort of system of putting in values in the last row as they occur and working out units for the next line. I added the graphs a while ago as extra bells - not entirely necessary but it's on the sheet and I use it for ideas on the general trends over time. The only other thing I added was a simple calculation of total invested versus total return as a simple total return calc. Not that important for units. Columns E to I are the only important ones. |
|
|