Free Pensions Software

hi,

i'm thinking of releasing an Excel addin that I've written over the last few years for free.

It essentially allows you to:

a) calculate all the standard annuities and assurances through excel user defined functions

b) define you own pension fund factors via a custom function generator.

This would effectively allow complete valuations to be performed through a simple spreadsheet that could be much more transparent than a traditional valuation system.

The addin is written in c++ so it calculates extremely quickly. For example, calculating past and future service liability for 20 members, for age retirement, withdrawal, illhealth and death in service took about 2 seconds on an example spreadsheet I made using the addin.

Do you think this sort of tool could be useful in your day to day work in pensions, or are you tied to using your in house tools?

My motivation for writing the addin, was that none of the existing systems I had used allowed the user to be in control of the calculation.

So rather than provide a blackbox with parameters, I'm saying let the actuary be in control and define the calculations themselves.

In my experience it is fairly common to write spreadsheets to value deferreds and pensioners, since they are based on a single decrement. The multiple decrement calculations for actives, have generally been too difficult (and slow) to write in a spreadsheet. This addin would overcome this problem, by doing the slow work outside of excel, through user defined pension fund factors.

If this sounds good, then maybe I will setup a download for it, some time after the exams.

 

Hi Gareth

That does sound excellent.

I personally would be very interested in seeing a copy - particularly if you'd be happy to show the source code?

However I'm obviously not your target market as I work in GI. I would think that most pensions companies wouldn't be that keen as:

*there's no proof that it has been tested thoroughly (although I'm not doubting it has). If a bug was discovered later that cost them money they wouldn't have any recourse - I doubt you have professional indemnity cover?
*I'm not sure whether it is best practice to adopt a software package downloaded for free off the Acted website. Operational risk etc..
*Lack of support if things go wrong (unless you're offering service contracts)
*In general I think a lot of actuaries are suspicious of things they can't run through to understand and I would be surprised if many know how to program in C++

Obviously some of this would be solved if you were thinking about doing this seriously (setting up a software company etc..)

One thing that companies may be more interested in was an actives valuation system that produced the yearly cashflows for the actives rather than just the present value (something that I believe might be required in legislation at some point??). For that all the problems of multi-decrements are compounded tenfold so the speed of C++ would be even more vital. Additionally while I suspect most companies will have existing PV active valuation systems many may not have cashflow ones.

 

sounds like an excellent idea. I have recently taken up a pension position and would really love to use your addin!

 

well, if you fancy having a play around with a early beta version of it, visit ActXL.com

 

out of curiousity, for those of you who do work in pensions, how long does your in house system take to calculate each member roughly (for past service, and future service in one run)?

i did a test run on 51 members (working out past service reserve and future service on projected unit (splitting results by retirement, ill-health, withdrawal, death in service)...and it came in at about 8 seconds (on a 1.5ghz machine). That works out at 0.16 seconds per member, which seems quite reasonable to me...(and this is on an Excel spreadsheet).

 

On our in house pensions system, 337 members took 15 seconds to value both past service and future service liabilities, assuming a withdrawal decrement.

This was done on a 2.20 ghz Intel Celeron machine.

 

In the field of microelectronics, the final simulation carried out on a meduim sized chip (1M transistors) before it is manufactured takes 15 days on 32 qual-core 3.125Ghz Sparc processors. Thats 13 days with 3000Ghz of un-interrupted processing power!!!

 

My company has developed its own inhouse excel addin written in c++. It seems to work pretty quickly. We've never used a blackbox approach to valuing benefits.

We also have our own inhouse actuarial scripting language which is even quicker than excel if we've got lots of members.

Not sure I could imagine working any other way so I'd certainly find your spreadsheet useful if I moved jobs.

 

Hi there, where could I find this add-in?