Tracking Portfolio Returns

[thekingisdead]

Question for wiser investors than myself.


I have a diversified portfolio - all trackers; equities, bonds, property etc.

I track this in a spreadsheet, but am under no illusion I'm tracking performance in a crude manner.

The funds pay out dividends as income (not accumulation) and these typically get reinvested to rebalance the portfolio - i.e. not automatically re-invested into the fund that paid out the dividend. My somewhat basic spreadsheet as the starting value of capital invested, and the PV (including purchases made with dividends from other funds)

What is the correct way of accounting for this (performance, not tax perspective - they're in a wrapper)

Should the dividend from Fund A be included in the overall performance of Fund A?
But then if re-invested be accounted as a withdrawal and "new money" added into Fund B?

Depending how complex the answer is I may not change my recording.
Overall portfolio growth over X years I already have - even if individual fund performance is not recorded correctly / performance is masked.

[Jon39]

People have their own favourite methods, but I have used the same spreadsheet monitoring system for years.
I try to keep everything as simple as possible, but need to know weekly how my portfolio is performing against an appropriate market benchmark.

There are some imperfections which I am aware of, but they help to make everything work easily.
Although I have a property investment, I don't include that with stock market investments.

Beginning afresh at the start of each year;
Equities Total Value ........................
Nominal Cash Amount .................. (to create a round number Total Value)
Dividends Received (so far) ...........
ISA Dividends Received (so far) ....
TOTAL .............................................. ?????? +WX.YZ%

FTSE All-Share Index ...................... ????.?? +AB.CD%

The spreadsheet shows the total dividends amount (based on past 12 months by announcement), so there is a yearly increasing percentage running for that.

Link those three total percentages to a line chart and you can always see at a glance how you are doing.

Years ago, setting up the spreadsheet took some time (depends how much information you like to follow), but now it just takes 10 minutes at the end of each business week. You can then sort the spreadsheet into descending annual performance and print what becomes weekly league table if you want to. Glance at what is top and bottom and think, I could never have predicted that on 1st January. - It all seems quite a leisurely job really.

The spreadsheet runs for a year, then is copied and renamed for the following year, all three percentages starting again at 0.00%.

When making fresh purchases, deduct the cost from the Nominal Cash. That is the imperfect aspect, but it makes little difference overall.
I now have 38 annual files, which all end showing 31st December information. Rarely used after each year finishes, but sometimes handy to refer back.

Hope that might be of help. The underlying principle being, we only really need to know the overall position. Individual holding move all over the place.

[oldaudi]

I subscribe to Simply Wall Street. Add Transactions in there, predicts all of my dividends, can export to Excel. Together with lots of research features I find it good value.

When I first started I exported all of my HL transactions into a CSV file, had to make a few changes with Field Values , imported the lot into Simply Wall Street and it gave me the historical returns too. Multiple Portfolios so you can keep track of an ISA, SIPP or get an over all performance. When the dividends get reinvested in my HL platform I do then have to add the transaction into Simply Wall street so its not that automated.....

[Mr Pointy]

Have a look at this tracker:

https://www.vertex42.com/ExcelTemplates/investment...

You can create multiple tabs for each fund & there is a summary sheet: it uses the Excel XIRR formula to calculate Annualised Return. Withdrawals from one fund & deposits in another are entered in the Amount Invested column.

[LeoSayer]

I capture the following data points in a spreadsheet once a week:

- Date
- Account (ie. name and type eg. Leo ISA)
- Holding identifier (eg. Cash, VWRP, T31H etc.)
- Inflows/outflows £ total for the past week
- Holding value £ amount
- Accrued interest (only if relevant eg. for Gilts)
- Dividends (only if needed eg. for gilts, Income funds etc.)

That's all the information you need to be able to calculate performance to the most granular (individual holding) level. To do that, excel has the XIRR formula but I created my own weekly time weighted performance calculator using the method described in the link here:

https://www.investopedia.com/ask/answers/062215/ho...

You could go more complicated and do a proper valuation with prices, exchange rates and transactions but that's a lot more work.

[thekingisdead]

Thanks everyone.

Mr Pointy - that looks perfect. Just what I was after.