Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@.

Search
  • FIRST POST
    • Lois and CK
    • By Lois and CK 18th Oct 16, 12:02 PM
    • 537Posts
    • 719Thanks
    Lois and CK
    Have you got a spreadsheet to plan your retirement forecast and income?
    • #1
    • 18th Oct 16, 12:02 PM
    Have you got a spreadsheet to plan your retirement forecast and income? 18th Oct 16 at 12:02 PM
    Instead of buying an annuity at retirement, I'd like to do income drawdown. So I've had a stab at redoing my retirement spreadsheet to roughly forecast drawdown in retirement.

    For the time being, while I'm playing with formulas, I've assumed in retirement: annual 5% growth for my fund, 3% inflation and 2.5% state pension increase.

    I'm just looking at my spreadsheet and wondering if I've missed anything obvious though?

    In column 1 I have the date of an annual withdrawal for my income.
    In column 2 I have the fund total (before any withdrawal) + a formula to add 5% growth each year.
    In column 3 I have the annual withdrawal amount + a formula to add 3% each year for inflation.
    In column 4 I have a formula that takes away the withdrawal amount in column 3 from the pot total in column 2 to give me the pot balance in column 4 after I've taken out my annual income.

    Then when the state pension kicks in at age 67:
    I've added a column 5 where I have a formula for the SP + 2.5% annual increase.
    And subsequently in column 3 I have a new formula that reduces my pot withdrawal amount by the state pension estimate.

    I'm trying to keep it all as simple as possible - and I appreciate it's mostly finger in the air guessing at inflation and growth and so on - but have I missed anything that any of you might have included in your own forecasts/spreadsheets?
