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)]