Sources and References

Primary textbook — Zvi Bodie, Alex Kane, and Alan Marcus, Essentials of Investments, 13th Edition (McGraw-Hill, 2024). [BKM]

Supplementary — Jonathan Berk, Peter DeMarzo, and David Stangeland, Corporate Finance, 5th Canadian Edition (Pearson Canada, 2022), Chapters 4–5 (Time Value of Money). [BDS]

Online resources — MIT OpenCourseWare 15.401 (Finance Theory I); Yves Hilpisch, Python for Finance, 2nd ed. (O’Reilly, 2019); CFA Institute introductory materials on capital markets; Bank of Canada statistical data releases; Statistics Canada national accounts.

Chapter 1: Investments — Background and Issues

1.1 Why Study Investments?

The study of investments addresses a central economic problem: individuals accumulate savings over their working lives and need those savings to generate returns sufficient to fund future consumption — retirement, education, housing, and unforeseen emergencies. Firms require capital to fund operations and growth, and they obtain it by issuing financial securities to investors. Financial markets serve as the institutional infrastructure that matches these two groups, and the study of investments examines how securities are priced, how portfolios should be constructed, and how markets allocate capital across competing uses.

From a second-year AFM perspective, AFM 127 builds directly on introductory accounting and finance, adding two new dimensions: an explicit focus on how global capital markets are structured, and a systematic emphasis on computational tools (Excel and Python) that are now prerequisite skills in virtually every finance career path.

The risk-return tradeoff is the organizing principle: rational investors require higher expected returns to compensate for bearing higher risk. A risk-free government bond offers a low but certain return. An equity stake in a small biotechnology firm offers a potentially large but highly uncertain return. Every investment decision navigates this tradeoff.

1.2 The Investment Process

A structured investment process involves five stages:

1. Setting investment policy — defining objectives (target return, risk tolerance), constraints (time horizon, liquidity needs, regulatory restrictions, tax status), and a performance benchmark against which results will be measured.
2. Security analysis — evaluating individual securities to identify mispriced assets using fundamental analysis (financial statements, industry dynamics) or quantitative methods (statistical models, factor models).
3. Portfolio construction — combining securities to achieve the target risk-return profile, using diversification to eliminate idiosyncratic risk and retain only compensated systematic risk.
4. Portfolio revision — periodically rebalancing as market prices shift, as the investor’s circumstances change, or as new information becomes available.
5. Performance evaluation — measuring realized returns against benchmarks and attributing performance to skill (alpha) versus market exposure (beta), using metrics such as the Sharpe ratio, Treynor measure, and Jensen’s alpha.

1.3 Participants in Financial Markets

Households are the ultimate owners of financial wealth, either directly (brokerage accounts, RRSPs, TFSAs) or indirectly through intermediaries.

Institutional investors — pension funds (Canada Pension Plan Investment Board, Ontario Teachers’ Pension Plan), insurance companies, endowments, sovereign wealth funds (Alberta Investment Management Corporation) — manage enormous pools of capital on behalf of beneficiaries. Their scale gives them access to illiquid asset classes (private equity, infrastructure, real estate) unavailable to retail investors.

Financial intermediaries — banks, investment dealers, mutual funds, ETF managers — transform the claims issued by borrowers (deposits, bonds, commercial paper) into the instruments demanded by savers (deposits, fund units, ETF shares). Intermediation reduces transaction costs, provides liquidity, and facilitates risk-sharing.

Market makers (dealers, specialists) post bid and ask prices continuously, providing liquidity in exchange for the bid-ask spread. Arbitrageurs exploit price discrepancies across markets or instruments, driving prices toward consistency. Speculators take directional views, providing liquidity and aiding price discovery, though they also amplify short-term volatility.

Chapter 2: Capital Markets — Structure and Instruments

2.1 The Asset Class Taxonomy

Financial instruments are organized into broad asset classes distinguished by their legal nature, risk characteristics, and return profiles.

2.2 Money Market Instruments

Money market instruments are short-term, highly liquid debt securities with maturities under one year. Their low default risk and high liquidity make them near-cash equivalents used for corporate cash management, bank funding, and monetary policy implementation.

Treasury bills are quoted on a bank discount basis:

where \(d\) is the discount yield, \(F\) is face value, \(P\) is purchase price, and \(n\) is days to maturity. The bond-equivalent yield (BEY) converts this to a comparable annual yield using actual purchase price and a 365-day year, and is always higher than the discount yield.

2.3 Fixed Income — Bonds

Government of Canada bonds are the domestic benchmark for fixed income, backed by the federal government’s taxing authority and treated as risk-free for credit purposes. Yields on Government of Canada bonds anchor the yield curve and serve as the reference risk-free rate in asset pricing models.

Provincial bonds carry a modest credit spread above federal bonds, reflecting the fiscal autonomy and somewhat lower creditworthiness of provincial governments, though all remain investment grade.

Corporate bonds carry credit spreads reflecting the issuing firm’s probability of default and the expected recovery rate in the event of default. Investment-grade bonds (rated BBB– or above by S&P/DBRS) have low default probabilities; high-yield or speculative-grade bonds (BB+ or below) carry materially higher default risk and correspondingly higher coupons.

Structured products — mortgage-backed securities (MBS), asset-backed securities (ABS), collateralized loan obligations (CLOs) — pool individual loans into securities with varying risk tranches. Senior tranches have first claim on cash flows and are rated AAA; equity tranches absorb first losses. These instruments played a central role in the 2007–09 global financial crisis when rating models underestimated the correlation of default across mortgages.

2.4 Equity Markets

Common shares entitle holders to residual earnings (dividends if declared) and voting rights in corporate governance. Common shareholders are last in priority in liquidation — they absorb losses before creditors. In return, common shares offer unlimited upside.

Preferred shares pay fixed dividends with priority over common dividends but typically without voting rights. They exhibit bond-like pricing when dividends are fixed. Unlike bond interest, missed preferred dividends do not trigger default, but often accumulate (cumulative preferred).

Key equity metrics:

2.5 Derivatives Markets

A derivative is a financial instrument whose value depends on the value of an underlying asset, index, or rate. Derivatives serve two primary economic functions: hedging (transferring risk from those who bear it unwillingly to those willing to bear it for compensation) and price discovery (futures prices reflect current market expectations about future spot prices).

Futures contracts obligate both buyer and seller to transact at a predetermined price on a future date. They are exchange-traded, standardized, and subject to daily mark-to-market through a clearing house (eliminating counterparty risk).

Options contracts give the buyer the right but not the obligation to buy (call) or sell (put) the underlying at the strike price on or before expiration. The buyer pays a premium; the seller (writer) receives the premium and accepts the obligation.

Foreign exchange (forex) market: The largest financial market globally, with daily turnover exceeding USD 7 trillion. The forex market is decentralized (OTC), operating 24 hours through dealer networks. Exchange rates are quoted as the price of one currency in terms of another (e.g., USD/CAD = 1.36 means 1 USD buys 1.36 CAD). The Bank of Canada publishes a daily nominal and real exchange rate index for the Canadian dollar.

Commodities markets: Participants include producers (hedging price risk), users (locking in input costs), and financial investors (return / diversification). Commodity prices are driven by global supply/demand dynamics, geopolitical risk, currency movements, and speculative flows. Canada’s equity market is heavily exposed to commodities through energy (oil sands: Canadian Natural Resources, Suncor) and mining (Barrick Gold, Teck Resources).

2.6 Primary vs. Secondary Markets

The existence of liquid secondary markets is essential to the functioning of primary markets: investors are willing to buy new issues only if they can exit those positions in secondary markets later. Without secondary market liquidity, primary market capital formation would be severely impaired.

Investment dealers (Morgan Stanley, RBC Capital Markets, TD Securities) underwrite new issues, advising issuers on pricing and timing, purchasing the securities from the issuer, and reselling them to investors. In a firm commitment underwriting the dealer bears the price risk; in a best-efforts offering the dealer simply acts as agent, and the issuer bears unsold inventory risk.

Chapter 3: Securities Markets — How Trading Works

3.1 Market Organization

Securities markets are organized along two dimensions: exchange-traded vs. OTC and order-driven vs. quote-driven.

Exchange-traded markets (Toronto Stock Exchange, NYSE, NASDAQ) use formal listing requirements, centralized price discovery, and standardized clearing and settlement. Listing requires meeting minimum size, float, earnings, and governance thresholds. Over-the-counter (OTC) markets consist of dealer networks; trades are negotiated bilaterally between counterparties. Most bonds, foreign exchange, and derivatives trade OTC.

Order-driven markets match buyers and sellers based on submitted orders without a designated market maker; the order book determines price. The TSX operates a continuous electronic order-matching system. Quote-driven (dealer) markets rely on dealers who continuously post bid and ask prices and trade against customer orders from their inventory.

3.2 Order Types

A market order executes immediately at the best available price. Execution is guaranteed but price is not — in illiquid markets, large market orders can move prices unfavorably (market impact or slippage).

A limit order specifies a maximum buying price (limit buy) or minimum selling price (limit sell). Execution is not guaranteed if the market does not reach the limit price. Limit orders accumulate in the limit order book, visible to market participants (the “depth of market”).

A stop-loss order becomes a market order when price falls to a specified trigger level. It limits downside losses but may execute at an unfavorable price in fast-moving (gap) markets.

A stop-limit order becomes a limit order (not a market order) when the stop is triggered, preventing execution at an unacceptable price but risking non-execution if the market falls through the limit rapidly.

3.3 Short Selling

Short selling allows investors to profit from price declines. The short seller borrows shares from a broker (who lends from client holdings), sells them in the market, and later repurchases them to return to the lender.

Risk is theoretically unlimited because prices can rise without bound. Short sellers must post margin and may face short squeezes — when a stock price rises sharply, short sellers are forced to cover simultaneously, further accelerating the price increase. The GameStop episode of January 2021 is the most prominent recent example.

