In: Finance
You want to retire in 35 years and plan to invest $2,000 per month until you retire. If you would like to be able to withdraw $200,000 per year for 25 years during retirement, what annual rate will you have to earn until retiring if you expect to earn 5% after you retire?
First, we calculate the amount required at retirement to enable the yearly withdrawals during retirement. The amount required at retirement is calculated using PV function in Excel :
rate = 5% (rate of return earned during retirement)
nper = 25 (number of years in retirement)
pmt = -200000 (yearly withdrawal. This is entered with a negative sign because it is a withdrawal)
PV is calculated to be $2,818,788.91
Next, we calculate the annual rate will you have to earn until retiring to accumulate the required amount at retirement. The annual rate will you have to earn until retiring is calculated using RATE function in Excel :
nper = 35 * 12 (total number of monthly investments = number of years until retirement * 12)
pmt = -2000 (Monthly investment. This is entered with a negative sign because it is a cash outflow)
pv = 0 (amount currently saved is zero)
fv = 2818,788.91 (required amount at retirement)
RATE is calculated to be 0.4961%. This is the monthly rate. To get annual rate, we multiply by 12. Annual rate is 5.953%
You will have to earn an annual rate of 5.953% until retiring