Portfolio Management and Optimization in Excel

Calculate holding period return (HPR) by including price changes and income like dividends, and apply compounding for multi-year horizons. Distinguish single and multiple period returns to assess total return.

Learn to download analysis ready historical stock prices in Excel using Yahoo Finance. Choose the time period and data frequency, and use adjusted close for concise financial analysis.

Format an Excel spreadsheet for a clean, professional look with a white background and Arial font size 9. Create a bold, dark blue title in cell b1: formatting a worksheet.

Learn to compute holding period returns and logarithmic (continuously compounded) returns, compare their time-additive properties, and explore portfolio returns with practical examples.

Compare arithmetic and geometric mean returns for aggregating holding period returns, and explain how compounding lowers the geometric mean, illustrated by Apple's three-year example.

Calculate the stock's average and geometric mean return in Excel using Apple data from Yahoo Finance, monthly adjusted close, and the holding period return formula.

Calculate the money weighted rate of return by weighting cash flows to zero, revealing the IRR and the impact of timing on holding period return.

Learn to calculate the time weighted rate of return by dividing the horizon into sub periods, computing holding period returns, and compounding to the geometric mean, with a Walmart example.

Compare money-weighted and time-weighted rates of return using a Brightside fund example, illustrating cash flows, dividends reinvested, capital gains, and the IRR-derived annualized returns.

Learn how to annualize returns by compounding periodic gains and compare short- and long-term investments using the appropriate number of periods per year, with a focus on reinvestment limits.

Analyze how fees, taxes, inflation, and leverage affect portfolio returns by comparing gross versus net and nominal versus real, pre- versus after-tax performance.

Explore diversification across asset classes, stocks, bonds, and cash equivalents, and show how spreading investments reduces risk, though not eliminating losses, while illustrating the power of compounding.

Explore the difference between historical and expected returns, the role of uncertainty, distribution assumptions, and market liquidity in portfolio decisions.

Compute the mean, variance, and standard deviation of asset returns; derive covariance and correlation to measure how two assets move together, using sample formulas and practical examples.

Compute variance and standard deviation as key investment risks in Excel. Use var, stdev, and sqrt on stock returns to compare assets by volatility.

Learn to compute covariance and correlation between two assets in Excel using monthly returns from Apple and CMS Energy, and interpret a low 0.19 correlation for diversification.

Explore how risk aversion shapes investments by comparing risk averse, risk neutral, and risk seeking profiles, illustrating with return versus risk and expected value through examples.

Explore how risk aversion shapes investment choices through the utility function, expected return, variance, and indifference curves, guiding portfolio ranking in Excel.

calculate portfolio return by weighting each asset's return and ensuring weights sum to one. analyze portfolio risk using variance, standard deviation, and covariance, illustrated with a two-asset example.

Learn to compute risk and return for a two-asset portfolio in Excel using weighted averages, variance formulas, and annualization, with Apple and CMS Energy as examples.

Explore how the correlation coefficient shapes portfolio risk by analyzing rho values of plus one, zero, and minus one, and observe how diversification lowers variance.

Explore how diversification lowers risk by combining assets with imperfect correlation, expanding the investment opportunity set across asset classes for a superior risk return trade off toward the northwest.

Identify the minimum variance frontier and global minimum variance portfolio within the investment opportunity set, and learn how the Markowitz efficient frontier guides risk-averse choices.

Construct an 11-portfolio two-asset efficient frontier in Excel for Apple and CMS Energy, computing returns and risk, and identify the minimum-variance portfolio around 10% Apple.

Explore how the capital allocation line blends a risk-free asset with an optimal risky portfolio to achieve the best risk-return trade-off, using the two fund separation theorem and Excel-based calculations.

Identify the optimal risky portfolio on the efficient frontier by maximizing the Sharpe ratio using Excel solver, adjusting portfolio weights to achieve the highest slope of the capital allocation line.

Combine the risk free asset with the optimal risky portfolio to form the capital allocation line and examine returns, standard deviation, risk, and the Sharpe ratio across weights.

