Question

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!*

I have provided the answers to the questions for reference, I just need to know how to get to them.

Samantha is going to retire in 20 years. In order to live comfortably, she thinks she will need to withdraw $10,000 every month during retirement. These monthly withdrawals will be made at the end of each month during retirement. Samantha believes she will live for 35 years after she retires. During retirement Samantha will earn 4% compounded annually.

Samantha wishes to set up a scholarship at Ryerson University. The scholarship will make annual payments to one Ryerson student. The first payment from the scholarship will be made 3 years after Samantha retires. Payments will then be made every year after that in perpetuity. The first payment from the scholarship will be for $30,000. The payments will increase by 2% per year.

Samantha has set up retirement savings account. The account earns 8% compounded quarterly. There is $15,000 in the account today.

If necessary Samantha has decided to set up a new savings account. The new account earns 6% compounded annually. Samantha will make quarterly payments into the account until she retires. The payments will be made at the end of each period.

a) How much money does she need when she retires?

Answer: $3,667,359.28

b) How much money will she have in her retirement savings account when she retires?

Answer: $73,131.59

c) What will be the amount of the quarterly payments to the new savings account that is needed to finance the shortfall?

Answer: $23,895.74

d) Samantha is prepared to increase her quarterly payments by 1% each quarter. Now what will be the amount of her first quarterly payment?

Answer: $16,961.35

Answer 1

	Pmt	Amount of withdrawal at end of each month	$10,000
	Nper	Number of months of withdrawal	420	(35*12)
		Annual compounded interest rate=4%=0.04
		Monthly interest rate=r
		(1+r)^12=1+0.04=1.04
		1+r=(1.04^(1/12))=	1.00327374
		Monthly interest rate=r=	0.00327374
	Rate	Monthly interest rate=r=	0.32737%
	A	Present Value of retirement needs at the time of retirement	$2,280,524.96	(Using PV function of excel with Rate=0.32737%, nper=420, Pmt=-10000)
		ScholarshipPayment
		Scholarshippayment at the end of year3	$30,000
		Interest Rate	4%	0.04
		Scholarship growth rate	2%	0.02
	B	Present Value of payment in perpetuity at end of year 2	$1,500,000	(30000/(0.04-0.02)
	C=B/(1.04^2)	Present Value of payment in perpetuity at the time of retirement	$1,386,834.32
a)	D=A+C	Amount she needs when she retires	$3,667,359.28
b)	Pv	Account Balance today	$15,000
	Rate	Quarterly interest rate =8/4=2%	2%
	Nper	Number of quarters to retirement	80	(20*4)
	FV	Amount of money in savings account at retirement	$73,131.59	(Using FV function of excel with Rate=2%, nper=80, Pv=-15000)
c)	Fv	Amount of shortfall=3667359.28-73131.59=	$3,594,227.69
		Quarterly interest =r
		Annual interest =6%=0.06
		(1+r)^4=1.06
		1+r=(1.06^(1/4))=	1.014673846
		Quarterly interest =r=	0.014673846
	Rate	Quarterly interest =r=	1.46738%
	Nper	Number of quarters of savings	80
	PMT	Quarterly Savings required to meet the shortfall	$23,895.74	(Using PMT function of excel with Rate=1.46738%, nper=80, Fv=-3594227.69)
d)		Present Value of Shortfall	$1,120,697.18	(3594227.69/(1.014673846^80)
		Present Value factor for quarterly increase of 1%	66.07357365
		Amount of first quarterly payment	$16,961.35	(1120697.18/66.07357365
		CALCULATION OF PRESENT VALUE FACTOR IS GIVEN BELOW
		Assume first quarter saving =$1
		N	A	PV=A/(1.014673846^N)
		Quarter	Saving	Present value
		1	$1	0.985538
		2	$1.01	0.980999
		3	$1.02	0.97648
		4	$1.03	0.971982
		5	$1.04	0.967505