20180709213824excess_returns_model_440_6380.xlsx
Post the results of the valuation that you prepared of a financial and banking institution.
What are the results of the valuation analysis? What is the range of estimated value for 1 standard deviation above and below your best estimate?
What special problems were encountered in the valuation? The Results of the valuation is on the excel spreadsheet attached.
2.What are some of the challenges, issues and *remedies* associated with valuing firms with negative, low or otherwise abnormal earnings?
>Sheet 78,000,000.0
Invested From Balance Sheet End of Previous Full 4,000,000.0
(May be Same as Above)
from Previous Year
.12%
(Computer Calculates)
(Computer Calculates)
Outstanding
Net Income $1,396,700,296.2 $1,606,161,899.1 $1,847,036,227.6 $2,124,034,213.4 $2,442,573,281.6 $5,031,745,129.51
EXCESS RETURNS MODEL – VALUING FINANCIAL INSTITUTION 1
(Insert Required Data in Yellow-Coded Cells Only)
$11,
4
Book
Value of Equity
Year
$8,
3
5
Book Value of Equity Invested Currently
$1,919,000,000.0
Annual
Net Income
10.3%
Dividend Payout Ratio
1.31
Beta of Company’s Stock
3.50%
Risk-free Rate
2.00%
Equity (Market) Excess Return Premium
6
Cost of Equity
16.72%
Return on Equity
489164000
Number of Shares
14.30%
Assumed Growth Rate for Net Income After Year 5
49.77%
Assumed Return on Equity After Year 5
6.12%
Assumed Cost of Equity After Year 5
Year 1 2 3 4 5 6
Net Income
$1,396,700,296.2
$1,606,161,899.1
$1,847,036,227.6
$2,124,034,213.4
$2,442,573,281.6
–
Equity Cost
$511,264,800.0
$587,938,618.1
$676,111,124.2
$777,506,763.8
$894,108,595.6
Excess Equity Return
$885,435,496.22
$1,018,223,280.97
$1,170,925,103.34
$1,346,527,449.58
$1,548,464,686.00
Present Value
$834,371,933.9
$904,166,766.4
$979,799,904.9
$1,061,759,721.0
$1,150,575,438.4
Beginning Book Value of Equity
8,354,000,000.0
9,606,840,165.7
11,047,567,389.2
12,704,358,885.3
14,609,617,574.7
Cost of Equity 6.12% 6.12% 6.12% 6.12% 6.12%
Equity Cost $511,264,800.0 $587,938,618.1 $676,111,124.2 $777,506,763.8 $894,108,595.6
Return on Equity 16.72% 16.72% 16.72% 16.72% 16.72%
Dividend Payout Ratio 10.3% 10.3% 10.3% 10.3% 10.3%
Dividends Paid
$143,860,130.5
$165,434,675.6
$190,244,731.4
$218,775,524.0
$251,585,048.0
Retained Earnings
$1,252,840,165.7
$1,440,727,223.5
$1,656,791,496.1
$1,905,258,689.4
$2,190,988,233.6
$2,504,299,551.0
Terminal Value of Excess Returns
$5,031,745,129.5
The PV of Excess Returns
Valuation
Will Equal 0 if the Assumed ROE
Book Value of Equity Invested Currently
$8,354,000,000.0
= or < the Assumed Cost of Equity
Present Value of Excess Return (Next 5 Years)
$4,930,673,764.6
Present Value of Terminal Value of Excess Returns
Value of Equity
$18,316,418,894.0
Number of Shares 489164000
Value per Share
$37.44
1 This model replicates Damodaran’s Excess Returns Model presented in Illustration 21-4
of Damodaran’s Investment Valuation, 3rd, John Wiley & Sons (2012).