Jim and Ellen Richardson are nearing retirement. They have each had successful careers and have diligently saved in a variety of investments over the years. They own their home, valued conservatively at $890,500. The home has no mortgage. They drive conservative vehicles; a 2018 Toyota Camry, a 2016 Toyota Tundra truck, and a 2019 Honda CRV. None of the vehicles have leins. The Richardsons have other items of value, including gold bullion held as a hedge against inflation and some modest artwork investments worth around $125,000 depending on fickle markets. The major income source for their retirement, however, is a portfolio of investments summarized in Table 1. They do not intend to liquidate their home or other possessions for the remainder of their lives. If they make other vehicle purchases, they will do so with income before retirement or using their monthly withdrawals after retirement.

The Richardsons are looking forward to their retirement, but want to make sure they continue to accrue sufficient wealth to support their continued standard of living. Both are in good positions to continue working. They are both in good health and both have the ability with their respective jobs to decide on their date of retirement. They want to retire simultaneously and begin travelling together soon after retirement. Jim and Ellen have established a baseline income level for their retirement years. They estimate that, given their expectations for inflation, they could comfortably live on a monthly withdrawal from a retirement account of $27,500 per month. Upon retirement, they intend to place the total of their savings into three income-generating instruments that are expected to generate a modest average return of 5.5%. The Richardsons want to make sure they would have a sufficient balance in order to fund their monthly withdrawal until Jim reaches age 103. If they die before that time, the residual estate is to be distributed to their heirs and beneficiaries according to their last will and testament.

Jim is currently 58 and Ellen is 56 years old. Calculate the number of years they should continue to work and continue their contributions in order to fund their required monthly retirement withdrawal.

Could someone show me how to set this up in excel using goal seek and time value of money?

Table 1. Balances and Contributions. 1. All estimates and returns are already adjusted for estimated taxes. All estimates are unadjusted for inflation, in order to match the nominal $27,500 monthly withdrawal amount specified by the Richardsons. 2. Monthly contributions not immediately compounded are deposited in a savings account earning 1.2% daily compounded interest. Table 1. Balances and Contributions. 1. All estimates and returns are already adjusted for estimated taxes. All estimates are unadjusted for inflation, in order to match the nominal $27,500 monthly withdrawal amount specified by the Richardsons. 2. Monthly contributions not immediately compounded are deposited in a savings account earning 1.2% daily compounded interest