This spreadsheet was inspired by a comment that SamClem made a couple years ago about saving our promotion pay raises. Aggressive savers should try to bank every promotion pay raise while holding expenses below the previous paygrade. I've built a rudimentary spreadsheet to show the results of saving those raises.
First I assumed that military pay keeps up with inflation over a 20-year career. Next I assumed what seem to be reasonable promotion gates for E-2 through E-7 and O-2 through O-5. Then I assumed that the member would save a percentage of base pay (living one paygrade below their current rank) and a bigger percentage of the pay raise received when they promoted to their current rank. The spreadsheet allows users to choose their percentages. Savings are invested over 20 years and compounded at a user-selected APY over inflation. Investments would include the TSP, IRAs, and taxable accounts.
Here's what I assumed for years served before promotion to that rank:
I'd appreciate feedback on whether or not these promotion assumptions seem reasonable. I think officer promotion gates are still set by DOPMA, although every service has found loopholes. Some Navy enlisted specialties promote slower and a few promote faster, but I believe these gates represent today's average sailor. I'm not sure about the other services, so please let me know if there are better numbers.
Military pay tables don't easily convert to spreadsheet formulae, so I just did those two samples and still had to edit nearly every darn cell. A DFAS pay table (http://www.dfas.mil/militarypay/militarypaytables/2009MilitaryPayTables.doc) includes pay rates for longevity in addition to ranks. One example is an E-5 in year 9 of their career. Their promotion to that rank occurred in year 7 and they're attempting to live as though they were still receiving E-4 pay. They're saving a percentage of E-4 base pay and a percentage of the pay raise (between E-5 and E-4 pay) from the "over 8" column of the pay table.
Next year (year 10) that E-5 would be promoted to E-6, but I assumed that the pay raise wouldn't take effect until the following year. The longevity hasn't changed yet either because they're still over eight years of service and not yet over 10. So the same "over 8" cells are used for year 10 as were used for year 9. Perfectly clear, right?
Next year (year 11) the recently-promoted E-6 would use the "over 10" column for E-6 & E-5 paygrades.
Some other conservative assumptions: no money is saved during the first or second years of a 20-year career, which really hurts from a compounding perspective but is a reasonable fact of life. Active-duty military are paid twice a month but I set savings & compounding rates at the end of each year.
I didn't include any tax-free allowances like Basic Allowance for Housing, although that would rise with promotions and members could spend it on higher housing costs. I didn't mess with special pays, either, so those could also be saved or "frittered away on luxuries". Bonus pay, of course, should be banked whenever possible, but was not included either.
So here are the results for saving 10% of base pay (of the lower paygrade), 80% of the promotion raise, and compounding at 3% over inflation. These are today's dollars, adjusted for inflation. Frankly I think these numbers are lower than what the typical service member could achieve.
It'd be nice to allow the user to set their own promotion gates-- for example promoting to E-7 in 12 years instead of in 16. If that can be done on a military pay table using Excel functions like HLOOKUP or Boolean operators, then I'd love to learn how. I know that I could reformat the DFAS pay table for easier lookups, but I'm hoping to use it "as is" with functions & Booleans instead.
First I assumed that military pay keeps up with inflation over a 20-year career. Next I assumed what seem to be reasonable promotion gates for E-2 through E-7 and O-2 through O-5. Then I assumed that the member would save a percentage of base pay (living one paygrade below their current rank) and a bigger percentage of the pay raise received when they promoted to their current rank. The spreadsheet allows users to choose their percentages. Savings are invested over 20 years and compounded at a user-selected APY over inflation. Investments would include the TSP, IRAs, and taxable accounts.
Here's what I assumed for years served before promotion to that rank:
E-2 | E-3 | E-4 | E-5 | E-6 | E-7 |
1 | 2 | 4 | 7 | 10 | 16 |
O-2 | O-3 | O-4 | O-5 | ||
2 | 4 | 10 | 16 |
I'd appreciate feedback on whether or not these promotion assumptions seem reasonable. I think officer promotion gates are still set by DOPMA, although every service has found loopholes. Some Navy enlisted specialties promote slower and a few promote faster, but I believe these gates represent today's average sailor. I'm not sure about the other services, so please let me know if there are better numbers.
Military pay tables don't easily convert to spreadsheet formulae, so I just did those two samples and still had to edit nearly every darn cell. A DFAS pay table (http://www.dfas.mil/militarypay/militarypaytables/2009MilitaryPayTables.doc) includes pay rates for longevity in addition to ranks. One example is an E-5 in year 9 of their career. Their promotion to that rank occurred in year 7 and they're attempting to live as though they were still receiving E-4 pay. They're saving a percentage of E-4 base pay and a percentage of the pay raise (between E-5 and E-4 pay) from the "over 8" column of the pay table.
Next year (year 10) that E-5 would be promoted to E-6, but I assumed that the pay raise wouldn't take effect until the following year. The longevity hasn't changed yet either because they're still over eight years of service and not yet over 10. So the same "over 8" cells are used for year 10 as were used for year 9. Perfectly clear, right?
Next year (year 11) the recently-promoted E-6 would use the "over 10" column for E-6 & E-5 paygrades.
Some other conservative assumptions: no money is saved during the first or second years of a 20-year career, which really hurts from a compounding perspective but is a reasonable fact of life. Active-duty military are paid twice a month but I set savings & compounding rates at the end of each year.
I didn't include any tax-free allowances like Basic Allowance for Housing, although that would rise with promotions and members could spend it on higher housing costs. I didn't mess with special pays, either, so those could also be saved or "frittered away on luxuries". Bonus pay, of course, should be banked whenever possible, but was not included either.
So here are the results for saving 10% of base pay (of the lower paygrade), 80% of the promotion raise, and compounding at 3% over inflation. These are today's dollars, adjusted for inflation. Frankly I think these numbers are lower than what the typical service member could achieve.
Start of Year #: | Enlisted | Officer |
0 | $0 | $0 |
1 | $0 | $0 |
2 | $3,304 | $3,186 |
3 | $7,059 | $13,513 |
4 | $11,947 | $24,378 |
5 | $17,109 | $35,605 |
6 | $22,426 | $47,169 |
7 | $28,775 | $60,533 |
8 | $34,993 | $74,298 |
9 | $43,048 | $90,860 |
10 | $51,345 | $107,920 |
11 | $58,502 | $123,992 |
12 | $65,873 | $140,546 |
13 | $75,063 | $158,260 |
14 | $84,527 | $176,506 |
15 | $94,812 | $196,149 |
16 | $105,405 | $216,381 |
17 | $117,508 | $236,937 |
18 | $129,974 | $258,109 |
19 | $143,498 | $281,313 |
20 | $157,428 | $305,214 |
It'd be nice to allow the user to set their own promotion gates-- for example promoting to E-7 in 12 years instead of in 16. If that can be done on a military pay table using Excel functions like HLOOKUP or Boolean operators, then I'd love to learn how. I know that I could reformat the DFAS pay table for easier lookups, but I'm hoping to use it "as is" with functions & Booleans instead.
Attachments
Last edited: