Hull, RMFI., 4th Ed: Question 3.19

[[email protected]]

Hello:

The answer to this question states: "The spreadsheet is used in conjunction with Solver..."; I can't find the Excel' spreadsheet with the Solver problem.

I could go ahead and do it...but, I do not want to re- invent the wheel...Please let me know if anyone has it, or if was it supposed to be included in the paid materials...or if I should go straight ahead and prepare the solution and posted here?

 

[David Harper CFA FRM]

Hi @[email protected] That's Hull's own Q&A, I couldn't find the referenced spreadsheet, so I just created it (I was happy to see I replicated his answer on the first try given there are a couple of nuanced decisions like "is the benefit paid at the beginning or end of the year?" but then again I'm so familiar with Hull's methodology)

Here is the first version (we will add this to the next update of the PQ document): http://trtl.bz/0811-hull-rm-fi-03-19-pension-v1
... I have contribution solving for the "End of 45+18 years" value = zero; I data > grouped years 6 to 39. I hope that's helpful, thanks for bringing this up!

 

[[email protected]]

Thanks!

 

[[email protected]]

I wrote this a long time ago; feel free to used it as you- and any member here- see fit. It was never published.Only caveat: it is in Spanish (mostly).

 

[daikhan]

Thank you so much!

 

[sam313]

Hello, this is a very intriguing exercise. However, is there a way to get a solution by way of equating the solution without using the spreadsheet? thank you for your support.

 

[David Harper CFA FRM]

Hi @sam313 is there an analytical (direct formula) solution? I didn't see that when I tried it ... it seems to want a simulation. Although this is sort of the sort of timeline-based problem that I perceive to be common in the CFP, and of course it should be able to be solved with a calculator. I don't see that anybody posted the actual question, btw, so here it is:

Hull EOC Problem 3.19 (4th and 5th Editions)

"Suppose that in a certain defined benefit pension plan

• Employees work for 45 years earning wages that increase at a real rate of 2%.
• They retire with a pension equal to 70% of their final salary.
• This pension increases at the rate of inflation minus 1%.
• The pension is received for 18 years. The pension fund's income is invested in bonds that earn the inflation rate plus 1.5%.

Estimate the percentage of an employee's salary that must be contributed to the pension plan if it is to remain solvent. (Hint: Do all calculations in real rather than nominal dollars.)" -- Hull, John C.. Risk Management and Financial Institutions (Wiley Finance) (Kindle Locations 3318-3324). Wiley. Kindle Edition

 

[sam313]

I am sorry I was not clear in my question. Could you solve this problem using a calculator? For some reason, I am not able to get my head around the spreadsheet solution.

 

[David Harper CFA FRM]

Hi @sam313 At first glance I actually don't see an easy calculator solution. Perhaps this is why Hull does not show a calculation but rather delegates to his spreadsheet. It's not the logic that's hard:

1. Assume $100,000 salary (as Hull notes: The salary of the employee makes no difference to the answer. (This is because it has the effect of scaling all numbers up or down.) and grow over working years at 2.0% such that $100,000 * 1.02^44 = $239,005.3 is the final salary
2. The initial pension benefit is 70% * $239,005.3 = $167,303.72. Per the XLS, this decreases annually at 2.0% until in the 18th year the benefit pays $141,028. That's a decreasing stream over 18 years that requires a lump sum (at the beginning of retirement) in the amount of $2,420,354.51
3. Then it's just a question, answered by solver, of what % of the salary needs to be contributed in each of the working 45 years to generate a final value (at the end of 45 years) of $2,420,354.51, to fund the retirement benefit. Answer: 25.02%

The reason I got stuck entering into the calendar is that the annuities aren't level: the contributions are increasing by 2.0% and the benefits are decreasing by 1.0%, so that the typical TVM keystrokes can't be applied. I would still expect this to be somehow solveable by the calculator but I'm not able to see it immediately.