Question

Mini-Case Barry Neill, 60, has jus where he has worked 0 has just retired from his job at the city council Barry has estimated that he will need around £3,700 per worked for 38 years. He has a family that is depen month to maintain his family and lifestyle. He will be eligible him. He has received a lump sum retirement bonus of for state pension of £300 a month once he turns 65. However, in addition to £1,700 per month as his pension. Besides if he defers withdrawing his state pension until he is 70, he will sum bonus, he also has £60,000 in an ISA account get £560 a month. downs mutual fund units worth £80,000 as of now. The ISA Advise Barry on what he should do with his receipts from mount pays 4 percent per annum. pension funds and savings.

Answer 1

I just solved this question. Not sure why it has come up again. I am reporducing the solution below.

Please follow the steps sequentially. Answers are highlighted in yellow colored cells. Adjacent cells contain excel formula

Retirement Funds		220,000	Excel formula
Savings annual rate		5%
Monthly rate		0.0041667	'=P215/12
Term (years)		35
Number of periods		420	'=12*P217
Part (1) Amount he can withdraw on monthly basis		1,110	'=PMT(P216,P218,-P214,0,0)
Monthly pension		1,700
Total monthly income		2,810	'=P219+P221
Monthly expenses		3,700
Part (2)
Since monthly income < monthly expenses
The amount determined in part (1)
is insufficient.
Monthly income required		2,000	'=P223-P221
How long his retirement savings will last? (Months after retirement)		147.45	'=NPER(P216,P229,-P214,0,0)
How long his retirement savings will last? (Years after retirement)		12.29	'=P230/12
If he reduces monthly expenditure to		3200
Monthly income required		1,500	'=P233-P221
How long his retirement savings will last? (Months after retirement)		227.14	'=NPER(P216,P234,-P214,0,0)
How long his retirement savings will last? (Years after retirement)		18.93	'=P235/12
Part (3)
Yes he should wait till he turns 65 to obtain his
statement pension.
No, even if he waits till 70, the state pension amount
will not solve the problem. The shortfall is
3700 - 2,810 = 890 > the monthly pension amount
Part (4)
Inflation		3%
How old he will be		83.45	'=60+NPER(P246,0,-1,2,0)
Cost of newspaper		3.52	'=1.25*(1+P246)^35