Spreadsheet for forecasting/modelling

Discussion in 'Sharemarket Investing Platforms, Tools & Services' started by PKFFW, 15th Oct, 2018.

Join Australia's most dynamic and respected property investment community
  1. PKFFW

    PKFFW Well-Known Member

    Joined:
    15th Mar, 2018
    Posts:
    161
    Location:
    NSW
    Hi All,

    I wanted to create a spreadsheet that would help with forecasting and for starters know nothing about excel and then also figured surely one of the creative minds at PC might have already done something similar so why try to reinvent the wheel.

    Basically I'm after something that can calculate how long it will take to get to a certain amount of dividend income or what dividend income would be achieved after X number of years. I figure for it to be useful I will need to be able to input at least the following;

    Amount of dividend income desired
    Current number of shares owned
    Current share price
    Current annual dividend income
    Current annual savings

    Other things that might also be useful;
    Expected dividend growth
    Expected share price growth
    Expected savings growth

    Ideally the spreadsheet would then work its magic and use all that information to calculate number of years to reach the amount of dividend income desired number or if I chose the amount of dividend income that I would be getting after X number of years.

    Any help greatly appreciated.
     
  2. Silverson

    Silverson Well-Known Member

    Joined:
    11th Jun, 2016
    Posts:
    458
    Location:
    Melbourne
    I'm a simple Sam and just work out how much I want in dividend income then calculate it based on a 4% yield. Once I come up with the total amount required to achieve income goal I divide it by a realistic savings rate.
    Any higher than a 4% yield and/or growing dividends are the bonus!
     
  3. PKFFW

    PKFFW Well-Known Member

    Joined:
    15th Mar, 2018
    Posts:
    161
    Location:
    NSW
    I've done the same. I just thought it might be nice to play around with some numbers to get a better idea of different scenarios. Having an excel spreadsheet that did the sums for me would make it a lot easier.
     
  4. Silverson

    Silverson Well-Known Member

    Joined:
    11th Jun, 2016
    Posts:
    458
    Location:
    Melbourne
    Yep!
    Unfortunately I'm useless with excel, almost break out in a sweat when using it
     
  5. Zenith Chaos

    Zenith Chaos Well-Known Member

    Joined:
    10th Jul, 2015
    Posts:
    1,142
    Location:
    Sydney
    The easiest way would be using the input variables to calculate returns for the next 50 years based on your current situation to see how long it takes to reach your goal.

    You can tweak the figures for different scenarios - E.g low and high to see what the extremes might be.

    The problem is the accuracy of the initial figures. I question dividend growth - is it the growth of the yield as a percentage or the value? The shorter the time horizon, the less accuracy in the results.

    The simplest way is looking at historical total shareholder returns (including dividends), historical dividend yields and then working out how long it will be until you have enough capital so that the historical dividend yield pays you what you want.

    4% is reasonable for LICs but if you diversify overseas and into bonds this number will be lower.
     
  6. dunno

    dunno Well-Known Member

    Joined:
    31st Aug, 2017
    Posts:
    587
    Location:
    .
    Hi @PKFFW

    I haven’t had a look at their early retirement calculator but have had a play with their sequence risk calculator and rate it highly.

    Go to the link, have a play and let us know what you think (from my rough skim it looks like what you may be looking for – historical data is for the USA but you can put in your own return assumptions)

    When Can I Retire? Early Retirement Calculator / FIRE Calculator - Engaging Data

    upload_2018-10-16_7-57-45.png
     
    sharon, Redwing, PKFFW and 1 other person like this.
  7. Hodor

    Hodor Well-Known Member

    Joined:
    18th Jun, 2015
    Posts:
    1,788
    Location:
    Homeless
    You have looked at lots of growth and inputs, which is fine. You also need to consider what inflation is going to do, neglecting this side of the equation is a mistake.
    Making the SS isn't overly difficult, online tools like dunno has put up take into account lots of things that you might forget and are premade so why not use them?

    There is a simple method that I like, work out your asset allocation whatever it might be and desired income. Say VAS 50% and VGS 50% and $50k pa ($25k each).
    VAS averaged just over $3 per share the last 5 years - About 8300 shares
    VGS a little under $1.80 per share for a 3 year history - About 13900 shares

    That's your target and you just tick off the shares as you accumulate. You ignore inflation, share prices, volatility and have faith that long term your returns will exceed inflation.
     
    sharon and PKFFW like this.
  8. Tony

    Tony Well-Known Member

    Joined:
    28th Jun, 2016
    Posts:
    159
    Location:
    Sydney
    Current values (VAS = $74.32 & VGS = $70.16) would mean a portfolio of $1,592,080 with total yield of 3.1% p.a running this combination. Just pointing our the reality for those hoping for 5% returns.
     
    Redwing and Hodor like this.
  9. Hodor

    Hodor Well-Known Member

    Joined:
    18th Jun, 2015
    Posts:
    1,788
    Location:
    Homeless
    Reality isn't for everyone.
     
  10. devank

    devank Well-Known Member

    Joined:
    18th Jun, 2015
    Posts:
    1,347
    Location:
    Inner West - Sydney
    PKFFW likes this.
  11. Intrigued_again

    Intrigued_again Well-Known Member

    Joined:
    4th Mar, 2016
    Posts:
    144
    Location:
    Perth
    This a rough one, but works well enough. Fill in the Blue Cells
     

    Attached Files:

    PKFFW likes this.
  12. PKFFW

    PKFFW Well-Known Member

    Joined:
    15th Mar, 2018
    Posts:
    161
    Location:
    NSW
    Thanks for the replies. A couple of good ones there to play around with and it looks like they all do pretty much wanted I wanted.
     
  13. Redwing

    Redwing Well-Known Member

    Joined:
    18th Jun, 2015
    Posts:
    3,005
    Location:
    WA
    It's over rated