A Simulation Package in VBA to Support Finance Students for Constructing Optimal Portfolios ^†

Abstract

This paper introduces a software component created in Visual Basic for Applications (VBA) that can be applied for creating an optimal portfolio using two different methods. The first method is the seminal approach of Markowitz and is based on finding budget shares via the minimization of the variance of the underlying portfolio. The second method, developed by Hatemi-J and El-Khatib, combines risk and return directly in the optimization problem and yields budget shares that lead to maximizing the risk-adjusted return of the portfolio. This approach is consistent with the expectation of rational investors since these investors consider both risk and return as the fundamental basis for the selection of the investment assets. Our package offers another advantage that is usually neglected in the literature, which is the number of assets that should be included in the portfolio. The common practice is to assume that the number of assets is given exogenously when the portfolio is constructed. However, the current software component constructs all possible combinations and thus the investor can figure out empirically which portfolio is the best one among all portfolios considered. The software is consumer-friendly via a graphical user interface. An application is also provided to demonstrate how the software can be used using real-time series data for several assets.

1. Introduction

Knowledge delivery as a method of continuing humanity’s mind to the next generation has employed forms of tacit, explicit, and implicit knowledge [1,2]. One major method that these deliveries are shown through is the teaching and training environment. However, to ensure the delivery of knowledge, the learned materials must be assessed. Continuous investigations by researchers are taking place to seek different methods of compatibility regarding teaching materials being in line with the learner’s learning style. Research has indicated that, by personalizing teaching materials to suit the specific needs of a learner, the learning performance shows improvement [3,4]. Furthermore, the practice on learned materials emphasizes that deep learning and its effect can stay with the learner for a longer time depending on the sessions of practice.

It is widely agreed that not all theories can be directly put into practice, such as aviation (new trainee pilots require many hours of flights before being called a pilot), medicine (medical students require long hours of practice in surgery before being allowed to perform independent surgery), mathematics (mathematicians and economists require an environment where they can apply theoretical concepts in practice before they become reality), and manufacturing (which requires a tremendous amount of resources through the planning, designing, and implementation of technologies before a tangible product gets produced), and many more industries can be testimony to the value of simulation-based learning with regard to saving resources and money. In addition, detailed instruction on the process of solving a problem, including giving immediate feedback, can enhance learning [5,6,7,8].

Hence, in this paper, simulation software that shows how a decision can be improved before an actual event, such as a decision on portfolio diversification, was developed. The following sections describe the logic behind portfolio diversification, mathematical derivation and the rationale behind each method, and a chosen group of algorithms for the decision maker unit (DMU) in both simulation software, and, at the end, we discuss our findings and conclude our work. The software verbiage is available from the authors upon request.

The rest of the paper is as follows. Section 2 describes the two alternative methods that can be used for constructing a portfolio. This section also illustrates how the dimension of a portfolio can be determined endogenously. Section 3 presents our software component and describes how it can be used. Section 4 presents the finding of an empirical application. The last section provides conclusions. In Appendix A, a schematic representation of both modules that are created in this paper is provided (Figure A1 and Figure A2).

2. Methodology

In this section, we describe the alternative approaches for constructing financial portfolios. Another issue that is usually neglected in the literature is the dimension of the portfolio. It is a common practice in literature to assume that the number of assets included in the portfolio is given a priori. However, this does not need to be the case in real markets. For many investors, the selection of assets is also an endogenous question. Our software takes this issue into account by constructing all possible combinations and providing the portfolio that is optimal even with regard to the number of assets also. This approach is described in the section’s sub-section.

2.1. Portfolio Construction

The seminal method for portfolio diversification was established by Markowitz [9], and leads to obtaining budget shares via minimizing the variance of the selected portfolio with regard to the budget restriction. Let us assume that r_i represents the rate of return for asset i, which has a normal as $r_i~\Phi \left({\overline{r}}_i, {\sigma }_i^2\right)$. The variance and covariance matrix for the assets included in the portfolio (denoted by n) is expressed as $\mathsf{\Omega }={\left({\sigma }_{i,j}\right)}_{1\le i,j\le n}$; here, ${\sigma }_{ij}^{}$ is the covariance measure between the returns of the two assets i and j. Let us also define w_i as the weight for asset i. Therefore, the average return of the portfolio is defined as $F\left(w\right)={\displaystyle \sum }_{i=1}^n{\overline{r}}_i{w}_i$ and its variance as a measure of risk is $V\left(w\right)=w^{\prime }\mathsf{\Omega }w$. Hence, the optimization objective of Markowitz [9] is the following minimization problem:

