1. IRR method

(1) Define the term internal rate of return(IRR). What is each project’s IRR?

(2) How is the IRR on a project related to the YTM on a bond?

(3)According to IRR, which project(s) should be accepted if they are independent? Mutually exclusive?

2. Suppose the firm estimates its WACC to be 10%. 

(1) Should the WACC be used to evaluate all of its potential projects, even if they vary in risk? Explain.

(2) Would the NPVs change if the WACC changed? Explain.

asapfollow directions

Chapter

1

0. Cost of

Capital

1

What sources of capital do firms use?

© 2020 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Capital

Debt

Preferred Stock

Common Equity

Retained Earnings

New Common Stock

Short-Term Debt

Long-Term Debt

Calculating the Weighted Average Cost of Capital

WACC = wdrd(1 – T) + wprp + wcrs

The w’s refer to the firm’s capital structure weights:

wd=weight of the debt

wp=weight of the preferred stock

wc=weight of the common equity

The r’s refer to the cost of each component:

rd=before-tax cost of the debt

rp =cost of the preferred stock

rs =cost of the common equity

3

Before-tax or after-tax capital costs?
Stockholders focus on after-tax CFs. Therefore, we should focus on after-tax capital costs; i.e., use after-tax costs of capital in WACC. Only rd needs adjustment, because interest is tax deductible.
After-tax cost of debt=Interest rate on debt – Tax saving

© 2020 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Cost of Debt
WACC = wdrd(1 – T) + wprp + wcrs
rd is the marginal cost of debt capital.
The yield to maturity on outstanding long-term debt is often used as a measure of rd.

5

Cost of Preferred Stock
WACC = wdrd(1 – T) + wprp + wcrs
rp is the marginal cost of preferred stock, which is the return investors require on a firm’s preferred stock.

D is the preferred dividend.
P is the current price of the preferred stock.
Preferred dividends are not tax-deductible, so no tax adjustments necessary.

6

The Cost of Common Equity
Cost of equity is more challenging to estimate than the cost of debt or the cost of preferred stock because common stockholder’s rate of return is not fixed as there is no stated coupon rate or dividend.
The costs will vary for two sources of equity:
Retained earnings (No flotation costs)
Note retained earnings are not a free source of capital. There is an opportunity cost.
rs=cost of the retained earning
New issue (incurs flotation costs)
re=cost of new common stock

Cost estimation techniques
Three Ways to Determine the Cost of Common Equity, rs
The Dividend Growth Model: rs = (D1/P0) + g
The Capital Asset Pricing Model: rs = rf + β(rM – rf )
Bond-Yield-Plus-Risk-Premium:
rs = Bond yield + risk premium= rd + RP

The Dividend Growth Model
Investors’ required rate of return (For Retained Earnings):

D1 = Dividend expected one year hence
P0 = Current price of common stock
g = growth rate

The Dividend Growth Model
Investors’ required rate of return (For new issues)

D1 = Dividends expected one year hence
NP = Net proceeds per share
= Stock price – flotation cost per share
F=the percentage flotation cost required to sell the new stock
g = growth rate

The Dividend Growth Model
Example: A company expects dividends this year to be $1.10, based upon the fact that $1 were paid last year. The firm expects dividends to grow 10% next year and into the foreseeable future. Stock is trading at $35 a share.

Cost of retained earnings:

Cost of new stock (with a $3 floatation cost per share):

The Capital Asset Pricing Model
CAPM: rs = rf + β(rM – rf )
rf = Risk Free rate
 = stock Beta
rm =market risk return
rm – rf = Market Risk Premium

Capital Asset Pricing Model
Example: If beta is 1.25, risk-free rate is 1.5% and expected return on market is 10%
rs = rf +  (rm – rf)
= .015 + 1.25(.10 – .015)
= 12.125%

Capital Asset Pricing Model Variable estimates
CAPM is easy to apply. Also, the estimates for model variables are generally available from public sources.
Risk Free Rate: Wide range of US government securities on which to base risk-free rate
Beta: Estimates of beta are available from a wide range of services, or can be estimated using regression analysis of historical data.
Market risk premium: It can be estimated by looking at history of stock returns and premium earned over risk-free rate.

Bond-Yield-Plus-Risk-Premium Approach
rd = 10% and RP = 4%.
This RP(risk premium) is not the same as the CAPM RPM.
This method produces a ballpark estimate of rs, and can serve as a useful check.
rs = rd + RP
rs = 10.0% + 4.0% = 14.0%

15

What factors influence a company’s composite WACC?
Market conditions.
The firm’s capital structure and dividend policy.
The firm’s investment policy. Firms with riskier projects generally have a higher WACC.

16

Should the company use the composite WACC as the hurdle rate for each of its projects?
NO! The composite WACC reflects the risk of an average project undertaken by the firm. Therefore, the WACC only represents the “hurdle rate” for a typical project with average risk.
Different projects have different risks. The project’s WACC should be adjusted to reflect the project’s risk.

17

Divisional Cost of Capital

p
r
D
=

P
P
D
r
p
=

Chapter

1

1. Capital Budgeting

1

Capital budgeting

deals with investment decisions where

Time is an important element of the decision

Cash flows of investment can be measured

But, there may be some uncertainties

Classification of decisions

Accept or reject

Choose best of a set (mutually exclusive)

Ranking (projects are independent and cash is limited)

Capital budgeting

