Portfolio Construction Report: Microsoft, Apple Inc., Amazon, and Walmart
1. Introduction
This report outlines the construction of a portfolio comprising Microsoft, Apple Inc.,
Amazon, and Walmart. Using five years of daily stock prices, we applied principles from
modern portfolio theory to build various portfolios and frontiers, including the
opportunity set, minimum variance frontier (MVF), global minimum variance portfolio
(GMVP), efficient frontier, and the optimal risky portfolio (ORP). The aim was to
determine the best portfolio mix for a client whose maximum risk tolerance is a 30%
standard deviation in returns.
By integrating theory with practical application in Excel, particularly through Solver and
matrix operations, we assess each stock's contribution to portfolio diversification and
optimality. This report connects directly to core concepts from Chapters 5 and 6, such as
mean-variance optimization, risk-return trade-offs, and the capital allocation line (CAL).
2. Stock Overview
Microsoft (MSFT): Microsoft is a global technology leader known for consistent
profitability, strong growth, and substantial R&D investment. Over the past five years, its
stock has exhibited moderate volatility with solid upward momentum, often considered a
reliable component in tech-focused portfolios.
Apple Inc. (AAPL): Apple’s innovation-driven ecosystem and brand loyalty contribute to
robust earnings and stable growth. It’s been a top performer in the S&P 500, although
occasional product-cycle risks introduce short-term volatility.
Amazon (AMZN): Amazon is a high-growth tech-giant with wide exposure across e-
commerce and cloud computing. Its returns are highly variable due to reinvestment
, strategies and global expansion, offering both high return potential and high risk.
Walmart (WMT): As a defensive stock, Walmart offers stability, lower volatility, and
predictable cash flows. It performs well in bear markets and complements more volatile
growth stocks in a diversified portfolio.
These four stocks represent a blend of growth and value, aggressive and defensive
characteristics, enabling robust portfolio diversification.
3. Data and Methodology
Daily adjusted closing prices from July 1, 2018, to July 1, 2023, were used to calculate
log returns. Risk-free returns were based on the 3-month U.S. Treasury bill rates (proxy
for daily risk-free return). The data was cleaned for missing values and transformed into
daily returns.
Using Excel, we followed these steps:
- Covariance matrix: Calculated to understand the interdependence of stock returns.
- Expected returns and standard deviations: Computed for individual stocks and used in
portfolio return/variance calculations.
- Solver tool: Used for optimization—minimizing variance subject to portfolio weights
summing to 1 and achieving a target return.
This approach adheres to the Markowitz efficient frontier framework (Bodie, Kane, &
Marcus, 2019).
4. Opportunity Set and Minimum Variance Frontier
The opportunity set was created by simulating thousands of portfolios with varying
weight combinations of Microsoft, Apple, Amazon, and Walmart. Each portfolio’s
expected return and standard deviation were computed, forming a cloud of possible
1. Introduction
This report outlines the construction of a portfolio comprising Microsoft, Apple Inc.,
Amazon, and Walmart. Using five years of daily stock prices, we applied principles from
modern portfolio theory to build various portfolios and frontiers, including the
opportunity set, minimum variance frontier (MVF), global minimum variance portfolio
(GMVP), efficient frontier, and the optimal risky portfolio (ORP). The aim was to
determine the best portfolio mix for a client whose maximum risk tolerance is a 30%
standard deviation in returns.
By integrating theory with practical application in Excel, particularly through Solver and
matrix operations, we assess each stock's contribution to portfolio diversification and
optimality. This report connects directly to core concepts from Chapters 5 and 6, such as
mean-variance optimization, risk-return trade-offs, and the capital allocation line (CAL).
2. Stock Overview
Microsoft (MSFT): Microsoft is a global technology leader known for consistent
profitability, strong growth, and substantial R&D investment. Over the past five years, its
stock has exhibited moderate volatility with solid upward momentum, often considered a
reliable component in tech-focused portfolios.
Apple Inc. (AAPL): Apple’s innovation-driven ecosystem and brand loyalty contribute to
robust earnings and stable growth. It’s been a top performer in the S&P 500, although
occasional product-cycle risks introduce short-term volatility.
Amazon (AMZN): Amazon is a high-growth tech-giant with wide exposure across e-
commerce and cloud computing. Its returns are highly variable due to reinvestment
, strategies and global expansion, offering both high return potential and high risk.
Walmart (WMT): As a defensive stock, Walmart offers stability, lower volatility, and
predictable cash flows. It performs well in bear markets and complements more volatile
growth stocks in a diversified portfolio.
These four stocks represent a blend of growth and value, aggressive and defensive
characteristics, enabling robust portfolio diversification.
3. Data and Methodology
Daily adjusted closing prices from July 1, 2018, to July 1, 2023, were used to calculate
log returns. Risk-free returns were based on the 3-month U.S. Treasury bill rates (proxy
for daily risk-free return). The data was cleaned for missing values and transformed into
daily returns.
Using Excel, we followed these steps:
- Covariance matrix: Calculated to understand the interdependence of stock returns.
- Expected returns and standard deviations: Computed for individual stocks and used in
portfolio return/variance calculations.
- Solver tool: Used for optimization—minimizing variance subject to portfolio weights
summing to 1 and achieving a target return.
This approach adheres to the Markowitz efficient frontier framework (Bodie, Kane, &
Marcus, 2019).
4. Opportunity Set and Minimum Variance Frontier
The opportunity set was created by simulating thousands of portfolios with varying
weight combinations of Microsoft, Apple, Amazon, and Walmart. Each portfolio’s
expected return and standard deviation were computed, forming a cloud of possible