3.4 Margin Trading

Buying on margin means borrowing from a broker to purchase securities, using the securities as collateral. The initial margin requirement specifies the minimum equity percentage the investor must contribute. The maintenance margin is the minimum equity ratio that must be maintained; falling below triggers a margin call requiring additional cash or liquidation.

Margin amplifies both gains and losses. It is a form of financial leverage and dramatically increases risk for retail investors.

3.5 Market Indices

A market index aggregates prices of a set of securities into a single number to measure overall market performance.

Price-weighted index (Dow Jones Industrial Average): Each stock is weighted by its share price. A high-priced stock dominates regardless of its market capitalization. Stock splits mechanically reduce a stock’s weight.

Market-capitalization-weighted index (S&P/TSX Composite, S&P 500, MSCI World): Each stock is weighted by its total market capitalization (price × shares outstanding). This is the theoretically correct weighting under the Capital Asset Pricing Model, as it reflects the actual investable opportunity set.

Equal-weighted index: Each stock has the same weight. Requires constant rebalancing and overweights small-cap stocks relative to the market portfolio.

The S&P/TSX Composite Index is Canada’s primary equity benchmark, comprising the largest companies listed on the TSX. Its sector composition is significantly more concentrated in Financials and Energy than the S&P 500, reflecting Canada’s industrial structure.

Chapter 4: Financial Data Sources and Platforms

4.1 The Landscape of Financial Data

Financial analytics begins with data. Modern practitioners access financial data through a hierarchy of sources:

1. Professional terminal services — Bloomberg Terminal, Refinitiv Eikon (now LSEG Workspace), FactSet. These provide real-time and historical market data, news, analytics, and company fundamentals with institutional-grade depth and reliability.
2. Company filings — SEDAR+ (Canada’s System for Electronic Document Analysis and Retrieval), SEC EDGAR (United States). These contain audited financial statements, MD&As, AIF, and material change reports.
3. Exchange data feeds — TSX DataLinx, NYSE TAQ (Trade and Quote). High-frequency intraday tick data for quantitative strategies.
4. Free / academic sources — Yahoo Finance (via yfinance), Alpha Vantage, Quandl (Nasdaq Data Link), Bank of Canada’s Valet API, Statistics Canada.

4.2 Bloomberg Terminal

The Bloomberg Terminal is the dominant information system in institutional finance. It is a proprietary hardware-software platform providing:

• Real-time market data: Equities, fixed income, currencies, commodities, derivatives, and alternative assets globally.
• Historical data: Long time-series of prices, fundamentals, economic indicators, and reference data.
• Analytics tools: Fixed income analytics (yield curve, duration, OAS), equity screening, credit analysis, portfolio analytics.
• News and research: Bloomberg News, sell-side research, central bank communications, earnings transcripts.
• Excel add-in (Bloomberg API / BQL): Pulls live and historical Bloomberg data directly into Excel using formulas such as =BDP() (Bloomberg Data Point — current data) and =BDH() (Bloomberg Data History — time series).

4.3 Refinitiv Eikon / LSEG Workspace

Refinitiv (now part of London Stock Exchange Group, LSEG) is Bloomberg’s primary competitor. LSEG Workspace offers similar functionality — real-time data, fundamentals, news, analytics — with a reputation for stronger coverage of European fixed income and emerging markets. Its Datastream module provides one of the longest historical financial databases available, extending decades further back than most competitors.

The Refinitiv Excel add-in uses functions like =RDP() (Refinitiv Data Point) and supports bulk data extraction for quantitative research. LSEG also provides a Python API through the refinitiv-data library, enabling programmatic data access.

4.4 FactSet

FactSet is widely used in equity research and investment management. Its FactSet API and Office Add-in integrate directly with Excel and PowerPoint. FactSet is particularly known for:

• Earnings estimates consensus data — aggregated analyst forecasts (EPS, revenue, EBITDA) across multiple periods.
• Ownership data — institutional and insider holdings for public companies.
• Supply chain and relationship mapping — linking companies through customer-supplier and competitor relationships.
• Portfolio attribution — Brinson-Hood-Beebower attribution for equity and fixed income mandates.

4.5 Free Data Sources for Analytics

For coursework and personal projects, high-quality financial data is accessible at no cost:

Chapter 5: Excel for Financial Analytics

5.1 Why Excel Remains Indispensable

Despite the growth of Python, R, and specialized analytics platforms, Microsoft Excel remains the dominant tool for financial analysis in practice. Virtually every financial model — merger analysis, LBO model, bond pricing, budget variance analysis — is built and communicated in Excel. Proficiency in Excel’s financial functions, modeling conventions, and data tools is a baseline expectation for internship and entry-level roles in finance.

5.2 Core Financial Functions

Net Present Value: NPV and XNPV

Excel’s NPV function assumes equally-spaced cash flows occurring at the end of each period. The formula is:

=NPV(rate, value1, [value2], ...) + initial_outflow

Note: Excel’s NPV does not include period 0. The initial investment must be added separately (as a negative number):

=NPV(8%, B2:B6) + B1

where B1 contains the time-0 cash flow (negative if an outflow).

XNPV handles unequally-spaced cash flows by specifying actual dates:

=XNPV(rate, values, dates)

Internal Rate of Return: IRR and XIRR

IRR finds the discount rate that makes NPV equal to zero, assuming equally-spaced cash flows:

=IRR(values, [guess])

XIRR is the date-based equivalent, essential for real investment cash flow streams where cash flows do not occur on exact anniversary dates:

=XIRR(values, dates, [guess])

PMT, PV, FV, NPER, RATE

Excel’s five TVM functions — PMT, PV, FV, NPER, RATE — solve for the missing variable in a standard annuity:

PRICE and YIELD — Bond Functions

Excel provides dedicated bond pricing functions consistent with ISDA day-count conventions:

=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Where rate is the coupon rate, yld is the yield to maturity, pr is the quoted (clean) price as a percentage of par, frequency is the number of coupon payments per year (2 for semi-annual), and basis specifies the day-count convention (0 = US 30/360, 3 = Actual/365).

5.3 Data Tables for Sensitivity Analysis

A data table in Excel recomputes a formula across a range of input values automatically. This is ideal for sensitivity analysis — showing how NPV, bond price, or any output changes as an input varies.

One-variable data table: Row or column of input values; formula references the input cell. Select the table range, use Data → What-If Analysis → Data Table, specify row or column input cell.

Two-variable data table: Matrix of two input values. One variable runs across columns, the other down rows. The formula must be in the upper-left corner of the table.

5.4 Solver for Optimization

Excel’s Solver add-in (Data → Solver) performs constrained optimization — finding inputs that maximize or minimize an objective subject to constraints. Financial applications include:

• Portfolio optimization: Minimize portfolio variance (objective) subject to target return (equality constraint) and weights summing to 1 (equality constraint), with weights ≥ 0 (inequality constraint for long-only).
• Bond yield solving: Find the yield that makes the bond pricing formula equal to an observed market price.
• Capital budgeting: Maximize NPV subject to a capital budget constraint.

The Solver uses nonlinear GRG (Generalized Reduced Gradient) or simplex algorithms depending on the nature of the problem (nonlinear vs. linear objective and constraints).

5.5 Pivot Tables for Financial Data

Pivot tables allow rapid summarization and cross-tabulation of large financial datasets without programming. In financial contexts, pivot tables are used for:

• Portfolio holdings analysis: Aggregate exposure by sector, geography, or asset class.
• Trade blotter analysis: Sum trading volume, P&L, and commissions by trader, security, or date.
• Comparative financial statement analysis: Pivot income statement data across multiple periods or business segments.

To create a pivot table: Insert → PivotTable → specify data range → drag fields to Rows, Columns, Values, and Filters. The Value Field Settings control aggregation (Sum, Average, Count, % of Total).

5.6 Dynamic Arrays (Excel 365)

Modern Excel (365 / 2019+) introduced dynamic array functions that spill results across multiple cells automatically. Key functions for financial analysis:

Chapter 6: Financial Statement Analysis Using Data

6.1 Purpose and Framework

Financial statement analysis serves multiple constituencies: equity investors (estimating intrinsic value), creditors (assessing debt repayment capacity), management (benchmarking operational efficiency), and regulators (monitoring financial stability). The three primary statements must be analyzed together:

• Income statement: Revenues, expenses, and profitability over a period. Accrual basis.
• Balance sheet: Assets, liabilities, and equity at a point in time. Accounting identity: Assets = Liabilities + Equity.
• Statement of cash flows: Actual cash generated and consumed, divided into operating, investing, and financing activities.

6.2 Key Financial Ratios

Profitability Ratios

DuPont decomposition of ROE decomposes performance into three drivers:

The extended five-factor DuPont further separates profit margin into tax burden, interest burden, and operating margin:

Liquidity Ratios

Leverage and Solvency Ratios

Efficiency (Activity) Ratios

Market Value Ratios

6.3 Automating Ratio Analysis in Python

import pandas as pd
import yfinance as yf

# Fetch financials for Royal Bank of Canada
ry = yf.Ticker("RY.TO")

# Income statement and balance sheet
income = ry.financials          # rows = line items, columns = fiscal years
balance = ry.balance_sheet

# Extract key items (yfinance returns values in dollars)
revenue = income.loc["Total Revenue"]
net_income = income.loc["Net Income"]
total_assets = balance.loc["Total Assets"]
total_equity = balance.loc["Stockholders Equity"]

# Compute ratios as a DataFrame
ratios = pd.DataFrame({
    "Net Margin": net_income / revenue,
    "ROA": net_income / total_assets,
    "ROE": net_income / total_equity,
})
print(ratios.T.round(4))

6.4 Trend Analysis Across Multiple Periods

Trend analysis plots ratio series over time to identify deteriorating or improving fundamentals. Common-size analysis expresses every income statement line as a percentage of revenue (and every balance sheet line as a percentage of total assets), enabling comparison across years and across firms of different sizes.