Independent vs. mutually exclusive projects
Independent projects:
if the cash flows of one are unaffected by the acceptance of the other.
Multiple projects can be chosen.
Mutually exclusive projects:
if the cash flows of one can be adversely impacted by the acceptance of the other.
Only ONE of several potential projects can be chosen

3

Financial managers should accept a project when its perceived benefits exceed perceived costs. In general, value is created when benefits exceed costs.
NPV = Total PV of future CFs – Initial Investment
When firms accept all positive Net Present Value investments, they maximize the value of their shareholders.
Net Present Value (NPV)

Net Present Value (NPV)
Sum of the PVs of all cash inflows and outflows of a project:

Estimating NPV:
1. Estimate future cash flows: how much? and when?
2. Estimate discount rate
3. Estimate initial costs
Reinvestment assumption
Assumes all cash flows are reinvested at discount rate
Rule
Accept if NPV > 0
Reject if NPV < 0. Ranking Criteria Choose the highest NPV What is Project S’ NPV? WACC = 10% Year CFt PV of CFt 0 - 100 - $100.00 1 70 63.64 2 50 41.32 3 20 15.02 NPVS = $ 19.98 Excel: =NPV(rate,CF1:CFn) + CF0 Here, CF0 is negative, rate is discount rate or WACC. 6 Rationale for the NPV Method NPV = PV of inflows – Cost = Net gain in wealth If projects are independent, accept if NPV > 0.
If projects are mutually exclusive, accept projects with the highest positive NPV, those that add the most value.

7

IRR is the discount rate that forces PV of inflows equal to cost, and the NPV = 0:

Reinvestment assumption: All future cash flows assumed reinvested at the IRR
Solving for IRR with a financial calculator:
Enter CFs in Cash Flow register.
Press IRR.
Solving for IRR with Excel:
=IRR(CF0:CFn)
Internal Rate of Return (IRR)

8

How is a project’s IRR similar to a bond’s YTM?
They are the same thing.
Think of a bond as a project. The YTM on the bond would be the IRR of the “bond” project.
EXAMPLE: Suppose a 10-year bond with a 9% annual coupon and $1,000 par value sells for $1,134.20.
Solve for IRR = YTM = 7.08%, the annual return for this project/bond.

9

Rules for the IRR Method
For independent projects:
Take all projects with IRR>r*
r*=the opportunity cost of capital or required rate of return
For mutually exclusive projects:
Take the project with the highest IRR, if IRR>r*

10

What is the IRR of the following project?

The IRR does not always exist!

Potential problems with IRR
Year 0 1 2
Project A 100 -200 150

Lending or Borrowing?

Potential problems with IRR

12

Potential problems with IRR
The following cash flow generates NPV=$ 3.3 million at 10%. It has IRRs of (-44%) and +11.6%.

Cash Flows (millions of Australian dollars)

13
When the sign of the cash flows changes more than once, you get multiple rates of return

The IRR does not always unique!

Potential problems with IRR

600
NPV
300
0
-30
-600
Discount Rate
IRR=11.6%
IRR=-44%

14

For cash flows that alternate in sign (i.e. negative, positive, negative), it is not clear whether you are a net borrower or a net lender. Thus, it is not clear whether you would prefer a high or low IRR.
If cash flows have the traditional pattern (one or several negative cash flows followed by only positive cash flows), then the NPV is positive whenever the IRR is greater than the opportunity cost of capital – Thus, the IRR rule usually works.
Potential problems with IRR

Flows Number of IRRs IRR criterion NPV criterion
First cash flow is (-) and all remaining cash flows are (+) 1 Accept if IRR>R
Reject if IRR0
Reject if NPV<0 First cash flows is (+) and all remaining cash flows are (-) 1 Accept if IRRR
Accept if NPV>0
Reject if NPV<0 Some cash flows after first are (+) and some cash flows after first are (-) Maybe more than 1 No valid IRR Accept if NPV>0
Reject if NPV<0 General rules Potential problems with IRR: scale issue Mutually Exclusive Projects Mutually exclusive Only ONE of several potential projects can be chosen Independent: Accepting/rejecting one project does not affect the decision of the other projects Scale issues IRR sometimes ignores the magnitude of the project. 17 Potential problems with IRR: scale issue In this case, can IRR be salvaged? Look at smaller project Acceptable? Yes. So, should you invest extra $$$ for larger project. Look at incremental CFs: INCREMENTAL IRR Now, which project is better? 18 Timing Issues Preferred project depends on the discount rate, not the IRR (mutually exclusive projects) Potential problems with IRR: timing issue 0 1 2 3 $10,000 $1,000 $1,000 -$10,000 Project A 0 1 2 3 $1,000 $1,000 $12,000 -$10,000 Project B 19 Potential problems with IRR: timing issue 20 20 Potential problems with IRR: timing issue 10.55% = IRR To find crossover rate: Find INCREMENTAL IRR! 21 The number of years required to recover a project’s cost, or “How long does it take to get our money back?” Calculated by adding project’s cash inflows to its cost until the cumulative cash flow for the project turns positive. Payback period The payback period is the number of years, t*, such that For independent projects Accept all projects for which t*Chapter

1

2. Cash Flow Estimation

1

Net Present Value analysis, IRR, and PI methods provide very sophisticated measures of shareholder value generated by potential capital investments.

If cash flow estimates are bad, any analytical technique for assessing project value will lead to poor investment decisions.

Capital Budgeting and Cash Flows

