Along with others, I provided a spreadsheet based solution to the following question from Maria at the Excel-G forum and you can download my spreadsheet from the link below the question:
“I am helping a co-worker who is 68 years old divorced woman with a 40 years old daughter. She will be retiring in the next 3 months.
She has several retirement options:
- Option 1 (no lump sum withdraw): will receive $5,416 a month for the rest of her life until she dies.
- Option 2 (no lump sum ” ): will receive $4,602 a month throughout her life, upon her death 50% of the annuity will be paid to her daughter until the daughter dies.
- Option 3 ($130,000 lump sum); will receive $4,232 a month for the rest of her life until she dies.
- Option 4 ($130,000 lump sum); will receive $3,595 a month throughout her life, upon her death 50% of the annuity will be paid to her daughter until the daughter dies.
I put in an Excel worksheet, assuming she will live until 87 years old and option 1 is the best of all. She is leaning toward option 3. According to her, if she dies, she still have $130k for her daughter (before tax).”
Duncan Williamson
11th May 2018
Spreadsheet from here: retirement_options_excel_g
“
Excel with Excel Master 