Dividend tracker template

[EssDeeAitch]

Hope this question is on the right forum topic,

I am creating an Excel spreadsheet to manage portfolio returns and thought someone may have already made solution. I would like to record share price change and dividend income.

Any help appreciated

[Breelander]

Hope this question is on the right forum topic,

You'd be better of asking on the Financial Software - Discussion board, home of the HYPTUSS which may already do much of what you want: https://www.lemonfool.co.uk/viewtopic.php?f=27&t=11579

[tjh290633]

For each share I have a spreadsheet. This records buys and sells and dividends received. Also the share price for each event, usually when announced for dividends although it could be when paid.

From these details I construct a cash flow, with the value of the holding at the present time being the final value. This gives me the IRR of the holding using the XIRR function. Among the columns are dividend per share and number of shares held. You can construct graphs if you wish.

The columns are:

Date
Event
Date announced (of dividends)
Date XD
Shares bought or sold
Number of shares held
Cost or receipts
Dividend per share
Dividend received
Cash flow

Cost of purchases is negative, all other items are positive.

You can add other columns if you wish.

TJH

[monabri]

I'd download HYPTUSS as a first step as this will give you share prices for all companies / ITs in your portfolio and save any labourious updating of share prices.

A separate Excel file is recommended where the main calculations are done. Do not do your calculations within the HYPTUSS sheet! ( One main reason - if a new version of HYPTUSS comes out, you don't want to be recreating calculation sheets). Let's call this the " portfolio" Excel file to differentiate it from the HYPTUSS Excel file.

So, two separate Excel files. I back them up after new buys or whenever I feel a need.

Summary Sheet
I have a summary sheet in the Portfolio file. The columns in the summary sheet comprise ( I'll list vertically here but they are in columns)

Company name
Number of shares
Share price ( from HYPTUSS)
Value of the shares for this company
Price Paid ( calculated from the XIRR sheet ...see below)
Total Return
XIRR

( There are a few other columns such as average price paid for a share)

The last 3 values are read from a separate worksheet in the Portfolio file which I call " XIRR".

XIRR Worksheet
In the XIRR worksheet, for each share I have

Company name
Column1 Column 2 Column 3
Date Price Paid in £ ( negative number) blank
Date Divi received in £ ( positive number). Divi received ( pence)
Date. Price of units sold (positive number)
.....
.....
.....
....
Today's date value of the shares held (read from the summary sheet)


At the end of this I calculate XIRR and Total Return which feed through to the summary sheet. The total return is simply the summation of column 2.


Note..as TJH says above, costs ( the cost of buying shares in company X) are recorded as negative numbers. Dividends or sales of shares are positive numbers.

If you sell a share and make a withdrawal from the portfolio, it is noted in the money in/out file.



Money In/Out
In another worksheet in the Portfolio Excel file, I record all money in and out of the " investment" portfolio.
Use this sheet to calculate a global portfolio XIRR value as it records date and monies in and out of the HYP.


Unitisation
In another worksheet the Portfolio is unitised. This is a separate topic in itself (unit values and income per unit).

Dividends
In ( yet another) worksheet I record dividends by month. I note the date they go ex-dividend, the pay date , the dividend in pence , the number of shares , the value of the dividend. I can then see a monthly profile of when divis are due.

Buys & Sells
Finally, I keep another worksheet which summarises share buys and sells. ..date, company, number of shares, price paid.I use this ( with the use of the filters in Excel) to calculate an average purchase price before costs and after costs which is fed through to the summary worksheet.

[Raptor]

This would be better posted on Financial Software. Raptor

Moderator Message:
RS: Done: with shadow left on original board.

[EssDeeAitch]

I'd download HYPTUSS as a first step as this will give you share prices for all companies / ITs in your portfolio and save any laborious updating of share prices.

A separate Excel file is recommended where the main calculations are done. Do not do your calculations within the HYPTUSS sheet! ( One main reason - if a new version of HYPTUSS comes out, you don't want to be recreating calculation sheets). Let's call this the " portfolio" Excel file to differentiate it from the HYPTUSS Excel file.

So, two separate Excel files. I back them up after new buys or whenever I feel a need.

Just to clarify, you are referring to the file hyptuss_version_11-55__excel_.zip in the HYP Top-up tab of the Financial Software? So populate and update the HYPTUSS file and link the base data to my "working" Excel file. I am OK with Excel and would not expect to run into any problems with this set up, but if I do.......

Many thanks for such a comprehensive reply

[monabri]

Yes, that's what I mean by HYPTUSS.

Note the comment about doing all calcs within your own Excel file. HYPTUSS has occasionally needed to be updated as the place it pulls down the data from has moved or changed.

I realise that the money in/out sheet could have been incorporated into the unitisation worksheet so that would have eliminated one worksheet. I didn't start to unitise until later but I kept both worksheets going.

One other thing I find HYPTUSS useful for is as a second check. I look at the value of the portfolio as a whole after making changes ( example, buying new shares). I then check that the value tallys with my main Excel file.