2

Capital budgeting concerned with cash flows, not accounting profit.
To evaluate a capital investment, we must know:
Incremental cash outflows of the investment (marginal cost of investment), and
Incremental cash inflows of the investment (marginal benefit of investment)
The timing and magnitude of cash flows and accounting profits can differ dramatically.
Cash Flows Versus Accounting Profit

3

Financing costs should be excluded when evaluating a project’s cash flow.
Ask the following: Is the project’s existence dependent on financing?
NO!!!– you must separate financing and investment decisions
Both interest expense from debt financing and dividend payments to equity investors should be excluded.
Financing costs are captured in the discounting future cash flows to present.
Financing Costs

4

Land, Buildings, Equipment, etc.
Asset purchases represent negative cash flows (Accounts don’t show purchase as a deduction from earnings-use depreciation instead).
Shipping and installation costs should be included in the purchase price.
Full initial cost becomes the depreciable basis.
Costs of fixed assets

5

Accountants subtract depreciation from revenues to obtain net income.
Depreciation shelters income from tax – which impacts cash flows – so it is relevant.
Depreciation must be added to net income when estimating the project’s cash flow.
Non-cash Charges

6

Working capital= Current assets – current liabilities
Usually inventory, accounts receivable, and accounts payable
Usually, working capital = Inv + AR – AP
New investment projects typically increase net working capital: cash outflow.
Most of the increase comes from additional inventories and receivables.
Typically an outflow at the beginning of a project and an inflow at the end.
Changes in Net Working capital

7

Relevant
Cash flows (not accounting earnings)
Depreciation
Incremental cash flows
Opportunity costs: The return on the best alternative use of an asset. Must be included. (Example: land previously owned.)
Side effects (externalities): Effects of a project on cash flows in other parts of the firm. (E.g. cannibalism or synergy)
Taxes
Irrelevant
Sunk costs: Outlays that have already occurred.
Cash Flows

8
Opportunity Costs: Currently own land or have to purchase land
Sunk costs: Bought a cell phone, now want new I-Phone…should original price of phone be included?

(1) Cash Flows from Operations
Operating Cash Flow = EBIT – Taxes + Depreciation
Other Methods for Computing OCF
Bottom-Up Approach
Works only when there is no interest expense
OCF = NI + depreciation
Top-Down Approach
OCF = Sales – Costs – Taxes
Don’t subtract non-cash deductions
Tax Shield Approach
OCF = (Sales – Costs)(1 – tc) + Depreciation* tc
Estimating Cash Flows

9
Capital Spending: Changes to fixed assets

(2) Cash Flows from Investments
Part I: Net Capital Spending
Usually up front and at the end
Remember salvage value (after tax)
Part II: Changes in Net Working Capital
Part III: Opportunity Costs
(3) Cash Flows from Side Effects
cannibalism or synergy
Estimating Cash Flows

10

Speedo has decided to introduce the LZR-2. An improved version of the LZR that Phelps and others wore.
Speedo spent 250k developing an improved “Pulse” fabric
Speedo spent 100k test marketing it with Olympic hopeful swimmers
Test market was successful
Example: Speedo LZR-2

11

Details
Speedo is assuming three years for the project. They assume that at the end of three years the technology will be essentially obsolete.
Cost of equipment to support the new swimsuit: $200,000 (depreciated according to MACRS 3-year life)
Increase in net working capital (mainly fabric materials): $20,000. This will be recovered at the end of the project.
Inflation has been built into financial statements already.
Operating costs are about 90% of revenues.
The equipment can be sold at the end of the project for an estimated $20,000.
Speedo has estimated the appropriate discount rate to be 10%.
Example: Speedo LZR-2

12
See the text for the details of the case.

Relevant or not?
250k developing fabric?

100k test marketing swimsuits?

Example: Speedo LZR-2

13

Starting point: Year 0 outflows
Equipment -200
NWC -20
Total -220
Example: Speedo LZR-2

14

Depreciation
Why do we care about depreciation?
Taxes
Tax shield = depreciation * tax rate
Now, do you want tax shields sooner or later?
Tax law allows accelerated depreciation (Modified Accelerated Cost Recovery System or MACRS)
Example: Speedo LZR-2

15
Tax Shield coming sooner implies that the Present Value of the tax shield will be larger

Example: Speedo LZR-2

16

Example: Speedo LZR-2
MACRS increases the present value of an investment’s tax benefits.
Speedo (rounded):
Year Investment % Depr
1 200 .33 66.00
2 200 .45 90.00
3 200 .15 30.00
4 200 .07 14.00

17

Example: Speedo LZR-2
Year 1 Year 2 Year 3

Revenues 4,000.00 3,000.00 2,000.00
Oper costs -3,600.00 -2,700.00 -1,800.00
Depreciation -66.00 -90.00 -30.00
Inc before tax 334.00 210.00 170.00
Tax (40%) -133.60 -84.00 -68.00
Net Inc 200.40 126.00 102.00

+ Depreciation +66.00 +90.00 +30.00
Oper CF 266.40 216.00 132.00

18

Equipment can be sold at end of year 3 for 20,000.
So, additional CF:
Book value: 14
Capital gain: 20 – 14 = 6
Taxes: 6 * .4 = 2.4
CF: 20 – 2.4 = 17.6
Put it all together!
Capital investment
Change in NWC
Operating CFs (need depreciation)
CF from Salvage
Evaluate!
Example: LZR-2