Indexed analysis sets the base year to 100 and expresses all subsequent years as an index, highlighting growth rates visually.

import matplotlib.pyplot as plt

# Indexed revenue trend
base_revenue = revenue.iloc[-1]   # earliest year as base
indexed_revenue = (revenue / base_revenue * 100).sort_index()

plt.figure(figsize=(8, 4))
plt.plot(indexed_revenue.index, indexed_revenue.values, marker='o')
plt.title("Royal Bank: Indexed Revenue Trend")
plt.ylabel("Index (Base Year = 100)")
plt.xlabel("Fiscal Year")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

6.5 Quality of Earnings

Reported earnings are subject to management discretion through accounting choices (revenue recognition timing, depreciation methods, inventory valuation: FIFO vs. weighted average). Analysts examine accruals as a quality signal. High accruals relative to assets indicate aggressive accounting:

Firms with high accrual ratios tend to experience lower subsequent earnings growth — the accruals anomaly documented by Sloan (1996). Non-cash items driving a wedge between earnings and cash flow include: unrealized gains, deferred revenue recognition, changes in receivables, and adjustments to provisions.

Chapter 7: Bond Prices and Yields

7.1 Bond Pricing Mechanics

A bond’s price is the present value of its cash flows — periodic coupons and the face value repaid at maturity. For a bond with face value \(F\), coupon \(C\) per period, \(n\) periods remaining, and yield per period \(y\):

Most Canadian and US government bonds pay coupons semi-annually, so: coupon per period = (coupon rate × F) / 2, \(y\) is the semi-annual yield, and \(n\) is the number of semi-annual periods.

Clean price (quoted price) excludes accrued interest. Dirty price (invoice price) = clean price + accrued interest. Bonds trade on a clean price basis but settle on a dirty price basis.

7.2 Bond Pricing in Excel and Python

In Excel, the PRICE function implements ISDA day-count conventions:

=PRICE("2026-02-01", "2031-02-01", 3%, 4%, 100, 2, 3)

Returns the clean price per $100 of face value.

In Python:

import numpy as np

def bond_price(face, coupon_rate, ytm, n_periods, freq=2):
    """Price a bond given YTM (annual, nominal)."""
    c = (coupon_rate / freq) * face     # coupon per period
    y = ytm / freq                       # yield per period
    t = np.arange(1, n_periods + 1)
    pv_coupons = np.sum(c / (1 + y)**t)
    pv_face = face / (1 + y)**n_periods
    return pv_coupons + pv_face

price = bond_price(1000, 0.03, 0.04, 10, freq=2)
print(f"Bond price: ${price:.2f}")   # → $955.09

7.3 The Term Structure of Interest Rates

The yield curve (term structure) plots yields to maturity against time to maturity for bonds of the same credit quality. Three canonical shapes:

• Normal (upward-sloping): Long rates > short rates. Reflects term premium and growth/inflation expectations.
• Inverted: Short rates > long rates. Historically a reliable leading indicator of recession (all but one Canadian recession since 1960 was preceded by an inversion).
• Flat or humped: Transition between normal and inverted; uncertainty about the near-term economic outlook.

Theories of the Term Structure

1. Pure Expectations Theory: Long-term yields equal the compounded average of expected future short-term rates. The yield curve is entirely forward-looking; no term premium. Implication: if investors expect rates to rise, the yield curve slopes up.

2. Liquidity Preference Theory: Extends the expectations theory by adding a term premium that increases with maturity, compensating investors for bearing interest rate (duration) risk. Explains why the yield curve is normally upward-sloping even when short rates are expected to remain constant.

3. Market Segmentation Theory: Different investor clienteles (e.g., insurance companies preferring long bonds to match long-duration liabilities; money market funds restricted to short maturities) create segmented demand in different maturity ranges. Yields in each segment are determined independently by supply and demand.

Forward Rates

Forward rates are implied future short-term rates embedded in today’s yield curve. The 1-year forward rate \(f_{n-1,n}\) one period from now, implied by current spot rates:

Under the pure expectations theory, forward rates equal expected future spot rates. Under liquidity preference theory, forward rates embed a term premium and overstate expected future rates.

7.4 Duration and Interest Rate Risk

Macaulay Duration

Macaulay Duration is the weighted average time (in years or periods) to receive a bond’s cash flows, where weights are the present values of each cash flow as a fraction of total bond price:

Properties: Duration increases with maturity and decreases with coupon rate and yield (higher coupons or yields weight near-term cash flows more heavily, shortening duration). For a zero-coupon bond, duration equals maturity exactly.

Modified Duration

Modified Duration converts Macaulay Duration into a price sensitivity measure:

Approximate percentage price change for a small yield change \(\Delta y\):

Convexity

The duration approximation is linear in yield changes, but the actual price-yield relationship is convex. Convexity captures the curvature:

Full price change approximation:

Convexity is always positive for non-callable bonds: the bond price rises more than duration predicts when yields fall, and falls less than duration predicts when yields rise. Convexity is therefore a desirable property; bonds with higher convexity command a price premium.

7.5 Yield Curve Construction in Python

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Hypothetical Government of Canada par yield curve (%)
maturities = [0.25, 0.5, 1, 2, 3, 5, 7, 10, 20, 30]
yields = [4.80, 4.75, 4.60, 4.35, 4.20, 4.05, 4.00, 3.95, 4.10, 4.15]

yield_curve = pd.Series(yields, index=maturities, name="Yield (%)")

plt.figure(figsize=(9, 4))
plt.plot(yield_curve.index, yield_curve.values, 'b-o', linewidth=2)
plt.title("Government of Canada Par Yield Curve")
plt.xlabel("Maturity (years)")
plt.ylabel("Yield to Maturity (%)")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

Chapter 8: Macroeconomic and Industry Analysis

8.1 The Top-Down Approach

The top-down approach to security analysis begins with the macroeconomic environment, then assesses industry dynamics, and finally values individual securities. This sequencing reflects the empirical evidence that macroeconomic conditions and industry membership together explain 30–50% of cross-sectional stock return variation.

Key Macroeconomic Indicators

The business cycle moves through expansion, peak, contraction, and trough. Asset allocation decisions should account for cyclical positioning: equity overweights in early expansion, defensive rotation near the peak, and duration overweight (long bonds) during contraction when rates are expected to fall.

8.2 Monetary Policy and Asset Prices

The Bank of Canada sets the overnight rate target to achieve a 2% inflation target (within a 1–3% control range). Changes in the overnight rate cascade through financial markets:

1. Short-term bond yields adjust immediately.
2. Long-term bond yields adjust as the yield curve re-prices expectations.
3. Equity discount rates change, affecting P/E multiples (the “Fed model” — or BoC model — relates earnings yield to bond yield).
4. Currency values shift (higher Canadian rates attract capital inflows, strengthening the CAD).
5. Credit conditions tighten or loosen, affecting corporate and household borrowing.

The Taylor Rule provides a policy rate benchmark:

where \(r^*\) is the neutral real rate, \(\pi\) is actual inflation, \(\pi^*\) is the inflation target (2%), and \((y - y^*)\) is the output gap.

8.3 Industry Analysis

Business Cycle Sensitivity

Industries differ substantially in their sensitivity to the economic cycle:

• Cyclical industries (energy, materials, industrials, consumer discretionary) see revenues and earnings amplify swings in aggregate demand. These sectors outperform in expansions.
• Defensive industries (utilities, consumer staples, healthcare) maintain relatively stable earnings through economic cycles. These sectors offer relative outperformance in contractions.

The sector rotation model exploits predictable industry performance patterns across the business cycle, though in practice the timing of cycle turning points is difficult to forecast.

Porter’s Five Forces

Michael Porter’s Five Forces framework assesses the competitive dynamics and long-run profitability of an industry:

1. Threat of new entrants: High barriers to entry (economies of scale, capital requirements, regulatory licences, brand loyalty, switching costs) protect incumbent profitability.
2. Bargaining power of buyers: Concentrated buyers with many supplier alternatives can negotiate lower prices, compressing margins.
3. Bargaining power of suppliers: Suppliers with few substitutes or high switching costs extract more value from the industry.
4. Threat of substitutes: Products outside the industry that satisfy the same customer need limit pricing power.
5. Intensity of competitive rivalry: High fixed costs, low differentiation, and slow industry growth intensify competition and depress profitability.

Applying Five Forces to Canadian banking: high barriers to entry (regulatory capital requirements, OSFI oversight, established brands and branch networks), low threat of substitutes for core banking (deposits, mortgages), moderate buyer power for retail clients, and low supplier bargaining power — resulting in persistently high ROE for the Big Six banks.

Industry Life Cycle

Chapter 9: Equity Valuation Analytics

9.1 Dividend Discount Models

The Dividend Discount Model (DDM) values a share as the present value of all future expected dividends, discounted at the required return on equity \(k\):

Gordon Growth Model (Constant Growth DDM)

For a firm with dividends growing at a constant rate \(g\) in perpetuity:

The Gordon model implies that the P/E multiple and the dividend yield are both determined by \(k\) and \(g\):

Multi-Stage DDM

For firms with non-constant near-term growth:

1. Explicitly forecast dividends for the high-growth stage.
2. Apply the Gordon model at the horizon (when growth stabilizes) to compute a terminal value.
3. Discount all cash flows and the terminal value to the present.

9.2 Free Cash Flow Valuation (DCF)

The Discounted Cash Flow (DCF) model values the firm using Free Cash Flow to the Firm (FCFF) discounted at the Weighted Average Cost of Capital (WACC):

Terminal Value is typically calculated using either the Gordon Growth Method (\(TV = \text{FCFF}_{T+1} / (\text{WACC} - g)\)) or the Exit Multiple Method (applying an EV/EBITDA multiple to projected EBITDA at the horizon year).

Building a DCF Model in Excel

A well-structured DCF model in Excel separates:

