| | | | | EXCESS RETURNS MODEL - VALUING FINANCIAL INSTITUTION 1 |
| | | | | (Insert Required Data in Yellow-Coded Cells Only) |
| | | $71,674.0 | Book Value of Equity Invested From Balance Sheet End of Previous Full Year |
| | | $78,228.0 | Book Value of Equity Invested Currently (May be Same as Above) |
| | | $8,354.0 | Annual Net Income from Previous Year |
| | | 10.0% | Dividend Payout Ratio |
| | | 1.20 | Beta of Company's Stock |
| | | 3.50% | Risk-free Rate |
| | | 5.00% | Equity (Market) Excess Return Premium |
| | | 9.50% | Cost of Equity (Computer Calculates) |
| | | 11.66% | Return on Equity (Computer Calculates) |
| | | 517.735 | Number of Shares Outstanding |
| | | 3.00% | Assumed Growth Rate for Net Income After Year 5 |
| | | 9.50% | Assumed Return on Equity After Year 5 |
| | | 9.50% | Assumed Cost of Equity After Year 5 |
| | | | Year | 1 | 2 | 3 | 4 | 5 | 6 |
| | | | Net Income | $9,117.9 | $10,074.4 | $11,131.2 | $12,298.8 | $13,589.0 |
| | | | - Equity Cost | $7,431.7 | $8,211.2 | $9,072.6 | $10,024.3 | $11,075.9 |
| | | | Excess Equity Return | $1,686.24 | $1,863.13 | $2,058.57 | $2,274.52 | $2,513.12 |
| | | | Present Value | $1,539.9 | $1,553.9 | $1,567.9 | $1,582.1 | $1,596.4 |
| | | | Beginning Book Value of Equity | 78,228.0 | 86,434.1 | 95,501.0 | 105,519.1 | 116,588.1 |
| | | | Cost of Equity | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% |
| | | | Equity Cost | $7,431.7 | $8,211.2 | $9,072.6 | $10,024.3 | $11,075.9 |
| | | | Return on Equity | 11.66% | 11.66% | 11.66% | 11.66% | 11.66% |
| | | | Net Income | $9,117.9 | $10,074.4 | $11,131.2 | $12,298.8 | $13,589.0 |
| | | | Dividend Payout Ratio | 10.0% | 10.0% | 10.0% | 10.0% | 10.0% |
| | | | Dividends Paid | $911.8 | $1,007.4 | $1,113.1 | $1,229.9 | $1,358.9 |
| | | | Retained Earnings | $8,206.1 | $9,066.9 | $10,018.1 | $11,069.0 | $12,230.1 | $12,597.0 |
| | | | Terminal Value of Excess Returns | | | | | $0.0 |
| | | | | | | | | The PV of Excess Returns |
| | | | | Valuation | | | | Will Equal 0 if the Assumed ROE |
| | | | | Book Value of Equity Invested Currently | $78,228.0 | | | = or < the Assumed Cost of Equity |
| | | | | Present Value of Excess Return (Next 5 Years) | $7,840.2 |
| | | | | Present Value of Terminal Value of Excess Returns | $0.0 |
| | | | | Value of Equity | $86,068.2 |
| | | | | Number of Shares | 517.735 |
| | | | | Value per Share | $166.24 |
| 1 This model replicates Damodaran's Excess Returns Model presented in Illustration 21-4 |
| of Damodaran's Investment Valuation, 3rd, John Wiley & Sons (2012). |