Skip to main content

Case Study: Impact of the oil price and FX and interest rates on profit and loss

Live access to large international data sources with Risk Kit Data

By Uwe Wehrspohn and Sergey Zhilyakov

[Download]

 

Contents

Presentation of Risk Kit Data

Profit and loss model

    Determination of the model

Selection of data

Synchronization of time-series

Calibration of oil price and interest rate models

Monte-Carlo simulation

Conclusion

 

Many people in the global economy process and analyze information on a daily basis. The importance of quick and easy access to that information cannot be overestimated today. Quite a few different data providers grant access to big databases with financial data and statistics over the internet through special APIs. From one hand, this simplifies the access to information. On the other hand, some technical knowledge is required to read and parse this information. Moreover, every data provider implements his own API, which makes it difficult to accumulate and aggregate the data in one place.

Risk Kit Data1 solves these problems and provides a standardized interface to access and process financial data and statistics from different data sources. Over a user-friendly graphical interface, users can browse statistics, perform searches, export data directly to Excel sheets and even more.

Risk Kit Data supports five popular data sources such as the European Central Bank ECB, Eurostat, the Federal Reserve Economic Data FRED, the World Bank and Apilayer Marketstack.

 

Presentation of Risk Kit Data

To top

Risk Kit Data is an add-in for Microsoft Excel. After the installation, the add-in is available as a new toolbar.

Figure 1. Risk Kit Data toolbar.

Risk Kit Data provides a graphical user interface to browse statistics and financial data. Use the Finance Task Pane by clicking ‘Browse Financial Data’ on the toolbar to get recent and historical stock and index prices and exchange rates. Access the Data Task Pane by clicking ‘Browse Historical Data’ to browse the full data bases of the European Central Bank ECB, Eurostat, the Federal Reserve Economic Data FRED, the World Bank and Apilayer Marketstack.

Use the examples under the ‘Example Workbooks’ drop-down menu to get started with Risk Kit Data. Under ‘Add-in Info’, you will find the online help as well as the information about the add-in.

To deactivate the add-in in Excel, go to the ‘Add-in Settings’ and click ‘Disable add-in’.

Note that you need a personal API token from Apilayer Marketstack to access their data feeds. To request a token go to ‘Add-in Settings’ on the toolbar and follow the displayed link. Here you also find a reference to their terms and conditions for the usage of their data.

Figure 2. Add-in Settings and API token

 

Profit and loss model

To top

The oil price has changed strongly over the last years. The high volatility especially affects companies that import crude oil and other raw materials. In this section, we consider a Profit and Loss model where the oil price is a major source of randomness. We also include the USD/EUR-exchange rate and a 1-year EUR-interest rate as additional examples of typical market factors. To get the historical prices of these factors and to consolidate the time-series, we will use the Risk Kit Data add-in.

We will also take another add-in, Risk Kit, to estimate the parameters of process models of the data and to perform a Monte-Carlo simulation of the profit and loss model. For a detailed introduction to Risk Kit see Wehrspohn/Zhilyakov, Monte-Carlo Simulation with Risk Kit, 2021.

 

Determination of the model

To top

As the first step, we define a simple purely illustrative P&L model and identify its sources of randomness (Figure 3). To calculate the gross profit and loss, we start with a turnover (D7) and deduct all expenses including the material costs (D8), raw materials (D9), personnel costs (D10) and write-offs (D11). That leads to the intermediate result ‘Earnings before interest and taxes’ (EBIT) (D12). Further, we calculate the ‘Earnings before Taxes’ (EBT) (D15) which is equal to the sum of the interest income (D13) and the extraordinary income (D14) added to EBIT.

Figure 3. Profit and Loss model.

Let’s assume that the turnover (C7), material costs (C8), raw materials (C9), personnel costs (C10) and the interest (C13) and extraordinary income (C14) are subject to risk.

