Software for managing investment portfolio

RD69

(Tables are in fixed fonts, but I've had to add periods "." to make them line up. Hopefully readable.)

There have been some questions about what I mean by "forecasting income and comparing it against actuals". I’ve also used the term "dividend forecast problem". because it is a generic data modelling problem.

Conceptually, it’s all fairly simple I think. The problem for me is implementing it using a spreadsheet.

Let us say that I have investments in reliable dividend paying equities, i.e. investment trusts etc. I can use the historical dividend data to forecast a dividend payment in any particular month and therefore make a prediction for cash-flow throughout the year and do some tax planning on that basis. This is how I learned to run a business, except the income was forecast on a per-customer basis.

The further out the ex-dividend date, the less reliable the forecast will be (and vice-versa).

Example:

Let's say I have 1000 shares in CTY and I can see the payment dates for the last 5 declared dividends. These are £0.045 on 29 Feb 2016, May, Aug, Nov and most recently scheduled for 28 Feb 2017.

Add some 1000 BRCI (less reliable I know)and we get £10 in Jan, Apr, Jun and Oct. We can put all this in a table, and get:

Quick Dividend Forecast
Month......Stock.......Forecast
Jan ........BRCI........£10
Feb ........CTY.........£45
Mar
Apr ........BRCI........£10
May ........CTY.........£45
... etc

We may want to smooth out our cashflow with some investments to pay dividends in Mar, Jul, and Dec, but this’ll do for now.

Add 1000 BT and we get 0.0485 in Feb and 0.096 in Sep.

Great, we can see that our Feb income is up, but where do we it in the table. Sep is easy, but we want to know that Feb is £45 + £48.

Dates down the column don’t work particularly well, so let’s rotate the table and have dates in a row.

Dividend.Forecast.Total
Stock.Jan.Feb.Mar.Apr...
BRCI..£10.........£10
CTY.......£45
BT........£48

---

......£10.£93.£0..£10

Now, I want to express a formula for the forecast, so it will calculate automatically. Therefore I need:

Dividend.Forecast
Stock.Units..Jan..Feb..Mar..Apr...
BRCI..1,000.0.01............0.01
CTY...1,000......0.045
BT....1,000......0.0485

And this means I need to add another dimension to this two dimensional table, i.e. keeping the headings.

Divided Forecast Calculation
Stock.Units..Jan..Feb..Mar..Apr...
BRCI..1,000..=f(x)..........=f(x)
CTY...1,000.......=f(x)
BT....1,000.......=f(x)

The function f(x) is whatever syntax it takes to execute the units * dividend value and this will depend on how it is implemented (i.e. another table in the same sheet, another sheet, or another file.)

This is the point at which spreadsheets stop working for me. As I add new features, I will create dependencies which I will not always notice. For example, it is natural to consider adding another table for actuals. In this table I would simply key in the amount received each month.

But in spreadsheet form, it'd be very hard to see a simple forecast/actual entry because the data would be in two different tables. So we'd need to combine them to form a single dashboard.

Dividend.Forecast.vs.Actual.
Stock.Jan.Jan.....Feb.Feb.....Mar.Mar...
......!forecast...!forecast...!forecast
..........!actual.....!actual.....!actual
BRCI..£10.£10.................
CTY...............£45.£45
BT................£48.£15

Oops looks like BT have sold off OpenReach!

Generally speaking the continued development of the structure would get harder and harder.

This is point I reached when I asked what everyone else was using.

Subsequently, I realised that I had Access as part of my Office licence and also a copy of LibreOffice Base so I spent a few hours investigating what a DB implementation would look like, and I can now produce the equivalent of:

........Forecast........Actual
January.Unit..Dividend..Unit..Dividend
..BRCI..0.01..£10.......0.01..£10
..

---

..Total.......£10.............£10
................
February
..CTY...0.045.£45.......0.045.£45
..BT....0.048.£48.......0.015.£15....*-33*

March...


And this is what I wanted.

RD69

RD69, can I ask why you are trying to 'predict' your future dividends?

Simply put, I'm trying to see if I can live on natural yield alone.

Could be retirement, but I'm a bit young for that. Could be a career change to "starving artist"

JohnRo

This is the sort of thing I do in my spread sheet, clearly there are some date related errors, historic payment days don't align with projected payment days and an undeclared future dividend has little else to go on but the dates and amounts of the last equivalent dividend in past year(s)
So without trawling through IT documentation searching for clues about predetermined future dividend schedules and payment dates I just let it be, in error, until declared and then update accordingly.

The obvious example is a future dividend projected to be paid, from historic data, on a future Sunday as shown below.

All I'm mainly using here is index match arrays and date related sumifs to pull data into the tables from a single dividend schedule data sheet of past and projected dividends.






edit: Having posted I've already spotted a few date related errors in the above, I haven't really paid much attention to the projected dividend side of things so will have to take a bit more care when cutting, pasting and dragging future dates, definitely need to tidy this aspect up now that you've prompted me to look at it.

Good luck (and thanks)

pafpcg

RD69 wrote: »

Simply put, I'm trying to see if I can live on natural yield alone.

OK, been there, done that! (Successfully!)

I assume that you've looked closely at your expenditure and want to know if your income from the portfolio will match that expenditure. Is there really any need to do an analysis over shorter time periods than annual? Obviously, there will be some months when expenditure will be higher than others, but to make the concept work you'll need a "float" in easy-access accounts of at least one year's expenditure (in my opinion). With a large enough float, you don't need to worry about monthly variations in income and expenditure, so long as it balances over the year. So just total up all the dividend income (you'll need to do this for your Tax Return anyway) and the expected expenditure for the year ahead and you'll know the answer!

If you prepare a financial calendar (one row per day) with all expected items of income and expenditure, you should be able to get an order-of-magnitude impression of your cashflow throughout the year and at what points you may need to dip into (or add to) your reserves.

The problem you face in creating a model (spreadsheet or data-base) is that it's only as good as the data which we all know is unreliable - dividend income is not guaranteed and most companies don't release the exact dividend payment dates let alone the amount to be paid until a few weeks before the due date. So any forecast is just an educated guess using past dividends and market speculation (unless you have access to detailed financial information).

Is it really worth spending time on constructing a detailed model when the data is so unreliable? But don't let me discourage you! When I took "early retirement", I spent ages developing little projects just for amusement. Then I found that local voluntary organisations had a real need for IT expertise and working there was much more rewarding......

Good luck!

RD69

OK, been there, done that! (Successfully!)

Congratulations. Just getting in sight of it is a pretty heady feeling.

I did run the same forecast last year (with an unwieldy spreadsheet) and got to with 10% of the overall outcome. But I'm aware of the no guarantee caveats.

This exercise is/was part of my year end review and tax plan for 17/18. Just identifying all the different investments and asking what are they for was useful.

Also I quite like previous poster JohnRo's concept of running this and next month.

And it was fun to do a bit of application design, but voluntary IT support may be a little while of just yet.

Regards

cloud_dog

RD69 wrote: »

Simply put, I'm trying to see if I can live on natural yield alone.

Could be retirement, but I'm a bit young for that. Could be a career change to "starving artist"

Ok. Thanks for confirming.

As mentioned I do what you are trying to achieve by using MS Money.

In my investment account(s) I have set up regular transactions based on the dividend payment frequency. The value I use is always the last confirmed payment amount.

To project my income for the next 12 months (or any time period) I have simply created a report within MS Money for the account(s) in question and listed the category (dividend).

As new payments are made I record the deposit and in the act of doing this I update the provisional value with the actual.

I'm not sure if it is as accurate as you are looking for but it is approximately correct. It does provide a history of dividend payments (and you can alter the report to break it down by account / holding etc etc).

An alternative way would be to use Excel to 'simply' (:)) work out your overall yield across investments and then use that yield figure on your total investments. Maybe more room for error here.