Principles of Finance with Excel (Основы финансов c Excel)

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

PFE Chapter 29, Functions in Excel page 13

AB C DEF

Years invested, t 3

Interest rate, r 10%

Number of compounding

periods per year, n

Future

value

1 133.100 <-- =$B$14*(1+$B$16/A19)^($B$15*A19)

2 134.010 <-- =$B$14*(1+$B$16/A20)^($B$15*A20)

3 134.327 <-- =$B$14*(1+$B$16/A21)^($B$15*A21)

4 134.489

5 134.587

6 134.653

7 134.700

8 134.735

9 134.763

10 134.785

20 134.885

As n gets large, this

converges to

134.9859 <-- =B14*EXP(B16*B15)

Future Value as Function of Number

of Compounding Periods per Year

133.00

133.20

133.40

133.60

133.80

134.00

134.20

134.40

134.60

134.80

135.00

01234567891011121314151617181920

Nomenclature: When the number of compounding periods becomes infinite, the

investment is said to be continuously compounded. Otherwise (that is, when there are a finite

number of compounding periods per year), the investment is said to be discretely compounded.

Using Exp to calculate present values

Above we illustrated how $100 grows to 100*Exp(r*t) when it is compounded

continuously for t years at interest rate r. Suppose you’re going to get $100 in 3 years. What is

the present value of this $100 if the relevant interest rate is r? The answer depends on the

number of compounding periods:

•

If the investment is discretely compounded n times per year, then its present value is

100

100* 1

−













PFE Chapter 29, Functions in Excel page 14

• If the investment will be continuously compounded, then its present value is

()

100

100*exp *

exp *

=−

In Excel:

AB C

Discounting--discrete versus continuous

Future value 100

What year received, t 3

Compounding periods per year, n 2

Interest rate, r 10%

Present value, discrete discounting 74.62154 <-- =B44/(1+B47/B46)^(B46*B45)

Present value, continuous discounting 74.08182 <-- =B44*EXP(-B47*B45)

You can use the above spreadsheet to show that as n gets very large, the two values in

B56 and B58 converge. For example, when n = 100:

AB C

Discounting--discrete versus continuous

Future value 100

What year received, t 3

Compounding periods per year, n 100

Interest rate, r 10%

Present value, discrete discounting 74.09293 <-- =B44/(1+B47/B46)^(B46*B45)

Present value, continuous discounting 74.08182 <-- =B44*EXP(-B47*B45)

This function (the “natural logarithm” to differentiate it from the “logarithm base 10” that

you learned in high school) is often used to calculate continuously compounded rates of return.

Suppose you invest in a stock that is worth $25 and suppose that one year later the stock is worth

In this book we’ve used it extensively in the option chapters, ?????.

PFE Chapter 29, Functions in Excel page 15

$40. What rate of return r have you earned? If you use discrete compounding, the rate of return

1 1 60%

=−=−=

Now suppose that your alternative is to earn continuously compounded interest r. Then

the rate of return has to solve the equation

() ()

exp exp

PrP r

=⇒ = .

The function which solves this equation is the natural logarithm ln:







In Excel:

ABC

Price of stock, t=0 25

Price of stock, t=1 40

Discretely compounded rate of return, r 60.00% <-- =B3/B2-1

Continously compounded rate of return, r 47.00% <-- =LN(B3/B2)

USING LN TO COMPUTE CONTINUOUSLY

COMPOUNDED RATES OF RETURN

When t

≠

1 , the problem looks like this:

() ()

exp * exp *

has solution:

PrtP rt

=⇒ =







For example: Suppose you invested in Intel stock on 25 October 1999, buying the stock

for its closing price of $38.6079, and suppose you sold it at the end of the day, 24 July 2000, for

$64.4379. As the calculation below shows, you would have earned a continuously compounded

return of 68.49% on your stock.

PFE Chapter 29, Functions in Excel page 16

ABCD

Intel stock

Purchase date and price 25-Oct-99 38.6079

Sale date and price 24-Jul-00 64.4379

Elapsed time, t 0.7479 <-- =(B9-B8)/365

Continuously compounded rate of return, r 68.49% <-- =1/B11*LN(C9/C8)

Note that this calculation is easier than the calculation of the annualized daily return—it

has one fewer step:

ABCD

Daily return, annualized

Purchase date and price 25-Oct-99 38.6079

Sale date and price 24-Jul-00 64.4379

Elapsed days 273 <-- =(B16-B15)

Daily return 0.1878% <-- =(C16/C15)^(1/B18)-1

Annualized 98.35% <-- =(1+B19)^365-1

A short finance note

We can’t resist a short finance note on the difference between the continuously

compounded annual return of 68.49% and the discretely-compounded annual return of 98.35%.

•

Both of these returns cause $38.6079 to grow over a period of 273 days to $64.4379. So

they’re both—in an economic sense—the same number.

•

The daily returns are very close: The continuously compounded daily return is calculated

365

annual continuousl

compounded return

and the discretely-compounded daily

return is calculated by

273

,273

Stock price day



−





. These numbers are very close:

ABC

Note

Daily, continuously-compounded return 0.1876% <-- =B12/365

Daily, discretely-compounded return 0.1878% <-- =B19

PFE Chapter 29, Functions in Excel page 17

However, when you compound them for 365 days, the differences are very large.

Round, RoundDown, RoundUp, Trunc

The Excel functions Round, RoundDown, RoundUp do exactly what they say. All 3 of

these functions require you to specify the number of decimal places to which you want to round

off the number. The function

Trunc cuts off a number after a specified number of places (if you

do not specify,

Trunc gives you the integer part of a number). Here are examples using the

