Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

798 Chapter 33

(Cell C7 initially showed a date, but was then reformatted with

Format|Cells|Number|General.)

You can also add a number to a date to ﬁ nd another date. What, for

example, was the date 165 days after November 16, 1947?

CDE

16-Nov-47

29-Apr-48 <

=C11+165

33.3.1 Stretching Out Dates

In the two cells that follow we’ve put in two dates and then “stretched”

the cells out to add more dates with the same difference between

them:

BCDE

2-Dec-00

8-Mar-99

Days between 635 <

=C5-C6

Write in two dates; mark

both cells.

Grab the handle (arrow on

previous drawing) and pull.

The result: More dates added

with same spacing (in this

case, six months).

Hours, minutes, and the like can also be typed into a cell. In the next cell

we’ve typed in 8:22:

799 Excel Functions

When we hit Enter, Excel interprets this entry as 8:22 a.m.:

Excel recognizes 24-hour times and also recognizes the symbol a for

a.m. and p for p.m.:

As entered

Note that the

p is separated from the time by a

space. (Of course

A.M. is represented by an a.)

When you hit

Enter

EXCEL RECOGNIZES 24-HOUR CLOCK

As entered When you hit Enter

800 Chapter 33

You can subtract times just as you subtract dates; cell B5 tells you that

7 hours and 32 minutes have elapsed between the two times (ignore the

“AM” in B5):

BCD

3:48 PM

8:16 AM

7:32 AM <

=B3-B4

When you reformat these cells with Format|Cells|Number|General,

you can see that times are represented in Excel as fractions of a day:

BCD

0.658333

0.344444

0.313889 <

=B3-B4

If you type in a date and a time and reformat, you can see the same

thing:

Here’s what you typed: Here’s how it appears:

If you reformat this to

General:

801 Excel Functions

33.3.3 Time and Date Functions in Excel

Excel has a whole set of time and date functions. Here are several func-

tions that we ﬁ nd useful:

•

Now reads the computer clock and represents the date and the

time. Now takes no arguments and is written with empty parentheses:

Now( ).

•

Today reads the computer’s clock and prints the date. This function,

like Now is written with empty parentheses: Today( ).

•

Date(yyyy,mm,dd) gives the date entered.

•

Weekday gives the day of the week.

•

Month gives the month.

Here are the ﬁ rst three functions in a spreadsheet:

CBA

Serial representation Date/time format

39339.48933 9/14/2007 11:44 <-- =NOW()

39339 9/14/2007 <-- =TODAY()

36245 3/26/1999 <-- =DATE(1999,3,26)

Different formatting of Now()

September 14, 2007 <-- =NOW()

9/14/2007 11:44 <-- =NOW()

11:44 AM <-- =NOW()

When was day 1?

1 <-- =DATE(1900,1,1)

FORMATTING NOW(), TODAY(), DATE()

The use of Weekday and Month is self-explanatory:

AB C

3-Nov-01 7 <

=WEEKDAY(A3)

=WEEKDAY("3nov2001")

Weekday

, 1=Sunday, 2=Monday, etc.

11 <

=MONTH(A3)

12 <

=MONTH("22dec2003")

802 Chapter 33

33.3.4 Calculating the Diference between Two Dates: The Function Datedif

The Excel function Datedif computes the difference between two dates

in various useful ways:

AB C

Date1 3-Apr-47

Date2 22-Dec-02

Explanation

55 <

=DATEDIF(B2,B3,"y") Number of years between dates

668 <

=DATEDIF(B2,B3,"m") Number of months between dates

20352 <

=DATEDIF(B2,B3,"d") Number of days between dates

19 <

=DATEDIF(B2,B3,"md") Number of days in excess of full number of months

=DATEDIF(B2,B3,"ym") Number of months in excess of full number of years

263 <

=DATEDIF(B2,B3,"yd") Number of days in excess of full number of years

DATEDIF COMPUTES DIFFERENCE BETWEEN TWO DATES

If Date1 is the author’s birth date and Date2 is today, then the author

