Cambridge Bancorp (CATC) Simple Excess Return Model

Cambridge Bancorp

CATC (NASDAQ)

(%)

Valuation
Cambridge Bancorp Simple Excess Return Model

"""
    Model: Simple Excess Return Model

# Render model description
model.render_description(r"""
## Simple Excess Return Model

Used to estimate the value of companies that have reached maturity and earn stable excess returns with little to no high growth chance. Excess Return models are better suited to calculate the intrinsic value of a financial company than an enterprise valuation model (such as the Discounted Free Cash Flow Model).

See our [GitHub Documentation](https://github.com/DiscountingCashFlows/Documentation/blob/main/models-documentation/excess-return-models.md#simple-excess-return-model-source-code)
""")

# Initialize assumptions
assumptions.init({
    "data": {
        "%discount_rate": None,
        "%return_on_equity": None,
        "%growth_in_perpetuity": "2.5%",
        "%risk_free_rate": data.get("treasury:year10"),
        "%market_premium":data.get("risk:totalEquityRiskPremium"),
        "beta": data.get("profile:beta", default=1),
        "historical_years": 10,
    },
    "hierarchies": [{
        "parent": "%discount_rate",
        "children": ["beta", "%risk_free_rate", "%market_premium"]
    }]
})

# Set the discount rate to the cost of equity
risk_free_rate = assumptions.get("%risk_free_rate")
beta = assumptions.get("beta")
market_premium = assumptions.get("%market_premium")
cost_of_equity = risk_free_rate + beta * market_premium

assumptions.set("%discount_rate", cost_of_equity)

# Compute historical data
data.compute({
    "dividendsPaidToCommon": "dividend:adjDividend * income:weightedAverageShsOut",
    "#bookValue": "balance:totalStockholdersEquity / income:weightedAverageShsOut",
    "%returnOnEquity": "income:netIncome / balance:totalStockholdersEquity:-1",
    "%payoutRatio": "dividend:adjDividend / income:eps",
    "#retainedEarnings": "income:eps - dividend:adjDividend",
})

# Calculate the average return on equity over the historical years
average_return_on_equity = data.average(f"%returnOnEquity:{-assumptions.get('historical_years')}->0")

assumptions.set("%return_on_equity", average_return_on_equity)
assumptions.set_bounds(
    "%return_on_equity", low="0%", high="100%"
)

# Calculate stable payout ratio
stable_payout_ratio = 1 - (assumptions.get("%growth_in_perpetuity") / assumptions.get("%return_on_equity"))

# Compute 5 years of forecasted values and ratios
data.compute({
    "income:eps": f"#bookValue:-1 * {assumptions.get('%return_on_equity')}",
    "dividend:adjDividend": f"income:eps * {stable_payout_ratio}",
    "#retainedEarnings": "income:eps - dividend:adjDividend",
    "#bookValue": "#bookValue:-1 + #retainedEarnings",
    "%returnOnEquity": "income:eps / #bookValue:-1",
    "#equityCostPerShare": f"#bookValue:-1 * {assumptions.get('%discount_rate')}",
    "#excessReturn": "income:eps - #equityCostPerShare",
    "%costOfEquity": assumptions.get("%discount_rate"),
    "#beginningBookValue": "#bookValue:-1",
}, forecast=5)

book_value = data.get("#bookValue")
excess_returns = data.get("#excessReturn:1")
if excess_returns and excess_returns <= 0:
    model.warn("Warning: Excess return is negative. Either EPS is negative or the Cost of Equity (Discount Rate) is higher than the Return on Equity.")

# Calculate the terminal value
terminal_value = excess_returns / (assumptions.get("%discount_rate") - assumptions.get("%growth_in_perpetuity"))

# Calculate value per share
stock_value = terminal_value + book_value

model.set_final_value({
    "value": stock_value,
    "units": '$'
})

model.render_results([
    [stock_value, 'Estimated Value', '$'],
    [book_value, "Book value of equity invested", '$'],
    [data.get('#bookValue:1'), "Next year's estimated book value", '$'],
    [terminal_value, "Present value of future excess returns", '$'],
    [excess_returns, "Excess Return per share", '$'],
    [assumptions.get("%discount_rate"), "Cost of Equity (the discount rate)", '%'],
    [average_return_on_equity, "Average historical Return on Equity", '%'],
    [data.average(f"%payoutRatio:{-assumptions.get('historical_years')}->0"), "Average historical Payout Ratio", '%'],
    [stable_payout_ratio, "Payout Ratio used", '%'],
    [data.get("treasury:year10"), 'Risk Free Rate of the 10 Year U.S. Treasury Note', '%']
])

# Render a chart for historical and forecasted data
model.render_chart({
    "data": {
        "income:eps": "Earnings Per Share",
        "#bookValue": "Book Value Per Share",
        "dividend:adjDividend": "Dividend",
        "#retainedEarnings": "Retained Earnings"
    },
    "start": -assumptions.get("historical_years"),
    "end": "*",
    "properties": {
        "title": "Historical and Forecasted Data",
        "hidden_keys": ["#retainedEarnings"]
    }
})

# Projections Table
model.render_table({
    "data": {
        "#beginningBookValue": "Beginning Book Value",
        "#bookValue": "Ending Book Value",
        "income:eps": "EPS",
        "%returnOnEquity": "Return on Equity",
        "dividend:adjDividend": "Dividend per Share",
        "#retainedEarnings": "Retained Earnings",
        "#equityCostPerShare": "Equity Cost per Share",
        "%costOfEquity": "Cost of Equity",
        "#excessReturn": "Excess Return"
    },
    "start": -1,
    "end": "*",
    "properties": {
        "title": "Projected Data",
        "order": "ascending",
    }
})

# Historical Table
model.render_table({
    "data": {
        "income:netIncome": "Net Income",
        "balance:totalStockholdersEquity": "Total Equity",
        "%returnOnEquity": "Return on Equity",
        "dividendsPaidToCommon": "Dividends Paid to Common Shareholders",
        "%payoutRatio": "Payout Ratio",
        "income:weightedAverageShsOut": "Shares Outstanding",
        "income:eps": "EPS",
        "dividend:adjDividend": "Dividend per Share",
        "#bookValue": "Book Value"
    },
    "start": -assumptions.get('historical_years'),
    "end": 0,
    "properties": {
        "title": "Historical data",
        "number_format": "M",
        "display_averages": True
    }
})

assumptions.set_description({
    "%discount_rate": r"""
        ## Discount Rate

`Discount Rate` = `Cost of Equity` = `Risk Free Rate` + `Beta` * `Market Premium`

The cost of equity is the theoretical rate of return that an equity investment should generate. It is calculated using the CAPM formula.

[Read More](https://www.investopedia.com/terms/c/costofequity.asp#mntl-sc-block_1-0-20)
    """,

"historical_years": r"""
        ## Historical Years

The number of historical years used to calculate averages for historical data.
    """,

"%risk_free_rate": r"""
        ## Risk-Free Rate

The risk-free rate represents the interest an investor would expect from an absolutely risk-free investment over a specified period of time.
        By default, it is equal to the current yield of the U.S. 10 Year Treasury Bond.
    """,

"%market_premium": r"""
        ## Market Premium

Market risk premium represents the excess returns over the risk-free rate that investors expect for taking on the incremental risks connected to the equities market.
    """,

"beta": r"""
        ## Beta

Beta is a value that measures the price fluctuations (volatility) of a stock with respect to fluctuations in the overall stock market.
    """,

"%growth_in_perpetuity": r"""
        ## Growth in Perpetuity

The rate at which the company's free cash flow is assumed to grow in perpetuity. By default, this is equal to the yield of the U.S. 10 Year Treasury Bond.
    """,

"%return_on_equity": r"""
        ## Return on Equity

Return On Equity (ROE) is used to estimate the Earnings Per Share (EPS) expressed as a percentage of Book Value.
    """
})

print(f"End of model.")

Estimated Value

USD

Market Price USD

* Values are not guaranteed, please do your own
research before making investment decisions.

Simple Excess Return Model

See our GitHub Documentation

Interactive Assumptions

Discount Rate Discount Rate Discount Rate `Discount Rate` = `Cost of Equity` = `Risk Free Rate` + `Beta` * `Market Premium` The cost of equity is the theoretical rate of return that an equity investment should generate. It is calculated using the CAPM formula. Read More	%
↳ Beta Beta Beta Beta is a value that measures the price fluctuations (volatility) of a stock with respect to fluctuations in the overall stock market.	#
↳ Risk Free Rate Risk Free Rate Risk-Free Rate The risk-free rate represents the interest an investor would expect from an absolutely risk-free investment over a specified period of time. By default, it is equal to the current yield of the U.S. 10 Year Treasury Bond.	%
↳ Market Premium Market Premium Market Premium Market risk premium represents the excess returns over the risk-free rate that investors expect for taking on the incremental risks connected to the equities market.	%
Return On Equity Return On Equity Return on Equity Return On Equity (ROE) is used to estimate the Earnings Per Share (EPS) expressed as a percentage of Book Value.	%
Growth In Perpetuity Growth In Perpetuity Growth in Perpetuity The rate at which the company's free cash flow is assumed to grow in perpetuity. By default, this is equal to the yield of the U.S. 10 Year Treasury Bond.	%
Historical Years Historical Years Historical Years The number of historical years used to calculate averages for historical data.	#

Results
Estimated Value	143.9 USD
Book value of equity invested	68.36 USD
Next year's estimated book value	69.99 USD
Present value of future excess returns	75.52 USD
Excess Return per share	3.39 USD
Cost of Equity (the discount rate)	6.99%
Average historical Return on Equity	11.96%
Average historical Payout Ratio	45.29%
Payout Ratio used	79.1%
Risk Free Rate of the 10 Year U.S. Treasury Note	4.12%

Historical and Forecasted Data

Monetary values in USD

{"sectionId": "section-chart-1", "sectionType": "chart", "title": "Historical and Forecasted Data", "timeseries": "annual", "series": [{"label": "Earnings Per Share", "mappingKey": "income:eps", "data": [{"l": "2014", "x": "2014-12-31", "y": 3.81}, {"l": "2015", "x": "2015-12-31", "y": 3.94}, {"l": "2016", "x": "2016-12-31", "y": 4.19}, {"l": "2017", "x": "2017-12-31", "y": 3.64}, {"l": "2018", "x": "2018-12-31", "y": 5.88}, {"l": "2019", "x": "2019-12-31", "y": 5.41}, {"l": "2020", "x": "2020-12-31", "y": 5.07}, {"l": "2021", "x": "2021-12-31", "y": 7.76}, {"l": "2022", "x": "2022-12-31", "y": 7.35}, {"l": "2023", "x": "2023-12-31", "y": 4.36}, {"l": "2024", "x": "2024-12-31", "y": 8.17}, {"l": "2025", "x": "2025-12-31", "y": 8.37}, {"l": "2026", "x": "2026-12-31", "y": 8.58}, {"l": "2027", "x": "2027-12-31", "y": 8.8}, {"l": "2028", "x": "2028-12-31", "y": 9.01}], "dataType": "#", "displayAs": "line", "dataTimeseries": "annual", "hidden": false}, {"label": "Book Value Per Share", "mappingKey": "#bookValue", "data": [{"l": "2014", "x": "2014-12-31", "y": 29.91}, {"l": "2015", "x": "2015-12-31", "y": 31.76}, {"l": "2016", "x": "2016-12-31", "y": 33.75}, {"l": "2017", "x": "2017-12-31", "y": 36.71}, {"l": "2018", "x": "2018-12-31", "y": 41.12}, {"l": "2019", "x": "2019-12-31", "y": 61.9}, {"l": "2020", "x": "2020-12-31", "y": 63.87}, {"l": "2021", "x": "2021-12-31", "y": 63.21}, {"l": "2022", "x": "2022-12-31", "y": 72.25}, {"l": "2023", "x": "2023-12-31", "y": 68.29}, {"l": "2024", "x": "2024-12-31", "y": 69.99}, {"l": "2025", "x": "2025-12-31", "y": 71.74}, {"l": "2026", "x": "2026-12-31", "y": 73.54}, {"l": "2027", "x": "2027-12-31", "y": 75.38}, {"l": "2028", "x": "2028-12-31", "y": 77.26}], "dataType": "#", "displayAs": "line", "dataTimeseries": "annual", "hidden": false}, {"label": "Dividend", "mappingKey": "dividend:adjDividend", "data": [{"l": "2014", "x": "2014-12-31", "y": 1.68}, {"l": "2015", "x": "2015-12-31", "y": 1.8}, {"l": "2016", "x": "2016-12-31", "y": 1.84}, {"l": "2017", "x": "2017-12-31", "y": 1.86}, {"l": "2018", "x": "2018-12-31", "y": 1.96}, {"l": "2019", "x": "2019-12-31", "y": 2.04}, {"l": "2020", "x": "2020-12-31", "y": 2.12}, {"l": "2021", "x": "2021-12-31", "y": 2.38}, {"l": "2022", "x": "2022-12-31", "y": 2.56}, {"l": "2023", "x": "2023-12-31", "y": 2.68}, {"l": "2024", "x": "2024-12-31", "y": 6.46}, {"l": "2025", "x": "2025-12-31", "y": 6.62}, {"l": "2026", "x": "2026-12-31", "y": 6.79}, {"l": "2027", "x": "2027-12-31", "y": 6.96}, {"l": "2028", "x": "2028-12-31", "y": 7.13}], "dataType": "#", "displayAs": "line", "dataTimeseries": "annual", "hidden": false}, {"label": "Retained Earnings", "mappingKey": "#retainedEarnings", "data": [{"l": "2014", "x": "2014-12-31", "y": 2.13}, {"l": "2015", "x": "2015-12-31", "y": 2.14}, {"l": "2016", "x": "2016-12-31", "y": 2.35}, {"l": "2017", "x": "2017-12-31", "y": 1.78}, {"l": "2018", "x": "2018-12-31", "y": 3.92}, {"l": "2019", "x": "2019-12-31", "y": 3.37}, {"l": "2020", "x": "2020-12-31", "y": 2.95}, {"l": "2021", "x": "2021-12-31", "y": 5.38}, {"l": "2022", "x": "2022-12-31", "y": 4.79}, {"l": "2023", "x": "2023-12-31", "y": 1.68}, {"l": "2024", "x": "2024-12-31", "y": 1.71}, {"l": "2025", "x": "2025-12-31", "y": 1.75}, {"l": "2026", "x": "2026-12-31", "y": 1.79}, {"l": "2027", "x": "2027-12-31", "y": 1.84}, {"l": "2028", "x": "2028-12-31", "y": 1.88}], "dataType": "#", "displayAs": "line", "dataTimeseries": "annual", "hidden": true}], "editableDates": {}, "properties": {"display_table": false}, "width": "full"}

Projected Data

Monetary values in USD amounts except #	2023 Dec 31	LTM Mar 10	2024 Dec 31	2025 Dec 31	2026 Dec 31	2027 Dec 31	2028 Dec 31
Beginning Book Value	72.25	68.29	68.29	69.99	71.74	73.54	75.38
Ending Book Value	68.29	68.36	69.99	71.74	73.54	75.38	77.26
EPS	4.36	3.64	8.17	8.37	8.58	8.8	9.01
Return on Equity	6.59%	5.35%	11.96%	11.96%	11.96%	11.96%	11.96%
Dividend per Share	2.68	2.68	6.46	6.62	6.79	6.96	7.13
Retained Earnings	1.68	0.96	1.71	1.75	1.79	1.84	1.88
Equity Cost per Share	5.05	4.77	4.77	4.89	5.02	5.14	5.27
Cost of Equity	6.99%	6.99%	6.99%	6.99%	6.99%	6.99%	6.99%
Excess Return	-0.692	-1.13	3.39	3.48	3.56	3.65	3.74

Historical data

Monetary values in USD amounts except #	Average	LTM Mar 10	2023 Dec 31	2022 Dec 31	2021 Dec 31	2020 Dec 31	2019 Dec 31	2018 Dec 31	2017 Dec 31	2016 Dec 31	2015 Dec 31	2014 Dec 31
Net Income	28.46	28.58	34.11	52.91	54.02	31.96	25.26	23.88	14.82	16.9	15.69	14.94
Total Equity	309.5	535.6	534.6	517.6	437.8	401.7	286.6	167	148	134.7	125.1	116.3
Return on Equity	11.96%	5.35%	6.59%	12.08%	13.45%	11.15%	15.12%	16.14%	11%	13.51%	13.5%	13.67%
Dividends Paid to Common Shareholders	12.36	21	20.98	18.34	16.48	13.33	9.44	7.96	7.5	7.34	7.09	6.53
Payout Ratio	45.29%	73.63%	61.47%	34.83%	30.67%	41.81%	37.71%	33.33%	51.1%	43.91%	45.69%	44.09%
Shares Outstanding	5.51	7.84	7.83	7.16	6.93	6.29	4.63	4.06	4.03	3.99	3.94	3.89
EPS	5.0	3.64	4.36	7.35	7.76	5.07	5.41	5.88	3.64	4.19	3.94	3.81
Dividend per Share	2.15	2.68	2.68	2.56	2.38	2.12	2.04	1.96	1.86	1.84	1.8	1.68
Book Value	51.92	68.36	68.29	72.25	63.21	63.87	61.9	41.12	36.71	33.75	31.76	29.91

Estimated Value

USD

Market Price USD

Simple Excess Return Model

Interactive Assumptions

Discount Rate

Discount Rate

Beta

Beta

Risk Free Rate

Risk-Free Rate

Market Premium

Market Premium

Return On Equity

Return on Equity

Growth In Perpetuity

Growth in Perpetuity

Historical Years

Historical Years

Results

Historical and Forecasted Data

Projected Data

Historical data

Discounted Cash Flow to Firm - Perpetual Growth

Earnings Power Value

Simple Dividend Discount Model

Simple Excess Return Model

Two-Stage Dividend Discount Model

Two-Stage Excess Return Model

Discounted Cash Flow to Firm - Exit Multiple

Discounted Future Market Cap

Peter Lynch Fair Value

Altman Z-Score

Beneish M-Score

Capital Asset Pricing Model (CAPM)

Margin Analysis

Piotroski F-Score

Return on Invested Capital (ROIC)

Shiller PE (CAPE) Ratio

Weighted Average Cost of Capital (WACC)

Market is Open

After-Hours Quote

Market is Open

After-Hours Quote

Cambridge Bancorp Simple Excess Return Model

Estimated Value

USD

Market Price USD

Simple Excess Return Model

Interactive Assumptions

Discount Rate

Discount Rate

Beta

Beta

Risk Free Rate

Risk-Free Rate

Market Premium

Market Premium

Return On Equity

Return on Equity

Growth In Perpetuity

Growth in Perpetuity

Historical Years

Historical Years

Results

Historical and Forecasted Data

Projected Data

Historical data

Discounted Cash Flow to Firm - Perpetual Growth

Earnings Power Value

Simple Dividend Discount Model

Simple Excess Return Model

Two-Stage Dividend Discount Model

Two-Stage Excess Return Model

Discounted Cash Flow to Firm - Exit Multiple

Discounted Future Market Cap

Peter Lynch Fair Value

Altman Z-Score

Beneish M-Score

Capital Asset Pricing Model (CAPM)

Margin Analysis

Piotroski F-Score

Return on Invested Capital (ROIC)

Shiller PE (CAPE) Ratio

Weighted Average Cost of Capital (WACC)