@mellowyellow If your goal is to be able to not work at age 50 w/ a collection of assets (pension/investments) that results in an income of $101k/yr, its seems like the question your spreadsheet needs to be asking is - what level of income is needed between your retirement eligibility age and 50 that enables you to maintain the same paycheck while also allowing you to save enough to have an investment nest age ready to draw from at age 50?
Back of the envelope says based on regular military compensation, when you are retirement eligible at 20 years as an O-4 youd be making $150,000 per year.
In order to have sufficient investments by the age of 50 (assuming no current investments), to make up the difference between what your 31 year retirement vs 21 year retirement is (about ~$80,000 a year), youd need to have about $2,000,000 saved. This is based on being able to safely withdraw 4% each year.
Assuming you had $0 saved the day you separated at 21 years, to have $2,000,000 in 10 years you would have to invest about $12,000 a month or $144,000 a year so that with a rate of return of 7% it would result in $2,000,000.
If you wanted to keep the same $150,000 lifestyle when you separate, while also saving this amount in order to be ready to not work at age 50, your annual income would need to be $294,000. Since youd be earning a retirement paycheck at that point, youd only need to have a job that pays you $242,000.
Now, I made a bunch of assumptions here, like no current savings, the goal being to not work at all at 50, and your risk posture as far as what draw down rate youd want for your investment portfolio.
So my gut check is that if your goal is to have $101,000 income at age 50 with no need to work, stay active duty until you can retire at 31 years. Otherwise, some other variable needs to change about the $101,000 @ 50 without work. I dont know your field, but I doubt that a job that can bring in $240,000 a year is going to be that much better when it comes to work life balance, stress, or benefits as being active duty. Also, that seems like a pretty tall order to snag such a job and keep it for 10 years if your goal really is to not have to work at all at age 50.
Side note - check my math people, I may be wildly off here.