$$Minimize V\left(w\right)=w^{\prime }\mathsf{\Omega }w$$

(1)

Bounded by the budget limitation expressed in Equation (2):

$$D\left(w\right)={\displaystyle \sum }_{i=1}^n{w}_i=1$$

(2)

The solution for each w_i of this optimization problem is obtained as the following, assuming that there are four assets in the portfolio (i.e., n = 4):

$$w_1=\frac{-\left|\begin{array}{ccc}{B}_{1,2}& B_{1,3}& B_{1,4}\\ B_{2,2}& B_{2,3}& B_{2,4}\\ B_{3,2}& B_{3,3}& B_{3,4}\end{array}\right|}{\left|E\right|}$$

(3)

$$w_2=\frac{\left|\begin{array}{ccc}{B}_{1,1}& B_{1,3}& B_{1,4}\\ B_{2,1}& B_{2,3}& B_{2,4}\\ B_{3,1}& B_{3,3}& B_{3,4}\end{array}\right|}{\left|E\right|}$$

(4)

$$w_3=\frac{-\left|\begin{array}{ccc}{B}_{1,1}& B_{1,2}& B_{1,4}\\ B_{2,1}& B_{2,2}& B_{2,4}\\ B_{3,1}& B_{3,2}& B_{3,4}\end{array}\right|}{\left|E\right|}$$

(5)

$$w_4=\frac{\left|\begin{array}{ccc}{B}_{1,1}& B_{1,2}& B_{1,3}\\ B_{2,1}& B_{2,2}& B_{2,3}\\ B_{3,1}& B_{3,2}& B_{3,3}\end{array}\right|}{\left|E\right|}$$

(6)

where

$$E=\left(\begin{array}{ccc}{B}_{1,1}& B_{1,2}& \begin{array}{cc}{B}_{1,3}& B_{1,4}\end{array}\\ B_{2,1}& B_{2,2}& \begin{array}{cc}{B}_{2,3}& B_{2,4}\end{array}\\ \begin{array}{c}{B}_{3,1}\\ 1\end{array}& \begin{array}{c}{B}_{3,2}\\ 1\end{array}& \begin{array}{cc}\begin{array}{c}{B}_{3,3}\\ 1\end{array}& \begin{array}{c}{B}_{3,4}\\ 1\end{array}\end{array}\end{array}\right)$$

Observe that $\left|Y\right|$ signifies the determinant of the matrix Y. In addition, notice that B is an n × n matrix that has the following attributes:

$$B_{i,j}=\left({\sigma }_{i+1,j}+{\sigma }_{j,i+1}\right)-\left({\sigma }_{i,j}+{\sigma }_{j,i}\right), \forall 1\le i\le n-1 and \forall 1\le j\le n.$$

The Markowitz approach, which is commonly utilized by investors, constructs a portfolio that has the smallest possible risk. Nonetheless, it is broadly agreed that rational investors pay attention to both risk and return when investment decisions are made. Consequently, Hatemi-J and El-Khatib [10] proposed optimizing the portfolio diversification problem, which combines risk and return directly when the portfolio is created. Specifically, the objective function in the optimization problem is the following as per the authors:

$$Maximize \frac{F\left(w\right)}{\sqrt{ V\left(w\right)}}=\frac{F\left(w\right)}{\sqrt{ w^{\prime }\mathsf{\Omega }w}}$$

(7)

subject to

$$D\left(w\right)={\displaystyle \sum }_{i=1}^n{w}_i=1$$

(8)

Via the application of Theorem 1 in the study by Hatemi-J, Hajji, and El-Khatib [11], the solutions for the optimal budget shares within this setting are provided in the following equations, when n = 4:

$$w_1=\frac{-\left|\begin{array}{ccc}{G}_{1,2}& G_{1,3}& G_{1,4}\\ G_{2,2}& G_{2,3}& G_{2,4}\\ G_{3,2}& G_{3,3}& G_{3,4}\end{array}\right|}{\left|K\right|}$$

(9)

$$w_2=\frac{\left|\begin{array}{ccc}{G}_{1,1}& G_{1,3}& G_{1,4}\\ G_{2,1}& G_{2,3}& G_{2,4}\\ G_{3,1}& G_{3,3}& G_{3,4}\end{array}\right|}{\left|K\right|}$$

