Question

You plan on starting a retirement fund at the end of the year. The fund earns 11% per year. You will invest X dollars into the fund. You plan to retire at the end of 40 years from now. Once you retire, you will take your entire retirement fund and transfer it into a safer investment that will only earn 5% interest per year compounded monthly. You will need $4000 per month for 30 years during your retirement to sustain your lifestyle. (After which point, RIP). Calculate exactly how much money you will deposit in your retirement account at the end of this year. Your cousin who is the same age as you and with the same life expectancy and desired retirement age decides she will wait 10 years to start saving for retirement (so she will save for only 30 years). How much does she need to invest per year to achieve the same retirement nest egg as you when she retires? Finally, your lazy best friend who is also your age decides he will wait 10 years to start saving for retirement just like your cousin. However, he will only save the same amount you save per year. He will retire at the same time as you and reinvest just like you do into a safer investment. He will try to live the same life style as you. How long after retirement (in months) before he is broke?

Answer 1

1-	value of investment at the time of retirement	Using present value function in MS excel	pv(rate,nper,pmt,fv,type) rate =5%/12 =.4166% nper = 30*12 =360 pmt = 4000 fv =0 type =0	PV(0.4166%,360,4000,0,0)	($745,194.34)
	Yearly payment to achieve the retirement fund at the time of retirement	Using PMT function in MS excel	pmt(rate,nper,pv,fv,type) rate =11% nper = 40 =360 pv =0 fv =745194.34 type =0	PMT(11%,40,0,745194.34,0)	($1,280.79)
	He should save 1280.79 per Year to avail the desired retirement benefits
2-	Yearly payment to achieve the retirement fund at the time of retirement	Using PMT function in MS excel	pmt(rate,nper,pv,fv,type) rate =11% nper = 30 =360 pv =0 fv =745194.34 type =0	PMT(11%,40,0,745194.34,0)	($3,744.30)
	she should save 3744.30 per Year to avail the desired retirement benefits
3-	Future value of investment at the time of retirement	Using Future value function in MS excel	fv(rate,nper,pmt,pv,type)	FV(11%,30,-1280.79,0,0)	$254,903.95
	Year till he can attain the retirement benefit	Using nper function in MS excel	nper(rate,pmt,pv,fv,type) rate = 5%/12 =.4166% pmt = 4000 pv = -254903.95 fv =0 type =0	NPER(0.4166%,4000,-254903.95,0,0)	74.22
	How long after retirement (in months) before he is broke	74.22