19
200 – 186 = BV of 14

Example: LZR-2
Year 0 Year 1 Year 2 Year 3
Capital Inv -200.00
D in NWC -20.00 0.00 0.00 +20.00
Oper CF +266.40 +216.00 +132.00
Salvage CF +17.60
Net CFs -220.00 266.40 216.00 169.60

Find NPV, IRR.

20
Discount rate of 10%
Year(s)3-Year5-Year7-Year10-Year15-Year20-Year
133.332014.291053.75
244.453224.49189.57.22
314.8119.217.4914.48.556.68
47.4111.5212.4911.527.76.18
511.528.939.226.935.71
65.768.927.376.235.28
7 8.936.555.94.89
8 4.456.555.94.52
9 6.565.94.46
10 6.555.94.46
11 3.295.94.46
12 5.94.46
13 5.914.46
14 5.94.46
15 5.914.46
16 2.994.46
17-20 4.46
21 2.23
Tax Depreciation Schedules by Recovery-Period Class
Table 6.1

Period
0 1 2 3 4 5 6 7
1 Capital Investment 10,000 (1,949)
2 Accumulated depreciation 1,583 3,167 4,750 6,333 7,917 9,500 – 0
3 Year-end book value 10,000 8,417 6,833 5,250 3,667 2,083 500 – 0
4 Working capital 550 1,289 3,261 4,890 3,583 2,002 – 0
5 Total book value (3+4) 10,000 8,967 8,122 8,511 8,557 5,666 2,502 – 0
6 Sales 523 12,887 32,610 48,901 35,834 19,717
7 Cost of goods sold 837 7,729 19,552 29,345 21,492 11,830
8 Other Costs 4,000 2,200 1,210 1,331 1,464 1,611 1,772
9 Depreciation 1,583 1,583 1,583 1,583 1,583 1,583
10 Pretax profit (6-7-8-9) (4,000) (4,097) 2,365 10,144 16,509 11,148 4,532 1,449
11 Tax at 35% (1,400) (1,434) 828 3,550 5,778 3,902 1,586 507
12 Profit after tax (10-11) 2,600 (2,663) 1,537 6,595 10,731 7,246 2,946 942

Table 6.2

Period
0 1 2 3 4 5 6 7
1 Sales 523 12,887 32,610 48,901 35,834 19,717
2 Cost of goods sold 837 7,729 19,552 29,345 21,492 11,830
3 Other costs 4,000 2,200 1,210 1,331 1,464 1,611 1,772
4 Tax on operations (1,400) (1,434) 828 3,550 5,778 3,902 1,586
5 Cash flow from operations (1-2-3-4) (2,600) (1,080) 3,120 8,177 12,314 8,829 4,529
6 Change in working capital (550) (739) (1,972) (1,629) 1,307 1,581 2,002
7 Capital investment and disposal (10,000) 1,442
8 Net cash flow (5+6+7) (12,600) (1,630) 2,381 6,205 10,685 10,136 6,110 3,444
9 Present value at 20% (12,600) (1,358) 1,654 3,591 5,153 4,074 2,046 961
Net Present value= +3520 (sum of 9)

Table 6.4

Tax Depreciation Schedules by Recovery-Period Class
Year(s) 3-Year 5-Year 7-Year 10-Year 15-Year 20-Year
1 33.33 20 14.29 10 5 3.75
2 44.45 32 24.49 18 9.5 7.22
3 14.81 19.2 17.49 14.4 8.55 6.68
4 7.41 11.52 12.49 11.52 7.7 6.18
5 11.52 8.93 9.22 6.93 5.71
6 5.76 8.92 7.37 6.23 5.28
7 8.93 6.55 5.9 4.89
8 4.45 6.55 5.9 4.52
9 6.56 5.9 4.46
10 6.55 5.9 4.46
11 3.29 5.9 4.46
12 5.9 4.46
13 5.91 4.46
14 5.9 4.46
15 5.91 4.46
16 2.99 4.46
17-20 4.46
21 2.23

Table 6.5

Period
0 1 2 3 4 5 6 7
1 Sales 523 12887 32610 48901 35834 19717
2 Cost of goods sold 837 7729 19552 29345 21492 11830
3 Other Costs 4000 2200 1210 1331 1464 1611 1772
4 Tax depreciation 2000 3200 1920 1152 576
5 Pretax profit (1-2-3-4) -4000 -4514 748 9807 16940 11579 5539 1949
6 Taxes at 35% -1400 -1580 262 3432 5929 4053 1939 682

Table 6.6

Period
0 1 2 3 4 5 6 7
1 Sales 523 12887 32610 48901 35834 19717
2 Cost of goods sold 837 7729 19552 29345 21492 11830
3 Other costs 4000 2200 1210 1331 1464 1611 1772
4 Tax -1400 -1580 262 3432 5929 4053 1939 682
5 Cash flow from operations (1-2-3-4) -2600 -934 3686 8295 12163 8678 4176 -682
6 Change in working capital -550 -739 -1972 -1629 1307 1581 2002
7 Capital investment and disposal -10000 1949
8 Net cash flow (5+6+7) -12600 -1484 2947 6323 10534 9985 5757 3269
9 Present Value= +3802 (sum of 9) -12600 -1237 2047 3659 5080 4013 1928 912
Net present value= +3802 (sum of 9)

7.1