(10)

$$w_3=\frac{-\left|\begin{array}{ccc}{G}_{1,1}& G_{1,2}& G_{1,4}\\ G_{2,1}& G_{2,2}& G_{2,4}\\ G_{3,1}& G_{3,2}& G_{3,4}\end{array}\right|}{\left|K\right|}$$

(11)

$$w_4=\frac{\left|\begin{array}{ccc}{G}_{1,1}& G_{1,2}& G_{1,3}\\ G_{2,1}& G_{2,2}& G_{2,3}\\ G_{3,1}& G_{3,2}& G_{3,3}\end{array}\right|}{\left|K\right|}$$

(12)

where

$$K=\left(\begin{array}{ccc}{G}_{1,1}& G_{1,2}& \begin{array}{cc}{G}_{1,3}& G_{1,4}\end{array}\\ G_{2,1}& G_{2,2}& \begin{array}{cc}{G}_{2,3}& G_{2,4}\end{array}\\ \begin{array}{c}{G}_{3,1}\\ 1\end{array}& \begin{array}{c}{G}_{3,2}\\ 1\end{array}& \begin{array}{cc}\begin{array}{c}{G}_{3,3}\\ 1\end{array}& \begin{array}{c}{G}_{3,4}\\ 1\end{array}\end{array}\end{array}\right)$$

Observe that G is an n × n matrix that has the following definition:

$$G_{i,j}={\overline{r}}_i\left({\sigma }_{i+1,j}+{\sigma }_{j,i+1}\right)-{\overline{r}}_{i+1}\left({\sigma }_{i,j}+{\sigma }_{j,i}\right), \forall 1\le i\le n-1 and \forall 1\le j\le n.$$

Accordingly, this new method merges risk and return in the optimization problem, which accords well with reality. This is the case because rational investors consider both risk and return when they make any investment decision.

2.2. The Dimension of a Portfolio

Prior to finding the budget shares, the investor must choose the assets to include in the portfolio. This is a crucial matter. The way that the literature deals with this issue is to assume that the number of assets included in the portfolio is provided exogenously. Nevertheless, this is not the way that the investors approach this issue in real markets. The selection of assets for inclusion in the portfolio is better dealt with as an endogenous question according to Hatemi-J and Hajji [12]. The authors suggested a solution that is based on selecting the maximum number of assets that the investor might be interested in based on his/her subjective preferences. Subsequently, a series of portfolios containing different permutations of these assets can be created. Suppose that n is the maximum number of assets considered by the investor for potential inclusion in the portfolio. Thus, the number of combinations (denoted by P) needed to be built is the following according to Hatemi-J and Hajji [12]:

$$P={\displaystyle \sum }_{l=0}^{n-2}C\left(n, n-l\right)={\displaystyle \sum }_{l=0}^{n-2}\frac{n!}{\left(n-l\right)!\times l!}$$

(13)

Therefore, P is the total number of permutations that are accessible to the investor as portfolios for a given n set of underlying assets. By creating all these P portfolios, the investor should calculate the risk-adjusted return for each portfolio in this set. For instance, when n is 4, P is equal to 11 portfolios based on Equation (13) as is the case in our application. Via the risk-adjusted returns for these P portfolios, the investor can retrieve the best portfolio, the second-best, the third-best, etc. This approach makes it operational to obtain the portfolio amongst these 11 portfolios that produces the highest magnitude of return for each unit of risk; that is, the best portfolio (BP) among the P combinations is acquired as

$$BP=Max\left[RA{R}_k, \cdots ,RA{R}_P \right]$$

(14)

where

$$RA{R}_k=\frac{E\left[R_{pk}\left(w\right)\right]}{\sqrt{V\left[R_{pk}\left(w\right)\right]}}$$

(15)

The denotation $E\left[R_{pk}\left(w\right)\right]$ represents the expected return of portfolio k (for k = 1, …, P) for the given vector for optimal budget shares (i.e., w). $V\left[R_{pk}\left(w\right)\right]$ represents the variance of the same portfolio and RAR_k denotes the risk-adjusted return of portfolio k. The needed optimal budget shares for each portfolio might be acquired via minimizing the variance of the portfolio as established by Markowitz [9]. Nonetheless, it is also feasible to find the optimal budget shares via the method introduced by Hatemi-J and El-Khatib [10] and generalized by Hatemi-J, Hajji, and El-Khatib [11]. These methods are described by Equations (1)–(12) above.

