1. Templates
  2. Valuation Models

Build Valuation Models

Create DCF and comparable company valuation models using Tickerdata.

Overview

Learn how to build professional valuation models in Google Sheets using Tickerdata to pull real financial data automatically.

Valuation Methods

This guide covers two popular valuation approaches:

  1. Discounted Cash Flow (DCF) - Intrinsic value based on future cash flows
  2. Comparable Company Analysis - Relative value based on peer multiples

DCF Valuation Model

Set Up Assumptions

Create an assumptions section with these inputs:

AssumptionCellValue
TickerB1AAPL
Discount RateB210%
Terminal GrowthB32.5%
Projection YearsB45

Pull Historical Data

// Free Cash Flow (TTM)
=TICKERDATA(B1, "Free Cash Flow", "ttm")

// Revenue (TTM)
=TICKERDATA(B1, "Revenue", "ttm")

// Market Cap
=TICKERDATALIVE(B1, "Market Cap")

// Current Price
=TICKERDATALIVE(B1, "Price")

Calculate FCF Growth Rate

Estimate growth based on historical performance:

// 3-Year Revenue Growth
=TICKERDATA(B1, "3 YR Revenue GPS", "ttm")

// 5-Year Revenue Growth
=TICKERDATA(B1, "5 YR Revenue GPS", "ttm")

Warning

Always sanity-check growth assumptions. High historical growth may not be sustainable.

Project Future Cash Flows

Create a table for 5-year projections:

Year12345
FCF=FCF*(1+growth)
PV Factor=1/(1+discount)^year
PV of FCF=FCF*PVfactor

Calculate Terminal Value

// Terminal Value using Gordon Growth Model
=Year5_FCF*(1+terminal_growth)/(discount_rate-terminal_growth)

// Present Value of Terminal Value
=Terminal_Value/(1+discount_rate)^5

Calculate Intrinsic Value

// Enterprise Value = Sum of PV(FCF) + PV(Terminal Value)
=SUM(PV_of_FCF_range) + PV_Terminal_Value

// Get Net Debt
// Net Debt = Total Debt - Cash
=TICKERDATA(B1, "Total Debt", 2023) - TICKERDATA(B1, "Cash And Cash Equivalents", 2023)

// Equity Value = Enterprise Value - Net Debt
=Enterprise_Value - Net_Debt

// Intrinsic Value per Share
=Equity_Value / Shares_Outstanding

// Compare to Current Price
=TICKERDATALIVE(B1, "Price")

// Upside/Downside
=(Intrinsic_Value - Current_Price) / Current_Price

Quick DCF Check

Tickerdata provides a built-in DCF calculation you can use as a reference:

// DCF Value (pre-calculated intrinsic value)
=TICKERDATALIVE(B1, "DCF")

// Difference from current price
=TICKERDATALIVE(B1, "DCF Diff")

Info

The built-in DCF is a quick reference. For detailed analysis, build your own model with custom assumptions.

Comparable Company Analysis

Create a peer comparison table:

CompanyTicker
TargetAAPL
Peer 1MSFT
Peer 2GOOGL
Peer 3META
Peer 4AMZN

Sensitivity Analysis

Create a data table showing how valuation changes with different assumptions:

  1. Set up discount rates across the top (8%, 9%, 10%, 11%, 12%)
  2. Set up growth rates down the side (1%, 2%, 3%, 4%, 5%)
  3. Use Data > Data table to calculate intrinsic value for each combination

Tip

Sensitivity tables help you understand the range of possible fair values and identify which assumptions matter most.

Best Practices

  1. Use conservative assumptions - Better to be surprised on the upside
  2. Cross-check methods - DCF and comps should give similar ballpark values
  3. Document your assumptions - Future you will thank you
  4. Update regularly - Financial data changes quarterly

Next Steps