Question

A man plans to retire in 25 years and spend 35 years in retirement. He currently earns $82,500 before-tax annually, which increases annually with the level of inflation. He has determined that he needs 70% of his pre-retirement income for his retirement years. He currently has $282,000 in his RRSP account and $10,000 in a non-registered account. He will earn 5.50% before retirement and during retirement he will readjust his portfolio to be more conservative earning 3.50%. Inflation is 2% and his marginal tax rate is 35%. All payments are at the end of the period unless stated otherwise.   

a). What is the amount he needs to have saved at retirement?

b). What is his shortfall?

c). This man plans to save monthly. How much does he need to save each month to address his shortfall and reach his retirement goal?

Answer 1

I have answered the question below using excel and have attached the image below.

Please up vote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

a)

		CALCULATION	REMARKS
	Number of years to retirement	25
	Inflation rate	2%	0.02
	Current before tax annual income	$82,500
	Current after tax annual income	$53,625	(82000*(1-0.35)
	After tax income before retirement	$87,977	(53625*((1+0.02)^25))
	After tax income after retirement	$61,584	(70%*87977)
Pmt	Before tax income after retirement	$94,745	(61584/(1-0.35)
Rate	Inflation adjusted return =(1.035/1.02)-1	0.014705882
Nper	Number of years of retirement income	35
PV	Amount he needs to save at retirement	$2,577,575
	(Using PV function of excel)	PV(1.4705882%,35,-94745)

b)

	Amount of Shortfall:		REMARKS
Pv	Current Savings=282000+10000	$292,000
Rate	Interest Rate	5.50%
Nper	Number of years to retirement	25
FV	Future value of current savings	$1,113,511	FV(5.5%,25,,292000)
	(Using FV function of excel)
	Amount needed	$2,577,575	From above
	Amount of Shortfall:2577575-1113511	$1,464,065

c)

	Monthly Savings Needed
Rate	Monthly Interest Rate=(1.055^(1/12))-1	0.45%
Nper	Number of months of savings =25*12	300
Fv	Future Value of savings	$1,464,065
PMT	Monthly Savings Needed	$2,327
	(Using PMT function of excel)	PMT(0.44717%, 300,0,1464065)