B C D E F
2 Inputs for Bond Valuation! Explanations
3 rate: 2.75% 5.5% Reqd return annual (2.75/semi)
4 nper: 30 Number of periods (15 years x 2)
5 pmt: $ 35.00 7% is $70/yr or $35 per semiannual
6 fv: $ 1,000.00 Stated maturity amount of $1000
7 type: 0 Ordinary Annuity
8 Output - Bond Valuation
9 Bond Value today (pv) ($1,151.87) =PV(D3,D4,D5,D6,D7)
This amount is "negative" as we assume the investor
will purchase the bond for that amount today.
B C D E F
2 Inputs for Bond Valuation! Explanations
3 rate: 10.00% 10% Reqd return annual
4 nper: 3,000 Assume a large number of pds
5 pmt: $ 8.00 Dividend is 8% of $100 par value
6 fv: $ - No cash flow as infinite life
7 type: 0 Ordinary Annuity
8 Output - Bond Valuation
9 Bond Value today (pv) ($80.00) =PV(D3,D4,D5,D6,D7)
This amount is "negative" as we assume the investor
will purchase the preferred stock for that amount today.
Normal Calculation: $ 80.00 =D5/D3
B C D E
2 Inputs
3 Dividend at t=0 (D0): $ 3.24
4 Growth in first phase (g1) 16%
5 Length of first phase (n1) 3
6 Growth in second phase (g2) 8%
7 Length of second phase (n2) 6
8 Growth in third phase (g3) 8%
Length of third phase (n3) infinite
9
10 Discount rate for cash flows: 15%
11 Outputs
12 Intrinsic Value (t=0) $61.19
B C D E
14 Step 1: Forecast Dividends
15 Period Forecasted Dividends
16 1 $3.76
17 2 $4.36
18 3 $5.06
19 4 $5.46
20 5 $5.90
21 6 $6.37
22 7 $6.88
23 8 $7.43
24 9 $8.03
25 10 $8.667329
B C D E
27 Step 2: Forecast Cash Flows
28 Period Forecasted Cash Flows
29 1 $3.76
30 2 $4.36
31 3 $5.06
32 4 $5.46
33 5 $5.90
34 6 $6.37
35 7 $6.88
36 8 $7.43
37 9 $131.84
38
39 Step 3: Value using the NPV function! (see above in output)
F
Explanations
Most recent 12-month dividend
Rate at which dividends grow
Length that they grow at rate g1
Rate at which dividends grow
Length that they grow at rate g2
Rate at which dividends grow forever
Will grow at the same rate forever after
phase 2 ends.
Rate investors expect to earn
=NPV(D10,D27:E35)
F
100.0066%
A sequence of 'IF' statements are used to
allow some flexibility in this model. It is
reasonable to assume that you generally
would create an individually calculated
spreadsheet for the specific asset and its
attributes. An example of the 'IF'
statement from cell D24 is:
=IF(C24=1,($D$3*(1+$D$4)^C24),IF(C24
>($D$5+$D$7),(D23*(1+$D$8)),IF(C24>$
D$5,(D23*(1+$D$6)),($D$3*(1+$D$4)^C2
4)))) $123.819
F
= $8.67 + PV of future cash flows in years
10 to infinity
B C D E F
2 Inputs for Bond Valuation! Explanations
3 pv $ (1,250.00) Purchase for $1,250 (negative)
4 nper: 15 Number of periods
5 pmt: $ 100.00 10% x $1,000 face (maturity) value
6 fv: $ 1,000.00 Stated maturity amount of $1000
7 type: 0 Ordinary Annuity
8 Output - Bond Valuation
9 Bond YTM per period 7.22% =RATE(D4,D5,D3,D6,D7)
B C D E F
12 Inputs for Bond Valuation! Explanations
13 pv $ (950.00) Purchase for $950 (negative)
14 nper: 40 Number of periods = 20 yrs x 2
15 pmt: $ 40.00 8%/2 x $1,000 face (maturity) value
16 fv: $ 1,000.00 Stated maturity amount of $1000
17 type: 0 Ordinary Annuity
18 Output - Bond Valuation
19 Bond YTM per period 4.26% =RATE(D4,D5,D3,D6,D7)
20 Bond YTM Annualized 8.71% =(1+D19)^2-1