We assume the turnover (C7) to have a triangular distribution with parameters a=900, b=1000 and c=1050 which means that the turnover fluctuates between 900 and 1050 and the likeliest value is 1000.

Figure 4. Identifying the sources of randomness.

You may also notice the ‘OutputName’ function in the formula bar appended to the main distribution. This function specifies the label for this particular risk, which we will see on the charts afterwards.

For the material costs, we assume a normal distribution with expectation = 4% and standard deviation = 2%.

Figure 5. Identifying the distribution for the material costs.

Since the actual material costs are variable and depend on the level of turnover, we model them as a relative quantity ‘in % of turnover’. Thus, the total material costs in D8 are equal to the relative costs (C8) multiplied by the total turnover (D7).

Figure 6. Calculating the total material costs.

One of the biggest expenses in our example is raw materials, which is the product of the total volume of the imported oil (barrels) and its price. We assume that the volume of the imported oil is uniformly distributed in interval from 7.5 to 9.5 thousand barrels. To estimate the oil price, let’s analyze the historical market data.

Selection of data

To top

To get the historical prices, activate the ‘Risk Kit Data’ toolbar (Figure 1) and click ‘Browse Historical Data’. The add-in then opens the data task pane.

Figure 7. Data task pane.

The data task pane allows to browse historical statistics from different data sources, perform searches, filter and select data and load the data to Excel.

To perform a search, activate the Search tab (Figure 8), enter the search query in the text field and press ENTER. In our example, we look for ‘crude oil’. Note that depending on the search query, the operation may take a few minutes.

Figure 8. Searching the data.

It is also possible to narrow down the search by excluding the results for some data sources. To do so, click ‘Filter Data Sources’ and clear the checkboxes against those data sources, that you want to exclude.

Figure 9. Filtering data sources.

We will use the historical prices of Brent crude oil for the estimations. Double click the ‘Crude Oil Prices: Brent – Europe’ table in the list to load the historical data. You may notice that there are four similar tables in the search results. The difference between these tables is in the frequency of the data. We use the first table with the daily frequency which provides prices for business days.

Once double clicked, the add-in activates the ‘Data’ tab.

Figure 10. Loaded data series.

In this example, we use the Federal Reserve Economic Data that has only one data series per table. The other data sources may have thousands of data series per table. Use the filters to reduce the number of data series and to specify the time window of the observations (Figure 12).

Figure 11. Oil price between 2011 and 2021

Oil prices were quite volatile in the last decade covering a range between 9.12 USD per barrel on 21 April 2020 to 128.14 on 13 March 2012. Therefore, to get a more homogenous sample that is representative for the current market situation we set the time window from 21 February 2015 to 21 February 2021.

Figure 12. Filtering data series.

Click ‘Apply Filter’ to filter the data series.

If you place the mouse cursor over the table name, you will see the detailed description in the tooltip. Notice the units of the data.

Figure 13. Table description.

To export the data to the worksheet, click ‘Export Data Series’. The tool gives two options here. The data can be exported either as a cell-function or as raw values. The cell-function is preferable because it gives more control on the data. In particular, it is possible to order the observations by date, get multiple data series at once, change the time window afterwards etc.

To output the series ID in the first row and the dates in the first column, activate the respective checkboxes.

Figure 14. Exporting the data.

The add-in automatically fills the necessary number of rows and columns.

Figure 15. Example of the DData cell-function.

To update the parameters of the function, either edit the function in Excel’s formula bar or use the Function dialog of Risk Kit Data. To do so, activate a cell where you want to place the function and click ‘Insert Function’ on the Risk Kit Data toolbar (Figure 1). In this particular example, we order the observations descending by date to have the most recent date at a stable position in the worksheet. This is often helpful since forecasts later on are rooted in the most recent known value of a series.

To do that, we set the ‘DatesInIncreasingOrder’ argument of the function to FALSE.

Figure 16. Updating the DData function using the Excel Function dialog.

