It is important to keep a watch on the progress of your trading portfolio or Self Managed Supr Fund. The best way of doint this is to see a chart of the value of the combined cash and shares in the account. The problem is that, while it is easy enough to construct a chart in Excel, the chart will be distorted by deposits to or withdrawals from the account.
The way to ovecome this problem is to convert the value of the initial account into units first and then adjust the value of the units according to the progress of the account. The value of the account will go up or down with movements of the market and and trading activity. The value of each unit will reflect this change and a chart is constructed to give a visual representation of the changes.
When funds are withdrawn or added the number of units is reduced or increased but seeing the value of each unit will most likely have changed by now the cash deposited/withdrawn is now divided by the current value of each unit to give the number of units to be added or subtracted. This way the chart will give a true reflection of the progress of your trading or investment activities.
This Excel spreadsheet is a work in progress and will require some understanding of Excel to make use of it. In time a better version will be constructed that will be easier to use.