▸  Personal Project

Equity
Pricing Model

A self-directed equity research tool built from scratch in Excel and VBA, wired to Bloomberg for market data. It values any public company three independent ways — intrinsic, relative, and break-up — then blends them into a single price target with explicit upside.

3
Valuation Methods
7yr
P&L History
Bloomberg
Live Market Data
1
Blended Target

Overview

Three lenses on
one price

No single valuation method is right. Each makes different assumptions and breaks in different ways. The model runs three in parallel off a shared financial spine, shows where they agree and disagree, and weights them into one defensible target.

DCF

Discounted Cash Flow

Intrinsic value. Projects free cash flow off the P&L, discounts it at a WACC built from the company's own capital structure, and adds a perpetuity-growth terminal value. Answers what the business is worth on its own fundamentals, independent of where the market is pricing it today.

RV

Relative Value

Market-implied value. Benchmarks the company against a hand-picked peer set on EV/Sales, EV/EBITDA, and EV/EBIT across current and forward years. Upside is the gap between the target's multiples and the peer average and median.

SOTP

Sum of the Parts

Break-up value. Values each operating segment and minority stake separately, nets out debt and other liabilities, and applies a holdco discount. Built for conglomerates where the consolidated multiple hides the value of the pieces.

FINAL

Blended Target

The three methods are combined on a configurable weighting (default 75 / 15 / 10 toward DCF) into one equity value per share and a single upside/downside figure against the live market price.

Workflow

From one company
to a price target

Step 01 — P&L Engine

Build the financial spine

Enter the company's income statement by hand — seven years of history from revenue through diluted EPS, split into annual and quarterly columns with automatic period headers, margin lines, and YoY growth. Only the forward/estimate columns are wired to Bloomberg consensus; everything historical is manual input. Every downstream method reads from this sheet.

Manual P&L entry 7yr history Bloomberg consensus on forecasts only
P&L Engine worksheet

Step 02 — DCF

Value the cash flows

FCF is projected from forecast revenue growth, operating margin, tax, D&A, capex, and working capital. The discount rate is a full WACC — cost of equity via CAPM (risk-free, equity risk premium, Bloomberg beta) and after-tax cost of debt, weighted by capital structure. Terminal value uses the Gordon growth approach. Output is intrinsic equity value per share and upside to market.

FCF projection Bottom-up WACC Gordon terminal value
DCF worksheet

Step 03 — Relative Value

Benchmark the peers

Build a comparable set by ticker. Bloomberg populates names, prices, market caps, and EV multiples (EV/Sales, EV/EBITDA, EV/EBIT) on current and forward years. The model computes peer average and median, then the target's implied upside or downside to each.

Custom peer set Fwd & trailing multiples Mean / median delta
Relative Value worksheet

Step 04 — Sum of the Parts

Break it up

Value each operating division (EV/Sales, EV/EBITDA, or EV/NOPAT) and each minority stake on a DCF or NAV basis. Net debt and other liabilities are subtracted, a holdco discount applied, and the result divided by shares outstanding for an SOTP value per share.

Per-segment EVs Stake valuation Holdco discount
Sum of the Parts worksheet

Step 05 — Final Blend

One number

The three methods feed a weighting table — set any split across DCF, RV, and SOTP. The sheet returns the blended equity value per share and total upside against the live price.

Configurable weights Blended target Upside vs. market
Final Valuation worksheet

The Math

Under the hood

Each valuation method rests on a different idea about where value comes from. One block per method below, plus the rule that blends them.

DCF — intrinsic value

Discount rate & free cash flow

Two ideas carry the DCF: a discount rate built from the company's own risk, and a free cash flow stream projected from its operating economics.

The discount rate should reflect the specific company's risk and how it is financed. WACC blends the return equity holders demand with the after-tax cost of debt, weighted by how much of each sits in the capital structure. The cost of equity comes from CAPM: a risk-free base plus a market risk premium scaled by the stock's beta.

