Yet Another BRS Calculator

Version
Total Downloads357
File Size0.00 KB
Create DateMarch 3, 2017
Last UpdatedMarch 3, 2017
Download LinkDownload

From redditor chemspastic comes a retirement calculator to determine your best options. I'll copy his description here but to keep up to date, follow his reddit page at https://www.reddit.com/r/AirForce/comments/5u21d6/yet_another_brs_calculator/.

Because the AF/DoD is taking its sweet time putting out a BRS calculator, I'm posting one that I made recently. There isn't really anything groundbreaking in this one compared to the other ones that are out there, but I do feel that this one is pretty easy to understand. Also, it can serve as a rough retirement calculator (I'll explain how later).

First the Google Drive link.

If you aren't 100% committed to 20 years, you should probably switch to the BRS. Then you will at least get something for retirement, a 5% match into retirement accounts is pretty good for civilian employment (cue anecdotes trying to prove me wrong). If you want to do 20 for sure, it is almost always better to do the traditional (especially if you live a long time, the pension difference keeps building up), but the difference between the two plans is mostly within rounding error.

Things that you can do with this calculator.

1) Make a copy and start changing your own copy.

2) Make sure it applies to you. I'm an officer, so I made it with an officer's career. I've linked a rough approximation of an enlisted career on sheet 2 that you can copy and past into your own copy. Put in your own plans for making rank/promotions, going to OTS, etc.

You should also change the dates, age, time in service, etc to correctly demonstrate your situation. You can also extend the calculator beyond 20 years of service (if you decide to do so).

3) Only change values in the yellow boxes. I've got the matching percentage set to calculate on DoD matching rate (nesting IF statements are cool!). You can choose your own values for inflation and return.

I am being doubly conservative with the initial numbers by using 7% as the rate of return (which is the historical real return of the market including inflation) and then I subtract inflation again.

Use whatever numbers for return and inflation you want, they will both be included in the calculations.

4) If you know what your expenses in retirement will be, put it here. It will help you determine how the TSP funds get used after age 60.

AFTER CHANGING THE YELLOW BOXES, AND MAKING YOUR RANK/PAY SITUATION CORRECT ARE ONLY MINOR CHANGES FOR YOU TO MAKE

5) The TSP/BRS Totals columns (Columns M and N) change to blue at age 60. This is when you can normally begin withdrawing without penalty (unless you do some fun conversions, etc. But that is another post for another subreddit). Select the beginning of the blue and drag it up to when you hit 60, or select the end of the black and drag it down to 59. This starts the withdrawal part from your TSP funds, it subtracts the difference between your retirement expenses and your pension amount (ie: the amount your retirement funds must cover to match your expenses).

5.2) Change the values in boxes A12 and B12 to MXX and NXX. XX is when you turn 60.

This allows you to see how long your retirement accounts will last past 60 (rough approximation). By changing your expenses, rate of return, etc you can see that these funds may run out, or may continue growing indefinitely.

6) If you decide to stay past 20 years of service, fill in the years of service through matching contributions columns (H-L), taking note to stop matching contributions after year 27 (if you want to go that far). Then go to the cyan color blocks, this determines your pension amount. Copy and Paste (don't drag and drop, this will keep the reference cells the same, copy and pasting will move the reference cells to the correct place) to the row below your last year of service. Delete everything above the blue blocks. If you leave at 20, ignore this step.

7) Decide when you are going to die. Pick an age when you will die. Delete everything below that age (probably should have a safety buffer or something in case you decide to live longer later in your life). This will allow the comparisons to pick when you die. If you think you will past 100, drag the cell format down and continue to play the marvelous game of life.

That should do it for changing the spreadsheet

Interpreting Results

There are four methods of seeing which plan is best. The first two are simple (and so labeled), and the second does funny financial math giving you the net present value of the pension when you retire (it makes looking at the money all in the same year). The calculations are done twice, once to age 60 and again to when you die (default 100).

You can play with your contribution %, rate of return, expenses in retirement etc, to see if you have enough money for retirement. This should be used very cautiously, (remember you are using a product made by a butterbar) and should not be used to plan a real retirement.

The four green boxes will tell you what plan is better. The number right above them will tell you the difference (in dollars) between the two retirements.

Assumptions

  • Under both programs, each service member contributes the same amount. This is to keep the current out of pocket expenses the same.
  • A constant rate of return even going into retirement. I'm not going to talk about reallocating assets or anything, if you want to really look into there is a lot of info out there about it.
  • I'm taking the value of the TSP accounts at retirement, and not at the age when you can pull the money from it (for the mathy approach). Arguably it should be at retirement, because that is when you will have funds, but NPV takes everything to the same year so it should be good.
  • Past performance is not an indicator of future outcomes.
  • Continuation pay isn't included in here, but it would be awesome if it could be added directly to retirement accounts (which is probably won't be). Anybody more knowledgeable about this aspect please chime in.

Lessons Learned

BRS isn't that bad. In exchange for the majority of servicemembers getting some kind of benefit, retirees get pretty much the same benefit as before (after age 60). It does come with more risk, but you could also come out way ahead (the more reward part of the statement).

For me, it lets me know that I can walk away from the military at any time and not be set terribly back in my retirement plans. Not being locked in for 20 years for any benefit is pretty nice (for me). I'm also not sure if I want to do 20, but if I end up doing 20 I know that I will probably be roughly equal than if I had stayed with the old plan.

Any comments on the calculator? I would love to improve it and hope that it isn't too complex (I thought it was super simple then I started writing the guide).


FileAction