Excel function

Pi as a basis:

ABC

Number 3.1415926535898 <-- =PI()

Round, no decimal places 3.00000000 <-- =ROUND(B2,0)

Round, 3 decimal places 3.14200000 <-- =ROUND(B2,3)

RoundDown, no decimal places 3.00000000 <-- =ROUNDDOWN(B2,0)

RoundDown, 3 decimal places 3.14100000 <-- =ROUNDDOWN(B2,3)

RoundUp, no decimal places 4.00000000 <-- =ROUNDUP(B2,0)

RoundUp, 4 decimal places 3.14160000 <-- =ROUNDUP(B2,4)

Truncate, no decimal places 3.00000000 <-- =TRUNC(B2)

Truncate, 5 decimal places 3.14159000 <-- =TRUNC(B2,5)

ROUNDING NUMBERS IN EXCEL

There’s a difference between using these functions and merely formatting a number so

that it looks rounded or truncated. Here’s an example:

ABC

Number 4.5632

Rounded to 2 decimals 4.56 <-- =ROUND(B16,2)

Formatted to 2 decimals 4.56 <-- =B16

10 times cell B20 45.6 <-- =10*B17

10 times cell B21 45.632 <-- =10*B18

PFE Chapter 29, Functions in Excel page 18

In cell B21 we used the “decrease decimal” button to change the representation

of the number. However, as you can see in cell B24, this button does not change the number,

whereas

Round actually changes the number.

Sqrt

This function calculates the square root of a number. In this book, we’ve used square

roots to calculate the standard deviation (see Chapter ???) of returns.

ABC

Number 3

Square root 1.732051 <-- =SQRT(B2)

Equivalent way 1.732051 <-- =B2^(1/2)

SQRT

Note that you can use the carat ( ^ ) as an alternative way of calculating the square root.

In Excel’s notation, a^b raises a to the power b (meaning

ab a

). Since a square root is

equivalent to the power ½, you can also use this notation (see cell B4 above).

Sum

The Excel function Sum adds numbers in a range of cells:

15 <-- =SUM(A2:A6)

SUM

PFE Chapter 29, Functions in Excel page 19

SumIf

SumIf allows you to add only numbers that fulfill some specific condition. Here’s an

example in which we add only those scores that are greater than 30:

Score

220 <-- =SUMIF(A10:A14,">30")

The function

SumIf also allows you to have the conditional column some other place. In

the following example, we add the numbers in D10:D14 for which the corresponding number in

E10:E14 is greater than 40 (highlighted here):

DE F

Score 1 Score 2

30 55

50 89

80 22

90 65

20 35

170 <-- =SUMIF(E10:E14,">40",D10:D14)

The function wizard really helps when you use this function. Here it is for the above

example. You’ll notice that

Range is the column of criteria (“Score 2”) and Sum_range is the

column to be added. If you don’t specify

Sum_range, Excel assumes that it’s the same as

Range:

PFE Chapter 29, Functions in Excel page 20

SumProduct

This function pairwise multiplies the entries in two columns and adds the results. It’s

sometimes useful in statistics (do we have an example?). Here’s a simple example that

calculates the expected return of a portfolio. There are four assets, each with a different

expected return. To calculate the expected portfolio return, we have to multiply the expected

return in column B by the portfolio proportion of each asset (column C).

SumProduct does this

nicely:

AB CDEF

Asset

Expected

return

Portfolio

proportion

1 20% 15%

28%22%

3 15% 38%

4 12% 25%

Expected portfolio return

13.46% <-- =SUMPRODUCT(B19:B22,C19:C22)

PFE Chapter 29, Functions in Excel page 21

29.3. Conditional functions

If( ), VLookup( ), and HLookup( ) are three functions that allow you to put in

conditional statements.

The syntax of Excel’s

If statement is: If(condition,output if condition is true, output if

condition is false)

. In the example below, if the initial number in B3 < 3, then the desired output

is 15. If B3 > 3, then the output is 0:

AB C

Initial number 2

If statement 15 <-- =IF(B2<=3,15,0)

THE IF FUNCTION

You can make

If print text also, by enclosing the desired text in double quotes:

AB C

Initial number 2

If statement Less than or equal to 3 <-- =IF(B5<=3,"Less than or equal to 3","More than 3")

VLookup and HLookup

Since VLookup( ), and HLookup( ) both have the same structure, we will concentrate on

VLookup( ) and leave you to figure out HLookup( ) for yourself. VLookup( ) is a way to

introduce a table search in your spreadsheet. Here is an example: Suppose the marginal tax

rates on income are given by the table below—for income less than $8,000, the marginal tax rate

is 0%; for income above $8,000, the marginal tax rate is 15%, etc. Cell B9 illustrates how the

function

VLookup is used to lookup the marginal tax rate.

PFE Chapter 29, Functions in Excel page 22

AB C

Income

Tax

rate

00%

8,000 15%

14,000 25%

25,000 38%

Income 15,000

Tax rate 25% <-- =VLOOKUP(B8,A3:B6,2)

VLOOKUP FUNCTION

The syntax of this function is

VLookup(lookup_value,table,column). The first column

of the lookup table, A3:A6, must be arranged in ascending (increasing) order. The

lookup_value ( in this case the income of 15,000) is used to determine the applicable row of the

table. The row is the first row whose value is < the lookup_value; in this case, this is the row

that starts with 14,000. The

column entry determines from which column of the applicable row

the answer is taken; in this case the marginal tax rates are in column 2.

Here’s the Excel function wizard for this table: