# Average book value

Document Sample

```					Ross, Westerfield, and Jordan's Spreadsheet Master
Essentials of Corporate Finance, 7 th edition
by Brad Jordan and Joe Smolira
Version 7.0

Chapter 8
In these spreadsheets, you will learn how to use the following Excel f

NPV
XNPV
AND
ABS
MAX
COUNTIF
AVERAGE
IRR
XIRR
MIRR

The following conventions are used in these spreadsheets:

1) Given data in blue
2) Calculations in red

NOTE: Some functions used in these spreadsheets may require that
the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.
To install these, click on the Office button
then "Excel Options," "Add-Ins" and select
"Go." Check "Analysis ToolPak" and
the following Excel functions:

Chapter 8 - Section 1
Net Present Value

We previously used the net present value function to find the present value of unequal cash flows. Now, you know
of all outflows, plus the present value of all inflows. Unfortunately, as we will see, computer programmers don't un

Suppose we have a project with the following cash flows and required return. What is the NPV of the project?

t         Cash flow
0       \$     (30,000)
1                8,000
2              10,000
3              11,000
4              17,000
5              12,000

Return:                      12%

NPV:              \$    10,557.31

RWJ Excel Tip
To calculate the NPV of the project using the NPV function, we entered the following:
Notice one very important thing: We did not include the cash flow at time 0 in the NPV function. The reason is simp
did not truly create a function that calculated the NPV, but rather created a function that calculated the present va
cash flows, we use the NPV function to calculate the present value of the cash flows beyond time 0, then add the c
simply go to the NPV cell above.

NPV and Cash Flows at Irregular Intervals

The NPV function does not really calculate the NPV of a set of cash flows, and it also has a potential problem in tha
flows occur at regular intervals. If the cash flows occur at irregular intervals, we need to use the XNPV function. The
date for each cash flow. Suppose we have the following set of cash flows and required return. What is the NPV of t

t            Cash flow
2/23/2009       \$     (50,000)
8/24/2009              14,000
3/5/2010                8,000
11/2/2010              23,000
6/5/2011              18,000
12/18/2011             19,000

Return:                       11%

NPV:              \$    18,107.66

RWJ Excel Tip
To calculate the NPV of the project using the XNPV function, we entered the following:

With the XNPV function, you need to include the first cash flow. The NPV of the cash flows will be on the date of th
unequal cash flows. Now, you know exactly what net present value means: The present value
ee, computer programmers don't understand net present value.

What is the NPV of the project?

owing:
the NPV function. The reason is simple. When the programmers created the NPV function, they
ction that calculated the present value of cash flows. So, to calculate the NPV of a series of
lows beyond time 0, then add the cash flow at time zero to the result. To see how we did this,

also has a potential problem in that the implicit assumption used by Excel is that the cash
need to use the XNPV function. The XNPV function has an additional argument, namely the
equired return. What is the NPV of the cash flows?

lowing:

cash flows will be on the date of the first cash flow.
Chapter 8 - Section 2
The Payback Rule

The payback rule is the simplest capital budgeting technique, but unfortunately, Excel has no function to directly ca
payback period, we need to program Excel to calculate it. We will show you three ways to do this.

Suppose we have a project with the following cash flows. What is the payback period for the project? Should the p

t            Cash flow
0          \$     (30,000)
1                   8,000
2                 10,000
3                 11,000
4                 17,000
5                 12,000

Maximum payback (years):                           3

In the first method, we will calculate the cumulative cash flows for the project by simply adding the cash flows each

Cumulative
t             cash flow
0          \$      (30,000)
1                 (22,000)
2                 (12,000)
3                   (1,000)
4                  16,000
5                  28,000

Next, we will calculate the payback period as a fractional number of years. We know that the payback period will o
become positive. So, we need to test each year to determine if the cumulative cash flows for the previous year wer
year are positive. To do this, we will use a nested function. The IF function will allow us to test this, but the IF funct
flows, we will insert an AND function in the IF function. In the following statement, the result will be zero if the con

Payback
t            calculation
0
1                    0.000
2                    0.000
3                    0.000
4                    3.059
5                    0.000
RWJ Excel Tip
In the calculation of the payback period, we used two new functions, AND and ABS. AND is a logical test that return
value if the arguments are false. Excel will allow you to simultaneously test up to 255 conditions with the AND func
a number. Since we are dividing the negative cumulative cash flow from the previous year by the positive cash flow
a negative answer. ABS makes sure that the calculation returns a positive value. Of course, since we know the cum
we could have inserted a negative sign in front of this cell reference as well.

Now, rather than looking through the payback calculation, we would like a cell that shows the payback period for t
accept the project or reject it according to the payback rule. Of course, the payback period could also be never so w
decision on the project is:

Payback period for project:                   3.06
Accept or reject?                            Reject

RWJ Excel Tip
Since the payback calculation only has one value greater than zero, we used the MAX function to return the maxim
values and returns only the maximum value found in that range of numbers. We nested the MAX function in an IF s
calculation column is zero, then the project has no payback period, so the logical test will return "Never." The acce
for the project is less than the required payback, it will return "Accept" and if the payback period for the project is
"Reject."

While the previous method accurately calculates the payback period, it is a little messy in that there are two interm
Although Albert Einstein argued that elegance is for tailors, we like a little elegance in our spreadsheets. We can us
single cell, although we should warn you that the equation is not for the faint of heart. First, we test to make sure t
from the end of the cash flows to the beginning, calculating longer payback periods first. You could test for shorter
is:

Payback period for project:                   3.06
Accept or reject?                            Reject

RWJ Excel Tip
When using nested IF statements, you can include up to 64 IF statements in a single nested statement.

Since we have already argued for elegance when using Excel, we have another method to calculate the payback pe
cumulative cash flow column, the number of negative cash flows in the cumulative cash flows is the number of yea
can count the number of negative cash flows in the cumulative cash flow column. The fractional part of the paybac
year divided by the amount the project will make in that next year. With this, the payback period is:

Cumulative
t            Cash flow         cash flow
0          \$      (30,000)
1                   8,000 \$         (22,000)
2                  10,000           (12,000)
3                  11,000            (1,000)
4                  17,000            16,000
5                  12,000            28,000

Payback period for project:                    3.06
Accept or reject?                             Reject

Let's talk about COUNTIF, then discuss the logic of the functions we used.

RWJ Excel Tip
The COUNTIF function is a combination of the COUNT function and the IF function. As you would expect, the COUN
selected array. The COUNTIF function first tests if a condition is true for each cell, then counts the number of cells i
COUNTIF function to count the number of negative cells.

In our equation, we first tested for the case in which the payback period is never. Next, we tested for the unusual c
we are going to count only the cumulative cash flows, we need a special function in case the payback period is less
We used a COUNTIF function to count the number of cumulative cash flows that are negative. This gives us the wh
VLOOKUP function to find the last negative cumulative cash flow. This is the numerator to determine the fractiona
the calculation to the whole number, but since the cumulative cash flow will be less than zero, we subtracted it. (A
negative is a positive.) In the denominator, we used a COUNTIF function nested inside a VLOOKUP function. The CO
cumulative cash flows, then we added one in order to get to the next year's cash inflow. The VLOOKUP function wi
the cash inflow in that year. So, which one of these payback calculations is the best? They are all equally correct. In
than one way to calculate an answer correctly.
, Excel has no function to directly calculate the payback period. In order to calculate the
ee ways to do this.

period for the project? Should the project be accepted?

by simply adding the cash flows each year as follows:

know that the payback period will occur during the first year that the cumulative cash flows
cash flows for the previous year were negative and the cumulative cash flows for the current
allow us to test this, but the IF function will perform only one logical test. To test both cash
ent, the result will be zero if the conditions are not met.
ABS. AND is a logical test that returns a value if one or more arguments are true and another
o 255 conditions with the AND function. The ABS value function returns the absolute value of
evious year by the positive cash flow that will occur during the current year, we will always get
. Of course, since we know the cumulative cash flow from the previous year will be negative,

that shows the payback period for the project, as well as a cell that tells us whether we should
back period could also be never so we include this possibility. The payback period and the

e MAX function to return the maximum value in the array. The MAX function tests two or more
e nested the MAX function in an IF statement. If the maximum value of the payback
al test will return "Never." The accept/reject cell is a simple IF statement. If the payback period
he payback period for the project is greater than the required payback, the function will return

e messy in that there are two intermediate calculations required for the final calculation.
nce in our spreadsheets. We can use a nested IF function to calculate the payback period in a
f heart. First, we test to make sure that the project does have a payback period, then we test
iods first. You could test for shorter payback periods first if you want. The nested IF statement

ngle nested statement.

method to calculate the payback period. Here is the logic behind this method: If we create a
tive cash flows is the number of years (plus the fractional year) for the payback period. So, we
mn. The fractional part of the payback period is amount the project is short in the last negative
he payback period is:
on. As you would expect, the COUNT function returns the total number of objects in the
ell, then counts the number of cells in which the condition is true. In this case, we used the

er. Next, we tested for the unusual case in which the payback period is less than one year. Since
on in case the payback period is less than one year. Now we get to the heart of the equation.
t are negative. This gives us the whole number for the payback period. We then used the
merator to determine the fractional number of years for the payback period. We need to add
less than zero, we subtracted it. (Actually, we used the ABS function since a negative of a
inside a VLOOKUP function. The COUNTIF function will again count the number of negative
h inflow. The VLOOKUP function will then return the value from the second column, which is
best? They are all equally correct. In Excel, as in Finance in general, there is almost always more
Chapter 8 - Section 3
The Average Accounting Return

Since Excel does not have a function to directly calculate the average accounting return (AAR), we need to construc
information for a project:

Initial cost:             \$     750,000
Tax rate:                           34%

Year 1           Year 2           Year 3           Year 4
Revenue                   \$     500,000 \$        640,000 \$        680,000 \$        560,000
Expenses                        200,000          330,000          360,000          290,000
Depreciation                    150,000          150,000          150,000          150,000
Earnings before taxes     \$     150,000 \$        160,000 \$        170,000 \$        120,000
Taxes                            51,000           54,400           57,800           40,800
Net income                \$      99,000 \$        105,600 \$        112,200 \$         79,200

The AAR is the average net income divided by the average book value. These averages are:

Average book value:       \$     375,000
Average net income:       \$      87,120

So, the AAR is:

AAR:                              23.23%

RWJ Excel Tip
To calculate the average net income, we used the AVERAGE function, under More Functions, Statistical, as follows:
The AVERAGE function returns the arithmetic average for a series of numbers.
(AAR), we need to construct a spreadsheet to calculate it. Suppose we have the following

Year 5
\$      470,000
260,000
150,000
\$       60,000
20,400
\$       39,600

are:

tions, Statistical, as follows:
Chapter 8 - Section 4
The Internal Rate of Return

The internal rate of return (IRR) is the rate of return for a series of cash flows that results in a zero NPV. Excel's IRR

What is the IRR for the project with the following cash flows?

t            Cash flow
0          \$     (30,000)
1                   8,000
2                 10,000
3                 11,000
4                 17,000
5                 12,000

IRR:                        24.01%

RWJ Excel Tip
To use the IRR function, we entered the following:

Notice that we need to include the first cash flow in the Values argument for IRR. We left the Guess argument blan
rate until it finds an interest rate that generates a zero NPV. If you enter a starting value for the Guess argument, E
unnecessary to enter a value for this argument, although as we will see later, when you want to find multiple IRRs,

IRR and Cash Flows at Irregular Intervals
Suppose you have cash flows that occur at irregular intervals. The standard IRR function assumes that cash flows o
XIRR function. Suppose you have the following set of cash flows. What is the IRR?

t            Cash flow
2/23/2009       \$     (50,000)
8/24/2009              14,000
3/5/2010                8,000
11/2/2010              23,000
6/5/2011              18,000
12/18/2011             19,000

IRR:                       33.36%

RWJ Excel Tip
To calculate the IRR of the project using the XIRR function, we entered the following:

As with the IRR function, Guess is an argument that will make Excel begin its calculations at the value entered.

NPV Profile

The NPV profile is a graphical representation of the NPV of a project for different interest rates. We will graph the

t           Cash flow
0         \$     (30,000)
1                  8,000
2                10,000
3                11,000
4                17,000
5                12,000
To construct the NPV profile, we will first need a table with the project's NPV at different interest rates:

Return                     NPV
0%              \$        28,000.00
5%                       19,579.81
10%                       12,863.94
15%                        7,436.56
20%                        2,997.69
25%                         (672.64)
30%                       (3,738.05)
35%                       (6,321.92)
40%                       (8,518.47)

NPV Profile
\$30,000

\$25,000

\$20,000

\$15,000

\$10,000
NPV

\$5,000
NPV > 0
\$-
0%           5%             10%    15%           20%
\$(5,000)

\$(10,000)

\$(15,000)
Interest Rate

All project's with conventional cash flows will have a downward sloping NPV profile similar to this.

Loan-Type Cash Flows

The use of the IRR decision rule becomes problematic when the cash flows resemble a loan. Consider the following
t            Cash flow
0          \$      25,000
1                (10,000)
2                (11,000)
3                (12,000)

The IRR of this project is:

IRR:                            14.77%

The IRR decision rule implies that we should accept projects when the IRR is greater than the required return. So, t
than the IRR. However, the NPV profile for the project looks like this:

Return             NPV
0%         \$     (8,000.00)
5%               (4,867.18)
10%               (2,197.60)
15%                   96.57
20%                2,083.33
25%                3,816.00
30%                5,336.82

NPV Profile
\$8,000.00

\$6,000.00

\$4,000.00
IRR
\$2,000.00

\$-
0%            5%        10%            15%
\$(2,000.00)
NPV < 0
\$(4,000.00)

\$(6,000.00)

\$(8,000.00)

\$(10,000.00)
With loan-type cash flows, the NPV increases as the interest rate increases. The standard IRR decision rule cannot b

Multiple IRRs

Excel uses an algorithm to calculate the IRR of a set of cash flows. Because the algorithm always starts at the same
not a problem, but with multiple IRRs we may want to find both IRRs. In this case, we can use the guess argument t
set of cash flows:

t              Cash flow
0          \$       (50,000)
1                   25,000
2                   29,000
3                   41,000
4                   32,000
5                  (35,000)

We would expect two IRRs. Using the IRR function without using the Guess argument, we find:

IRR:                        39.13%

Now we will use the Guess argument to find what the other IRR is:

IRR:                       -44.59%

RWJ Excel Tip
In the second IRR cell, we entered:

We entered -.99 (or -99%) as the starting point for Excel. Why did we enter -99%? It was a guess! But, it also is a lon
had returned the same IRR, we would have tried 99% next.
We can graph the NPV profile for this project as well. The NPV profile will look like this:

Return            NPV
-50%         \$ (164,000.00)
-45%             (7,975.42)                                                     NPV Profile with Multiple
-40%             58,847.74                                                              \$150,000.00
-35%             84,011.95
-30%             89,462.72
-25%             85,720.16                                                              \$100,000.00
-20%             77,954.10
-15%             68,732.59
\$50,000.00
-10%             59,321.92
-5%             50,324.30

Net Present Value
0%             42,000.00
5%             34,433.78             -50%                       -40%   -30%    -20%    -10%
10%              27,622.31
15%              21,520.38                                                               \$(50,000.00)
20%              16,065.46
25%              11,190.40                                                              \$(100,000.00)
30%               6,829.92
35%               2,923.56
40%                (583.09)                                                             \$(150,000.00)
45%              (3,738.29)
50%              (6,584.36)
\$(200,000.00)
55%              (9,158.27)
60%             (11,492.16)

Mutually Exclusive Investments

Even when there is a single IRR, it is not possible to rank projects according to IRR. In other words, the project with
comparing two mutually exclusive investments, we may want to know the crossover rate, that is, the interest rate
have the cash flows for two projects:

t           Investment A    Investment B
0          \$     (100,000) \$     (110,000)
1                  35,000          38,000
2                  29,000          36,000
3                  29,000          30,000
4                  29,000          29,000
5                  20,000          21,000

IRR:                 14.07%                             13.73%
To find the crossover rate, we calculate the incremental cash flows of the larger project, that is, subtract the cash f
project. Notice we used an IF statement to makes sure the cash flows below are always the larger project minus th
larger project are:

Incremental
t                   cash flows
0                 \$      (10,000)
1                           3,000
2                           7,000
3                           1,000
4                               -
5                           1,000

The crossover rate is the IRR of these incremental cash flows, or:

Crossover rate:                              9.36%

We can create a table to show the NPV of each project at different interest rates and graph the NPV profile of each

R                           Investment A   Investment B
0%                         \$ 42,000.00 \$ 44,000.00
5%                              24,217.37      25,071.09
10%                               9,799.07       9,683.70
15%                              (2,044.70)     (2,988.31)
20%                             (11,889.15)    (13,547.45)

Net Present Value for Mutually Exclusive Investmen
\$50,000.00

\$40,000.00

\$30,000.00

Crossover point
Net present value

\$20,000.00
\$11,484.54

\$10,000.00

\$-
0%                  5%              10%
9.36%
9.36%
\$(10,000.00)

\$(20,000.00)
Interest rate

The Modified Internal Rate of Return (MIRR)

Excel does have a built-in function to calculate the MIRR, but we work through the MIRR calculation for each meth
following cash flows, reinvestment rate, and discount rate:

t               Cash flow
0             \$     (12,000)
1                      5,800
2                      6,500
3                      6,200
4                      5,100
5                     (4,300)

Discount rate:                                  11%
Reinvestment rate:                               8%

With the discounting approach, we discount all negative cash flows to the beginning of the project. In order to hav
statement for each cash flow as follows. Notice that the equation at time 0 is not a nested IF, but simply a series of
cash flow if the cash flow is negative, otherwise it returns a value of 0 to be added in to the cash flow. Once we get
of these cash flows.

Discounting approach:
t           Cash flow
0         \$ (14,551.84)
1              5,800.00
2              6,500.00
3              6,200.00
4              5,100.00
5                    -

MIRR:                        23.08%

With the reinvestment approach, we simply find the future value of all cash flows (except the cash flow at time 0) a
two remaining cash flows. Doing so, we find that the MIRR using the reinvestment approach is:

Reinvestment approach:
t           Cash flow
0          \$   (12,000.00)
1                     -
2                     -
3                     -
4                     -
5               24,518.64

MIRR:                      15.36%

We should note that to have Excel accurately calculate the IRR of these modified cash flows, the intermediate cash

For the combination approach, we need to find the present value of all cash flows at time 0, the future value of all
IRR of the modified cash flows. Again, we can use a series of IF statements to test whether each cash flow is negati
approach is:

Combination approach:
t           Cash flow
0         \$ (14,551.84)
1                    -
2                    -
3                    -
4                    -
5             28,818.64

MIRR:                      14.64%

As we mentioned earlier, Excel does have a built-in MIRR function. Using the MIRR function, the MIRR is:

MIRR:                      14.64%

What method is Excel using to calculate the MIRR? Of course, remember that Excel was written by computer progr
more correct, just the method the programmers selected.

RWJ Excel Tip
To use the MIRR function, we entered the following:
The Values are the cash flows, the Finance_rate is the discount rate, and the Reinvest_rate is the reinvestment rate
at results in a zero NPV. Excel's IRR function easily computes the IRR for a series of cash flows.

R. We left the Guess argument blank. Excel uses an algorithm to repeatedly change the interest
ng value for the Guess argument, Excel will begin its calculations at that value. In general, it is
hen you want to find multiple IRRs, the Guess argument can be useful.
unction assumes that cash flows occur at regular intervals. In this case, we need to use the
?

wing:

culations at the value entered.

t interest rates. We will graph the NPV profile of the following project:
different interest rates:

IRR

NPV < 0

25%         30%          35%           40%

nterest Rate

file similar to this.

mble a loan. Consider the following project.
ater than the required return. So, this project looks acceptable for any required return less

NPV > 0

20%            25%             30%
standard IRR decision rule cannot be used in such cases.

lgorithm always starts at the same point, it will always arrive at the same IRR. Generally, this is
e, we can use the guess argument to try to find multiple IRRs. Suppose we have the following

ment, we find:

%? It was a guess! But, it also is a long way away from the first IRR that Excel calculated. If Excel
ke this:

V Profile with Multiple IRRs
\$150,000.00

\$100,000.00

\$50,000.00

\$-
10%            0%    10%     20%       30%        40%       50%

\$(50,000.00)

IRR
\$(100,000.00)

\$(150,000.00)

\$(200,000.00)
Interest rate

R. In other words, the project with the highest IRR is not necessarily the best project. When
over rate, that is, the interest rate that makes the NPV of the two projects equal. Below we
project, that is, subtract the cash flows of the smaller project from the cash flows of the large
always the larger project minus the smaller project. So, the incremental cash flows from the

s and graph the NPV profile of each project. Doing so, we get:

lly Exclusive Investments

rossover point

Investment A
Investment B

15%                       20%
he MIRR calculation for each method manually first. Suppose we have a project with the

ning of the project. In order to have Excel discount only negative cash flows, we can use an IF
t a nested IF, but simply a series of IF statements that calculates the present value of each
ed in to the cash flow. Once we get these modified cash flows, we can simply calculate the IRR

ws (except the cash flow at time 0) at the end of the project and then calculate the IRR of the
nt approach is:
d cash flows, the intermediate cash flows must be entered as 0, not left blank.

ws at time 0, the future value of all positive cash flows at the end of the project, then find the
st whether each cash flow is negative or positive. So, the MIRR using the combination

RR function, the MIRR is:

cel was written by computer programmers, so the method that Excel uses is not necessarily
nvest_rate is the reinvestment rate.
Chapter 8 - Section 5
The Profitability Index

The profitability index is the present value of the future cash flows divided by the initial investment. If you rememb
of future cash flows, so we will use the NPV function divided by the initial investment to calculate the profitability i

Suppose we have a project with the following cash flows and required return. What is the profitability index of the

t            Cash flow
0          \$     (30,000)
1                   8,000
2                 10,000
3                 11,000
4                 17,000
5                 12,000

Required return:                12%

PI:                          1.352
Accept or reject:            Accept
e initial investment. If you remember, the NPV function really only calculates the present value
ment to calculate the profitability index as follows:

hat is the profitability index of the project?
Chapter 8 - Master it!

As you have already seen, Excel does not have a function to calculate the payback period. We have shown thr
numerous other methods as well. Below, the cash flows for a project are shown. You need to calculate the pa

Calculate the payback period in a table. The first three columns of the table will be the year, the cash flow for
a.   show the whole year for the payback. In other words, if the payback period is 3 plus years, this column will ha
the fractional part of the payback period, or else it will display zero. The last column will add the previous two
should also have a cell that displays the final payback period by itself, and a cell that returns the correct accep

Write a nested IF statement that calculates the payback period using only the project cash flow column. The I
b.   no payback period. In contrast to the example we showed previously, the nested IF function should test for th
working towards longer payback periods. Another cell should display the correct accept or reject decision bas

t                 Cash flow
0             \$      (250,000)
1                      41,000
2                      48,000
3                      63,000
4                      79,000
5                      88,000
6                      64,000
7                      41,000

Required payback:                     5
ack period. We have shown three ways to calculate the payback period, but there are
n. You need to calculate the payback period using two different methods:

l be the year, the cash flow for that year, and the cumulative cash flow. The fourth column will
plus years, this column will have a 3, otherwise it will be a zero. The next column will calculate
umn will add the previous two columns and display the final payback period calculation. You
that returns the correct accept or reject decision based on the payback criteria.

roject cash flow column. The IF statement should return a value of "Never" if the project has
ed IF function should test for the payback period starting with shorter payback periods and
ct accept or reject decision based on the payback criteria.
Master it! Solution

a.                Using the table below, calculate the payback period for the project. The cash flows should direct

Cumulative         Year of         Fractional
t             Cash flow        cash flow         payback            year
0
1
2
3
4
5
6
7

Payback period:
Accept or reject:

b.   Using only the cash flow column, write a nested IF statement that calculates the payback period. The stateme

Payback period:
Accept or reject:
ect. The cash flows should directly reference the inputs in the previous worksheet.

Payback
period

the payback period. The statement should show "Never" if the project has no payback period.

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 13 posted: 8/7/2012 language: English pages: 67
How are you planning on using Docstoc?