I have been wanting to share this spreadsheet so you can see how I am calculating my "Retire Now" numbers. I would be happy to email this spreadsheet to anyone who would like to modify it for themselves.



So at the very top, I update the current value of my various accounts ear-marked for retirement. The pension value is included because if I were to retire now, I would not be able to receive a monthly pension as I do not have at least 5 service credits. Instead, I would roll the balance to my 457 plan. Once I reach the point that I do have 5 service credits, I will no longer include this value but will instead alter my monthly portfolio withdrawal at age 62; the age at which I can begin to draw a monthly pension check.

Please note that months which have actually happened are highlighted in green; months which are not yet highlighted are in the future and thus are purely estimates.

So let's look more closely at the December 2019 line. The beginning balance is the ending balance from the month before. In this case, $359,847.64.

The additions column is the amount of new money added during the month. For December 2019, the amount of new money is $701.52.

The subtractions column is the base amount withdrawn, or starting withdrawal amount. As I am not yet retired, the amount withdrawn for December 2019 is $0.00.

The COLA column is for the annual 2% cost of living adjustment. The first COLA will happen in January of the year after I retire. As I am not retired yet, the COLA for December 2019 is $0.00.

The growth column is for the amount the portfolio has changed during the month, excluding both new money and withdrawals. Looking forward, this amount is an estimate. But as each month actually happens, the estimate is replaced with the actual. For December 2019, my portfolio grew by $7,441.16.

The ending balance column is the total portfolio value at the end of the month. The future numbers are estimates, but the past and present numbers are actual. For December 2019, the actual ending number is $367,990.32.

Now let's take a closer look at January 2020. This is the first non-actual month and thus the assumption is that I retire in January and begin taking portfolio withdrawals.

The beginning balance is the ending balance from the month before.

The additions column for January 2020 is $0.00. This is because the assumption is I have retired with no new money ever being added again.

The subtractions column shows a base withdrawal of $1,535 per month. I find this number by trial and error. I plug in a number and the spreadsheet re-calculates. I am looking for the number which gives me the lowest positive balance at age 100. I do not bother with cents. So if I were to change this base withdrawal number to $1,536, I would have a negative balance the month I turn 100. I do not want a negative number until the month after I turn 100, so I go with $1,535.

COLA column. This begins at $0 as the first increase will happen the January after I retire. So if I were to retire right now, the first COLA would occur in January 2021.

Growth column. For January 2020 and all following months, this number is calculated at 5% APR (so divided by 12 to get a monthly increase) of previous month's ending balance. While 5% is not unrealistic, it is of course not guaranteed. More importantly, even if the average annual return is exactly 5%, there will almost certainly be many months with a much higher return as well as many months with a much lower return. This is referred to as "sequence of returns" and greatly impacts what actually happens to your portfolio especially in the withdrawal phase. Unfortunately, the actual sequence of returns cannot be known ahead of time.

The ending balance is the beginning balance minus withdrawals plus estimated growth. Notice that at this point, withdrawal and growth are almost equal. However, as COLAs begin and increase, monthly withdrawals begin to outpace estimated growth and the portfolio balance decreases.



Now let's skip ahead to the time when I will turn 67, which is my Social Security full-retirement age (FRA). Currently, I am planning that I will begin to draw SS benefits the month after I reach my FRA. I will look at this decision more closely as I approach age 62 to determine what will make the most sense for me. I am estimating only $700 per month, because the assumption is I quit working at age 52. As I work longer, I will have to re-visit what is a reasonable estimate of my future SS benefits.

Notice that the month I turn 67, my total portfolio withdrawal is $1535 + $472 = $2,007 which is outpacing estimated portfolio growth by a good bit and the portfolio is decreasing. However, the month I begin receiving SS benefits I reduce my withdrawal by $700 and the monthly withdrawal is once again close to estimated monthly growth.



And finally, the month I turn 100. After that, I will have only SS benefits to live on.

I do not expect I will reach age 100, and this provides some cushion against the unknown actual portfolio performance and the all important sequence of returns.

Also, I am thinking that I will do tax preparation post retirement, full-time for 2.5 months or so each year. Working for a CPA firm would be best, but I would be OK with working for H&R Block. This also provides some cushion as I will not be entirely dependent on portfolio withdrawals.

I am thinking this would be money for travel, but if portfolio returns are low in the first several years of my retirement, it would give me the flexibility to reduce portfolio withdrawals, which would help get it back on track and extend it's life.

So, there you have it.