Question

Assuming you have had $55,000 balance in your 401k account and your annual salary is $50,000, suppose from now on your employer will match your deposit in your 401k plan but the amount of match money will be capped by 6% of your salary. If you decide to input 8% of your salary in your 401k plan each month and expect to make 10% return, compounded monthly, at time when you retire, how much money will you have in your 401k account? If you need $1.5 million dollars for your retirement life, based on the above investment in your 401k plan, at what age can you retire to enjoy life?

Requirements: Show calculations and formulas via Microsoft Excel Only.

Answer 1

While answering this question, i will have to make two reasonable assumptions:

• My current age is 25 years
• I will retire at the age of 60 years

Since i can't attach an excel file, i am producing below the workings for you to create your won excel file.

----------------------------

Annual salary = 50,000

Monthly salary = 50,000 / 12 = 4,166.67

My contribution to 401K plan = 8% of my monthly salary = 8% x 4,166.67 = 333.33

Amount from employer in your 401K plan = min (your contribution, 6% of your salary) = min (8% of my salary, 6% of my salary) = 6% of my salary = 6% x 4,166.67 = 250.00

Hence, total addition per month to 501K account = My contribution + employer contribution = 333.33 + 250 = 583.33

Opening balance in 501K account = 55,000

Interest rate = 10% per annum compounded monthly

Hence, interest rate per period of 1 month = R = 10%/12 = 0.8333%

Total time period = Retirement age - Current age = 60 - 25 = 35 years = 12 x 35 = 420 months

Future value of the kitty at retirement = FV of all the monthly payments over next 420 periods + FV of opening balance of 55,000 = Amount deposited every month x Sum of FV factor over 420 periods + Opening balance x FV factor for 420 periods

Sum of FV factors over 420 periods

$=[(1 + R)^N -1] / R$

= [(1 + 0.8333%)⁴²⁰ - 1] / 0.8333% = 3,796.64

FV factor for 420 periods = (1 + R)^N = (1+0.8333%)⁴²⁰ = 32.64

hence the size of the kitty at retirement = FV of all the monthly payments over next 420 periods + FV of opening balance of 55,000 = Amount deposited every month x Sum of FV factor over 420 periods + Opening balance x FV factor for 420 periods = 3,796.64 x 583.33 + 55,000 x 32.64 = $ 4,009,831

At time when I retire, I will you have in my 401k account, an amount = $ 4,009,831

If you need $1.5 million dollars for your retirement life, based on the above investment in your 401k plan, at what age can you retire to enjoy life?

Now, we have to figure out the value of N in the expression above such that size of the kitty os $ 1.5 mn

So, we have to solve the following equation:

Required size of the kitty = $ 1.5 mn = 1,500,000 = FV of all the monthly payments over next N periods + FV of opening balance of 55,000 = Amount deposited every month x Sum of FV factor over N periods + Opening balance x FV factor for N periods = [(1+R)^N - 1] x 583.33 / R + 55,000 x (1 + R)^N = [(1+0.8333%)^N - 1] x 583.33 / 0.8333% + 55,000 x (1 + 0.8333%)^N = 125,000 x (1.008333)^N - 70,000

Hence, (1.008333)^N = (1,500,000 + 70,000) / 125,000 = 12.56

hence, N = Log (12.56) / Log(1.008333) = 304.93 months = say 305 months from now

Age then will be = Current age + years in 305 months = 25 + 25 years and 5 months = 50 years and 5 months = 50.41 years.