3. Experimental Design (Designing a New Tool)

In this section, the design of a new tool to present the process of simplifying and solving a complicated process that usually takes a long time using either pen and paper, calculators, or a simple spreadsheet for manually performing the calculations, is recommended. The authors used the power of Microsoft Visual Basic for Applications (VBA) in Microsoft Excel to create this module to automate lengthy processes of creating multiple portfolios and their comparison to select the best possible choice of portfolio from a list of diversified portfolios, where the efficiency of the work is, of course, incomparable, since the use of VBA as a tool to automate complex calculations in the industry has become the norm. Kalwar et. al. [13] gave a comprehensive list of VBA applications in the industry that clearly backs this statement. Blayney et al. [14] also presented the capabilities and use of VBA in conjunction with MS Excel to conduct preliminary analysis in big data research.

As an example, one of the commonly used methods in finance is portfolio diversification. A personal investor or financial organization’s task is to carry out investment on a series of instruments. These assets can be chosen from any of commodities, indices, forex, metal, energy, and stocks, to name a few. The issue here is determining what the best combination of those assets for the investment would be based on their historical market price and by minimizing the risk involved in trading those markets.

Markowitz [9], in his paper, recommended a solution for finding the optimal selection of the best combination of assets for investment. The approach was mainly based on the weights as budget shares that minimized the variance of the underlying portfolio. In this approach, however, the risk on the amount of return was not considered. Hatemi-J and El-Khatib [10] devised a new method based on the effects of valuing risks on the selection of assets so as to maximize the return. The method is named ‘maximizing the risk-adjusted return of the portfolio’. It combines risk and returns when the optimal budget shares are searched for. Hence, two applications are presented in this section. The basis of the design for the first application is a set of a predetermined list of assets (denoted by PD-RAR, which stands for ‘portfolio diversification with risk-adjusted return’), and the second one presents a comparison between portfolios based on a different number of assets (denoted PD-RAR-Comb). This last design is aimed at helping the investor to endogenize the number of assets in the portfolio by considering all possible combinations. Equations (1)–(14) are used for this purpose.

4. Development of the Tool

Since there are complex calculations involved in calculating portfolios with the best performance in both methods of Markowitz [9] and Hatemi-J and El-Khatib [10], there is the need to develop a module that performs all the required calculations efficiently via graphical user interfaces (GUIs). The aim of this research is to fill this gap in the existing literature. Schematics of these two designs as presented in the previous section are given in Appendix A.

The portfolio diversification with risk-adjusted return (PD-RAR) creates a portfolio for a set of data inputs via its dashboard panel (Figure 1). There are two methods available for entering data: (i) either through copy and paste functions to paste the set of data on the sheet named “Data”, or (ii) the option named “Data as Parameters”. The use of the first option is straightforward, and the application is ready to process data. The second option provides an extra option for entering the input data in the form of the available number of assets, calculated expected values, and covariance of the set of data, which could all have come from another application software (Figure 2).

After processing data based on Equations (1)–(12), the number of portfolios is given (Figure 3). This is followed by creating two types of output: (i) detailed calculations of applied algorithms (Figure 4), and (ii) the “Estimated Results” (

Table 1. Summary of estimation results table for portfolio number 10 with best value based on minimum variance approach (MV), which includes two assets.

Assets	Average Return (ṝ)	SD (σ)	Risk Adjusted Return	w for MV	w for MRAR
Brent Oil	0.00364822	0.01435503	0.25414215	0.17252711	0.4888804
Dow Jones	0.0017301	0.00862258	0.20064716	0.82747289	0.5111196
Portfolio-Minimum Variance (MV)	0.00206103	0.00826817	0.24927208
Portfolio-Maximum Risk Adjusted Return (MRAR)	0.00266783	0.00940691	0.28360313

and

Table 2. Summary of estimation results table for portfolio number 1 with the best value based on maximum risk-adjusted return (MRAR), which includes all assets.

Assets	Average Return (ṝ)	SD (σ)	Risk Adjusted Return	w for MV	w for MRAR
USD-JPY	0.00029673	0.00349409	0.0849228	1.09569014	0.11642855
Brent Oil	0.00364822	0.01435503	0.25414215	0.08033079	0.46561635
DAX	0.00142506	0.00937355	0.15203045	−0.07538021	−0.11039877
Dow Jones	0.0017301	0.00862258	0.20064716	−0.10064071	0.52835387
Portfolio-Minimum Variance (MV)	0.00033665	0.00321823	0.10460585
Portfolio-Maximum Risk Adjusted Return (MRAR)	0.00249	0.00875246	0.284491

).

