Calculating Pension Liability Duration

[jg1996business]

I have been asked to put together a report for a client that discusses the implementation of an immunization strategy on their pension plan. Most of what we have done in the past is simply asset only efficient frontier type studies but this is more of an asset/liability duration matching type study so I am having to get up to speed on all of this stuff pretty quickly.
So step 1: Calculate the current duration of their pension liabilities
and brick wall………..
I have the Projected Liability Cashflows from their actuary and I wrote a simple Excel function to calculate the duration based on the following formula from wikipedia:
D=Sum((Pi) * (ti) / V)
Pi = present value of the ith cash liability
ti = time in years until the ith cash liability will be payed out
V = Sum (Pi)
Can someone look over the following VBA code to see if I am implementing this formula properly and also perhaps if you have some experience in this area, let me know if I am approaching this case with the proper duration formula.
Function PensionDuration(r As Single, PLC As Range) As Single
‘r is our current discount rate
‘PLC is a range of cells that contain our
‘projected liability cashflows
Dim i As Integer
Dim Temp As Single
Dim sumPV As Single
sumPV = 0
Temp = 0
‘Calculate NPV or our Projected Liability Cashflows
‘assuming the first row of our cashflows occurs at
‘t = 1
For i = 1 To PLC.Rows.Count
sumPV = sumPV + PLC.Resize(1, 1).Offset(i - 1, 0) / (1 + r) ^ i
Next
‘Calculate the top half of our Pension Duration
‘formula
For i = 1 To PLC.Rows.Count
Temp = Temp + (PLC.Resize(1, 1).Offset(i - 1, 0) / (1 + r) ^ i) * i
Next
‘divide the top half by the bottom half
PensionDuration = Temp / sumPV
End Function

 

[jg1996business]

Bump…….
Aren’t there any pension people or even Fixed Income jocks here that can give me their insight?

 

[ymmt]

Can you provide us with some sample data. I think you have the right approach to getting the duration. It’s like assuming all the CFs are zero coupon bonds, therefore the duration is just the time to mat. I can’t address the VBA cause the code is beyond me…
Re-read the contingent immunization chapter in LIII if you’ve taken it. It should help. Will think on this more tonight…