Calculating original cost of share purchase after dividends?

glider3560

philng wrote: »

So a share that has paid a 5p dividend in 12 months since purchase but no growth in share price is the same return as a share that pays no dividend but 5p share increase.

I have several columns:
- Purchase price
- Current price
- Dividends
- Gain = (current price - purchase price) + dividends

EnglishMohican

I have the same problem as the OP and would love to see a good solution.

I have struggled to make sense with the XIRR function.The answer you get depends on when the last dividend was payed in. For example if you buy a share, get a dividend a few days later and do an XIRR calculation it will give a huge percentage return because it (sort of ) assumes that you will continue to get that amount every few days rather than nothing for a good while until the next dividend is due. XIRR is correct for what it knows - its just not helpful.

The solution I am attempting to make sense of is roughly what the OP is trying to do. I keep a spreadsheet that has the original cost, all dividends, any costs for purchasing more shares plus any money received for selling some of the shares on it and it keeps a running total of money out (purchases) minus money in (dividends and sales) all by date. I then guess an interest rate, calculate for each total what interest I would receive on that amount if held for the number of days that that total was valid for, add all those figures up and compare that total to the current sale value of all the shares. At the moment I have to iterate around likely interest rates until I get a match between my interest total and the difference between the current running total and the current share value. I have not succeeded in automating the iteration so far.

This method allows for the time value of money, so dividends payed sooner are worth more than dividends payed later and it allows for several tranches of purchases/sales to be combined.
It still suffers from trying to analyse too short a timescale which include a dividend payment but it is relatively easy to extend the timescale forward for 3, 6 or 12 months with no more dividends but a continuing build up of interest to correct for that.

Having written all that out, it occurs to me that that may even be the same as XIRR does - just not as visibly and therefore as controllably.

Comments (helpful) welcome.

Linton

XIRR does what the previous post describes. It calculates the equivalent average return per year so if your investment makes you 5% return in a month after purchase the annual return is about 80%/year. This is independent of whether the return comes in the form of dividends or increased price. There are no assumptions involved, it is just simple mathematics. If you want total % return return rather than average return per year just divide the total profit both in terms of dividend and increased value by your initial investment and multply by 100. You can calculate average return per month by (1+annual return/100)^(1/12)-1.