$$\text{WACC} = w_e \cdot r_e + w_d \cdot r_d$$
$$r_e = r_f + \beta\,(\text{ERP})$$
$w_e, w_d$ Weight of equity and debt in capital employed (E/V, D/V)
$r_f$ Risk-free rate (10Y US Treasury)
$\beta$ Equity beta, pulled live from Bloomberg
ERP Equity risk premium
$r_d$ Pre-tax cost of debt

The model carries both a Bloomberg-fed and a manual-input column, so every WACC component can be overridden and the live feed frozen to a static value.

Most of a growth company's value sits beyond the explicit forecast horizon. The model projects free cash flow year by year, discounts each back at the WACC, and caps the stream with a terminal value that assumes cash flows grow at a constant long-run rate forever.

$$\text{FCF} = \text{NOPAT} + D\&A - \text{CapEx} - \Delta\text{NWC}$$
$$\text{TV} = \dfrac{\text{FCF}_{T}\,(1+g)}{\text{WACC} - g}$$
$$\text{EV} = \sum_{t=1}^{T}\dfrac{\text{FCF}_t}{(1+\text{WACC})^t} + \dfrac{\text{TV}}{(1+\text{WACC})^T}$$

Equity value is EV less net debt, pension provisions, and minority interests, divided by diluted shares. The sheet flags what share of EV comes from the terminal value — a sanity check on how forecast-dependent the result is.

RV — relative value

Peer multiples

A company should trade in line with comparable businesses. The model benchmarks the target against a peer set on EV/Sales, EV/EBITDA, and EV/EBIT, and reads upside as the gap between peer multiples and the target's own.

$$\text{Upside}_{\text{RV}} = \dfrac{\overline{M}_{\text{peers}}}{M_{\text{target}}} - 1$$

Computed on both mean and median across the peer set, for each multiple and year, to limit the pull of any single outlier comp.

SOTP — sum of the parts

Break-up valuation

For a conglomerate, the consolidated multiple hides the value of the pieces. Each operating segment is valued on its own multiple, each minority stake on a DCF or NAV basis, and the parts are summed into a gross enterprise value. Net debt and other liabilities are subtracted, and a holdco discount is applied to reflect the conglomerate structure before dividing by shares outstanding.

$$\text{EV}_{\text{SOTP}} = \sum_{j} \text{EV}_j + \sum_{k} \text{Stake}_k$$
$$\text{Equity} = \big(\text{EV}_{\text{SOTP}} - \text{Net Debt} - \text{Other Liab.}\big)\,(1 - d)$$
$$P_{\text{SOTP}} = \dfrac{\text{Equity}}{\text{Shares}}$$
$\text{EV}_j$ Enterprise value of operating segment j (EV/Sales, EV/EBITDA, or EV/NOPAT)
$\text{Stake}_k$ Value of minority stake k, on a DCF or NAV basis
$d$ Holdco discount

Blended target

Three methods, one weighted price

The three methods feed one weighted price.

$$P^* = \sum_i w_i \, P_i, \quad i \in \{\text{DCF, RV, SOTP}\}, \quad \sum_i w_i = 1$$

Default weighting leans on DCF (75%) with RV and SOTP as cross-checks, but the split is fully configurable.

Data & Automation

Live, then frozen

The P&L is built by hand — historical financials are entered manually, with Bloomberg BDH feeding only the forward estimate columns. Market data for the valuation sheets is where the live feed does the heavy lifting: BDP and BDH array formulas pull security names, prices, market caps, betas, and consensus EV multiples into the DCF and Relative Value sheets.

A set of VBA macros then does two jobs: Reset clears all inputs back to a blank template for the next company, and Save Bloom Data pastes the live feed as static values, so a finished valuation is portable and stops moving when the terminal disconnects.

Manual historicals BDP / BDH on market data VBA reset & freeze macros

Download

Try the model

Workbook (.xlsm)

The full macro-enabled Excel workbook: P&L engine, DCF, Relative Value, SOTP, and the blended Final sheet. Requires Microsoft Excel with macros enabled; a Bloomberg Terminal is needed for live data, but the included worked example (a European media-sector valuation) is saved as static values and opens without one.

Download (.xlsm)

Macro-enabled · example data frozen for offline viewing