Since the oil prices are in U.S. dollars, but we are interested in euros, the next step is to get the historical exchange rates and convert the prices.

To do so we stay with Federal Reserve Economic Data (FRED) to have one data provider for all time-series.

In the table of contents of the Data task pane, we choose FRED as the data source and then go to ‘Money, Banking & Finance’ – ‘Exchange Rates’ and select ‘Daily Rates’ to have a consistent periodicity with the oil price data. There we select ‘US / EUR Foreign Exchange Rate’ by double-click on the entry.

Figure 17. Browsing data tables and sources

To match the time-window for oil prices, we set the filter accordingly and import the data into our worksheet as an array function. Note that the time window stated on one of the previous steps is automatically applied to the newly selected data series.

Figure 18. Filtering the target time-window

Finally, we select a 1-year-EUR-interest rate accordingly. From the Data task pane, we choose ‘FRED’ – ‘Money, Banking & Finance’ – ‘Interest Rates’ – ‘Interest Rate Swaps’ and by double-click select the 1-year tenor based on EUR2. We filter the target time-window as for the previous series and insert the data as an array formula into the worksheet.

Figure 19. Selecting swap rates from FRED

Note that the ‘From’-date and ‘To’-date as borders of the time-window for the data selection can be defined by reference. This provides an easy way to create dynamic models that can be updated easily. Also, the end date of the series is optional and therefore can be omitted. In this case the function returns the most recent data available.

In the example below, reference dates are defined on a central worksheet ‘PARAMS’ and then quoted in the formulas.

Figure 20. Dynamic link to dates

 

Synchronization of time-series

To top

To synchronize the oil prices and exchange rates as well as the interest rates by date, we use the DDataSync function that returns a union of dates in the first column and the data series in subsequent columns. To employ the function, click ‘Insert Function’ on the toolbar (Figure 1). In the opened dialog select the DDataSync function and click OK.

Figure 21. Risk Kit Data Functions dialog.

The function takes the ranges in the first argument. Use the Excel argument separator to delimit the ranges.

Figure 22. Entering the DDataSync cell-function.

By default, the dates are in descending order. To invert the order, you can set the ‘DatesInIncreasingOrder’ argument to TRUE. Furthermore, the function interpolates the missing values using the nearest neighbors by default. To disable the interpolation, set the ‘MissingValue’ to a non-empty value that will be used instead for missing observations. In this particular example, we leave this parameter empty and also keep the descending date-order.

Click ‘Insert’ to insert the function into a cell. Use the function as array formula where the number of rows is equal to the number of dates in the specified data ranges whereas the number of columns is equal to the number of specified data ranges plus one for the date column.

Figure 23. Use of the DDataSync cell-function.

To further calculate the oil prices in euro, we simply divide the oil price by the exchange rate. We also convert the interest rate from [%] to real numbers by dividing it by 100.

Figure 24. Calculating oil price in euro.

Calibration of oil price and interest rate models

To top

To simulate the oil price and the interest rate in the context of the P&L calculation, we need models that render it possible to extrapolate the price processes into the future and well fit the historical data and the general properties of the price processes. We consider a forecast horizon of 1 year.

Risk Kit offers five process types with calibration, simulation, trends and bounds. These processes have distinct properties that make them more or less suitable for certain applications.

Name

Sign

Mean-Reversion

Long-term Increase of Percentiles

Brownian-motion

Both

No

Square root

Wiener process

Both

No

Square root plus Linear

Geometric-Brownian motion

Positive

No

Exponential

Vasicek process

Both

Yes

Stationary

Black-Karasinski process

Positive

Yes

Stationary

Figure 25. Process types and their properties

For a qualitative selection of a process type it is helpful to plot the process to be calibrated.

Figure 26. Oil price development

The oil price as a commodity price typically cannot take on negative values. Thus, only a geometric Brownian-motion or a Black-Karasinski process are suitable.