Page 1
    • AnotherJoe
    • By AnotherJoe 18th Oct 16, 1:23 PM
    • 4,152 Posts
    • 4,178 Thanks
    AnotherJoe
    • #2
    • 18th Oct 16, 1:23 PM
    • #2
    • 18th Oct 16, 1:23 PM
    Look at retireeasy. It does this but with far more bells and whistles. I think its £2 a month now unless the free version lets you do this but IMO worth it as it brings in all sorts of elements you may not have thought of. For example what about ISAs?

    Under major categories it has
    Desired Income
    Capital
    Investments
    Income Breakdown
    Home
    Expenses And within each of those, a set of subject areas such as different types of pension, ages at which different pensions come in, different types of investments such as ISA, cash, other investments, etc. Many many categories. You can also set the order in which you drawdown, for example its more advantageous taxwise to withdraw from ISAs or cash first, and so on.
    • Lois and CK
    • By Lois and CK 18th Oct 16, 1:43 PM
    • 537 Posts
    • 719 Thanks
    Lois and CK
    • #3
    • 18th Oct 16, 1:43 PM
    • #3
    • 18th Oct 16, 1:43 PM
    Thanks. I've had a look at that RetireEasy and it seems really good - thanks for the tip.

    I just realised I missed out tax so maybe using an online tool like this is safer!
    • Silvertabby
    • By Silvertabby 18th Oct 16, 2:41 PM
    • 372 Posts
    • 401 Thanks
    Silvertabby
    • #4
    • 18th Oct 16, 2:41 PM
    • #4
    • 18th Oct 16, 2:41 PM
    The 2.5% State pension triple lock is only guaranteed until 2020.

    Mind you, who knows what interest rates will be then. September CPI is usually less than August CPI so, based on August CPI of 0.6%, I guessed that September would be 0.5%. It's just been confirmed as a whole 1% (whoopee!) so we'll see what happens in the next few months.

    Mr S has already worked out our pension increases for next April!
    • westv
    • By westv 18th Oct 16, 3:16 PM
    • 4,064 Posts
    • 1,719 Thanks
    westv
    • #5
    • 18th Oct 16, 3:16 PM
    • #5
    • 18th Oct 16, 3:16 PM
    MY ss would increase state pension at the same rate as estimated inflation.
    • Triumph13
    • By Triumph13 18th Oct 16, 4:46 PM
    • 714 Posts
    • 656 Thanks
    Triumph13
    • #6
    • 18th Oct 16, 4:46 PM
    • #6
    • 18th Oct 16, 4:46 PM
    You'll find the whole exercise much simpler and the answers much more meaningful if you do it all in today's money rather than having to keep increasing everything by inflation and then needing to strip the inflation back out again to understand what any of the answers mean.
    On your assumptions that would mean you worked with 2% investment growth and SP shrinking by 0.5% pa. I use different assumptions, but each to their own on that one.
    • westv
    • By westv 18th Oct 16, 5:15 PM
    • 4,064 Posts
    • 1,719 Thanks
    westv
    • #7
    • 18th Oct 16, 5:15 PM
    • #7
    • 18th Oct 16, 5:15 PM
    You'll find the whole exercise much simpler and the answers much more meaningful if you do it all in today's money rather than having to keep increasing everything by inflation and then needing to strip the inflation back out again to understand what any of the answers mean.
    On your assumptions that would mean you worked with 2% investment growth and SP shrinking by 0.5% pa. I use different assumptions, but each to their own on that one.
    Originally posted by Triumph13
    Or, based on the way I would do SP increase, there wouldn't need to be any increases apart from the 2% investment growth.
    • Lois and CK
    • By Lois and CK 18th Oct 16, 6:38 PM
    • 537 Posts
    • 719 Thanks
    Lois and CK
    • #8
    • 18th Oct 16, 6:38 PM
    • #8
    • 18th Oct 16, 6:38 PM
    Thanks everyone for all the tips. That's very useful.
    • pandora205
    • By pandora205 18th Oct 16, 6:56 PM
    • 2,737 Posts
    • 2,590 Thanks
    pandora205
    • #9
    • 18th Oct 16, 6:56 PM
    • #9
    • 18th Oct 16, 6:56 PM
    Retire Easy does it for you - or at least it worked for me. There was one aspect of my finances that I couldn't work out what to do but a quick email to their support sorted it out.
    somewhere between Heaven and Woolworth's
    • Lois and CK
    • By Lois and CK 18th Oct 16, 7:08 PM
    • 537 Posts
    • 719 Thanks
    Lois and CK
    Thanks Pandora. I'm going to have a play with that this weekend :-)
    • pandora205
    • By pandora205 18th Oct 16, 7:37 PM
    • 2,737 Posts
    • 2,590 Thanks
    pandora205
    It takes a while to enter everything in, checking figures etc but the results are very useful. You can play with the information, such as retiring at different ages and saving different amounts to see the impact. I think its really useful though my situation is quite simple (having a defined benefit pension and AVCs rather more complex arrangements).
    somewhere between Heaven and Woolworth's
    • ukdw
    • By ukdw 19th Oct 16, 4:56 AM
    • 8 Posts
    • 2 Thanks
    ukdw
    When you say that your state pension column kicks in at 67 - do the values only start at age 67, or does the growth start from now, with the drawdown kicking in at 67?

    Whilst I see the benefits of removing inflation and corresponding growth from the calculations - I favour leaving them in my version of your spreadsheet because a) I intend to use my spreadsheet as a long term tracker too b) If a large percentage of annual drawdown is expected to come from the annual growth of the pot in the early years, then I wonder whether you would lose some of this aspect if you reduce growth by the whole inflation percentage amount?
  • jamesd
    Neither of those is really a reason not to use after inflation growth.

    There is one reason not to use inflation, though: a plan to pay off an interest only mortgage. If you use today's money throughout you'll also be increasing your mortgage balance by inflation each year. Since it stays the same that means you'll have too much in your plan to repay it. You can deal with that by using negative inflation just for the mortgage portion.

    I tend to use cfiresim for main planning because it handles the sequence of returns risk issue.
    Last edited by jamesd; 19-10-2016 at 5:10 AM.
    • Triumph13
    • By Triumph13 19th Oct 16, 8:20 AM
    • 714 Posts
    • 656 Thanks
    Triumph13
    Whilst I see the benefits of removing inflation and corresponding growth from the calculations - I favour leaving them in my version of your spreadsheet because a) I intend to use my spreadsheet as a long term tracker too
    Originally posted by ukdw
    I just do an annual exercise to uplift my balance by inflation when I also review to see whether I should 'mark to market' at all.
    b) If a large percentage of annual drawdown is expected to come from the annual growth of the pot in the early years, then I wonder whether you would lose some of this aspect if you reduce growth by the whole inflation percentage amount?
    You've completely lost me on that one. Mathematically it wouldn't make any difference - unless you've missed something out like uplifting your drawdown each year for inflation. If you are taking out more than the planned returns then the real terms pot will shrink and disguising that shrinkage by including inflation is probably not the best idea.
    • ukdw
    • By ukdw 19th Oct 16, 11:51 AM
    • 8 Posts
    • 2 Thanks
    ukdw
    On point 2 I was expecting it to make a big difference if you withdrew from a growing pot - i.e. If you withdraw 4% from the pot at the same time as it grows by 5% then the pot would actually be getting bigger each year for the first few years. Whereas if you ignore inflation and most of the growth then the pot would show as getting smaller from year 1. Having run a few scenarios through with both inflation included and not I was surprised to see that you are both right, and the year when the pot is emptied seems to be more or less the same either way.

    Despite this, Personally I am going to stick with including inflation and growth in my spreadsheet as I want to front load my draw down amounts when I am younger and more active.
    Last edited by ukdw; 19-10-2016 at 11:52 AM. Reason: Corrected typo
    • Triumph13
    • By Triumph13 19th Oct 16, 12:56 PM
    • 714 Posts
    • 656 Thanks
    Triumph13
    On your 5% cash terms growth, 3% inflation and 4% withdrawal scenario you can indeed model in absolute cash terms rather than real terms if that is what you prefer. It's just important that doing so doesn't lull you into thinking that your pot is 'still growing' during that phase when in reality it is shrinking by 2% a year because of inflation.
    • Suffolk lass
    • By Suffolk lass 20th Oct 16, 6:32 PM
    • 795 Posts
    • 12,729 Thanks
    Suffolk lass

    I'm trying to keep it all as simple as possible - and I appreciate it's mostly finger in the air guessing at inflation and growth and so on - but have I missed anything that any of you might have included in your own forecasts/spreadsheets?
    Originally posted by Lois and CK
    I also include an assumption for tax liability on the money I plan to drawdown. I work on the basis that the first x% of any drawdown is tax-free (so x% of each annual amount, as opposed to the first x% of the whole lot)

    SL
    MFiT T4 #2 Q2 update 11.53% after Q3
    Save £12k in 2016 thread #92 - so far £9689/£10,000 (target inc from £6000 in Aug)
    OS Grocery Challenge budget of £3000 - 86.97/84.62% after 11/12 months
    Reading and learning about investing... lots to learn and I am consciously incompetent
    • Lois and CK
    • By Lois and CK 21st Oct 16, 8:27 AM
    • 537 Posts
    • 719 Thanks
    Lois and CK
    Thanks everyone. I'll also take a look at that cfiresim. Exciting weekend ahead!
    • Spreadsheetman
    • By Spreadsheetman 21st Oct 16, 8:45 AM
    • 12 Posts
    • 5 Thanks
    Spreadsheetman
    Thanks everyone. I'll also take a look at that cfiresim. Exciting weekend ahead!
    Originally posted by Lois and CK
    Firecalc (firecalc.com) is useful too.

    Be aware that they are both based around USA-only stock market returns which are a little higher than most other markets. See Monevator (monevator.com/world-stock-markets-data) for more detail.
    • brewerdave
    • By brewerdave 21st Oct 16, 8:58 AM
    • 4,019 Posts
    • 1,600 Thanks
    brewerdave
    I've got several spreadsheets running -but I take quite a pessimistic view!
    Based on our outgoings currently I've built in 7% year on year cost inflation,whilst I have only got 2.5% for SP and investments, and 1% for my and wife's DB pensions. As a result we will be living in a cardboard box by the time I'm 90!!
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

1,356Posts Today

7,054Users online

Martin's Twitter