1. Assumptions tab: Revenue growth rates, EBITDA margins, D&A as % of revenue, CapEx intensity, tax rate, WACC, terminal growth rate.
2. Income statement projection: Revenue → Gross Profit → EBITDA → EBIT → NOPAT.
3. FCFF schedule: NOPAT + D&A − CapEx − ΔWorking Capital.
4. Valuation: PV of FCFFs + PV of Terminal Value = Enterprise Value; deduct net debt to get equity value; divide by shares outstanding for intrinsic value per share.
5. Sensitivity tables: Two-variable data tables showing intrinsic value across WACC and terminal growth rate ranges.

9.3 Comparable Companies Analysis (Trading Comps)

Comparable companies analysis (or “trading comps”) values a target company by applying valuation multiples from publicly traded peer companies. The logic: similar companies with similar growth, margins, and risk profiles should trade at similar multiples of their fundamental metrics.

Process

1. Select the peer group: Identify 5–10 companies similar in business model, size, geography, and financial profile to the target. Avoid being too narrow (insufficient data points) or too broad (irrelevant comparisons).

2. Spread the data: For each comparable, extract market cap, debt, cash, and forward revenue, EBITDA, EBIT, and net income estimates.

3. Compute Enterprise Value (EV):

4. Calculate multiples for each comparable:

5. Apply to the target: Use the median (or 25th–75th percentile range) of each multiple and apply to the target’s corresponding metric to derive an implied valuation range.

9.4 Precedent Transaction Analysis

Precedent transaction comps apply multiples observed in historical M&A transactions involving similar companies. Unlike trading comps (which reflect minority-interest market prices), transaction multiples include a control premium — the additional amount a buyer pays above the pre-announcement market price to acquire a controlling stake. Control premiums typically range from 20–40%.

Analysts search databases (Bloomberg M&A, FactSet MergerMetrics, Thomson SDC) for transactions involving comparable companies in the past 5 years, spread the announced transaction multiples (EV/EBITDA, EV/EBIT, EV/Revenue), and apply the median or appropriate percentile to the target.

9.5 PVGO and the P/E Decomposition

A firm’s stock price can be decomposed into:

where \(E_1/k\) is the value of the firm if it paid out 100% of earnings as dividends and made no new investments. PVGO (Present Value of Growth Opportunities) is positive only if the firm reinvests in projects earning above the required return \(k\).

Firms with high P/E ratios are priced for growth — the market is attributing significant PVGO. This is a disciplining framework: if ROIC < WACC, additional investment destroys value and a higher growth rate actually reduces intrinsic value.

Chapter 10: Fixed Income Analytics

10.1 Yield Spread Analysis

Beyond government bonds, investors analyze credit spreads to assess relative value:

Option-Adjusted Spread (OAS): The constant spread added to the risk-free yield curve that equates a bond’s model price (accounting for embedded options) to its market price. OAS is the preferred spread measure for callable bonds and MBS.

Z-spread: The constant spread added to the risk-free spot (zero-coupon) curve to equate the PV of a bond’s cash flows to its market price. Appropriate for non-callable bonds.

Nominal spread: Simply the difference between the bond’s YTM and the YTM of an on-the-run government bond of similar maturity. Easiest to compute, but ignores the term structure shape.

10.2 Bond Analytics in Excel

Building a comprehensive bond analytics workbook in Excel involves:

Sheet 1 — Bond Pricing Dashboard: Input cells for face value, coupon rate, settlement date, maturity date, frequency, day-count. Output cells using PRICE, YIELD, DURATION, MDURATION (modified duration), and a manual convexity calculation.

Excel DURATION and MDURATION functions:

=DURATION(settlement, maturity, coupon, yield, frequency, [basis])
=MDURATION(settlement, maturity, coupon, yield, frequency, [basis])

Convexity calculation (manual in Excel — no built-in function):

For a bond with semi-annual coupons and \(n\) periods, convexity ≈:

In Excel, compute bond price at \(y\), \(y + \Delta y\), and \(y - \Delta y\) using PRICE, then apply the formula above. Use \(\Delta y = 0.0001\) (1 basis point) for numerical precision.

10.3 Portfolio Duration and Immunization

Portfolio duration is the market-value-weighted average of the durations of individual bonds:

Immunization matches the duration of a bond portfolio to the duration of a liability stream, protecting the portfolio against interest rate changes. If duration is matched and rates shift in parallel, the change in asset value approximately equals the change in liability value — the net position is hedged.

Rebalancing: As time passes and as yields change, duration drifts. Immunization portfolios must be periodically rebalanced to maintain the duration match.

Chapter 11: Portfolio Analytics

11.1 Return and Risk Measurement

For a portfolio of \(n\) assets with weights \(w_i\), the portfolio expected return and variance are:

where \(\boldsymbol{\Sigma}\) is the covariance matrix and \(\mathbf{w}\) is the vector of portfolio weights.

Covariance between two assets: \(\sigma_{ij} = \rho_{ij} \sigma_i \sigma_j\), where \(\rho_{ij}\) is the correlation coefficient.

The fundamental insight of modern portfolio theory (Markowitz, 1952): Diversification reduces portfolio risk when assets are imperfectly correlated (\(\rho < 1\)). The reduction in risk from diversification depends on the average pairwise correlation — lower correlations generate greater diversification benefit.

11.2 The Efficient Frontier

The minimum variance frontier is the set of portfolios that minimize portfolio variance for each level of expected return. The efficient frontier is the upper portion of the minimum variance frontier — portfolios that maximize expected return for each level of variance.

Portfolios below the efficient frontier are dominated (same risk, lower return). A rational investor selects the efficient portfolio that best matches their risk tolerance.

The global minimum variance portfolio (GMVP) is the portfolio with the lowest possible variance.

11.3 Mean-Variance Optimization in Python

import numpy as np
import pandas as pd
import yfinance as yf
import matplotlib.pyplot as plt

# Download historical prices for a small Canadian equity portfolio
tickers = ["RY.TO", "TD.TO", "ENB.TO", "CNQ.TO", "SU.TO"]
prices = yf.download(tickers, start="2021-01-01", end="2024-12-31")["Close"]
returns = prices.pct_change().dropna()

# Mean returns and covariance matrix (annualized)
mu = returns.mean() * 252
cov = returns.cov() * 252

n = len(tickers)
n_portfolios = 5000
np.random.seed(42)

port_returns = []
port_vols = []
port_sharpes = []
port_weights = []

rf = 0.045  # risk-free rate (approximate 1Y GoC bill yield)

for _ in range(n_portfolios):
    w = np.random.dirichlet(np.ones(n))  # random weights summing to 1
    ret = w @ mu
    vol = np.sqrt(w @ cov @ w)
    sharpe = (ret - rf) / vol
    port_returns.append(ret)
    port_vols.append(vol)
    port_sharpes.append(sharpe)
    port_weights.append(w)

# Plot the efficient frontier (Monte Carlo simulation)
plt.figure(figsize=(9, 5))
sc = plt.scatter(port_vols, port_returns, c=port_sharpes, cmap='viridis',
                 alpha=0.5, s=10)
plt.colorbar(sc, label='Sharpe Ratio')
plt.xlabel('Annualized Volatility')
plt.ylabel('Annualized Expected Return')
plt.title('Monte Carlo Efficient Frontier — Canadian Equity Portfolio')
plt.tight_layout()
plt.show()

# Identify maximum Sharpe ratio portfolio
best_idx = np.argmax(port_sharpes)
print("Maximum Sharpe Portfolio Weights:")
for ticker, w in zip(tickers, port_weights[best_idx]):
    print(f"  {ticker}: {w:.1%}")
print(f"Expected Return: {port_returns[best_idx]:.1%}")
print(f"Volatility:      {port_vols[best_idx]:.1%}")
print(f"Sharpe Ratio:    {port_sharpes[best_idx]:.2f}")

11.4 Capital Asset Pricing Model (CAPM)

The CAPM is the equilibrium asset pricing model that emerges when all investors hold mean-variance efficient portfolios:

where \(\beta_i = \text{Cov}(R_i, R_M) / \text{Var}(R_M)\) measures the systematic risk of asset \(i\) relative to the market.

The Security Market Line (SML) graphs expected return against beta. Stocks above the SML are underpriced (positive alpha); stocks below the SML are overpriced (negative alpha).

Estimating beta in Python:

import yfinance as yf
import numpy as np

# Download Royal Bank and S&P/TSX Composite returns
ry = yf.download("RY.TO", start="2021-01-01", end="2024-12-31")["Close"]
tsx = yf.download("^GSPTSE", start="2021-01-01", end="2024-12-31")["Close"]

r_ry = ry.pct_change().dropna()
r_tsx = tsx.pct_change().dropna()

# Align the two series
data = pd.concat([r_ry, r_tsx], axis=1).dropna()
data.columns = ["RY", "TSX"]

# OLS regression: beta = Cov(Ri, Rm) / Var(Rm)
cov_matrix = np.cov(data["RY"], data["TSX"])
beta = cov_matrix[0, 1] / cov_matrix[1, 1]
alpha_daily = data["RY"].mean() - beta * data["TSX"].mean()

print(f"Beta (RY vs TSX): {beta:.3f}")
print(f"Alpha (daily):    {alpha_daily:.5f}")

Chapter 12: Introduction to Python for Finance

12.1 Python in the Financial Industry

Python has become the dominant language for quantitative finance, financial data analysis, and risk management. It is used at:

• Investment banks (Goldman Sachs, JP Morgan, RBC, TD): Derivatives pricing, risk model implementation, trade surveillance.
• Asset managers (Bridgewater, CPPIB, OTPP): Factor model research, portfolio optimization, performance attribution.
• Fintech companies: Algorithmic trading, robo-advisors, credit scoring models.
• Regulatory bodies: Bank of Canada, OSFI — macroprudential risk analysis.