Plotting data Fig 7.1
Equities Bonds Bills 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004
1900 1.00 1.00 1.00 Equities 1.00 1.24 1.47 1.55 1.33 1.70 2.08 2.16 1.53 2.24 2.69 2.46 2.60 2.79 2.57 2.43 3.37 3.59 2.91 3.48 4.20 3.45 3.85 5.03 5.18 6.58 8.44 9.24 12.30 17.06 14.58 10.46 5.87 5.29 8.34 8.70 12.53 16.58 10.84 13.89 14.29 13.26 11.95 13.86 17.78 21.58 29.78 28.02 29.01 29.64 35.67 46.17 55.75 63.24 63.61 95.48 119.55 129.40 116.09 168.32 190.44 192.01 244.70 219.72 265.71 308.62 353.13 322.38 414.85 473.70 421.87 422.01 496.62 585.91 477.42 341.89 473.43 599.30 583.50 637.61 800.60 1,070.16 1,030.03 1,222.75 1,509.69 1,555.69 2,062.29 2,394.21 2,448.67 2,888.00 3,730.54 3,499.96 4,697.09 5,118.31 5,695.73 5,692.05 7,766.72 9,414.13 12,359.92 15,255.97 18,850.17 16,796.60 14,953.34 11,834.11 15,578.51
1901 1.24 1.04 1.04 Bonds 1.00 1.04 1.07 1.09 1.13 1.15 1.19 1.22 1.23 1.28 1.31 1.35 1.40 1.45 1.49 1.55 1.60 1.65 1.70 1.65 1.66 1.76 1.98 2.05 2.17 2.31 2.44 2.63 2.86 2.86 2.96 3.10 2.94 3.43 3.43 3.77 3.96 4.26 4.27 4.50 4.77 5.06 5.11 5.27 5.38 5.53 6.13 6.12 5.96 6.16 6.56 6.57 6.31 6.38 6.61 7.09 7.00 6.61 7.10 6.67 6.52 7.41 7.49 8.00 8.10 8.38 8.44 8.75 7.95 7.93 7.52 8.43 9.55 10.09 9.98 10.42 11.37 13.28 13.19 13.03 12.87 12.36 12.59 17.68 17.79 20.55 26.91 33.51 32.60 35.75 42.23 44.84 53.49 57.80 68.34 63.03 82.99 82.22 95.26 107.70 98.05 119.11 123.51 145.54 147.65
1902 1.47 1.07 1.09 Bills 1.00 1.04 1.09 1.14 1.20 1.25 1.31 1.38 1.47 1.53 1.59 1.67 1.73 1.81 1.91 2.01 2.08 2.14 2.24 2.37 2.50 2.70 2.88 3.02 3.18 3.31 3.44 3.55 3.66 3.79 3.98 4.07 4.11 4.15 4.17 4.17 4.18 4.19 4.20 4.20 4.20 4.20 4.20 4.21 4.23 4.24 4.26 4.27 4.29 4.33 4.38 4.43 4.50 4.57 4.65 4.69 4.77 4.88 5.04 5.12 5.27 5.41 5.52 5.67 5.85 6.06 6.29 6.59 6.87 7.23 7.70 8.21 8.57 8.90 9.51 10.27 10.87 11.42 12.01 12.87 14.20 15.80 18.12 20.04 21.80 23.95 25.79 27.38 28.88 30.71 33.29 35.89 37.89 39.22 40.36 41.93 44.28 46.59 49.04 51.42 53.82 57.00 59.18 60.15 60.77
1903 1.55 1.09 1.14
1904 1.33 1.13 1.20
1905 1.70 1.15 1.25
1906 2.08 1.19 1.31
1907 2.16 1.22 1.38
1908 1.53 1.23 1.47
1909 2.24 1.28 1.53
1910 2.69 1.31 1.59
1911 2.46 1.35 1.67
1912 2.60 1.40 1.73
1913 2.79 1.45 1.81
1914 2.57 1.49 1.91
1915 2.43 1.55 2.01
1916 3.37 1.60 2.08
1917 3.59 1.65 2.14
1918 2.91 1.70 2.24
1919 3.48 1.65 2.37
1920 4.20 1.66 2.50
1921 3.45 1.76 2.70
1922 3.85 1.98 2.88
1923 5.03 2.05 3.02
1924 5.18 2.17 3.18
1925 6.58 2.31 3.31
1926 8.44 2.44 3.44
1927 9.24 2.63 3.55
1928 12.30 2.86 3.66
1929 17.06 2.86 3.79
1930 14.58 2.96 3.98
1931 10.46 3.10 4.07
1932 5.87 2.94 4.11
1933 5.29 3.43 4.15
1934 8.34 3.43 4.17
1935 8.70 3.77 4.17
1936 12.53 3.96 4.18
1937 16.58 4.26 4.19
1938 10.84 4.27 4.20
1939 13.89 4.50 4.20
1940 14.29 4.77 4.20
1941 13.26 5.06 4.20
1942 11.95 5.11 4.20
1943 13.86 5.27 4.21
1944 17.78 5.38 4.23
1945 21.58 5.53 4.24
1946 29.78 6.13 4.26
1947 28.02 6.12 4.27
1948 29.01 5.96 4.29
1949 29.64 6.16 4.33
1950 35.67 6.56 4.38
1951 46.17 6.57 4.43
1952 55.75 6.31 4.50
1953 63.24 6.38 4.57
1954 63.61 6.61 4.65
1955 95.48 7.09 4.69
1956 119.55 7.00 4.77
1957 129.40 6.61 4.88
1958 116.09 7.10 5.04
1959 168.32 6.67 5.12
1960 190.44 6.52 5.27
1961 192.01 7.41 5.41
1962 244.70 7.49 5.52
1963 219.72 8.00 5.67
1964 265.71 8.10 5.85
1965 308.62 8.38 6.06
1966 353.13 8.44 6.29
1967 322.38 8.75 6.59
1968 414.85 7.95 6.87
1969 473.70 7.93 7.23
1970 421.87 7.52 7.70
1971 422.01 8.43 8.21
1972 496.62 9.55 8.57
1973 585.91 10.09 8.90
1974 477.42 9.98 9.51
1975 341.89 10.42 10.27
1976 473.43 11.37 10.87
1977 599.30 13.28 11.42
1978 583.50 13.19 12.01
1979 637.61 13.03 12.87
1980 800.60 12.87 14.20
1981 1,070.16 12.36 15.80
1982 1,030.03 12.59 18.12
1983 1,222.75 17.68 20.04
1984 1,509.69 17.79 21.80
1985 1,555.69 20.55 23.95
1986 2,062.29 26.91 25.79
1987 2,394.21 33.51 27.38
1988 2,448.67 32.60 28.88
1989 2,888.00 35.75 30.71
1990 3,730.54 42.23 33.29
1991 3,499.96 44.84 35.89
1992 4,697.09 53.49 37.89
1993 5,118.31 57.80 39.22
1994 5,695.73 68.34 40.36
1995 5,692.05 63.03 41.93
1996 7,766.72 82.99 44.28
1997 9,414.13 82.22 46.59
1998 12,359.92 95.26 49.04
1999 15,255.97 107.70 51.42
2000 18,850.17 98.05 53.82
2001 16,796.60 119.11 57.00
2002 14,953.34 123.51 59.18
2003 11,834.11 145.54 60.15
2004 15,578.51 147.65 60.77

