Changes from version XVI to XVII:
Fixed error when employee mandatory contribution was set to zero.
Fixed error in partial year savings to 401k and nestegg. This only affected
the first year's savings.
Fixed error in which 'Annual Savings' was used to determine the LANS 401k match.
The match is now based on 'TCP2 Savings'.
Fixed error in which savings continued after separation.
Changes from version XV to XVI:
Fixed error in which TCP2 savings continued even after separation.
Changes from version XIV to XV:
Fixed error in which TCP1 savings continued even after separation.
Added a 'DoubleDip' worksheet. Contains a table that shows a crude summary
of lifetime benefits as a function of when you start taking benefits.
Changed colors on the charts to be more consistent.
Changes from version XIII to XIV:
Fixed error in which LANS savings match was happening even when Annual Savings was zero.
Fixed error in which LANS savings match was happening even after separation.
Incorporated changes to make it more printable (thanks Bob!).
Cleaned up the first chart on the 'Charts' page. There was some poor labeling.
Changes from version XII to XIII:
Added the notion of separation from LANL before retirement. E.g. you're 45, leave LANL at 50, and retire at 60.
See 'Age@Separation'. This required changes to how HAPC is calculated (increases by Raise before separation,
COLA afterwards), and how the anti-401k is assessed (only from June 1 'til separation). After separation, years
of service freezes, but age factor still changes.
This time I really did change sick leave credit to 2000 hours = 1 year.
Changes from version XI to XII:
Added column for SS Supplement, and summed it into the total income columns for the pensions.
Changed sick leave credit to 2000 hours = 1 year.
Changes from version X to XI:
In version X I had neglected to lock the cell labeled 'TCP2 Savings'.
Added some more comments.
Changes from version IX to X:
A user noticed an error in column C. 'Annual Savings' was being used (i.e. the savings rate for TCP1) rather than
'TCP2 Savings'. I think this would not matter if your 'Annual Savings' rate is above 6%.
Changes from version VIII to IX:
Fixed the 'partial year problem' for the period from June 1, 2006 to the next birthday. Savings and mandatory
employee contribution had been computed for a full year, not the fractional year.
Added columns for nest egg yield.
Hid the last of the ugly zeros.
Fixed a couple of comments.
Changes from version VI to VIII:
Fixes problem in applying the raise properly
Has a better treatment of the 'lost opportunity' anti-401(k)
Added instructions on comparing with the April 10 examples
(My TCP1 numbers are roughly 5% higher than theirs, but my TCP2 numbers are right on.)
(Below are the contents of the 'Notes' sheet, for convenience)
Use this spreadsheet to compare your options. The scenarios covered are:
LANS TCP1 pension
UC inactive pension + LANS/TCP2 matching 401(k)
UC inactive lump sum + LANS/TCP2 matching 401(k)
Edit only the bold numbers to in the upper left of the 'Table' worksheet.
About the 'Anti-401k':
UC, and therefore probably LANS will require a mandatory employee contribution to support the pension starting in 2007. At this time I haven't seen any official word on
how much that might be, but between employer and employee there is said to be a ~16% shortfall. Noone yet knows how that burden will be shared. Make your best guess (I'm splitting the difference at 8%).On this spreadsheet, enter your 'Annual Savings' and the 'Employee Pension Contribution' (EPC) percentages. I assume that if you didn't have to make the pension contribution you would have save it to your 401(k). So the bigger the EPC, the better TCP2 looks. To make that comparison properly, look at the right-hand side of the chart (in blue) and the third chart on the 'Charts' page. I find that the higher the raise and the lower the EPC, the better TCP1 looks.
If you wish to edit the formulas, go to: Tools/Protection/Unprotect SheetÉ
If you're under 40 you may wish to 'unhide' the appropriate rows. This requires that you first 'unprotect'. See the menu 'Tools/Protection'.
I'm calling the LANS contribution '401k', and your contribution 'Annual Savings' or 'Nest Egg'. The 401(k) columns represent only LANS' contributions and their derived earnings. They don't include your contributions, which I assume you'll make even if you're on TCP1. To get LANS to contribute the full match you'll have to save at least 6% (Annual Savings) yourself.
The LANS 401k contribution automatically increases with years of service, and is capped at 6% or your Annual Savings, whichever is less.
I'm assuming:
You'll spend all the income from the 401(k) and lump sum. When you retire the accumulated values of those funds goes flat.
Medical benefits are identical.
If you go 'Inactive UC', you won't have to contribute to the pension fund, but TCP1 employees probably will. HAPC
will increase (compounded annually) by 'Raise' for TCP1. HAPC increases (compounded annually) each year by the COLA for TCP2.
If you wish to check these (TCP1) numbers against those of the UC website, go to https://www.bencom.ucop.edu
Click on 'View Your Retirement Estimates'
Take this from the web: and put into this cell of the spreadsheet:
Current covered compensation rate HAPC Monthly
60 + 'Current years UCRP service' - 'Est UCRP service @age 60' Years@5/31/06
Put 133 into 'SS Fudge' on the spreadsheet (if you're paying into Social Security, otherwise 0).
Put 0 into 'Hours Sick Leave', 'Inactive COLA', 'Raise' and 'Emp Pens Contrib'.