<<

. 20
( 62 .)



>>

u

portion of the text consisting of the number of characters
defined. ¼LEFT(˜˜Good Morning™™,7) will return
˜˜Good Mo.™™




TLFeBOOK
Your Model-Building Toolbox: Functions 103




RIGHT(Text, number of characters) will return the rightmost
u

portion of the text, consisting of the number of characters.
¼RIGHT(˜˜Good Morning™™,7) will return ˜˜Morning.™™
MID(text, start number, number of characters) will return a
u

portion of the text from the letter of the start number. The
portion will have the length of the number of characters.
¼MID(˜˜Good Morning”,2,5) will return ˜˜ood M.™™
LEN(text) returns the number of characters in the text.
u

LEN is short for ˜˜length.™™ ¼LEN(˜˜Good Morning™™) will
return 12.
LOWER(text) will return the text all in lowercase.
u

¼LOWER(˜˜Good Morning™™) will return ˜˜good morning.™™
UPPER(text) returns everything in uppercase.
u

¼UPPER(˜˜Good Morning™™) will result in ˜˜GOOD
MORNING.™™
PROPER(text) will return text with the first letter of
u

each word in uppercase and the remaining letters in
lowercase. ¼PROPER(˜˜goOd moRNing™™) will return
˜˜Good Morning.™™
TEXT(value, format) is a useful function for creating
u

dynamic labels that include values. Excel is able to handle
text strings and values together, but if you want the values
to carry a particular format when you want to show the
two together, you must use TEXT and define the format of
how that value will appear.
¼TEXT(1.386, ˜˜$0.00™™) will return ˜˜$1.39.™™ Note
that the conversion also includes a rounding effect. The
˜˜$1.39™™ is now a text string, so that you can link it to
other text strings by using the ampersand (&) symbol.
Amazingly, you can still apply an operation to this text
string so that it still performs as a value, but the format
does not work on the result. TEXT(1.386, ˜˜$0.00™™) * 2
will return ˜˜2.78,™™ not ˜˜$2.78.™™ The rounding effect in
TEXT also causes 1.386 to become 1.39.
VALUE(text) converts the text of a number into the value of
u

that number. However, you do not really need this
function as Excel can convert text to values as necessary.




TLFeBOOK
Chapter 5
104




Financial Functions: NPV, XNPV, IRR, XIRR
NPV (net present value) and IRR (internal rate of return) are the
necessary functions to know when you start working with cash
flows and estimating the returns of various projects. Both these
functions deal with the time value of money. Time value of money
is a way of saying that a dollar today is not worth the same value
as a dollar will be in the future, or as it was in the past. No, we
are not talking about inflation here. We are talking about the fact
that money can earn interest.
Let™s take a brief detour: Let™s say that we can earn 5 percent
interest every year on our dollar. Thus, our $1.00 today will
be worth $1.05 next year (the arithmetic is simple: $1 ‚ 1.05 ¼
$1.05), and $1.1025 ($1.05 ‚ 1.05) a year after that. By the
same token, to have our $1.00 today, we actually needed to
have only $0.9524 last year ($1.00 divided by 1.05. The proof
that we have the right answer is $0.9524 ‚ 1.05 ¼$1.00) and
$0.9070 two years ago. The important point to remember is that
the following values:


2 Years Ago Last Year Now Next Year 2 Years Later
$0.9070 $0.9524 $1.0000 $1.0500 $1.1025



represent the same time value of $1.00. So to rephrase what we said
in the previous paragraph, a dollar today is the same as some-
thing more than a dollar in the future, and something less than a
dollar in the past. The term for adjusting the $1.00 across time to
become a higher future number is called future valuing; reducing
it by going backward in time is present valuing. Present valuing is
also called discounting, although you can use it to describe future
valuing by saying ˜˜discounting forward.™™ We used the idea of
an interest rate above, but the rate for looking at time value of
money is usually called the discount rate.
Back to our functions. You should keep in mind the follow-
ing points.
If you are dealing with annual periods, the NPV and IRR
functions will work perfectly for your calculations. These
functions use the spacing of the columns, with data in each




TLFeBOOK
Your Model-Building Toolbox: Functions 105