Python’s ecosystem for finance is unmatched: numpy for linear algebra, pandas for time series, scipy for optimization, matplotlib/seaborn/plotly for visualization, scikit-learn for machine learning, and specialized libraries like QuantLib, PyPortfolioOpt, and statsmodels.

12.2 Core Python Libraries for Finance

NumPy — Numerical Foundations

import numpy as np

# Present value of cash flow stream
cash_flows = np.array([0, 500, 700, 900, 1100])
r = 0.08
t = np.arange(len(cash_flows))
pv_factors = (1 + r) ** (-t)
pv = np.dot(cash_flows, pv_factors)
print(f"PV = ${pv:.2f}")

# Annualized return from monthly returns
monthly_returns = np.array([0.01, -0.02, 0.015, 0.008, -0.005, 0.02])
annualized = (np.prod(1 + monthly_returns)) ** (12 / len(monthly_returns)) - 1
print(f"Annualized return: {annualized:.2%}")

Pandas — DataFrames and Time Series

import pandas as pd
import yfinance as yf

# Download and process market data
tickers = ["SHOP.TO", "RY.TO", "CNQ.TO"]
raw = yf.download(tickers, start="2022-01-01", end="2024-12-31")["Close"]

# Compute simple returns
returns = raw.pct_change().dropna()

# Summary statistics
print(returns.describe())

# Compute rolling 30-day volatility (annualized)
rolling_vol = returns.rolling(30).std() * np.sqrt(252)
print(rolling_vol.tail())

# Correlation matrix
print(returns.corr().round(3))

Matplotlib — Financial Data Visualization

import matplotlib.pyplot as plt
import matplotlib.dates as mdates

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(10, 7), sharex=True)

# Price chart
ax1.plot(raw.index, raw["SHOP.TO"], label="Shopify (SHOP.TO)", color="blue")
ax1.set_ylabel("Price (CAD)")
ax1.set_title("Shopify — Price and Rolling Volatility")
ax1.legend()
ax1.grid(True, alpha=0.3)

# Rolling volatility chart
ax2.plot(rolling_vol.index, rolling_vol["SHOP.TO"] * 100,
         label="30-Day Rolling Vol (%)", color="orange")
ax2.set_ylabel("Annualized Volatility (%)")
ax2.set_xlabel("Date")
ax2.legend()
ax2.grid(True, alpha=0.3)

ax2.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))
plt.tight_layout()
plt.show()

12.3 Calculating Returns and Risk Metrics

Key return and risk metrics in portfolio analytics:

import numpy as np
import pandas as pd
import yfinance as yf

prices = yf.download("RY.TO", start="2020-01-01", end="2024-12-31")["Close"]
r = prices.pct_change().dropna()

# Annualized statistics
annual_return = r.mean() * 252
annual_vol = r.std() * np.sqrt(252)
rf = 0.045 / 252   # daily risk-free rate

# Sharpe Ratio (annualized)
sharpe = (r.mean() - rf) / r.std() * np.sqrt(252)

# Maximum Drawdown
cum_returns = (1 + r).cumprod()
rolling_max = cum_returns.cummax()
drawdown = (cum_returns - rolling_max) / rolling_max
max_drawdown = drawdown.min()

# Value at Risk (VaR) — historical simulation, 95% confidence
var_95 = r.quantile(0.05)

# Conditional Value at Risk (CVaR / Expected Shortfall)
cvar_95 = r[r <= var_95].mean()

print(f"Annualized Return:  {annual_return:.2%}")
print(f"Annualized Vol:     {annual_vol:.2%}")
print(f"Sharpe Ratio:       {sharpe:.2f}")
print(f"Max Drawdown:       {max_drawdown:.2%}")
print(f"VaR (95%, 1-day):   {var_95:.2%}")
print(f"CVaR (95%, 1-day):  {cvar_95:.2%}")

12.4 Log Returns vs. Simple Returns

Simple (arithmetic) return: \(r_t = (P_t - P_{t-1}) / P_{t-1}\). Appropriate for computing cross-sectional portfolio returns (portfolio return = weighted average of simple returns of constituents).

Log (geometric/continuous) return: \(r_t^{log} = \ln(P_t / P_{t-1})\). Additive across time: the multi-period log return equals the sum of single-period log returns. Approximately normally distributed for daily data. Preferred for time-series analysis and volatility modeling.

Relationship: \(r_t^{log} \approx r_t - r_t^2/2\) for small returns. The two measures diverge meaningfully for large returns or long horizons.

import numpy as np
import pandas as pd
import yfinance as yf

prices = yf.download("ENB.TO", start="2022-01-01", end="2024-12-31")["Close"]

simple_returns = prices.pct_change().dropna()
log_returns = np.log(prices / prices.shift(1)).dropna()

print("Simple returns mean:   ", simple_returns.mean().round(6))
print("Log returns mean:      ", log_returns.mean().round(6))
print("Simple returns annual: ", (simple_returns.mean() * 252).round(4))
print("Log returns annual:    ", (log_returns.sum() / 3).round(4))  # 3 years

Chapter 13: Data Visualization for Financial Decision-Making

13.1 Principles of Financial Data Visualization

Effective financial visualization communicates analytical conclusions efficiently and accurately. Poorly designed charts — excessive decoration, inappropriate chart types, misleading axes — obscure rather than illuminate. Core principles:

1. Choose the right chart type for the data relationship: Time series → line chart. Composition → stacked bar or pie chart. Distribution → histogram or box plot. Correlation → scatter plot. Comparison across categories → grouped bar chart.
2. Lead with the insight: Title the chart with the conclusion (“Revenue Growth Accelerated in Q4 2024”) rather than a neutral description (“Revenue, 2022–2024”).
3. Minimize chartjunk: Remove gridlines, background colours, 3D effects, and unnecessary legends that do not add information.
4. Use colour purposefully: Reserve colour to encode meaning (e.g., positive/negative, group membership). Use a colourblind-safe palette (e.g., blue/orange rather than red/green).
5. Label directly: Place labels directly on data points or series rather than using a separate legend when possible.

13.2 Chart Types for Financial Data

13.3 Candlestick Charts in Python

import yfinance as yf
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches

ticker = yf.Ticker("RY.TO")
hist = ticker.history(period="3mo")

fig, ax = plt.subplots(figsize=(12, 5))

for i, (date, row) in enumerate(hist.iterrows()):
    o, h, l, c = row["Open"], row["High"], row["Low"], row["Close"]
    color = "green" if c >= o else "red"
    # Draw wick
    ax.plot([i, i], [l, h], color=color, linewidth=0.8)
    # Draw body
    ax.add_patch(mpatches.Rectangle(
        (i - 0.3, min(o, c)), 0.6, abs(c - o),
        color=color, alpha=0.8
    ))

ax.set_xlim(-1, len(hist))
ax.set_xticks(range(0, len(hist), 10))
ax.set_xticklabels(
    [hist.index[i].strftime("%Y-%m-%d") for i in range(0, len(hist), 10)],
    rotation=30, ha='right'
)
ax.set_title("Royal Bank (RY.TO) — 3-Month Candlestick Chart")
ax.set_ylabel("Price (CAD)")
ax.grid(True, alpha=0.2)
plt.tight_layout()
plt.show()

13.4 Dashboard Design for Financial Analysis

A financial analytics dashboard aggregates multiple charts and metrics into a single view, enabling rapid assessment of portfolio health, market conditions, or company performance. Best practices:

• Logical flow: Organize panels in reading order (top-left to bottom-right) following the analytical story — big picture first, detail second.
• Consistent scales: When comparing multiple time series on separate panels, use consistent y-axis scales to avoid misleading magnitude comparisons.
• Key metrics prominently displayed: Show the most critical KPIs (total return, Sharpe ratio, drawdown) in large, prominent cells.
• Interactivity: Where tools allow (Power BI, Tableau, Plotly Dash), enable drill-down and date range filtering.

In Excel, a dashboard is typically a dedicated sheet with no gridlines (View → Gridlines unchecked), charts linked to pivot table data sources, and key metrics pulled using GETPIVOTDATA or direct formula references from the underlying data model.

13.5 Storytelling with Financial Data

The Pyramid Principle (Barbara Minto) structures financial communication: lead with the conclusion (the recommendation), support it with grouped evidence, and provide detailed backup in appendices. Applied to an equity research pitch:

1. Opening (30 seconds): “We recommend buying Enbridge (ENB.TO). Our DCF implies a 12-month target price of $68, representing 25% upside from the current $54 price.”
2. Three supporting pillars: (a) Regulated pipeline assets provide a 98% cost-of-service revenue floor; (b) 25-year debt maturity extension eliminates refinancing risk; (c) 3% annual rate base growth is fully funded from operating cash flows.
3. Key risk: Regulatory approval delays for Line 5 replacement could impair 2027 cash flows; our bear case assigns a 30% probability.
4. Valuation: Comp analysis (8× EV/EBITDA) corroborates DCF target; transaction comps from 2022–2024 midstream M&A imply a 10–15% control premium further supports the case.

Chapter 14: Time Value of Money — Python Applications

14.1 TVM Review and Python Implementation

The fundamental present value identity underlies all of quantitative finance:

A general TVM solver in Python handles arbitrary cash flow streams:

import numpy as np

def npv(rate, cash_flows):
    """Compute NPV for cash flows at periods 0, 1, 2, ..., n."""
    t = np.arange(len(cash_flows))
    return np.sum(np.array(cash_flows) / (1 + rate) ** t)

def irr(cash_flows, tol=1e-8, max_iter=1000):
    """Compute IRR using Newton-Raphson method."""
    from scipy.optimize import brentq
    f = lambda r: npv(r, cash_flows)
    try:
        return brentq(f, -0.9, 10.0, xtol=tol, maxiter=max_iter)
    except ValueError:
        return float('nan')

# Example: project cash flows
cfs = [-500_000, 120_000, 150_000, 180_000, 200_000, 220_000]
rate = 0.10