Explore the two fund separation theorem and the capital allocation line to identify the optimal risky portfolio with a risk-free asset. See how indifference curves shape the investor’s preferred mix.

Analyze a five-asset portfolio to compute risk and return, derive minimum variance and the optimal risky portfolio using Markowitz optimization and matrix algebra.

Explore matrices as two-dimensional arrays of numbers, defined by rows and columns, such as a two-by-three matrix. Learn how linear algebra uses these matrices in operations like addition and multiplication.

Define scalars as numbers with no dimensions, and vectors as one-dimensional objects, including column vectors (m by 1) and row vectors (1 by m); matrices are collections of vectors.

explore how the transpose operation converts column vectors to row vectors and swaps matrix dimensions, preserving data and reshaping matrices from m by n to n by m.

Learn the dot product, also called inner or scalar product, and how to multiply vectors and matrices via row-by-column dot products, including dimension compatibility and resulting shapes.

Explore calculating portfolio variance using matrix notation for two and multi-asset portfolios, including weight vectors, covariance, and the variance-covariance matrix, with practical steps for N assets.

Estimate and construct five-asset variance-covariance matrix in Excel by building excess return matrix, applying transpose and mmult, and using the Analysis ToolPak; note diagonals are variances and off-diagonals covariances.

Learn to identify the optimal risky portfolio on the capital allocation line, maximize the Sharpe ratio using Excel's solver, and compare to a minimum variance portfolio.

explains the two-fund separation theorem and how investors combine a risk-free asset with the optimal risky portfolio along the capital allocation line to tailor risk and return.

Understand the capital market line, a special capital allocation line where the market portfolio is the optimal risky asset under CAPM. Learn to compute the CML slope and build lending and borrowing portfolios using a market proxy like the S&P 500, with a practical Melissa example.

Understand that systematic risk, a non diversifiable market-wide risk, persists regardless of diversification, while unsystematic risk is diversifiable through a portfolio of 25 to 30 not correlated stocks.

Learn to estimate expected excess returns using macroeconomic and fundamental return-generating models, including market, single-index, and Fama-French frameworks.

Calculate and interpret beta as the covariance between a stock and the market portfolio divided by market variance, illustrating defensive and aggressive stocks with walmart and ford.

Apply regression analysis to quantify the relationship between a dependent variable and one or more independent variables, to forecast outcomes using simple or multivariate linear models.

Learn three methods to calculate a stock beta in Excel: covariance over variance, regression via the Analysis Toolpak, and the slope function, using holding period return and a market index.

Explore the capital asset pricing model (Capm) and its formula, linking expected returns to the risk-free rate and beta times the market premium. Understand Capm assumptions and beta's role.

Use the security market line in CAPM to map beta to expected return, with the risk-free rate on the Y-axis and the market risk premium as the slope.

Explore the capital asset pricing model, linking market risk to expected return through the risk free rate, beta, and the market risk premium, with portfolio applications and real life proxies.

Apply the capital asset pricing model to compare expected returns with the SML-based required returns, identify overvalued and undervalued stocks, and derive trading strategies from example market data.

Explore how the Sharpe ratio and M squared measure risk-adjusted portfolio performance, compare to the market, and discuss their limitations and interpretations.

Explore the Treynor ratio and Jensen’s alpha as metrics of portfolio performance using beta and systematic risk to reveal excess return, outperformance, and the security market line under CAPM.

