Question

Your father is 50 years old and will retire in 10 years. He expects to live for 25 years after he retires, until he is 85. He wants a fixed retirement income that has the same purchasing power at the time he retires as $50,000 has today. (The real value of his retirement income will decline annually after he retires.) His retirement income will begin the day he retires, 10 years from today, at which time he will receive 24 additional annual payments. Annual inflation is expected to be 6%. He currently has $145,000 saved, and he expects to earn 10% annually on his savings. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.

Answer 1

New Microsoft Office Excel Worksheet - Microsoft Excel Page Layout Formulas Data Review View Currency COD = Home Insert * Cut La Copy Paste Format Painter Clipboard L2989 - F Calibri B I Wrap Text Merge & Center - 0.00 8.09 - 11 - A A U - - >> A E Font Fax =PV(10%,25,89542.38,0,1) G WD - Autosum - 4 A Fill Z Conditional Format Conditional Format Cell Cell Insert Delete Format Insert Delete Format Sort & Find & 2 Clear Formatting as Table Styles Filter Select Styles Cells Editing EFF Alignment $ - % , Number I J K L M N O P Q R S value of 50000 today present at the date of value*(1+inflation retirement rate)an 2988 50000*(1.06)-10 89542.3848 present value of annual retirement Using present benefits at the time value function in of retirement MS excel pv(rate, nper,pmt,fv, typ e) rate -10% nper = 25 pmt = 89542.38 fv =0 type=1 PV(10%,25, 89542.38,0, 2989 ($894,057.74) fv(rate,nper,pmt,pv, typ future value of Using future value e) rate=10% nper =10 FV(10%,10, 135000 at the time of function in MS pmt=0 pv =-145000 type 0,- retirement excel 145000,1) 2990 $376,092.66 remaining Amount to be saved for getting Sheet25 Sheet26 Sheet27 Ke Sheet22 Sheet23 Sheet24 Sheet28 Sheet29

New Microsoft Office Excel Worksheet - Microsoft Excel View Η Σ AutoSum" Α Currency Eg Copy COD = Home Insert Page Layout Formulas Data Review Cut Calibri - 11 - A A Paste Format Painter BI U B A E Clipboard Font L2990 fo =FV(10%, 10,0,-145000,1) F G н Fill Wrap Text E E Merge & Center - Alignment $ 8:09 % , Number Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Z Sort & Find & Filter Select Editing Cells I J K L M N O P Q R S value of 50000 today present at the date of value*(1+inflation retirement rate)an 2988 50000*(1.06)-10 89542.3848 present value of annual retirement Using present benefits at the time value function in of retirement MS excel pv(rate, nper,pmt,fv, typ e) rate -10% nper = 25 pmt = 89542.38 fv =0 type=1 PV(10%,25, 89542.38,0, 2989 ($894,057.74) fv(rate,nper,pmt,pv, typ future value of Using future value e) rate=10% nper =10 FV(10%,10, 135000 at the time of function in MS pmt=0 pv =-145000 type 0,- retirement excel =1 145000,1) 2990 $376,092.66 remaining Amount to be saved for getting Sheet25 Sheet26 Sheet27 Sheet22 Sheet23 Sheet24 Sheet28 Sheet29

Eg Copy COD = New Microsoft Office Excel Worksheet - Microsoft Excel Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Wrap Text Currency WD - Autosum - 4 A Fill Z Paste Format Painter BI U B A E E E Merge & Center - $ % , 8:09 Conditional Format Cell Insert Delete Format Sort & Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing L2992 Fax =PMT(10%, 10,0,517965.08,1) F G н I J K M N O P Q R S remaining Amount to be saved for getting the retirement 894057.74- benefits 376092.66 2991 517965.08 pmt(rate,nper,pv,fv, typ e) rate -10% nper =10 pv =0 fv = 517965.08type =1 Using pmt function in MS excel Annual payment till time of retirement ($29,545.38) 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002

value of 50000 today at the date of retirement	present value*(1+inflation rate)^n	50000*(1.06)^10	89542.3848
present value of annual retirement benefits at the time of retirement	Using present value function in MS excel	pv(rate,nper,pmt,fv,type) rate =10% nper = 25 pmt = 89542.38 fv =0 type =1	PV(10%,25,89542.38,0,1)	($894,057.74)
future value of 135000 at the time of retirement	Using future value function in MS excel	fv(rate,nper,pmt,pv,type) rate =10% nper =10 pmt =0 pv =-145000 type =1	FV(10%,10,0,-145000,1)	$376,092.66
remaining Amount to be saved for getting the retirement benefits	894057.74-376092.66			517965.08
Annual payment till time of retirement	Using pmt function in MS excel	pmt(rate,nper,pv,fv,type) rate =10% nper =10 pv =0 fv = 517965.08 type =1		($29,545.38)
He should save 29545.38 annually to get the retirement benefits