print(f"NPV at 10%: ${npv(rate, cfs):,.0f}")
print(f"IRR:        {irr(cfs):.2%}")

14.2 Loan Amortization Schedule

A loan amortization schedule breaks down each payment into interest and principal components:

import pandas as pd

def amortization_schedule(principal, annual_rate, years, freq=12):
    """Generate a loan amortization table."""
    r = annual_rate / freq
    n = years * freq
    payment = principal * r / (1 - (1 + r) ** (-n))

    records = []
    balance = principal
    for period in range(1, n + 1):
        interest = balance * r
        principal_paid = payment - interest
        balance -= principal_paid
        records.append({
            "Period": period,
            "Payment": round(payment, 2),
            "Interest": round(interest, 2),
            "Principal": round(principal_paid, 2),
            "Balance": round(max(balance, 0), 2)
        })

    return pd.DataFrame(records)

schedule = amortization_schedule(500_000, 0.055, 25)
print(schedule.head(12).to_string(index=False))
print(f"\nTotal Interest Paid: ${schedule['Interest'].sum():,.0f}")

14.3 Capital Budgeting Scenarios with Python

import pandas as pd
import numpy as np

scenarios = {
    "Bear Case": {"growth": 0.03, "margin": 0.12, "wacc": 0.11},
    "Base Case": {"growth": 0.05, "margin": 0.15, "wacc": 0.09},
    "Bull Case": {"growth": 0.08, "margin": 0.18, "wacc": 0.08},
}

base_revenue = 100_000_000  # $100M
terminal_growth = 0.025
projection_years = 5

results = []
for name, params in scenarios.items():
    fcffs = []
    revenue = base_revenue
    for yr in range(1, projection_years + 1):
        revenue *= (1 + params["growth"])
        fcff = revenue * params["margin"]
        fcffs.append(fcff)

    tv = fcffs[-1] * (1 + terminal_growth) / (params["wacc"] - terminal_growth)

    t = np.arange(1, projection_years + 1)
    pv_fcffs = np.sum(np.array(fcffs) / (1 + params["wacc"]) ** t)
    pv_tv = tv / (1 + params["wacc"]) ** projection_years
    ev = pv_fcffs + pv_tv

    results.append({
        "Scenario": name,
        "WACC": f"{params['wacc']:.0%}",
        "Revenue Growth": f"{params['growth']:.0%}",
        "EBITDA Margin": f"{params['margin']:.0%}",
        "Enterprise Value ($M)": f"${ev/1e6:,.1f}M"
    })

print(pd.DataFrame(results).to_string(index=False))

Chapter 15: Applied Analytics — Putting It All Together

15.1 The Equity Research Workflow

A professional equity research process integrates all the tools and frameworks covered in AFM 127:

1. Data gathering: Download price history (yfinance), financial statements (SEDAR+, FactSet), and macroeconomic data (Statistics Canada, Bank of Canada Valet API).

2. Financial statement analysis: Compute ratios (profitability, leverage, efficiency, market value) and trend them over 5–10 years. Flag deterioration in quality-of-earnings metrics.

3. Valuation: Build a DCF model in Excel with scenario analysis. Cross-check with trading comps (EV/EBITDA) and precedent transactions.

4. Portfolio context: Estimate the stock’s beta relative to the TSX. Assess how adding the stock affects portfolio Sharpe ratio.

5. Presentation: Communicate findings using a structured equity research report (executive summary, business overview, industry analysis, financial analysis, valuation, risks, recommendation).

15.2 Equity Screening with Python

Systematic screening identifies investment candidates meeting predefined criteria before fundamental analysis:

import pandas as pd

# Hypothetical screening dataset (would come from Bloomberg/FactSet in practice)
# Columns: Ticker, Sector, P/E, EV/EBITDA, ROE, Dividend Yield, Debt/EBITDA
data = {
    "Ticker": ["RY.TO", "TD.TO", "ENB.TO", "CNQ.TO", "SU.TO",
               "SHOP.TO", "CP.TO", "BCE.TO", "MFC.TO", "TRP.TO"],
    "Sector": ["Financials", "Financials", "Energy", "Energy", "Energy",
               "Technology", "Industrials", "Comm Services", "Financials", "Energy"],
    "PE": [11.5, 12.0, 18.2, 8.4, 9.1, 52.0, 22.1, 15.3, 10.8, 14.7],
    "EV_EBITDA": [8.2, 8.5, 9.1, 4.3, 4.8, 35.0, 13.2, 6.9, 7.4, 9.8],
    "ROE": [0.134, 0.141, 0.089, 0.218, 0.189, 0.041, 0.178, 0.095, 0.112, 0.082],
    "DivYield": [0.044, 0.042, 0.073, 0.024, 0.051, 0.000, 0.009, 0.082, 0.052, 0.063],
    "NetDebt_EBITDA": [1.2, 1.3, 4.8, 0.6, 0.9, 0.0, 3.1, 4.2, 1.5, 5.1],
}
df = pd.DataFrame(data)

# Screen: P/E < 20, EV/EBITDA < 12, ROE > 10%, Net Debt/EBITDA < 4
screen = df[
    (df["PE"] < 20) &
    (df["EV_EBITDA"] < 12) &
    (df["ROE"] > 0.10) &
    (df["NetDebt_EBITDA"] < 4)
].sort_values("EV_EBITDA")

print("Stocks passing the screen:")
print(screen.to_string(index=False))

15.3 Building a Portfolio Performance Dashboard

import pandas as pd
import numpy as np
import yfinance as yf
import matplotlib.pyplot as plt

# Define portfolio
holdings = {
    "RY.TO": 0.20, "TD.TO": 0.15, "ENB.TO": 0.15,
    "CNQ.TO": 0.15, "CP.TO": 0.15, "SHOP.TO": 0.10,
    "BCE.TO": 0.10
}
benchmark = "^GSPTSE"   # S&P/TSX Composite

tickers = list(holdings.keys()) + [benchmark]
prices = yf.download(tickers, start="2022-01-01", end="2024-12-31")["Close"]
returns = prices.pct_change().dropna()

# Compute portfolio daily returns
weights = np.array(list(holdings.values()))
port_returns = returns[list(holdings.keys())] @ weights
bench_returns = returns[benchmark]

# Align and compute cumulative returns
cum_port = (1 + port_returns).cumprod()
cum_bench = (1 + bench_returns).cumprod()

rf_daily = 0.045 / 252

# Performance metrics
def performance_metrics(r, rf=rf_daily, label="Portfolio"):
    ann_ret = r.mean() * 252
    ann_vol = r.std() * np.sqrt(252)
    sharpe = (r.mean() - rf) / r.std() * np.sqrt(252)
    cumulative = (1 + r).cumprod()
    rolling_max = cumulative.cummax()
    max_dd = ((cumulative - rolling_max) / rolling_max).min()
    print(f"\n{label} Performance Summary:")
    print(f"  Annualized Return: {ann_ret:.2%}")
    print(f"  Annualized Vol:    {ann_vol:.2%}")
    print(f"  Sharpe Ratio:      {sharpe:.2f}")
    print(f"  Max Drawdown:      {max_dd:.2%}")

performance_metrics(port_returns, label="Portfolio")
performance_metrics(bench_returns, label="S&P/TSX Composite")

# Plot
fig, axes = plt.subplots(2, 1, figsize=(11, 7), sharex=True)

axes[0].plot(cum_port.index, cum_port.values, label="Portfolio", linewidth=2)
axes[0].plot(cum_bench.index, cum_bench.values, label="S&P/TSX", linewidth=1.5, linestyle='--')
axes[0].set_title("Portfolio vs. Benchmark — Cumulative Return")
axes[0].set_ylabel("Cumulative Return (rebased to 1.0)")
axes[0].legend()
axes[0].grid(True, alpha=0.3)

rolling_vol_port = port_returns.rolling(30).std() * np.sqrt(252)
rolling_vol_bench = bench_returns.rolling(30).std() * np.sqrt(252)
axes[1].plot(rolling_vol_port.index, rolling_vol_port * 100, label="Portfolio", linewidth=1.5)
axes[1].plot(rolling_vol_bench.index, rolling_vol_bench * 100, label="S&P/TSX", linewidth=1.5, linestyle='--')
axes[1].set_ylabel("30-Day Rolling Vol (%)")
axes[1].set_xlabel("Date")
axes[1].legend()
axes[1].grid(True, alpha=0.3)

plt.tight_layout()
plt.show()

15.4 Sensitivity Analysis and Scenario Presentation

A complete presentation of a DCF model should always include a sensitivity table showing intrinsic value per share across a matrix of WACC and terminal growth rate assumptions:

import numpy as np
import pandas as pd

def dcf_sensitivity(base_fcff, wacc_range, tg_range, years=5, shares=100e6, net_debt=200e6):
    """Compute intrinsic equity value per share across WACC and terminal growth rates."""
    results = {}
    for tg in tg_range:
        row = {}
        for wacc in wacc_range:
            t = np.arange(1, years + 1)
            # Simple projection: FCFF grows at 5% for 5 years
            fcffs = base_fcff * (1.05 ** t)
            pv_fcffs = np.sum(fcffs / (1 + wacc) ** t)
            tv = fcffs[-1] * (1 + tg) / (wacc - tg) if wacc > tg else float('nan')
            pv_tv = tv / (1 + wacc) ** years
            ev = pv_fcffs + pv_tv
            equity_val = ev - net_debt
            price = equity_val / shares
            row[f"{wacc:.1%}"] = f"${price:.2f}"
        results[f"{tg:.1%}"] = row
    return pd.DataFrame(results).T

waccs = [0.07, 0.08, 0.09, 0.10, 0.11]
tgs   = [0.015, 0.020, 0.025, 0.030, 0.035]
base_fcff = 50e6  # $50M FCFF in Year 0

sensitivity = dcf_sensitivity(base_fcff, waccs, tgs)
sensitivity.index.name = "Terminal Growth \\ WACC"
print(sensitivity.to_string())