Learn to calculate and interpret performance measures (Sharpe, Treynor, M squared, and Jensen's alpha) for comparing funds A and B, with guidance on diversification and Excel use.

Compute Sharpe, Treynor, M squared, and Jensen's Alpha for Amazon in Excel, using three years of Amazon and S&P 500 data to derive returns, beta, CAGR, and annualized volatility.

Outline how an investment policy statement (IPS) describes a client’s risk tolerance, return objectives, and constraints, aligning risk and return and guiding regular reviews after changes.

Explore the building blocks of an investment policy statement and how it directs the investment process with objectives, constraints, risk tolerance, and a guiding strategy. Examine how the IPS specifies duties and responsibilities, evaluation against benchmarks, and target allocations, and how updates, appendices, and rebalancing guide portfolio management.

Define risk and return objectives using absolute and relative terms, with 12-month loss limits and probability statements against benchmarks (e.g., the S&P 500) to ensure realistic, investable goals.

Learn how risk tolerance blends ability and willingness, shaped by time horizon and assets. Balance ability and willingness when advising clients; if they diverge, the lower factor prevails.

Explain five investment constraints: time horizon, tax considerations, liquidity needs, legal and regulatory concerns, and unique circumstances, and show how they shape risk and asset choices.

Define how strategic asset allocation sets asset class mix and proportions to match a client's risk-return profile. Show how diversification relies on asset class correlations and traditional versus alternative investments.

Apply strategic asset allocation, tactical asset allocation, and security selection to build an optimal portfolio on the efficient frontier aligned with investor objectives.

Tactical asset allocation actively adjusts asset mix to harness market trends, moving beyond passive strategy, while incorporating risk budgeting and security selection to improve returns.

Explore ESG investing within portfolio management by evaluating environmental, social, and governance factors, and apply screening, thematic, and impact approaches to build sustainable portfolios.

Define risk management as a process to delineate risk tolerance, identify and quantify risks, mitigate but not eliminate, and monitor exposure.

Define risk governance and its core processes—risk appetite recognition, risk strategy optimization, and monitoring—driven by leadership to align business actions with acceptable risk. A risk management committee facilitates risk communication.

Define the firm's risk tolerance by assessing all risks and distinguishing overall risk from risks to be avoided. Link tolerance to resources and trade-offs to guide mitigation.

Analyze how allocating resources to asset categories shapes a company's risk-return profile, using metrics such as beta and value at risk to optimize returns for a given risk.

Explore financial and non-financial risk sources, including credit, liquidity, market, solvency, regulatory, operational, tail risk, model, and accounting risks, and learn to quantify and manage risk within a cohesive framework.

Explore standard deviation, beta, and duration as core risk measures. Understand standard deviation as dispersion, beta as market relation, and duration as interest-rate sensitivity for debt securities.

Master delta, gamma, vega, and rho to assess derivative risk; delta covers price moves in the underlying, gamma shows delta variation, and vega and rho reflect volatility and rate effects.

Analyze how fat tails in returns affect portfolio risk, and explain 5% and 1% value at risk, plus expected shortfall as the average loss in the worst cases.

Identify and quantify current risks to compare with desired risk levels, then choose actions: prevent, avoid, accept, transfer, or shift, to manage risk in a portfolio.

Technical analysis uses price and volume data to project future prices by studying supply and demand, investor psychology, and historical patterns, with applications in commodities, currencies, and futures.

Explore common technical analysis charts: line, bar, candlestick, and point and figure, and learn how they display opening, closing, high, low, and volatility to reveal price trends for portfolio management.

Explore how trading volume and relative strength analysis help technical analysts forecast price, gauge signals, and compare securities against benchmarks like the S&P 500 and Nasdaq.

Learn how trend defines price direction, with higher peaks and higher troughs in uptrends. Identify support and resistance levels and polarity changes; retracement signals potential reversals.

Learn to identify reversal and continuation chart patterns, including head and shoulders, inverse head and shoulders, double tops and bottoms, triple tops and bottoms, triangles, and rectangles, with price targets.

Explore price-based indicators such as moving averages and Bollinger bands, including golden cross and death cross signals, and how volatility affects decision making.

Analyze momentum oscillators to identify overbought or oversold conditions and anticipate trend moves. Examine ROC, RSI, MACD, and stochastic oscillator and how crossovers and readings indicate buy or sell signals.

Explore cycles in markets using Elliott wave theory and Fibonacci ratios to identify entry and exit points, guided by the golden ratio in bull and bear trends.

Explore non price based indicators used by technical analysts, including put-call ratio, VIX, margin debt, short interest, mutual fund cash positions, IPO activity, new equity offerings, and the Trin index.

Develop intermarket analysis skills by monitoring multiple asset classes - stocks, bonds, commodities, and currencies - for relative strength insights. Use these comparisons to identify which sectors, regions, or sizes within equities may outperform.