In the time-window under consideration, the oil price is also quite stationary without a clearly distinguishable long-term trend.

Calibrated to the data, both process types give quite different forecasts. In particular, the exponentially increasing percentiles at the 99%-quantile reach oil price levels that are far beyond the range of the historical data. The confidence bands of the Black-Karasinski process on the other hand provide a moderately conservative cover of the historical range of the oil price3. Therefore, we choose the Black-Karasinski model for the 1-year forecast4.

Figure 27. Calibrated geometric Brownian motion and Black-Karasinski process

 

Figure 28. Oil price history and forecast with Black-Karasinski model

Similarly, we look at the development of interest rates since 2015.

Figure 29. Development of interest rates

Interest rates show very different properties from commodity prices. In particular, they are currently negative. They also sometimes show episodes of non-stochastic drifts such as in 2015 in the example due to interventions of the central bank. Generally big movements often go hand in hand with exogenous shocks.

Since interest rates are a relative quantity, they usually have a stationary long-term trend and do not systematically grow.

From these considerations, we select the Vasicek-model for interest rates.

Figure 30. Calibrated Vasicek-model for interest rates

Note that the range of the forecast over the next year does not include interest rate levels as in early 2015. We accept this model behaviour because the movement in 2015 was largely due to exogenous factors and the policy of the central bank.

Figure 31. History of interest rates and forecast with Vasicek-model

To calibrate both models for oil prices and interest rates, we use the Risk Kit-function ‘CalibrateMVProcess’. It estimates the parameters of both processes and the correlations between them so that the joint behaviour of the processes is covered. Note that the function allows for each process to have a different type. Thus, it is possible for oil prices to follow a Black-Karasinski process while interest rates have a Vasicek process.

The parameter ‘DeltaT’ assumes that the input time-series for the calibration has equidistant time-steps. The estimated process parameters are scaled to this time-increment.

Figure 32. Joint calibration of both processes

For the simulation we use the complementary function ‘MV_General_path’ which uses the parameters in the same order that the function ‘CalibrateMVProcess’ returns.

Note that now it is particularly convenient to have the historical price processes in descending date-order so that the most recent price the simulation starts at has a stable position in the worksheet.

For the simulation the parameter ‘DeltaT’ has to be chosen as in the calibration in the previous step because the process parameters are scaled accordingly. The ‘times’ where the simulated process is evaluated have to be expressed in multiples of ‘DeltaT’. Thus, if DeltaT=1 stands for daily data which is available for 5 business days per week, a forecast over a month has a forecast horizon of 22 business days and over a year of 260 days.

Figure 33. Joint simulation of both processes

Now we are ready to complete our profit and loss model.

The amount of oil needed is uniformly distributed over the interval from 7.500 to 9.500 barrels. We model the random oil price that is effectively paid over the year as the average oil price over the twelve months.

Figure 34. Simulating the raw material costs.

Figure 35. The effective oil price paid as 12-months-average

We assume that the interest income is determined by the 1YEUR rate in one year paid on a notional of 10 mio. EUR.

Figure 36. Simulating the interest income

The value of the simulated interest rate is taken from the interest rate process after 260 days.

Figure 37. Reference to simulated interest rate after 365 days

By analogy, we model the remaining expenses. The personnel costs are specified as a normal distribution with expectation 325 and standard deviation 20. We also limit the lowest possible value to 300, which means that personnel costs lower than 300 will not be sampled.

An extraordinary income occurs if one or several counterparties default before they have paid their bills. If a default occurs, we assume that the size of the loss is PERT-distributed in a range from 0 to 5 with the likeliest value of 3. Since every loss is independent of each other, we use the Compound cell-function (Figure 38) that adds up a certain number of random variates. The number of defaults in each fiscal year is Poisson-distributed with an expected number of 5 defaults.

Figure 38. Identifying the extraordinary income.

 

Monte-Carlo simulation

