Principles of Corporate Finance_ 12th Edition

xii

bre44380_fm_i-xxviii xii 10/09/15 10:47 PM

Excel Treatment

Confirming pages

38

bre44380_ch02_019-045.indd 38 09/02/15 03:42 PM

● ● USEFUL SPREADSHEET FUNCTIONS● ● ●

❱ functions to solve discounted-cash-flow (DCF) prob-Spreadsheet programs such as Excel provide built-in

lems. You can find these functions by pressing Excel toolbar. If you then click on the function that you fx on the

wish to use, Excel asks you for the inputs that it needs.

At the bottom left of the function box there is a Help facility with an example of how the function is used.

and some points to remember when entering data:Here is a list of useful functions for DCF problems

∙ FV: Future value of single investment or annuity.

∙ PV: Present value of single future cash flow or

annuity.

∙ RATE:produce given future value or annuity. Interest rate (or rate of return) needed to

∙ NPER:an investment to reach a given future value or Number of periods (e.g., years) that it takes

series of future cash flows.

∙ PMT: Amount of annuity payment with a given

present or future value.

∙ NPV: Calculates the value of a stream of negative

and positive cash flows. (When using this function, note the warning below.)

∙ XNPV:date of the first cash flow of a series of cash flows Calculates the net present value at the

occurring at uneven intervals.

∙ EFFECT:the quoted rate (APR) and number of interest pay- The effective annual interest rate, given

ments in a year.

∙ NOMINAL: The quoted interest rate (APR) given

the effective annual interest rate.

All the inputs in these functions can be entered directly as numbers or as the addresses of cells that contain the

numbers.Three warnings:

1. PV is the amount that needs to be invested today to produce a given future value. It should therefore
   be entered as a negative number. Entering both PV
   and FV with the same sign when solving for RATE results in an error message.


2. Always enter the interest or discount rate as a deci-mal value (e.g., .05 rather than 5%).


3. Use the NPV function with care. Better still, don’t
   use it at all. It gives the value of the cash flows one period before the first cash flow and not the value at
   the date of the first cash flow.
   Spreadsheet Questions
   The following questions provide opportunities to prac-
   tice each of the Excel functions.


4. (FV) In 1880, five aboriginal trackers were each promised the equivalent of 100 Australian dollars
   for helping to capture the notorious outlaw Ned Kelly. One hundred and thirteen years later the
   granddaughters of two of the trackers claimed that
   this reward had not been paid. If the interest rate over this period averaged about 4.5%, how much
   would the A$100 have accumulated to?


5. (PV) Your adviser has produced revised figures for your office building. It is forecasted to produce a
   cash flow of $40,000 in year 1, but only $850,000 in year 2, when you come to sell it. If the cost of capi-
   tal is 12%, what is the value of the building?


6. (PV) Your company can lease a truck for $10,000 a year (paid at the end of the year) for six years, or it
   can buy the truck today for $50,000. At the end of
   the six years the truck will be worthless. If the inter-est rate is 6%, what is the present value of the lease
   payments? Is the lease worthwhile?


7. (RATE) Ford Motor stock was one of the victims of the 2008 credit crisis. In June 2007, Ford stock price
   stood at $9.42. Eighteen months later it was $2.72. What was the annual rate of return over this period
   to an investor in Ford stock?

Discounting Cash Flows

❱ Spreadsheet Functions

Boxes
These boxes provide detailed examples
of how to use Excel spreadsheets when
applying financial concepts. Questions
that apply to the spreadsheet follow for
additional practice.

bre44380_ch07_162-191.indd 184 10/09/15 10:08 PM

Confirming pages

184 Part Two Risk

We have seen that diversification reduces risk and, therefore, makes sense for investors. But

does it also make sense for the firm? Is a diversified firm more attractive to investors than

an undiversified one? If it is, we have an extremely disturbing result. If diversification is an

appropriate corporate objective, each project has to be analyzed as a potential addition to the

firm’s portfolio of assets. The value of the diversified package would be greater than the sum

of the parts. So present values would no longer add.

Diversification is undoubtedly a good thing, but that does not mean that firms should prac-

tice it. If investors were not able to hold a large number of securities, then they might want

firms to diversify for them. But investors can diversify. In many ways they can do so more

easily than firms. Individuals can invest in the steel industry this week and pull out next week.

A firm cannot do that. To be sure, the individual would have to pay brokerage fees on the pur-

chase and sale of steel company shares, but think of the time and expense for a firm to acquire

a steel company or to start up a new steel-making operation.

You can probably see where we are heading. If investors can diversify on their own account,

they will not pay any extra for firms that diversify. And if they have a sufficiently wide choice

of securities, they will not pay any less because they are unable to invest separately in each

factory. Therefore, in countries like the United States, which have large and competitive capi-

tal markets, diversification does not add to a firm’s value or subtract from it. The total value

is the sum of its parts.

This conclusion is important for corporate finance, because it justifies adding present val-

ues. The concept of value additivity is so important that we will give a formal definition of it.

❱ TABLE 7.7 Calculating the variance of the market returns and the covariance between

the returns on the market and those of Anchovy Queen. Beta is the ratio of the variance to

the covariance (i.e., β = σim/ σ (^) m^2 ).
1 (1) (2) (3) (4) (5) (6) (7)
2 Product of
3 Deviation Deviation Squared deviations
4 from from average deviation from average
5 Market Anchovy Q average Anchovy Q from average returns
6 Month return return market return return market return(cols 4 × 5)
7 1 – 8% – 11% –^10 –^13 100
8 2 4 8 2 6 4 12
9 3 12 19 10 17 100 170
10 4 – 6 – 13 – 8 – 15 64 120
11 5 2 3 0 1 0 0
12 6 8 6 6 4 36 24
13 Average 2 2 Total 304 456
14 Variance =^ σm^2 = 304/6 = 50.
15 Covariance =^ σim^ = 456/6 =^76
16 Beta (β) = σim/σm^2 = 76/50.67 = 1.
BEYOND THE PAGE
mhhe.com/brealey12e
Try It! Table 7.7: Calculating
Anchovy Queen’s beta
7-5 Diversification and Value Additivity
❱ Excel Exhibits
Select tables are set as spreadsheets, and
the corresponding Excel files are also
available in Connect and through the
Beyond the Page features.