column regarded as one year™s data, as the annual timing for the
calculations. If you have a year where there are no flows and you
want these functions to include that year, you must have a 0
there. NPV and IRR disregards blanks. However, if you are deal-
ing with uneven flows”they do not happen every year, or they
happen at irregular intervals across the years, or both”then you
should use the XNPV and XIRR functions. These more powerful
functions look not just to the flows in each column, but also to
the date labels in each column, and calculate the results based on
the time intervals.
NPV and IRR are in the standard set of functions in Excel.
However, XNPV and XIRR are part of the extended set of func-
tions that are available only when the Analysis ToolPak Add-In
is enabled. You can check whether it is enabled or not by the
sequence Tools > Add-Ins.
NPV(rate,value1,value2,. . .) returns the net present value
u

of the annual flows represented by the values. These
values can be positive or negative numbers, representing,
by the usual convention, inflows and outflows of
cash, respectively. Instead of individual values,
you can use a range of values. Rate is the annual
discount rate.
Be very clear about the timing of the discounting
when you use the NPV function, because the values are
assumed to occur at the end of each year. So you have to
be careful about whether you want to discount the first
value in the function. In the following illustration, cell B6™s
NPV of 151.34 shows the result of the first value of (100)”
representing an investment outflow of 100 at the end of
2004”being part of the NPV function. This means that it
is also being discounted at 10.00 percent (entered in cell
B1). Thus, this shows the NPV as of the beginning of 2004
for the cash flows that occur at the end of the year for the
period 2004“2008.
In contrast, the formula for cell B9 shows that the
first value in cell B4 is simply added to the columns C to F
that is part of the NPV function. Thus, the (100) is not
being discounted at all, and cell B9™s value of 166.48




TLFeBOOK
Chapter 5
106




represents the NPV of the flows at the end of 2004, at the
time the investment is being made.




A necessary simplification with the use of the NPV
function is the fact the cash flows are assumed to occur at
the end of the year. Let™s consider what this means. In the
example we have been looking at, each of the inflows
from year 2005 onward are recognized as if they suddenly
appeared on December 31 of each year. Realistically, this
is not so as a project produces cash flows throughout
the whole year. Thus, a more accurate conceptual
representation would be to recognize them at the average
of their individual timings, i.e., at the middle of the year.
If we want to find out the NPV of the cash flows as of
December 31, 2004, the 50 flow we see for 2005 should
really be discounted only 0.5 years, rather than 1 year.
Likewise, all the other flows should be discounted at 1.5,
2.5, and 3.5 years. The undiscounted starting investment
value of (100) aside, the NPV function as it is being used
means that we have an NPV of the 2005“2008 flows as of
June 30, 2003.
In this case, if we agree that:
The initial investment outflow is December 31, 2004, and
u

Project inflows should be recognized at the middle
u

of the year, then we can make an adjustment to our
NPV calculation by future valuing the NPV part half
a year. The formula multiples the NPV(B1,C4:F4)
part by (1 þ B1) ^ 0.5. B1 contains the discount rate,




TLFeBOOK
Your Model-Building Toolbox: Functions 107




and the ^0.5 is an exponent for the half year. The final
NPV value of 179.48 is larger than the 166.48, reflecting
the fact that there has been less discounting done on the
flows for this third NPV value.




XNPV(rate,values,dates) returns the net present value
u

occurring at the dates specified.




TLFeBOOK
Chapter 5
108




The illustration shows a comparison between XNPV
and NPV. In the first XNPV on row 17, the result 166.45
represents the net present value of the flows based on an
investment outflow of (100) at the end of 2004, and the
subsequent year-end inflows. This is very close to the
second NPV we did; the discrepancy comes from the
more exact count of days that the XNPV has. Note also
that we do not have to exclude (100) from the function
because XNPV associates the flows with dates. It regards
the first flow as occurring at the time of the XNPV, so
there is no discounting of that.
The second XNPV example, on row 21, uses the same
cash flow but a different dating for the post-December
2004 columns. So with this function, you can change the
discounting intervals to use the midyear recognition by
changing the dates that the function is reading. The result
of 179.57 is virtually identical to the NPV calculation on
row 12; the slight difference is due to the different count
of days that XNPV is using.
The dates in XNPV can have irregular intervals of
months or years.
IRR(values,guess) returns the internal rate of return on the

<<

. 20
( 62 .)



>>