To top

Before we simulate the model, let’s identify the model inputs and outputs. Since we are interested in the behavior of EBIT (D12) and EBT (D15) under these influences of the specified risks, we mark these quantities as model outputs. We are also interested in the impact of the risks on EBIT and EBT. Therefore, we mark all the risks (C7:C10, C13:C14) as inputs.

Figure 39. Model inputs and outputs.

To mark the model inputs and outputs, we use the ‘Modelling’ group on the Risk Kit toolbar.

At this point, the model is completely defined and we can start the simulation. However, to better interpret the simulation results, we make a few additional steps5.

First, we define the Plot cell-function to get an impression of the shape of the EBIT and EBT. The function takes the output-cells in the first argument and plots the results in a dialog window after the simulation.

Figure 40. Plot cell-function.

A variety of optional arguments of the function allows to customize the appearance of the charts.

Via the function 'PlotInCell' or via the configuration setting 'Show in-cell histograms', charts can also be displayed in the cell6.

Secondly, we calculate basic statistics using Risk Kit functions. These functions take the output cell as the first argument and calculate the results after the simulation.

Figure 41. Calculating statistics.

Thirdly, we specify the ‘Sensitivity’ cell-function that calculates the impact of the model inputs on the respective output

Figure 42. Calculating sensitivities.

The Sensitivity function can also produce the chart and embed it into the worksheet. To take advantage of this feature, set the ‘CreateChart’ argument of the function to TRUE and specify the ‘ResultCell’ where the chart is embedded.

Figure 43. Customizing the Sensitivity function.

To finally start the simulation, click ‘Simulate’ on the Risk Kit toolbar. After the simulation, the add-in plots the EBIT and EBT distributions in a separate window.

Figure 44. Plot window.

It also calculates the statistics (Figure 45) and sensitivities (Figure 46).

Figure 45. Basic statistics and charts.

Figure 46. Sensitivity results.

In our example, the raw material costs have the largest impact on EBT. In particular, the negative sign of the sensitivity indicates that an increase of the oil price leads to a decrease in EBT.

Compared to raw materials, the impact of the uncertainty in interest rates and interest income is very small.

Conclusion

To top

In this example, we have considered the impact of the oil price in USD on a profit and loss statement in EUR. We have also taken account of uncertainty in interest rates.

With Risk Kit Data, we were able to find and process historical oil prices, foreign exchange rates and interest rates quickly and with only a minimum effort. The data was synchronized, missing dates were interpolated. Process models were selected and calibrated. Forecasts were simulated and included into the P&L calculation. Various statistics, analysis techniques and graphs were derived from the simulation of the P&L model and presented in a cockpit that featured interpretable and reportable results.

This reduces the time and cost necessary to get and analyse data. Over a user-friendly graphical interface, users can browse and aggregate statistics from different sources without the need to have a broad technical background.


1 To get more information about Risk Kit Data and the other products, refer to the www.wehrpohn.info website.

2 See here for details on ICE rates.

3 Note that the sharp drop in oil prices in April 2020 due to a sudden worldwide lockdown at the beginning of the COVID-19 pandemic is outside the range of the forecast. We tolerate this deviation of the model because the price behaviour in April / May 2020 is due to an exogenous shock and since we are as oil-buyers particularly interested in procurement risk.

4 For longer-term forecasts the strength of the mean-reversion or the property entirely would have to be reconsidered.

5 For a detailed discussion and explanation of running a simulation and creating a result cockpit see Wehrspohn/Zhilyakov, Monte-Carlo Simulation with Risk Kit, 2021, p. 27ff.

6 You can find a detailed description of the graphs in cells in Wehrspohn/Zhilyakov, Monte-Carlo Simulation with Risk Kit, 2021, S. 29ff. Note that a cell can have markers as an input and output cell at the same time, so that the distributions in input cells can also be plotted with the 'PlotInCell' function.