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