Microsoft Office Tutorials and References

In Depth Information

Figure 12-10 shows a worksheet that demonstrates the relationship between IRR, NPV, and PV by verifying the

results of some calculations. This verification is based on the definition of IRR: The rate at which the sum of

positive and negative discounted flows is 0.

Figure 12-10:
Checking IRR and NPV using sum of PV approach.

The NPV is calculated in cell B16:

=NPV(D3,B7:B14)+B6

The internal rate of return is calculated in cell B17:

=IRR(B6:B14,–90%)

In column C, formulas calculate the present value. They use the IRR (calculated in cell B17) as the discount

rate, and use the period number (in column A) for the nper argument
.
For example, the formula in cell C6 is

=PV($B$17,A6,0,–B6)

The sum of the values in column C is 0, which verifies that the IRR calculation is accurate.