7.2

Plotting data Fig 7.2
Equities Bonds Bills 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 0.916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004
1900 1.00 1.00 1.00 Equities 1.00 1.23 1.44 1.51 1.27 1.59 1.95 2.01 1.38 2.00 2.44 2.20 2.27 2.41 2.18 2.04 2.77 2.64 1.81 1.80 1.89 1.52 1.89 2.54 2.55 3.24 4.01 4.45 6.05 8.47 7.23 5.52 3.42 3.44 5.39 5.52 7.72 10.08 6.39 8.43 8.71 8.01 6.58 6.98 8.68 10.32 13.92 11.09 10.53 10.48 12.84 15.71 17.92 20.14 20.14 30.37 37.89 39.87 34.72 49.47 55.15 54.79 69.36 61.53 73.20 84.02 94.32 83.31 104.05 113.45 95.22 90.29 102.80 117.28 87.84 56.06 72.54 87.61 79.90 80.08 88.74 105.53 93.24 106.56 126.75 125.65 160.51 184.26 180.50 203.87 251.65 222.51 289.74 306.82 332.30 323.44 430.40 504.92 651.82 791.78 952.74 821.14 719.86 556.47 719.03
1901 1.23 1.03 1.04 Bonds 1.00 1.03 1.05 1.06 1.07 1.08 1.11 1.14 1.11 1.15 1.19 1.21 1.22 1.25 1.26 1.30 1.32 1.21 1.06 0.85 0.75 0.77 0.97 1.04 1.07 1.14 1.16 1.27 1.41 1.42 1.47 1.64 1.71 2.23 2.22 2.39 2.44 2.59 2.52 2.73 2.91 3.06 2.81 2.66 2.63 2.65 2.87 2.42 2.16 2.18 2.36 2.23 2.03 2.03 2.09 2.25 2.22 2.04 2.12 1.96 1.89 2.12 2.12 2.24 2.23 2.28 2.25 2.26 1.99 1.90 1.70 1.80 1.98 2.02 1.84 1.71 1.74 1.94 1.81 1.64 1.43 1.22 1.14 1.54 1.49 1.66 2.09 2.58 2.40 2.52 2.85 2.85 3.30 3.46 3.99 3.58 4.60 4.41 5.02 5.59 4.96 5.82 5.95 6.84 6.81
1902 1.44 1.05 1.07 Bills 1.00 1.04 1.07 1.11 1.15 1.18 1.22 1.29 1.32 1.37 1.44 1.49 1.51 1.57 1.62 1.68 1.71 1.58 1.39 1.22 1.13 1.18 1.42 1.52 1.56 1.63 1.63 1.71 1.80 1.89 1.97 2.15 2.40 2.70 2.69 2.65 2.57 2.55 2.48 2.55 2.56 2.54 2.31 2.12 2.06 2.03 1.99 1.69 1.56 1.53 1.58 1.51 1.44 1.46 1.47 1.49 1.51 1.50 1.51 1.50 1.52 1.54 1.57 1.59 1.61 1.65 1.68 1.70 1.72 1.73 1.74 1.76 1.77 1.78 1.75 1.68 1.67 1.67 1.64 1.62 1.57 1.56 1.64 1.75 1.83 1.93 2.01 2.11 2.13 2.17 2.25 2.28 2.34 2.35 2.35 2.38 2.45 2.50 2.59 2.67 2.72 2.79 2.85 2.83 2.80
1903 1.51 1.06 1.11
1904 1.27 1.07 1.15
1905 1.59 1.08 1.18
1906 1.95 1.11 1.22
1907 2.01 1.14 1.29
1908 1.38 1.11 1.32
1909 2.00 1.15 1.37
1910 2.44 1.19 1.44
1911 2.20 1.21 1.49
1912 2.27 1.22 1.51
1913 2.41 1.25 1.57
1914 2.18 1.26 1.62
1915 2.04 1.30 1.68
0.916 2.77 1.32 1.71
1917 2.64 1.21 1.58
1918 1.81 1.06 1.39
1919 1.80 0.85 1.22
1920 1.89 0.75 1.13
1921 1.52 0.77 1.18
1922 1.89 0.97 1.42
1923 2.54 1.04 1.52
1924 2.55 1.07 1.56
1925 3.24 1.14 1.63
1926 4.01 1.16 1.63
1927 4.45 1.27 1.71
1928 6.05 1.41 1.80
1929 8.47 1.42 1.89
1930 7.23 1.47 1.97
1931 5.52 1.64 2.15
1932 3.42 1.71 2.40
1933 3.44 2.23 2.70
1934 5.39 2.22 2.69
1935 5.52 2.39 2.65
1936 7.72 2.44 2.57
1937 10.08 2.59 2.55
1938 6.39 2.52 2.48
1939 8.43 2.73 2.55
1940 8.71 2.91 2.56
1941 8.01 3.06 2.54
1942 6.58 2.81 2.31
1943 6.98 2.66 2.12
1944 8.68 2.63 2.06
1945 10.32 2.65 2.03
1946 13.92 2.87 1.99
1947 11.09 2.42 1.69
1948 10.53 2.16 1.56
1949 10.48 2.18 1.53
1950 12.84 2.36 1.58
1951 15.71 2.23 1.51
1952 17.92 2.03 1.44
1953 20.14 2.03 1.46
1954 20.14 2.09 1.47
1955 30.37 2.25 1.49
1956 37.89 2.22 1.51
1957 39.87 2.04 1.50
1958 34.72 2.12 1.51
1959 49.47 1.96 1.50
1960 55.15 1.89 1.52
1961 54.79 2.12 1.54
1962 69.36 2.12 1.57
1963 61.53 2.24 1.59
1964 73.20 2.23 1.61
1965 84.02 2.28 1.65
1966 94.32 2.25 1.68
1967 83.31 2.26 1.70
1968 104.05 1.99 1.72
1969 113.45 1.90 1.73
1970 95.22 1.70 1.74
1971 90.29 1.80 1.76
1972 102.80 1.98 1.77
1973 117.28 2.02 1.78
1974 87.84 1.84 1.75
1975 56.06 1.71 1.68
1976 72.54 1.74 1.67
1977 87.61 1.94 1.67
1978 79.90 1.81 1.64
1979 80.08 1.64 1.62
1980 88.74 1.43 1.57
1981 105.53 1.22 1.56
1982 93.24 1.14 1.64
1983 106.56 1.54 1.75
1984 126.75 1.49 1.83
1985 125.65 1.66 1.93
1986 160.51 2.09 2.01
1987 184.26 2.58 2.11
1988 180.50 2.40 2.13
1989 203.87 2.52 2.17
1990 251.65 2.85 2.25
1991 222.51 2.85 2.28
1992 289.74 3.30 2.34
1993 306.82 3.46 2.35
1994 332.30 3.99 2.35
1995 323.44 3.58 2.38
1996 430.40 4.60 2.45
1997 504.92 4.41 2.50
1998 651.82 5.02 2.59
1999 791.78 5.59 2.67
2000 952.74 4.96 2.72
2001 821.14 5.82 2.79
2002 719.86 5.95 2.85
2003 556.47 6.84 2.83
2004 719.03 6.81 2.80