15.5 Group Presentation Standards

The AFM 127 group presentation requires applying the analytical tools covered throughout the course to a real publicly traded company. Key success criteria:

Analytical rigour:

• DCF model with documented assumptions and sensitivity analysis.
• At least one set of trading comps (5+ peers, 2+ multiples).
• Historical financial ratio trend analysis (3+ years).
• Beta estimation and CAPM cost of equity calculation.

Professional presentation:

• Lead with the recommendation and price target.
• No more than 10–12 slides for a 15-minute presentation.
• All charts properly labelled (title, axis labels, data source).
• Speak to the audience, not to the slides.

Python or Excel evidence:

• Attach a clearly organized Excel model as an appendix.
• Optionally include a Jupyter notebook demonstrating Python-based ratio analysis or portfolio analytics.

Chapter 16: Yield Curve Bootstrapping and Spot Rate Construction

16.1 Why Bootstrapping Matters

The yield curve that practitioners observe in the market is typically a par yield curve — the coupon rates at which bonds of various maturities would be priced exactly at par. However, rigorous fixed income analytics require the spot rate curve (also called the zero-coupon curve): the yield on a hypothetical zero-coupon bond of each maturity. Spot rates are used to discount individual cash flows at maturity-matched rates, producing more accurate valuations than a single flat YTM.

16.2 The Bootstrap Algorithm

Suppose the par yield curve (annual coupons, annual periods) is:

Step 1 — Year 1 spot rate. A 1-year par bond pays one coupon plus par at maturity. Since there is only one cash flow, the spot rate equals the par yield:

Step 2 — Year 2 spot rate. A 2-year par bond pays coupon \(c_2 = 4.25\) at Year 1 and \(100 + 4.25\) at Year 2. Its price is par (100):

Step 3 — Year 3 spot rate (general pattern):

Solve for \(s_3\) given already-known \(s_1, s_2\).

The general recursion for year \(n\):

16.3 Bootstrapping in Python

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# Par yield curve input
maturities = [1, 2, 3, 4, 5]
par_yields  = [0.0400, 0.0425, 0.0450, 0.0470, 0.0485]

spot_rates = []

for n, c in zip(maturities, par_yields):
    if n == 1:
        # Year-1 spot rate = par yield directly
        s_n = c
    else:
        # PV of coupons at already-known spot rates
        pv_coupons = sum(
            c / (1 + spot_rates[t - 1]) ** t
            for t in range(1, n)
        )
        # Solve for s_n
        s_n = ((1 + c) / (1 - pv_coupons)) ** (1 / n) - 1
    spot_rates.append(s_n)

# Build a comparison DataFrame
curve_df = pd.DataFrame({
    "Maturity": maturities,
    "Par Yield (%)": [y * 100 for y in par_yields],
    "Spot Rate (%)": [s * 100 for s in spot_rates],
})
print(curve_df.to_string(index=False))

# Plot
plt.figure(figsize=(8, 4))
plt.plot(curve_df["Maturity"], curve_df["Par Yield (%)"],  'b-o', label="Par Yield")
plt.plot(curve_df["Maturity"], curve_df["Spot Rate (%)"], 'r--s', label="Spot Rate")
plt.title("Par Yield Curve vs. Bootstrapped Spot Curve")
plt.xlabel("Maturity (years)")
plt.ylabel("Rate (%)")
plt.legend()
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

16.4 Deriving Forward Rates from Spot Rates

Once spot rates are known, forward rates are mechanical:

forward_rates = []
for i in range(1, len(spot_rates)):
    n   = maturities[i]
    n_1 = maturities[i - 1]
    f   = (1 + spot_rates[i]) ** n / (1 + spot_rates[i - 1]) ** n_1 - 1
    forward_rates.append(f)

print("\n1-year forward rates:")
for i, f in enumerate(forward_rates, start=1):
    print(f"  f({i},{i+1}) = {f:.4%}")

Forward rates rising above the spot rates is consistent with the term premium interpretation: the market is pricing in higher future short rates (or a liquidity premium) for long-dated commitments.

Chapter 17: Pandas for Financial Data Analysis

17.1 Loading and Inspecting Financial Data

Pandas is the cornerstone of Python-based financial data work. Most real-world data arrives as flat files (CSV, Excel) or database queries. The read_csv function is the standard entry point:

import pandas as pd

# Load a CSV of daily equity price data exported from Bloomberg or FactSet
# Expected columns: Date, Ticker, Open, High, Low, Close, Volume
prices_raw = pd.read_csv(
    "equity_prices.csv",
    parse_dates=["Date"],   # automatically convert Date column to datetime
    index_col="Date",       # use Date as the row index
)

print(prices_raw.shape)          # (rows, columns)
print(prices_raw.dtypes)         # column data types
print(prices_raw.head())         # first 5 rows
print(prices_raw.describe())     # summary statistics
print(prices_raw.isnull().sum()) # count missing values per column

17.2 Reshaping: From Long to Wide Format

Bloomberg typically exports price data in long format (one row per ticker-date pair). Portfolio analytics require wide format (one column per ticker):

# Long format: Date | Ticker | Close
long_df = pd.read_csv("bloomberg_export.csv", parse_dates=["Date"])

# Pivot to wide format
wide_df = long_df.pivot(index="Date", columns="Ticker", values="Close")

# Forward-fill any remaining gaps (e.g., holidays causing missing dates)
wide_df = wide_df.sort_index().ffill()

print(wide_df.head())

17.3 Computing Returns with GroupBy

When working with long-format data, groupby computes returns correctly for each ticker without pivoting:

import numpy as np

# Sort by Ticker then Date to ensure correct lag calculation
long_df = long_df.sort_values(["Ticker", "Date"])

# Compute simple returns within each ticker group
long_df["Return"] = (
    long_df.groupby("Ticker")["Close"]
    .pct_change()
)

# Compute log returns
long_df["LogReturn"] = (
    long_df.groupby("Ticker")["Close"]
    .transform(lambda x: np.log(x / x.shift(1)))
)

# Annualised volatility by ticker
annual_vol = (
    long_df.groupby("Ticker")["Return"]
    .std()
    .mul(np.sqrt(252))
    .rename("Ann. Volatility")
)
print(annual_vol.sort_values(ascending=False))

# Add sector mapping
sector_map = {
    "RY.TO": "Financials", "TD.TO": "Financials",
    "ENB.TO": "Energy",    "CNQ.TO": "Energy",
    "SU.TO":  "Energy",    "SHOP.TO": "Technology",
    "CP.TO":  "Industrials"
}
long_df["Sector"] = long_df["Ticker"].map(sector_map)

# Average annualised return by sector
sector_returns = (
    long_df.groupby(["Sector", long_df["Date"].dt.year])["Return"]
    .mean()
    .mul(252)
    .unstack("Sector")
    .round(4)
)
print(sector_returns)

17.4 Merging Financial DataFrames

Combining price data with fundamentals data requires merge (or join). A common workflow merges daily price returns with quarterly earnings data:

# Daily returns DataFrame: index = Date, columns = Ticker returns
returns_daily = wide_df.pct_change().dropna()

# Quarterly earnings DataFrame: Ticker | ReportDate | EPS | RevenueGrowth
earnings = pd.read_csv("earnings.csv", parse_dates=["ReportDate"])

# Merge on nearest prior reporting date using merge_asof
# (requires sorted DataFrames)
returns_daily_flat = returns_daily.reset_index().melt(
    id_vars="Date", var_name="Ticker", value_name="Return"
)
returns_daily_flat = returns_daily_flat.sort_values(["Ticker", "Date"])
earnings = earnings.sort_values(["Ticker", "ReportDate"])

merged = pd.merge_asof(
    returns_daily_flat,
    earnings,
    left_on="Date",
    right_on="ReportDate",
    by="Ticker",
    direction="backward"   # use the most recent prior report
)

# Now examine return patterns around earnings beats/misses
print(merged[["Date", "Ticker", "Return", "EPS"]].head(20))

17.5 Time-Series Resampling

Financial data often needs to be converted between frequencies. Pandas resample handles this cleanly:

# Convert daily prices to monthly OHLC
monthly_ohlc = wide_df["RY.TO"].resample("ME").ohlc()
print(monthly_ohlc.head(12))

# Compute monthly returns from daily prices
monthly_returns = (
    wide_df.resample("ME").last()   # end-of-month prices
    .pct_change()
    .dropna()
)

# Rolling 12-month annualized Sharpe ratio
rf_monthly = 0.045 / 12
rolling_sharpe = (
    (monthly_returns - rf_monthly)
    .rolling(12)
    .apply(lambda x: x.mean() / x.std() * np.sqrt(12), raw=True)
)
print(rolling_sharpe.tail(12).round(2))

Chapter 18: Mean-Variance Optimization with Excel Solver

18.1 Solver Setup for Portfolio Optimization

Excel Solver solves the classic Markowitz minimum-variance problem without any Python code. The setup:

Input data (Sheet: “MVO_Data”):

• Cells B2:F2 — ticker labels (e.g., RY.TO, TD.TO, ENB.TO, CNQ.TO, SHOP.TO)
• Cells B3:F3 — expected annual returns (manually entered or pulled from Bloomberg)
• Cells B5:F9 — 5×5 annual covariance matrix (computed from historical returns)

Decision variables (Sheet: “MVO_Solver”):

• Cells B12:F12 — portfolio weights \(w_i\) (the cells Solver will change)

Formulas:

Cell B14 (Portfolio Return):  =SUMPRODUCT(B3:F3, B12:F12)
Cell B15 (Portfolio Variance): =MMULT(TRANSPOSE(B12:F12), MMULT(B5:F9, B12:F12))
                                — entered as a single cell (not array formula in Excel 365)
