+ Reply to Thread
Results 1 to 17 of 17

"Pensions" calculations

  1. #1
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    "Pensions" calculations

    Standard practice here when one applies for the state pension is to be offered a choice of either a weekly income stream, or a reduced income stream in return for an additional tax free lump sum. The question is how in the current environment to make an informed choice - which to me means working out the "return" that would result from NOT taking the lump sum, and comparing this with the reduced income stream from taking it.

    I am waiting to be told what the exact data would be. In the interim I imagine that the procedure would be to obtain the "discounted cash flow" or "net present value" - ??? this is where I am unclear - of the two income streams, and express the difference between them as a percentage of the lump sum to give the yield of the latter.

    Is my reasoning correct, and what formula would I use to generate the DCF's / NPV's?

    Tks

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: "Pensions" calculations

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    Yes I did read and think about the banner. The problem is that if I knew what to put in the sheet I probably would not be asking the question in the first place I can imagine that 2 rows of entirely fictional repeating numbers in isolation would be something that your members could easily envisage without me putting this in a spreadsheet. If I'm wrong I will of course oblige.

    It could be that my explanation of the issue is not as clear as it was intended to be in which case I can invent some figures.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: "Pensions" calculations

    The thing is, why should anyone helping you have to create a dummy spreadsheet for you when you could provide one in the format you are hoping for and with some dummy data populated? It will save your helpers time.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: "Pensions" calculations

    Quote Originally Posted by Telegraph Sam View Post
    which to me means working out the "return" that would result from NOT taking the lump sum, and comparing this with the reduced income stream from taking it.
    Not really. And I would not bother with a NPV analysis.

    Instead, the typical approach is to create a "simulation" to compare the future behavior of the two options.

    That might be as simple as multiplying expenses by a fixed inflation rate and multiplying income streams by a fixed rate of return, then subtracting the two.

    Of course, a Monte Carlo simulation is more sophisticated. And it might be more "realistic", if it is done correctly.

    Initially, you can simplifying your estimated expenses. Eventually, it might be good to break them down, applying different inflation factors (e.g. medical v. consumer).

    Initially, you can make simplifying assumptions about income streams (social security, retirement income, etc). Later, you might factor in estimates of changes. For example, if we might assume that gross social security increases according to an inflation factor (which we can only guesstimate), it is offet by medicare costs that vary according to a different inflation factor. Consequently, __net__ social security income might varying differently.

    Eventually, you will want to factor in the effect of taxes and tax laws. For example, at 70 1/2, you will be required to withdraw a varying amount of the lump sum (RMD). That can have a significant impact on taxes (an expense).

    All of these factors (and more) add complication and sophistication to the "simulation".

    -----

    Some investment firms offer online tools that can help with the comparison; Fidelity and Schwab, for example. IIRC, one or both offer both simple models and Monte Carlo models that requires input of a lot of data (that you can simplify at first, and add more later as you become more sophisticated).

    You don't have to be an "investor" in order to use those tools. Just open an account and fund it with the minimum -- $1, I think; maybe $100.

    If you familiarize yourself with their tools, it might give you insight into how you might design your own tool using Excel. Or you might be satisfied with their tools.

    If you pursue your own Excel implementation, then and only then would it make sense for you to ask for assistance in an Excel forum.

    In other words, your questions would be about how to use this function for that calculation, or simply how to do that calculation. Not how to design such a major project from the ground up.

    Someone might offer something that works for them. But it might not work for you, because you have (or lack) certain income streams, your investment strategy is very different, etc. Caveat emptor!

    And speaking of "emptor" (which means "buyer"), do __not__ pay one penny for any such tool -- at least, not until you are sophisticated enough to understand their value, if any.

    There are so many decent free tools available. And all of them suffer from the fact that no one can predict the future with any degree of accuracy.

    The goal of the "simulations" is to compare the choices under the same assumptions. Hopefully, the assumptions are reasonably close to reality. But it is sufficient, for the most part, that the assumptions are just the same.
    Last edited by joeu2004; 04-23-2020 at 07:13 PM.

  6. #6
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    Thanks for the detailed advice. This goes way beyond my powers of sophistication and forecasting! I am looking to set up only very general [UK based] guidelines for approaching making a decision. I can see now that putting a few numbers in the hopefully attached spreadsheet should help convey the bare outlines. At some point I will want to compare the "return" from foregoing the cash lump sum option with the "return" from investing the sum in some savings account all of which seem to be competing to see who can offer the lowest rates.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: "Pensions" calculations

    Nothing attached (yet).

  8. #8
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    You can judge my technical competence by my ability to attach and send a simple spreadsheet. Second attempt herewith.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    Which function would I use in order to make a simple comparison (of "attractiveness") between a lump sum and a future income stream as in the attached theoretical example? Something which would allow me to vary the interest rate and no of periods in various what if situations?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    Let me refine my query above:

    With the NPV function how do you accomodate the fact that the "periods" could be weeks or months, but one is accustomed to think of the percentage return or discount in the form of an annual figure? There is obviously a need to do a conversion somewhere along the line.

  11. #11
    Registered User
    Join Date
    06-02-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    51

    Re: "Pensions" calculations

    You are missing the key consideration when it comes to annuities... longevity.

    How many years do you believe you are going to live? If you have any reason to suspect you wont have a long lifespan then probability is that you are better off with a lump sum whereas if longevity runs in the family then you may be better off with the higher payments. In reality this is more of a game than that because the insurer will be modelling your longevity when providing the quotes and if you are someway atypical then there could be advantages for you in one way or the other.

    I assume you are looking at a flat rate rather then escalating annuity? If its indexed or even increasing by a fixed amount this will offset the time/value component (though again reduce the layments in the earlier months/years).

    In reality, almost everyone takes as much of the 25% tax free cash lump sum as they can (in some schemes you may not be able to get the full 25% due to guaranteed minimum pensions etc).
    Last edited by Sandtree; 06-08-2020 at 09:00 AM.

  12. #12
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    This is the whole point:
    In the face of uncertainties and unknowns you can model various what-if scenarios in a spreadsheet by altering the variables and come to tentative conclusions which is what I want to do as a first step.
    Details such as tax considerations can be added in subsequently (there are differences between state and private pensions) but I need to get the basics clear as a starting point. Hence my query as to how to use the function correctly. I am not (yet) convinced that in the present and predictable low interest environment the (?? tax free??) lump sum would be the automatic choice.
    Flat rate non-escalating payments.

  13. #13
    Registered User
    Join Date
    06-02-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    51

    Re: "Pensions" calculations

    Sorry, private pensions it is a TFCLS but the state pension it is taxable.

    I have a number of excel models that are used to calculate "best estimate of liability" or proxies to it which are used by insurers and pension schemes etc to effectively calculate how much the payments are going to cost on a NPV basis however their approach to longevity is to effectively decay your payments by the probability of you being alive at each payment point which is fine for modelling 2,000 people where some will outlive the average and others will die earlier but doesn't help with your situation where you will be alive or dead at any given point (rather than 50% chance of being alive and therefore 50% of the payment).

    For the NPV function you need to simply ensure your discount rate matches your payment interval, so reduce the per annum figure down to represent the fact its more frequent payments.

  14. #14
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    I am sure that the insurers and pensions schemes are way more sophisticated than I am and the differences you state are no doubt valid. Things which I could discuss with tax and pension providers at a later stage.

    If I want to "convert" the NPV % rate from say monthly payments to an annual equivalent, is it a simple x 12 multiplication??? Weekly x 52? Or is there some element of compounding in the calculation somewhere?

  15. #15
    Registered User
    Join Date
    06-02-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    51

    Re: "Pensions" calculations

    The model for the cashflows and NPV is actually fairly simple, working out the longevity tables, discount curves and then subsequently testing its sensitivities is where the complexity exists.

    No, you can't just times the rate by the number of months etc because the impact is compound

  16. #16
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    So is there a "clever" way of doing the conversion that takes account of the compounding element? Or a different / modified formula that does this? You would think that this question would crop up constantly.

  17. #17
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: "Pensions" calculations

    With long distant memories of financial mathematics in mind I have tried to answer my query in the attached modified spreadsheet. Would you care to check it (and help me fill in the reverse conversions)? I suspect this must be standard for all building society AER calculations.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1