Question

John starts his career at 21 years old and expects to retire 44 years later at the age of 65. His first annual salary is $72,000 that will increase at 1.5% per year until he finishes his part-time MBA at 28 years old. With his MBA, John expects salary to increase at 3% per year until retirement. At the end of each year, he deposits 10% of his annual salary into a retirement saving plan that pays 6% interest per year compounded monthly. On the first day of his retirement, John converts his whole retirement saving plan into a registered retirement income fund (RRIF) that earns 8% interest per year compounded quarterly. The RRIF will pay John $Y per quarter, the first payment being paid on the day he buys the RRIF, for 25 years. Find Y. (Show your work without using MS Excel)

Answer 1

		Future value(FV) of Cash flow at the end of Year N
		FV=(Cash Flow)*(1+i)^(N-t)
		t=time (year) of Cash Flow
		i=interest rate
		Monthly interest rate=(6/12)%=	0.005
		Annual effective Rate =((1+0.005)^12)-1=	0.061677812
		i=0.061678, N=44
		Salary in Year2=72000*1.015=	$73,080
		Till t=7(Age 28), Salary in year (t+1)=1.015*(Salary in year(t)
		After t=7, Salary in year (t+1)=1.03*(salary in year (t)
		t	A	B=0.1*A	FV=B*(1.061678^(44-t)
		Year	Salary	Cashflow(Savings)	Future Value of Cash flow
		1	$72,000	$7,200	$94,411
		2	$73,080	$7,308	$90,260
		3	$74,176	$7,418	$86,292
		4	$75,289	$7,529	$82,498
		5	$76,418	$7,642	$78,871
		6	$77,564	$7,756	$75,403
		7	$78,728	$7,873	$72,088
		8	$81,090	$8,109	$69,937
		9	$83,522	$8,352	$67,850
		10	$86,028	$8,603	$65,826
		11	$88,609	$8,861	$63,862
		12	$91,267	$9,127	$61,956
		13	$94,005	$9,401	$60,108
		14	$96,825	$9,683	$58,314
		15	$99,730	$9,973	$56,574
		16	$102,722	$10,272	$54,886
		17	$105,804	$10,580	$53,248
		18	$108,978	$10,898	$51,660
		19	$112,247	$11,225	$50,118
		20	$115,615	$11,561	$48,623
		21	$119,083	$11,908	$47,172
		22	$122,656	$12,266	$45,765
		23	$126,335	$12,634	$44,399
		24	$130,125	$13,013	$43,074
		25	$134,029	$13,403	$41,789
		26	$138,050	$13,805	$40,542
		27	$142,191	$14,219	$39,332
		28	$146,457	$14,646	$38,159
		29	$150,851	$15,085	$37,020
		30	$155,376	$15,538	$35,916
		31	$160,038	$16,004	$34,844
		32	$164,839	$16,484	$33,804
		33	$169,784	$16,978	$32,796
		34	$174,877	$17,488	$31,817
		35	$180,124	$18,012	$30,868
		36	$185,527	$18,553	$29,947
		37	$191,093	$19,109	$29,053
		38	$196,826	$19,683	$28,186
		39	$202,731	$20,273	$27,345
		40	$208,813	$20,881	$26,529
		41	$215,077	$21,508	$25,738
		42	$221,530	$22,153	$24,970
		43	$228,175	$22,818	$24,225
		44	$235,021	$23,502	$23,502
				SUM	$2,159,580
	PV	Retirement Savings at age 65	$2,159,580
	Rate	Quarterly Interest Rate=(8/4)%	2%
	Nper	Number of quarterly payments	100	(25*4)
	Type	Payments at beginning of quarters	1
	PMT	Y=Payment per quarter	$49,125.67	(Using PMT function of excel with Rate=2%,Nper=100,Pv=-2159500,Type=1)
		ExcelCommand : PMT(2%,100,-2159580,,1)