7.3

Denmark 4.3
Belgium 4.7 Denmark Belgium Switzerland Spain Canada Ireland Germany UK Average Netherlands USA Sweden South Africa Australia France Japan Italy
Switzerland 5.1 4.3 4.7 5.1 5.3 5.8 5.9 5.9 6.3 6.4 6.6 7.6 8.1 8.2 8.6 9.3 10 10.7
Spain 5.3
Canada 5.8
Ireland 5.9
Germany 5.9
UK 6.3
Average 6.4
Netherlands 6.6
USA 7.6
Sweden 8.1
South Africa 8.2
Australia 8.6
France 9.3
Japan 10
Italy 10.7

7.4

1900 24.2%
1901 18.0%
1902 6.1% 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002
1903 -14.6% 24.2% 18.0% 6.1% -14.6% 28.1% 22.1% 4.1% -29.3% 46.8% 20.0% -8.5% 5.7% 7.2% -7.8% -5.5% 38.8% 6.4% -18.9% 19.6% 20.6% -17.9% 11.6% 30.6% 3.0% 27.0% 28.3% 9.5% 33.1% 38.7% -14.6% -28.3% -43.9% -9.8% 57.6% 4.4% 44.0% 32.3% -34.6% 28.2% 2.9% -7.2% -9.9% 16.0% 28.3% 21.4% 38.0% -5.9% 3.6% 2.2% 20.3% 29.4% 20.8% 13.4% 0.6% 50.1% 25.2% 8.2% -10.3% 45.0% 13.1% 0.8% 27.4% -10.2% 20.9% 16.2% 14.4% -8.7% 28.7% 14.2% -10.9% 0.0% 17.7% 18.0% -18.5% -28.4% 38.5% 26.6% -2.6% 9.3% 25.6% 33.7% -3.8% 18.7% 23.5% 3.1% 32.6% 16.1% 2.3% 17.9% 29.2% -6.2% 34.2% 9.0% 11.3% -0.1% 36.5% 21.2% 31.3% 23.4% 23.6% -10.9% -11.0% -20.9%
1904 28.1%
1905 22.1%
1906 4.1%
1907 -29.3%
1908 46.8%
1909 20.0%
1910 -8.5%
1911 5.7%
1912 7.2%
1913 -7.8%
1914 -5.5%
1915 38.8%
1916 6.4%
1917 -18.9%
1918 19.6%
1919 20.6%
1920 -17.9%
1921 11.6%
1922 30.6%
1923 3.0%
1924 27.0%
1925 28.3%
1926 9.5%
1927 33.1%
1928 38.7%
1929 -14.6%
1930 -28.3%
1931 -43.9%
1932 -9.8%
1933 57.6%
1934 4.4%
1935 44.0%
1936 32.3%
1937 -34.6%
1938 28.2%
1939 2.9%
1940 -7.2%
1941 -9.9%
1942 16.0%
1943 28.3%
1944 21.4%
1945 38.0%
1946 -5.9%
1947 3.6%
1948 2.2%
1949 20.3%
1950 29.4%
1951 20.8%
1952 13.4%
1953 0.6%
1954 50.1%
1955 25.2%
1956 8.2%
1957 -10.3%
1958 45.0%
1959 13.1%
1960 0.8%
1961 27.4%
1962 -10.2%
1963 20.9%
1964 16.2%
1965 14.4%
1966 -8.7%
1967 28.7%
1968 14.2%
1969 -10.9%
1970 0.0%
1971 17.7%
1972 18.0%
1973 -18.5%
1974 -28.4%
1975 38.5%
1976 26.6%
1977 -2.6%
1978 9.3%
1979 25.6%
1980 33.7%
1981 -3.8%
1982 18.7%
1983 23.5%
1984 3.1%
1985 32.6%
1986 16.1%
1987 2.3%
1988 17.9%
1989 29.2%
1990 -6.2%
1991 34.2%
1992 9.0%
1993 11.3%
1994 -0.1%
1995 36.5%
1996 21.2%
1997 31.3%
1998 23.4%
1999 23.6%
2000 -10.9%
2001 -11.0%
2002 -20.9%

