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.
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.
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
Copy the portfolio return data to the MATLAB workspace
by executing the function in the cell
Generate efficient frontier data for 20 points along
the frontier by executing the Financial
Toolbox functions in
Copy the output data to the Excel worksheet by
executing the Spreadsheet
Link functions in
The output data contains the highest rate of return
a given risk. The output data also contains the weighted investment
in each portfolio
Weights that achieves that rate
Plot the efficient frontier for the same portfolio
data by executing the Financial
Toolbox functions in cell
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