Microsoft Office Tutorials and References

In Depth Information

**Chapter 29: NPV and IRR—Evaluating Capital Investments**

your initial investment is shown as a negative value) once you complete your func-

tion. The function used by Excel is:

X

n

values
i

NPV

¼

ð

29

:

2

Þ

i

ð

1

þ

rate

Þ

i

¼

1

You will have to enter the discount rate and the cash flows. Cash flows should be

timed equally to occur at the end of each period.

An Example Using the NPV Function
Open the NPV sheet for the Chapter 29 file/

workbook to see the project examples (Figure 29.1). We have three different projects

with an initial investment of $5,000. The discount rate is in cell G1. Using Create

Name from Selection, I named the cell Rate.

I chose to first use the formula (29.1) to calculate the NPV. As you see in columns

J, K, and L, after calculation of the NPV, the result is $7,164.66. If you add to it the

initial investment, the result is $2,164.66.

NPV

¼

5000

þ

1000

=ð

1

þ :

12

Þ

1

þ

2000

=ð

1

þ :

12

Þ

2

þ

3000

=ð

1

þ

1

:

2

Þ

3

þ

4000

=ð

1

þ :

12

Þ

4

¼

2,164.66

Using the Excel NPV function, we select the Rate in the first field of the Function

Arguments menu, and enter the cash flows range B3:B6 in the Value 1 field (See

Figure 29.2). The result is the same as in the formula calculations.

FIGURE 29.1
Calculating the NPV with Formulas

FIGURE 29.2
The Excel NPV Function