The client
Cumpston Sarjeant was engaged by a global creative communication consultancy to develop a financial calculator for the website of an Australian superannuation fund. The client designed and built the website itself. Cumpston Sarjeant was engaged to develop the financial back-end model.
The challenge
- Develop a financial calculator for an Australian superannuation fund which will be used by the fund’s members.
- Provide the superannuation fund with transparency regarding the underlying calculations, to ensure confidence in the calculations which will be delivered to the fund’s members.
- Perform the task within a robust software development framework, enabling thorough testing of the underlying calculations (including by the superannuation fund) and in a manner which allows for code re-use of the core financial model by the communications consultancy.
The solution
The financial model was developed as a C# code library in Visual Studio. A Visual Studio solution can include an Excel spreadsheet. By doing so, an Excel spreadsheet can be used as a user interface to the C# code library.
With this approach, scenario and batch testing were able to be conducted using Excel; with all scenario variables input via Excel and detailed model calculations and projections output to Excel. This enabled thorough internal testing of the underlying model whilst providing a familiar user-friendly environment to the superannuation fund for additional external testing and auditing.
The core financial model though remains contained within the C# code library, independent of all Excel worksheet functions and instances. The code library can therefore also be used in a stand-alone desktop application, a mobile app, or configured as a server-side web API.
The communications consultancy also used automatic conversion tools to translate the C# code into TypeScript, for use on the web as a client-side script.
The benefits
1. User-friendly testing
In addition to thorough internal testing, the superannuation fund was able to test model calculations and outputs in a familiar and user-friendly Excel spreadsheet environment.
The fund obtained comfort regarding the projections provided by the model; instead of naively using the financial model as a “black-box”.
2. Re-usable code library
The core financial model was developed within a C# code library. This meant that the communications consultancy could easily re-use the financial model in a range of applications and user interfaces.
This was preferred to a VBA developed model, which is inextricably tied to the spreadsheet in which it is written, and prevents re-use outside of the Excel/VBA environment.
The approach we took ensured much greater flexibility in the final financial model. The code library can be used just as easily by an Excel spreadsheet as it can by a stand-alone desktop application, a mobile app, or configured as a server-side web API.
Cumpston Sarjeant
Cumpston Sarjeant has a range of software development experience including the development of valuation models in traditional actuarial areas of general insurance liabilities and long service leave liabilities, and the development of retirement income models for accumulation and defined benefit superannuation funds.
We also provide analytic programming tasks such as manipulation of large databases and data parsing.
Further information
Paul Thomson has extensive experience in the creation of superannuation and retirement income calculators. Contact Paul on (03) 9642 2242.