The calculation results are provided in

Table 3. List of portfolios with the minimum variance (MV) and maximum risk-adjusted return (MRAR) with the highest selection.

Portfolio	MV	MRAR	Portfolio with the Highest RAR 1
Portfolio-1	0.10460585	0.284491	MV	MRAR
Portfolio-2	0.12222364	0.26876603	0.24927208	0.284491
Portfolio-3	0.10252419	0.28364519	Portfolio 10	Portfolio 1
Portfolio-4	0.0426772	0.20488306
Portfolio-5	0.23869796	0.28430838
Portfolio-6	0.13814161	0.26645813
Portfolio-7	0.0582005	0.15205724
Portfolio-8	0.04421402	0.20429712
Portfolio-9	0.21516302	0.26479205
Portfolio-10	0.24927208	0.28360313
Portfolio-11	0.19537426	0.20076821

¹ Portfolio construction methods: MV = minimum variance approach; MRAR = maximum risk-adjusted return approach.

for the best portfolio that is created based on the maximum risk-adjusted return.

PD-RAR-Comb

In this sub-section, the results for all eleven combinations are briefly presented in Table 3. These results are obtained by using the PD-RAR-Comb module. This module provides a list of all possible combinations of portfolios to be created for all assets with the addition of presenting a comparison between both algorithms used in creating those portfolios [9,10] [Appendix A part A.2]. The first phase of this process is to create a list of possible combinations of assets, and then to create a portfolio for each combination and list them in a sheet arranged in descending order from the maximum number of assets to the minimum number of assets in combination. The next step is to find the maximum value for both used algorithms of minimum variance (MV) and maximum risk-adjusted return (MRAR). The outcome of this process is shown in Table 3.

5. Findings

In this section, the findings for executing both modules are discussed. The performance of calculations mainly depends on the type of data processing (i.e., the option of “with or without detailed presentation of step-by-step calculations”) and on the size of the dataset (i.e., the number of assets and recorded closing prices for each asset). For example, by running a set of 10 assets with 65 records (which results in 1013 different portfolios), it takes around 100 s to process the data using the option “without details”. Comparatively, it takes more than 15 min to implement the same calculations with the option “with details”. The reason for this is due to the interaction with an individual worksheet (i.e., reading and writing data from and into a worksheet). It should be mentioned that the main purpose of using the module with detailed steps is for educational purposes, which gives the outcome of step-by-step calculations in the process of creating portfolios.

Note that portfolio construction based on the method by Hatemi-J and El-Khatib [4] clearly shows a better outcome if the goal is finding a portfolio that provides the highest possible return per unit of risk. However, the portfolio that is constructed by Markowitz’s [9] method results in the lowest possible risk. By using this module, it is also possible to directly enter the parameters that are necessary inputs for portfolio diversification (such as the average returns and the variance–covariance values) instead of importing the time-series data of the prices (see Figure 2).

6. Conclusions

Constructing an optimal portfolio is an important issue for investors and financial institutions. There are several methods available in the literature for this purpose. The seminal approach provided by Markowitz [9] yields an optimal portfolio that results in the minimum possible risk. This approach is widely applied by practitioners. An alternative approach that was developed by Hatemi-J and El-Khatib [10] produces a conditional optimal portfolio that gives the maximum return per unit of risk. The aim of this work was to provide a VBA module that can construct portfolios using both methods.

A pertinent issue within this context that is usually neglected in the literature is the dimension of the portfolio; that is, the number of assets included in the portfolio is assumed to be exogenous. However, it is rational to deal with this issue endogenously. The approach that was suggested by Hatemi-J and Hajji [12] for this purpose is to estimate all possible combinations of portfolios and estimate the risk-adjusted return for each. The portfolio that gives the highest risk-adjusted return among all possible ones is the one that should be selected. Our module also provides this possibility. It constructs all possible portfolios that the investor might be interested in and indicates the optimal one using both portfolio diversification methods. An example of four assets was provided to demonstrate how the module operates. However, the results can be generalized in future applications. The module is very consumer-friendly. The software verbiage of the module is accessible from the authors upon demand.