is currently 55 years and 263 days old (cells A6 and A11).

33.4 The Functions XIRR and XNPV

The functions XIRR and XNPV calculate the internal rate of return

and the net present value for a series of cash ﬂ ows received on

speciﬁ c dates. They are especially useful for calculating IRR and NPV

when the dates are unevenly spaced.

If you do not have these functions,

you will have to activate Tools|Add-ins and then click on Analysis

ToolPak:

2. Excel’s IRR function assumes that the ﬁ rst cash ﬂ ow occurs today, the next cash

ﬂ ow occurs one period hence, the following cash ﬂ ow two periods hence, and so

on. Excel’s NPV function assumes that the ﬁ rst cash ﬂ ow occurs one period from

now, the next cash ﬂ ow in two periods, and so on. We call this pattern “even

spacing of cash ﬂ ows.” When this is not the case, you’ll need the XIRR and XNPV

functions.

803 Excel Functions

33.4.1 XIRR

Here’s an example: You pay $600 on 16 February 2001 for an asset that

repays $100 on 5 April 2001, $100 on 15 July 2001, and then $100 on every

22 September from 2001 until 2009. The dates are not evenly spaced, so

that you cannot use IRR. With XIRR (cell B16 in the next spreadsheet),

you can compute the annualized IRR.

AB C

Date Payment

16-Feb-01 -600

5-Apr-01 100

15-Jul-01 100

22-Sep-01 100

22-Sep-02 100

22-Sep-03 100

22-Sep-04 100

22-Sep-05 100

22-Sep-06 100

22-Sep-07 100

22-Sep-08 100

22-Sep-09 100

XIRR

21.97% <

=XIRR(B3:B14,A3:A14)

THE EXCEL FUNCTION XIRR

804 Chapter 33

The XIRR works by discounting each cash ﬂ ow at the daily rate. In

our example the ﬁ rst cash ﬂ ow of $100 occurs 48 days from now, the

second in 149 days, . . . . The XIRR transforms 21.97 percent to a daily

rate and uses it to discount the cash ﬂ ows:

−+ + ++600

100

1 2197

100

1 2197

100

1 2197

48 365 149 365 3

(. ) (. )

...

(. )

// 1140 365

AB C D E

Date Payment Days from initial date

Present

value

16-Feb-01 -600 -600.00

5-Apr-01 100 48 97.42 <

=B4/(1+$B$16)^(C4/365)

15-Jul-01 100 149 92.21 <

=B5/(1+$B$16)^(C5/365)

22-Sep-01 100 218 88.81 <

=B6/(1+$B$16)^(C6/365)

22-Sep-02 100 583 72.81

22-Sep-03 100 948 59.70

22-Sep-04 100 1,314 48.91

22-Sep-05 100 1,679 40.10

22-Sep-06 100 2,044 32.88

22-Sep-07 100 2,409 26.96

22-Sep-08 100 2,775 22.09

22-Sep-09 100 3,140 18.11

XIRR

21.97% <

=XIRR(B3:B14,A3:A14) 0.00 <

=SUM(D3:D14)

HOW DOES XIRR WORK?

XIRR computes the daily internal rate of return

Cell C4 contains the

formula=A4-$A$3

33.4.2 XNPV

The function XNPV computes the NPV for unevenly spaced cash ﬂ ows.

In the next example we use the function to compute the NPV on the

same example we used for XIRR.

805 Excel Functions

Notice that XNPV requires you to indicate all the cash ﬂ ows (starting

with the initial cash ﬂ ow), as opposed to NPV, which starts from the ﬁ rst

cash ﬂ ow.

33.5 Statistical Functions

Excel contains a number of statistical functions. We illustrate these func-

tions using the following data set:

AB C

Date Payment

16-Feb-01 -600

5-Apr-01 100

15-Jul-01 100

22-Sep-01 100

22-Sep-02 100

22-Sep-03 100

22-Sep-04 100

22-Sep-05 100

22-Sep-06 100

22-Sep-07 100

22-Sep-08 100

22-Sep-09 100

