Bob has just retired with an investment portfolio equal to $1 million. He plans on using the 4% capital balance approach to retirement distributions. He also plans on distributing the amount in one lump sum at the beginning of the year. His first year, he distributes $40,000 to live on, in addition to his other income. However, the market takes a significant decline and his portfolio loses 40%. His second year, after he takes his distribution, his portfolio increases by 20%. How much can he take out in the third year if he is sticking with the 4% method? Round to the nearest thousand.

Answer is: 27,000.00

Please explain how to solve with formula break down on excel format.