Cell B16 (Portfolio StdDev):   =SQRT(B15)
Cell B17 (Sharpe Ratio):       =(B14 - B19) / B16   [B19 = risk-free rate input]
Cell B18 (Weights Sum):        =SUM(B12:F12)

Solver configuration (for maximum Sharpe ratio):

• Set Objective: B17 (Sharpe ratio) → Max
• By Changing Variable Cells: B12:F12
• Subject to the Constraints:
  • B18 = 1 (weights sum to 1)
  • B12:F12 >= 0 (no short selling)
• Solving Method: GRG Nonlinear

18.2 Generating the Efficient Frontier in Excel

To trace the entire efficient frontier (not just the tangency portfolio), run Solver repeatedly across a range of target return values:

1. Add a target return input cell (e.g., B20 = target return).
2. Change Solver objective to Minimize B15 (variance).
3. Add constraint: B14 = B20.
4. Use a macro (VBA or Excel Script) to loop B20 from the minimum to maximum feasible return in small increments, recording weight solutions and the resulting (return, variance) pairs.
5. Plot the (σ, E[R]) pairs as a scatter chart to visualize the frontier.

In Excel VBA, the loop looks like:

Sub EfficientFrontier()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MVO_Solver")
    Dim outputRow As Integer
    outputRow = 25   ' starting row for output

    Dim targetReturn As Double
    For targetReturn = 0.07 To 0.18 Step 0.005
        ws.Range("B20").Value = targetReturn
        SolverSolve UserFinish:=True
        ws.Cells(outputRow, 1).Value = targetReturn
        ws.Cells(outputRow, 2).Value = ws.Range("B16").Value  ' portfolio vol
        outputRow = outputRow + 1
    Next targetReturn
End Sub

18.3 Adding the Capital Market Line

Once the tangency portfolio is identified, the Capital Market Line (CML) is plotted in Excel as a straight line from the risk-free rate (σ = 0, E[R] = R_f) through the tangency portfolio and beyond. The CML formula:

In Excel, compute the CML slope (Sharpe ratio of the tangency portfolio) and draw a line chart series using SEQUENCE-generated σ values from 0% to 40%:

CML Return at σ = x:  =B19 + (B14_tangency - B19) / B16_tangency * x

Where B14_tangency and B16_tangency hold the tangency portfolio’s return and volatility, and B19 holds the risk-free rate. All investors, regardless of risk tolerance, should hold a combination of the risk-free asset and the tangency portfolio — the CML traces all efficient combinations.

Chapter 19: Advanced Python — Factor Models and Heat Maps

19.1 Fama-French Three-Factor Model

The CAPM’s single-factor (market beta) framework leaves substantial cross-sectional return variation unexplained. The Fama-French three-factor model adds size (SMB) and value (HML) factors:

• SMB (Small Minus Big): Return of small-cap stocks minus large-cap stocks. Captures the size premium.
• HML (High Minus Low): Return of high book-to-market (value) stocks minus low book-to-market (growth) stocks. Captures the value premium.

import pandas as pd
import numpy as np
import statsmodels.api as sm
import yfinance as yf

# Download factor data from Kenneth French's Data Library
# In practice, download from: https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html
# Here we simulate illustrative factor returns
np.random.seed(99)
n = 756  # 3 years of daily returns
dates = pd.bdate_range("2022-01-01", periods=n)

factors = pd.DataFrame({
    "MktRF": np.random.normal(0.0003, 0.01, n),
    "SMB":   np.random.normal(0.0001, 0.005, n),
    "HML":   np.random.normal(0.0001, 0.004, n),
    "RF":    np.full(n, 0.045 / 252),
}, index=dates)

# Download a Canadian stock
shop = yf.download("SHOP.TO", start="2022-01-01", end="2024-12-31")["Close"]
shop_ret = shop.pct_change().dropna()

# Align
data = factors.join(shop_ret.rename("SHOP"), how="inner").dropna()
data["ExRet_SHOP"] = data["SHOP"] - data["RF"]

# OLS regression
X = sm.add_constant(data[["MktRF", "SMB", "HML"]])
y = data["ExRet_SHOP"]
model = sm.OLS(y, X).fit()
print(model.summary())
print(f"\nAlpha (daily):  {model.params['const']:.6f}")
print(f"Market Beta:    {model.params['MktRF']:.3f}")
print(f"SMB Beta:       {model.params['SMB']:.3f}")
print(f"HML Beta:       {model.params['HML']:.3f}")

19.2 Correlation Heat Maps in Python

A correlation heat map visualises pairwise return correlations across a portfolio, revealing diversification opportunities and hidden concentration risks.

import pandas as pd
import numpy as np
import yfinance as yf
import matplotlib.pyplot as plt
import matplotlib.colors as mcolors

tickers = ["RY.TO", "TD.TO", "ENB.TO", "CNQ.TO", "SU.TO",
           "SHOP.TO", "CP.TO", "BCE.TO", "MFC.TO", "TRP.TO"]
prices = yf.download(tickers, start="2022-01-01", end="2024-12-31")["Close"]
returns = prices.pct_change().dropna()

corr = returns.corr()

fig, ax = plt.subplots(figsize=(10, 8))
cmap = plt.cm.RdYlGn

im = ax.imshow(corr, cmap=cmap, vmin=-1, vmax=1)
plt.colorbar(im, ax=ax, shrink=0.8, label="Pearson Correlation")

# Annotate each cell
for i in range(len(corr)):
    for j in range(len(corr)):
        text = ax.text(j, i, f"{corr.iloc[i, j]:.2f}",
                       ha="center", va="center", fontsize=8,
                       color="black" if abs(corr.iloc[i, j]) < 0.7 else "white")

ax.set_xticks(range(len(tickers)))
ax.set_yticks(range(len(tickers)))
ax.set_xticklabels([t.replace(".TO", "") for t in tickers], rotation=45, ha="right")
ax.set_yticklabels([t.replace(".TO", "") for t in tickers])
ax.set_title("Pairwise Return Correlations — Canadian Equity Portfolio")
plt.tight_layout()
plt.show()

19.3 Waterfall Charts for Financial Bridge Analysis

A waterfall chart decomposes how one value moves to another through a series of positive and negative contributions. In finance, waterfall charts are used for:

• EBITDA-to-Net-Income bridges (starting from EBITDA, subtracting D&A, interest, taxes)
• Year-over-year revenue change decomposition (volume, price, FX, acquisitions)
• Portfolio return attribution (factor returns, security selection, currency)

import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import numpy as np

# EBITDA to Net Income bridge for an illustrative company ($M)
items = ["EBITDA", "D&A", "EBIT", "Interest", "EBT", "Tax", "Net Income"]
values = [450, -85, 365, -45, 320, -80, 240]
running_total = [0, 450, 450-85, 365, 365-45, 320, 320-80]

colors = []
for v in values:
    if v > 0 and items[values.index(v)] in ["EBITDA", "EBIT", "EBT", "Net Income"]:
        colors.append("#2196F3")   # Blue = total/subtotal
    elif v < 0:
        colors.append("#F44336")   # Red = reduction
    else:
        colors.append("#4CAF50")   # Green = increase

fig, ax = plt.subplots(figsize=(10, 5))

for i, (item, val, base) in enumerate(zip(items, values, running_total)):
    if item in ["EBITDA", "EBIT", "EBT", "Net Income"]:
        ax.bar(i, val if val > 0 else abs(val), bottom=0 if val > 0 else base,
               color="#2196F3", edgecolor="white")
        label_y = val / 2 if val > 0 else base + val / 2
        ax.text(i, val if val > 0 else val + base, f"${abs(val)}", ha="center",
                va="bottom", fontsize=9, fontweight="bold")
    else:
        bottom = base + val if val < 0 else base
        ax.bar(i, abs(val), bottom=bottom,
               color="#F44336" if val < 0 else "#4CAF50", edgecolor="white", alpha=0.85)
        ax.text(i, bottom + abs(val) / 2, f"(${abs(val)})" if val < 0 else f"+${val}",
                ha="center", va="center", fontsize=9, color="white", fontweight="bold")

ax.set_xticks(range(len(items)))
ax.set_xticklabels(items, rotation=15, ha="right")
ax.set_ylabel("$M")
ax.set_title("EBITDA to Net Income Bridge — Illustrative Company")
ax.axhline(0, color="black", linewidth=0.5)
ax.grid(True, axis="y", alpha=0.3)
plt.tight_layout()
plt.show()

Appendix: Quick Reference

Excel Financial Functions Summary

Python Quick Reference

# ---- Data acquisition ----
import yfinance as yf
prices = yf.download("RY.TO", start="2022-01-01", end="2024-12-31")["Close"]

# ---- Returns ----
import numpy as np
simple_ret = prices.pct_change().dropna()
log_ret = np.log(prices / prices.shift(1)).dropna()

# ---- Annualized stats ----
ann_return = simple_ret.mean() * 252
ann_vol    = simple_ret.std() * np.sqrt(252)
sharpe     = (ann_return - 0.045) / ann_vol

# ---- Portfolio math ----
import numpy as np
w = np.array([0.4, 0.3, 0.3])
mu = np.array([0.08, 0.10, 0.07])
cov = np.array([[0.04, 0.01, 0.005],
                [0.01, 0.06, 0.008],
                [0.005, 0.008, 0.03]])
port_return = w @ mu
port_vol    = np.sqrt(w @ cov @ w)

# ---- Optimization (PyPortfolioOpt) ----
# pip install PyPortfolioOpt
from pypfopt import EfficientFrontier, risk_models, expected_returns
mu_df   = expected_returns.mean_historical_return(prices_df)
S       = risk_models.sample_cov(prices_df)
ef      = EfficientFrontier(mu_df, S)
weights = ef.max_sharpe(risk_free_rate=0.045)
cleaned = ef.clean_weights()
ef.portfolio_performance(verbose=True)

Key Formulas Reference

Time Value of Money

Bond Pricing

Equity Valuation

Portfolio Theory

CAPM

DuPont