Discount rate 15%

XNPV 97.29 <

=XNPV(B16,B3:B14,A3:A14)

THE EXCEL FUNCTION XNPV

806 Chapter 33

The functions Varp and Stdevp calculate the population variance and

standard deviation, whereas the functions Var and Stdev calculate the

sample variance and standard deviation. The difference between these

two functions is that Varp assumes that your data include the whole

population and thus divides by the number of data points, whereas Var

assumes that the data are a sample from the distribution:

Varp( ) Average

Stdevp(

xxx

Ni N

,..., [ ( ,..., )]

=−

∑

..., ( ,..., )xxx

) Varp=

ABCD

Observation

1 35.30 10.98

2 29.70 11.13

3 30.80 12.51

4 58.80 8.40

5 61.40 9.27

6 71.30 8.73

7 74.40 6.36

8 76.70 8.50

9 70.70 7.82

10 57.50 9.14

Average 56.6600 9.2840 <

=AVERAGE(C3:C12)

Sample variance 334.1493 3.2342 <

=VAR(C3:C12)

Population variance 300.7344 2.9108 <

=VARP(C3:C12)

Sample standard deviation 18.2798 1.7984 <

=STDEV(C3:C12)

Population standard deviation 17.3417 1.7061 <

=STDEVP(C3:C12)

Correlation -0.9049 <

=CORREL(C3:C12,B3:B12)

Covariance -26.7746 <

=COVAR(C3:C12,B3:B12)

Regression intercept 14.3285 <

=INTERCEPT(C3:C12,B3:B12)

Regression slope -0.0890 <

=SLOPE(C3:C12,B3:B12)

Regression r-squared 0.8189 <

=RSQ(C3:C12,B3:B12)

BASIC STATISTICAL FUNCTIONS

3. We cannot resist a quote from Numerical Recipes, a wonderful book by W. H. Press,

B. P. Flannery, S. A. Teukolsky, and W. T. Vetterling (Cambridge University Press, 1986):

“There is a long story about why the denominator [of Var] is N − 1 instead of N. If you

have never heard that story, you may consult any good statistics text. Here we will be

content to note that the N − 1 should be changed to N if you are ever in the situation

of measuring the variance of a distribution whose mean x¯ is known a priori rather than

being estimated from the data. (We might also comment that if the difference between

N and N − 1 ever matters to you, then you are probably up to no good anyway—e.g.,

trying to substantiate a questionable hypothesis with marginal data.)”

807 Excel Functions

Var( ) Average

Stdev(

xxx

Ni N

,..., [ ( ,..., )]

−

∑

..., ,...,xxx

)Var( )=

Here’s an example, showing both the brute-force calculations and the

Excel functions Var and Varp.

AB C D

Observation X (X

- average)

1 35.30 456.2496 <

=(B3-$B$14)^2

2 29.70 726.8416

3 30.80 668.7396

4 58.80 4.5796

5 61.40 22.4676

6 71.30 214.3296

7 74.40 314.7076

8 76.70 401.6016

9 70.70 197.1216

10 57.50 0.7056

Average 56.66 <

=AVERAGE(B3:B12)

Var 334.1493 <

=VAR(B3:B12)

334.1493 <

=SUM(C3:C12)/9

Varp 300.7344 <

=VARP(B3:B12)

300.7344 <

=SUM(C3:C12)/10

VAR VERSUS VARP

33.5.1 Covar and Correl

These two functions—used extensively in the portfolio chapters, 8–13—

are used to compute the covariance and correlation of two series of

numbers. For the deﬁ nitions, we refer you to section 8.2. See the follow-

ing example, in which we compute the covariance and correlation for

returns on McDonald’s stock and Wendy’s stock. Note the two com-

putations for the correlation. In the ﬁ rst (cell B33) we use the Excel

Correl function; in cell B34 we use the deﬁ nition correlation =

Correlation(MCD,WEN) = Covar(MCD,WEN)/(σ

MCD

WEN

). Because

Excel’s Covar is the population covariance (that is, divides by 1/M, where