Question

You have recently been appointed as a pension fund specialist for your company and you have been tasked to perform valuation on some of the pension fund portfolios.
Your company wants to know the minimum annual return required on the pension fund in order to make all required payments over the next five years and not diminish the current asset base. The fund currently has assets of R500 million.
Required:
3.1 Determine the rate of return if outflows are expected to exceed inflows by R50 million per year.
3.2 Determine the rate of return with the following fund cash flows.
yr 1 inflows R55 000 000 outflows R100 000 000
yr2 inflows R60 000 000 outflows R110 000 000
yr3 inflows R60 000 000 outflows R120 000 000
yr4 inflows R60 000 000 outflows R135 000 000
yr5 inflows R64 000 000 outflows R145 000 000
3.3 Consider the cash flows in part 3.2. What will happen to your asset base if you earn 10%?20%?

Answer 1

Part 3.1

Given Initial Fund = R500mn, Every year net outflows for 5 years = Outflows - Inflows = 50mn

Each year if we make 50mn on the initial fund, it should be sufficient to pay off the net ouflows of 50mn.

So required minimum rate of return to see that there is no diminish in asset base = 50mn/500mn = 10%

Part 3.2

Determining Rate of return is not possible without the use of a calculator or an Excel sheet.

Given below I used an excel sheet and used the goal seek tool in DATA tab of excel to arrive at the Required Rate of Return

F	G	H	I	J	K
23				Rate of return	12.00%
24	Year	Inflows	Outflows	Net cashflow	Balance
25	0	500000000		500000000	500000000
26	1	55000000	100000000	-45000000	515022525.2
27	2	60000000	110000000	-50000000	526848430.3
28	3	60000000	120000000	-60000000	530093976.7
29	4	60000000	135000000	-75000000	518729134.8
30	5	64000000	145000000	-81000000	500000000.0

Given below is the formula sheet used

F	G	H	I	J	K
23					0.120045050461063
24	Year	Inflows	Outflows	Net cashflow	Balance
25	0	500000000		=H25-I25	=J25
26	1	55000000	100000000	=H26-I26	=K25*(1+K$23)+J26
27	2	60000000	110000000	=H27-I27	=K26*(1+K$23)+J27
28	3	60000000	120000000	=H28-I28	=K27*(1+K$23)+J28
29	4	60000000	135000000	=H29-I29	=K28*(1+K$23)+J29
30	5	64000000	145000000	=H30-I30	=K29*(1+K$23)+J30

After I give the following data and formulae in excel. Go to DATA Tool Bar --> What-If Analysis --> Goal Seek.

We get the required rate of Return in K23 cell. 12%

Part 3.3

If we have a return rate = 10%, the asset base reduces to $436,720,500 as the required rate is 12% to maintain the asset base without diminishing. Given below is the working

F	G	H	I	J	K
23					10.00%
24	Year	Inflows	Outflows	Net cashflow	Balance
25	0	500000000		500000000	500000000
26	1	55000000	100000000	-45000000	505000000
27	2	60000000	110000000	-50000000	505500000
28	3	60000000	120000000	-60000000	496050000
29	4	60000000	135000000	-75000000	470655000
30	5	64000000	145000000	-81000000	436720500.0

If the return rate = 20%, the asset base appreciates to $807,048,000 as the required rate is 12% to maintain the asset base without diminishing or appreciating. Given below is the working.

F	G	H	I	J	K
23					20.00%
24	Year	Inflows	Outflows	Net cashflow	Balance
25	0	500000000		500000000	500000000
26	1	55000000	100000000	-45000000	555000000
27	2	60000000	110000000	-50000000	616000000
28	3	60000000	120000000	-60000000	679200000
29	4	60000000	135000000	-75000000	740040000
30	5	64000000	145000000	-81000000	807048000.0

Both the above workings have the same formulae in the cells as of the first given formulae sheet.

Please provide feedback.