In the development of RiskAgility Financial Modeller (RiskAgility FM), we looked at every part of the reporting cycle and, based on feedback from our clients, developed a financial projection system that improved on the functionality that was in MoSes. The area that saw the biggest change was input management.
This article demonstrates how the Input Manager of RiskAgility FM can be used in different model structures to set assumptions. We will illustrate its benefits and advantages with some examples, including in a situation with arrayed submodels.
To begin, let us recall briefly the nature and structure of the Input Manager. It is a crucial part of RiskAgility FM where all input data can be organised and maintained in a simple and user-friendly way. Its main purpose is to draw a clear line between input and coding.
Input information can be either set directly in RiskAgility FM by using default values or assumptions, or it can be extracted from external sources. External sources can be Excel workbooks, text files or databases (including FoxPro tables to retain MoSes compatibility). Organising all external sources in a single location provides a high level of transparency. It also helps the user prevent duplication and allows the sources to be adapted in a very quick and easy way.
Splitting the assumptions from the code has many advantages. For example, no code changes are required to swap between looking up the mortality rate by age and sex to using an additional lookup like the smoker status or even changing to a single value. Hence different sensitivities can use assumptions in different formats to the base case. During development, the coder can concentrate on what the code should do rather than thinking about the specific format of the assumptions.
A model can contain multiple Input Managers, e.g. one for movement runs and one for sensitivity runs. Within one Input Manager, variables can be organised into Input Pages to gain a better overview of the entire input process, e.g. by grouping expense variables together. It is also possible to create assumption sets for each of the Input Pages meaning that the user can decide which value to use according to the specific scenario. This makes the process efficient and flexible.
Reading input data from external sources is a straightforward process. Instead of writing lengthy or complicated lookups in the code, this can be achieved in three simple steps which are explained in the following example:
Here, we would like to assign a value to a Code Variable (exp_load_acq_alpha_gamma_sa_t) identified as a cost parameter. Code Variables can either get their value from the Input Manager or are set a value directly in the code, but not both.
Step 1: Tell the model that the value of this Code Variable should be allocated in the Input Manager by just choosing the option “All” in the box “Set Value in Input Manager” in the “properties” window (see Figure 1).
Step 2: Create a new Input Variable in the Input Manager. Then, by dragging the Code Variable from the list on the left-hand side directly onto the Input Variable, the association between both variables is established (see Figure 2). In this example we are associating the Input Variable α_ γ (sum assured) with the Code Variable: exp_load_acq_alpha_gamma_sa_t. This is one method to ensure that the Code Variable gets its value from the Input Manager. Alternatively, we could have dragged the Code Variable into the empty area on the Run Page (or on a heading) to automatically create a new Input Variable with the same name and type as the Code Variable.
Step 3: One method to obtain a value for the Input Variable is to use a lookup in an underlying Excel file. In this example we are looking up the value for α_ γ (sum assured). For the row lookup a Code Variable (trad_pol_prod_i) is used which is set in the Code Manager and is dependent on the relevant product code. The column lookup is the constant string “alpha_gamma_SA”. The lookup procedure is made easier by the option to preview the data in the external source (see Figure 3).
Various fallback options are available for a lookup which does not produce a result. In our example, we want the model to give an error when the lookups do not produce a result.
In other cases, for example if a look up for mortality rates for ages greater than the omega (max) age was attempted, one option would be to choose the mortality rate for the omega age, i.e. the last age in the table, or to return the value 1.
The “Lookup Model Object” is relevant to Lookup Terms that are obtained from the RiskAgility FM Code Manager (i.e. Code Variables, Columns or Scalers) and can be either “Absolute” or “Relative”. Absolute means that the lookup term is taken from the model object specified when selecting the Lookup Term. If the model object is an arrayed submodel, the Lookup Term from the corresponding instance is requested. This will be shown in the next example. Relative means that the Lookup Term is taken from the same model object in which the Code Variable linked to the Input Variable sits. Hence the option Relative is usually used if the same model class is used in several model objects in the model tree. However the underlying model object does not have to be of the same model class; as long as it contains the lookup term, it can be used.
These features reduce the coding requirements of changing assumptions for the cost variable in this example. Again, this leads to higher transparency and user friendliness of the code.
Analogous to the first example, here, a Code Variable representing the lapse rate is associated with an Input Variable (lapse_rates) whose value will be populated using a look up.
We want to read in the product- and policy-year-dependent lapse rates from an Excel spreadsheet. This three-dimensional lookup is divided into two two-dimensional lookups (Figures 4 and 5).
In the first lookup a header (lapse-rates-header) for the lapse rate is determined using the product code. This Input Variable is associated with a variable with the same name in the Code Manager.
A variable (trad_pol_prod_i), which is determined in the Code Manager using the product code, is used in the row lookup. The column lookup is a constant string, “Lapse”. By splitting the three-dimensional lookup into two separate two-dimensional lookups, it is an easy exercise to determine the desired rate.
In the second step (Figure 5) we use the header determined in the previous lookup (Figure 4) as the column lookup. To obtain the lapse rate, we use a Column (trad_pol: pol_year) as the row lookup. For example, if “L1” was the value returned for lapse rate header from the first step and we needed to know the lapse rate in policy year 4, we would obtain 0.05 as result.
Example with arrayed submodels
Let us now consider a more complex model. Imagine that there exists a top model, in our case a cashflow model which aggregates gains and losses calculated in an arrayed submodel representing funds. In other words, this submodel consists of several instances and, each of them is identified as a specific fund investing into certain stock indices. The number of funds or, technically speaking, the number of instances is specified in the top model. In our example we assume that there are three different funds being modelled, but this could easily be extended to more funds.
Within the model, we use the following simplified roll forward of the fund value (see Figure 7):
The calculation of the investment return rate for each fund can be done in a straightforward way.
In the first step, we initialise the fund model with three instances (Figure 8). The number of funds is hard-coded here, but could also be determined from the assumptions, or the underlying data.
In the next step, we define a scalar “fund_name” in the fund_model (Figure 9) to perform a lookup for the investment return header in Figure 10. Here, we are using the function “getElementNumber()” which returns the number of the instance in an arrayed submodel, in our example 0, 1 or 2.
Then we create a single Input Variable investment_rate_header in the Input Manager that uses the Scalar fund_name as the row lookup to translate the fund name into the header name. If our model instance was 0, which would correspond to Fund 1, we would have an investement rate header EuroStoxx50 (see Figure 10).
In the last step we can look up the investment return rate using the header obtained in the previous step, i.e. we use a variable of the fund submodel to determine the correct investment return rate for each fund. For example, investment_rate_header returned EuroStoxx50 for Fund 1, we can use this in the next lookup to read the correct investment return, which is 0.03 (Figure 11).
In this example, we have shown how the Input Manager can be used to efficiently determine the appropriate assumption and parameter values to use in arrayed models containing multiple instances.
We have chosen the setup above because this is how many of our clients’ models are currently set up. However, the flexibility of RiskAgility FM Input Manager allows you to set up your data and assumptions in any way that suits your company’s structure and processes. For example, with RiskAgility FM, there is an interesting alternative to the above investment rate reading process. Since “data“ can now be any external source, for example a named range in a spreadsheet, Data Driven arrayed submodels can now be used much more widely. In the example above, we could have set up a “data” table in a spreadsheet containing e.g. the fund name and the index to be used for the investment return.
Then the submodel array is automatically resized to the number of funds in the table and the investment_rate_header can be linked to the data item. The lookup of the return in Figure 11 would stay the same.
The Input Manager is designed to enable users to manage input data in a transparent and straightforward way. Not only in simple examples but also in more complex model structures it provides an efficient and elegant solution to import data into a RiskAgility project without affecting the code.