IRR and NPV demonstrator

Time value of money

This module provides a demonstration of the internal rate of return calculation based on unconventional cash flows. Unconventional cash flows occur when cash flows change sign more than once. This can result in multiple answers.

The Excel IRR function includes an optional argument named Guess, by default set to 10%
It is possible to have more that one solution to the IRR calculation, thus you set Guess to a solution you choose
This occurs when the cash flows are unconventional
Conventional cash flows have only one change in cash flow sign. In project evaluation this means a cash outflow, followed by multiple cash inflows. eg. - + + + + + ...
On the other hand, unconventional cash flows change sign more than once. A company may undertake a project that requires a cash outflow for site restoration and rehabilitation at the end of the project. eg - + + + + -
If there are two changes in sign, then there could be two IRR values
The bottom line. It is possible to have no rate at which the NPV is zero, but if there is an IRR value, then the maximum number of IRR values is equal to the number of changes in sign in successive cash flow values
This point illustrates Descartes rule of signs for solution to polynomials

xlf YouTube channel

Watch the video on the xlf YouTube channel - 0:49 seconds

Notes:

The worksheet used in the demonstration is driven entirely by the keyboard - with NO use of the mouse
To display the Scenario Manager dialog, the ribbon sequence is Data > What-If-Analysis > Scenario
On the keyboard the sequence is Alt A W S as shown by the prompts that appear on the ribbon when the Alt key is pressed. Lower case font a w s can be used

xlf Excel file

Download the file - 36 Kb xlf-npv-irr-demo

Published: 11 March 2015
Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]