Question

Please write down the intermidiate steps clearly. Intermidiate answers should be stated in 4 decimals in $/% while final answers in 2 decimals in $/%.

Paul is celebrating his 43d birthday today and is planning for a retirement savings his family [Planned expenses] - Paul's daughter is now 6 years old and he plans to send her to a top local university in 12 years. programe for Currently, the cost of studying (tuition fees and living expenses) is S150,000 per year, but it is expected to increase with the inflation rate of 3% per year. It takes his daughter 4 years to complete her undergraduate study, and it is assumed that he will have to pay the tutition fees and living expenses for his daughter on his 55th, 56h, 57th and 58th birthdays. Besides, he would need $2,000,000 to take his wife for a round-the-world trip at their 30th anniversary when he reaches 62. - Pal is planning to retire at the age of 65. He estimates that he will need $30,000 a month to - He would also like to donate $40,000 semiannually to his university, starting from his 70h - He plans to leave $10,000,000 to his family at his 90th birthday [Planned savings and cash inflows] enjoy his retirement years with his wife, with the first withdrawal on his 65 birthday and the last withdrawal on his 90h birthday birthday through the 80th birthday - Eight years ago, Paul joined his employer's retirement plan that paid him 6% interest (compounded monthly). At the end of each month, he deposited S5,000 into the retirement account. He is planning to move all the funds from his retirement account to a high-yielod savings account after he made the last deposit today -In addition he plans to make a quarterly deposit to the high-yield savings account, with the first deposit to be made a quarter from today and the last deposit one year before his retirement (ie, on his 64th birthday). He expects to earn a quarterly return of 3.7971% from the high-yield money to a low-risk account that enables him to withdraw money whenever he needs. It is expected that the low-risk account will pay account. When he retires, he will have to move all his i him an interest of 2% (compounded semiannually). 60th birthday. The loyalty bonus will be deposited to the high-yield account as well. He expects to stay in his company until he retires and receive a loyalty bonus of $80,000 at his - (a) How much does Paul currently have in his employer's retirement account (before he moves his (3 marks) fund to the high-yield account)? (b) What is the present value (at Paul's 43rd birthday) of the total costs for sending her daughter to (5 marks) the university for undergraduate study? (c) How much must Paul deposit every quarter in order to meet all his withdrawal needs? (12 marks)

Answer 1

(a)

Amount Available in Retirement Account

Pmt

Amount Saved Each Month

$5,000

Nper

Number of months of savings

96

(8*12)

Rate

Monthly Interest Rate

0.50%

(6/12)

FV

Amount Available in Retirement Account

$614,142.71

(Using FV function of excel with Rate=0.5%, Nper=96,Pmt=-5000)

Excel Command: FV(0.5%,96,-5000)

(b)

Present value of total cost of undergraduate study

Current Cost of study per year

$150,000

Inflation Rate

3%

A

Expected cost in first year of study

$213,864.13

(150000*(1.03^12)

B=A*1.03

Expected cost in Second year of study

$220,280.06

C=B*1.03

Expected cost in Third year of study

$226,888.46

D=C*1.03

Expected cost in Fourth year of study

$233,695.11

Expected Quarterly Return

3.7971%

Expected annual return=((1+0.037971)^4)-1

0.160755846

Expected annual return=

16.08%

12

PV1=A/(1.160756^12)

Present Value of cost of First Year of study

$35,747.59

13

PV2=B/(1.160756^13)

Present Value of cost of Second Year of study

$31,720.72

14

PV3=C/(1.160756^14)

Present Value of cost of Third Year of study

$28,147.46

15

PV4=D/(1.160756^15)

Present Value of cost of Fourth Year of study

$24,976.73

PV1+PV2+PV3+PV4

Total Present Value of undergraduate study

$120,592.50

Amount available in his retirement account

$614,142.71

Balance available after providing for study cost

$493,550.21

(614142.71-120592.50)

Present Value Today of withdrawal and terminalneeds $499,204.60 (13261089.30/(1.160756 22) Present value at the age of 70 for donations Amount of semi annual donations Number of Donations Semi annual interest rate Present value at the age of 70 for donations ExcelComm and: PV(19%,21,-40000) PVS Pmt 40,000 er Rate PV 21 1% $754,279.33 (using PVfunction of excel with Rate-196,Nper-21, Pm ta-40000,) Number of semi annual period retirement age 70 676,078.45 (754279.33/1.01 11) Value on the Date of Retirement Value of Donations today Present value of Total after retirement needs Amount available after all expenses till retirement Present Value of savings required Number of quarters of deposit Quarterly Interest Amount of deposit required every quarter ExcelCommand: PMT(3.7971%,88-148852.53) QUARTERLY DEPOSIT REQUIRED 25,450.51 (676078.45/(1.16075622)) 524,655.11 375,802.58 148,852.53 PV6 Nper Rate PMT 88 (22 4) 3737196 $5,873.17 (Using PMIT function of excel with Rate-3.7971%,Nper-88,Pv:-14885253.) - $5,873.17