ComputeIRR and NPV in Microsoft Excel

1.IRR Function

Description:

The Microsoft Excel IRR function returns the internal rate of return for a series of cash flows. The cash

flows must occur at regular intervals, but do not have to be the same amounts for each interval.

Syntax

The syntax for the IRR function in Microsoft Excel is:

IRR(range, [estimated_irr] )

Parameters or Arguments

range
A range of cells that represent the series of cash flows.

estimated_irr
Optional. It is your guess at the internal rate of return. If this parameter is omitted, it
assumes an estimated_irr of 0.1 or 10%

Example (as Worksheet Function)

Let’s look at some Excel IRR function examples and explore how to use the IRR function as a
worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above:

This first example returns an internal rate of return of 28%. It assumes that you start a
business at a cost of $7,500. You net the following income for the first four years: $3,000,
$5,000, $1,200, and $4,000.

This next example returns an internal rate of return of 5%. It assumes that you start a
business at a cost of $10,000. You net the following income for the first three years: $3,400,
$6,500, and $1,000.

=IRR(B1:B4)

Result: 5%

2.NPV Function

Description

The Microsoft Excel NPV function returns the net present value of an investment.

Syntax

The syntax for the NPV function in Microsoft Excel is:

NPV( discount_rate, value1, [value2, … value_n] )

Parameters or Arguments

discount_rate
The discount rate for the period.

value1, value2, … value_n
The future payments and income for the investment (ie: cash flows). There can be up
to 29 values entered.

Note

Microsoft Excel’s NPV function does not account for the intial cash outlay, or may account for
it improperly depending on the version of Excel. However, there is a workaround.

This workaround requires that you NOT include the initial investment in the future
payments/income for the investment (ie: value1, value2, … value_n), but instead, you need to
subtract from the result of the NPV function, the amount of the initial investment.

The workaround formula is also different depending on whether the cash flows occur at the
end of the period (EOP) or at the beginning of the period (BOP).

If the cash flows occur at the end of the period (EOP), you would use the following formula:

=NPV( discount_rate, value1, value2, … value_n ) – Initial Investment

If the cash flows occur at the beginning of the period (BOP), ou would use the following
formula:

=NPV( discount_rate, value2, … value_n ) – Initial Investment + value1

Example (as Worksheet Function)

Let’s look at some NPV examples and explore how to use the NPV function as a worksheet
function in Microsoft Excel:

This first example returns a net present value of $3,457.19. It assumes that you pay $7,500
as an initial investment . You then receive the following income for the first four years (EOP):
$3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.

=NPV(8%, 3000, 5000, 1200, 4000) – 7500

This next example returns a net present value of $8,660.77. It assumes that you pay $10,000
as an initial investment. You then receive the following income for the first three years (BOP):
$3,400, $6,500, and $10,000. An annual discount rate of 5% is used.

=NPV(5%, 6500, 10000) – 10000 + 3400

Below is the example used in Chapter 10 PPT.