Questions

Compute IRR and NPV in Microsoft Excel

1.IRR Function

Description:

The Microsoft Excel IRR function returns the internal rate of return for a series of cash flows. The cash

flows must occur at regular intervals, but do not have to be the same amounts for each interval.

Syntax

The syntax for the IRR function in Microsoft Excel is:

IRR(range, [estimated_irr] )

Parameters or Arguments

range A range of cells that represent the series of cash flows.

estimated_irr Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

Example (as Worksheet Function)

Let's look at some Excel IRR function examples and explore how to use the IRR function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above:

This first example returns an internal rate of return of 28%. It assumes that you start a business at a cost of $7,500. You net the following income for the first four years: $3,000, $5,000, $1,200, and $4,000.

This next example returns an internal rate of return of 5%. It assumes that you start a business at a cost of $10,000. You net the following income for the first three years: $3,400, $6,500, and $1,000.

=IRR(B1:B4)

Result: 5%

2.NPV Function

Description

The Microsoft Excel NPV function returns the net present value of an investment.

Syntax

The syntax for the NPV function in Microsoft Excel is:

NPV( discount_rate, value1, [value2, ... value_n] )

Parameters or Arguments

discount_rate The discount rate for the period.

value1, value2, ... value_n The future payments and income for the investment (ie: cash flows). There can be up to 29 values entered.

Note

Microsoft Excel's NPV function does not account for the intial cash outlay, or may account for it improperly depending on the version of Excel. However, there is a workaround.

This workaround requires that you NOT include the initial investment in the future payments/income for the investment (ie: value1, value2, ... value_n), but instead, you need to subtract from the result of the NPV function, the amount of the initial investment.

The workaround formula is also different depending on whether the cash flows occur at the end of the period (EOP) or at the beginning of the period (BOP).

If the cash flows occur at the end of the period (EOP), you would use the following formula:

=NPV( discount_rate, value1, value2, ... value_n ) - Initial Investment

If the cash flows occur at the beginning of the period (BOP), ou would use the following formula:

=NPV( discount_rate, value2, ... value_n ) - Initial Investment + value1

Example (as Worksheet Function)

Let's look at some NPV examples and explore how to use the NPV function as a worksheet function in Microsoft Excel:

This first example returns a net present value of $3,457.19. It assumes that you pay $7,500 as an initial investment . You then receive the following income for the first four years (EOP): $3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.

=NPV(8%, 3000, 5000, 1200, 4000) - 7500

This next example returns a net present value of $8,660.77. It assumes that you pay $10,000 as an initial investment. You then receive the following income for the first three years (BOP): $3,400, $6,500, and $10,000. An annual discount rate of 5% is used.

=NPV(5%, 6500, 10000) - 10000 + 3400

Below is the example used in Chapter 10 PPT.