7 SORP Import Tool

The utility of the Loan Calculator and SORP Calculator is apparent from a financial advising and consumer prospective, and is a clear demonstration of R’s statistical and graphical capabilities. However, R is famed for its ability to easily manipulate and cleanse large data sets - the need for which arises all too often in the actuarial industry. If this app was to be a showcase of where R can add value, then a demonstration of this database manipulation was essential.

It was decided that the best way to show this functionality was to built on top of an existing part of the app, specifically the SORP Calculator. This is a task that despite its complexity, is relatively formulaic, in that despite an individuals’ personal circumstances, the underlying assumptions and calculations are consistent. There is also no randomness involved, and being able to calculate projections of pension plans for many members of a scheme at once would be of great help to an actuary.

When building the SORP Calculator, along with other features of the app, the functions were designed so that they could be called by other parts of the app. For the SORP in particular, “SORP Annuity” and “SORP Contributions” functions were created which can be called from anywhere in the app. The outputs of these functions can be fed into others to calculate future and discounted values of fund values and payments. Essentially this meant that once the SORP Calculator was built once, its code and outputs could be implemented into any other page that was created. This flexibility and reusablity of code with R allows for further features to be built on top of simpler functions with ease, and the Import Tool is the perfect example of this.

Both a template file and an example database are hosted within the file structure of the app. These can be downloaded by the user, and by utilising these files, the tool can be made use of and its value fully appreciated. Before any file is uploaded to the app, these two download links are present, along with an upload tool (Figure 7.1) - this allows the user to select a file from their PC that contains the SORP Database that they wish to analyse.

Figure 7.1: Before Importing Example File

Once the file is uploaded to the app, the database file is read into R as a dataframe using the readxl package [13]. Each entry in the dataframe is sent through the functions that were built for the SORP Calculator as discussed above, in accordance with the SORP Assumptions [27]. These assumptions, as with the SORP Calculator, can be adjusted (Figure 7.2), however the box to edit these assumptions is collapsed by default to keep the UI as clean as possible.

Figure 7.2: SORP Assumptions

For each entry, the Import Tool calculates both the projected fund value and associated periodic pension payment (as both future and discounted values). These data are then appended to the original database, and displayed as a Data Table (Figure 7.3). This table is then written to a .xlsx file using the writexl package [14], which can subsequently be downloaded by the user.

Figure 7.3: After Importing Example File

The Import Tool does have its limitations - in particular, that the database has to be formatted correctly before uploading - however, it is mearly meant to serve as a demonstration of the ability to import, cleanse, manipulate, and export datasets using R.