This example analyzes three portfolios with given rates of return for six time periods by executing MATLAB® functions using Spreadsheet Link™. In actual practice, these functions can analyze many portfolios over many time periods, limited only by the amount of computer memory available.
For details about the efficient frontier of financial portfolios, see Analyzing Portfolios (Financial Toolbox). To learn about portfolio optimization theory, see Portfolio Optimization Theory (Financial Toolbox).
The example organizes and displays the input and output data in a Microsoft® Excel® worksheet. Spreadsheet Link functions copy data to a MATLAB matrix, perform calculations using Financial Toolbox™ functions, and return data to the worksheet.
Open the ExliSamp.xls
file and select the Sheet5 worksheet.
For help finding the ExliSamp.xls
file, see Installation.
This worksheet contains rates of return for three different portfolios: Global, Corporate Bond, and Small Cap.
Note
This example requires Financial Toolbox, Statistics and Machine Learning Toolbox™, and Optimization Toolbox™.
Execute the Spreadsheet Link function that transfers
the plot labels for the x-axis and y-axis
to the MATLAB workspace by double-clicking the cell A15
and
pressing Enter.
Copy the portfolio return data to the MATLAB workspace
by executing the function in the cell A16
.
Generate efficient frontier data for 20 points along
the frontier by executing the Financial Toolbox functions in A19
and A20
.
Copy the output data to the Excel worksheet by
executing the Spreadsheet Link functions in A23
, A24
,
and A25
.
The output data contains the highest rate of return ROR
for
a given risk. The output data also contains the weighted investment
in each portfolio Weights
that achieves that rate
of return.
Plot the efficient frontier for the same portfolio
data by executing the Financial Toolbox functions in cell A28
.
The light blue line shows the efficient frontier. Observe the change in slope above a 6.8% return because the Corporate Bond portfolio no longer contributes to the efficient frontier.
To generate different efficient frontier data, close the figure
and change the data in cells B4:D9
. Then, execute
all the Spreadsheet Link functions again. The worksheet updates
with new frontier data and MATLAB generates a new efficient frontier
plot.
MLEvalString
| MLGetMatrix
| MLPutMatrix
| ewstats
(Financial Toolbox) | portopt
(Financial Toolbox)