This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference text "A fundamental principle of corporate finance is that management should maximise Net Present Value (NPV), NPV being the difference between the financial market price of an opportunities cashflows, and the manager’s cost of undertaking that opportunity. Investors and owners want management to maximise NPV as this maximises their own wealth."
Copeland and Antikarov have noted that NPV is the single most widely used tool for major investments by corporations, the usage in the US having risen from 19% in 1958 (100 corporations) to 86% by 1978 (424 corporations). Whilst the most common application of NPV (and usage in Excel) is discounted cashflow analysis for now or never no flexibility (NONNF) opportunities, NPV can also be calculated using real option analysis in situations where management has deferral/expansion decision making ability.
Another common reported financial metric for an opportunity is Internal Rate of Return (IRR).
Excels NPV and IRR functions
A quickly summary in descending chronological order of the NPV and IRR functions available in Excel.
Excel 2010 offers the following formulae as built-in functions.
NPV – Returns the net present value for evenly staged cashflows (measured as at the end of the period).
XNPV - Returns the net present value for a schedule of cash flows that is not necessarily periodic.
IRR - Returns the internal rate of return for evenly staged cashflows.
XIRR - Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
MIRR – The modified rate of return includes an assumption for a separate re-investment amount for liberated cashflows.
NB: Microsoft has implemented a new algorithm
for IRR in xl2010. This update can produce different IRR results than earlier Excel versions. Different IRR result, same data, Excel 2007 vs 2010
These five functions are implemented in the same manner as xl 2010.
XIRR and XNPV are not built-in functions, they need to be enabled via checking the Analysis Toolpak addin (Tools – Addins – check Analysis Toolpak).
The other three functions are included as native functions inside Excel.
Calculating NPV in Excel
There are three commonly used methods for calculating NPV in Excel:
Calculation of manual PV by period
My advice is to always use the manual method and then double check this calculation with XNPV as:
- These two methods handle irregular timing periods for cashflows more straightforwardly than the NPV function.
- The manual approach forces modellers to think about when projects cashflows should be modelled as occurring (beginning or end of period, averaged at mid-period etc) and how far each period is away from the valuation date.
- I find this method also provides clarity as to how to deal with an initial part period - for example should a portion of the first period cashflow be excluded if this portion precedes the valuation date.
- A period by period approach provides insight as to how NPV is derived over time, highlighting possible inconsistencies, or perhaps opportunities in a deal sense (i.e. does all the valuable component happen in the short term).
- The NPV formula ignores
blank cells and text. I have seen this impact reported values before, in one case the modeller had taken a NPV over a range that began with three empty cells (representing a project delay), and the NPV formula simply ignored these 3 blanks cells and took the 4th year as being period 1. Whereas XNPV spits out an error if any cashflow that should match a date is left blank.
The example below shows the NPV function applied to end-period cashflows.
The distance of the periods from the valuation date follows an integer pattern expected for end period discounting, 1.0, 2.0, 3.0 …. X, X+1 etc.
Cashflows normally occur continuously over time, so for these cases mid-point discounting is applied to better approximate average cashflow timing over each period. Multiplying the NPV by the square root of (1+Discount Rate) converts the end-period NPV to mid-period NPV.
The distance of the periods from the valuation date when mid-point discounting (for a full first period) are 0.5, 1.5, 2.5 …. X.5, X+1.5 etc. Some modellers may get used to the repetitive +1 period pattern exhibited in these first two example, alert readers would have picked up the implication from the early caveat re a full first period that this is not always the case – it commonly isn’t.
A common issue when calculating NPV is the need to cater for a partial first period. For example a company may forecast cashflows in financial years from 1 July to 30 June but then look to calculate value mid-way through the financial year (1 Jan).
Initial part periods raise two issues:
1) Excluding cashflows that occurred prior to the valuation date if these cashflows are either accounted for elsewhere (for example as cash on the balance sheet), or if these cashflows would not accrue to the entity undertaking the valuation (for example an acquirer may only have access to the cashflows after the valuation date).
2) The mid-point timing of both the first and second periods will change from the “normal” 1 year gap sequence in full period forecast models . In the example below the time to the mid-point of the partial first period is 3 months (0.25 years) and the time to the mid-point of the second period is 1 year. From this point on the 1 year gap repeats.
As per my initial comments in this section I prefer to:
- Use an actual date as the mid-period timing which also sets up the model to calculate XNPV in addition to the manual method. I use the DATE function to derive the midpoints of the full periods given that the model will be built either in calendar years (making 1-July a suitable midpoint) or financial years (1-Jan).
- Use YEARFRAC to calculate the distance of each midpoint date from the valuation date. Please note that YEARFRAC provides 5 calculation variants (see Excel help for the details), I prefer 3 which works on actual days in the year/365.
- Ensure that a partial first period is addressed properly (for example excluding cashflow prior to the valuation date).
- Check that the manual method (which uses actual days in the year/365 days) and XNPV (which somewhat confusingly both includes the impact of leap years by using total day count, but then normalises to a 365 day year - see figure below) are within .1% or so (there may be very minor differences depending on how any partial first portion cashflow is backed out of both methods)
Some people prefer to use “month end” measurement for valuation (30-Jun and 31-Dec etc) rather than the “month beginning factors” that I have used above. This has no significant impact on the valuation (Note that as there are more days in July-Dec than Jan-Feb, 31-Dec is slightly more accurate than 1-Jan as a true half year measurement)
The attached sample file (graphic above) shows all the three different NPV methods applied to a partial first period that needs adjusting to remove cashflow that occurs prior to the valuation date.
Calculating IRR Excel
If you asked any analyst what IRR is, then an automatic response would be along the lines of “it is discount rate at which NPV = 0”. If you followed up with “Great. So then how should I use IRR to inform decision making ” then more than likely you will get some blank looks or blustering, perhaps some commentary that the projects should be progressed if they exceed a hurdle rate, hopefully a response that you should rely on NPV. But if it is unclear as to how IRR should be used for a decision then the next question is “So why calculate it in the first place?” More on IRR’s limitation shortly.
Excel offers three native IRR functions
The application of Excel’s IRR function’s usefulness is limited; it is suitable only for evenly spaced end-period cashflows (more on IRR as an overall technique later). This is because IRR is solved by an iterative process which means that unlike NPV, IRR cannot be adjusted algebraically to cater for mid-period discounting and/ or an initial partial period cashflow.
XIRR provides the means to apply an internal rate or return calculation for cashflows occurring at specific dates. A model that has been configured with date ranges for the XNPV function is immediately ready to apply XIRR.
Excel also offers a MIRR function which allows users to vary the re-investment assumption, although like IRR it is designed for evenly spaced end-period cashflows. MIRR is interesting as improves on the standard IRR function by separating project returns from the re-investment, but I have never seen it quoted for an actual investment decision.
David Hager overcomes the end period limitation of MIRR with this VBA User Defined Function
that produces a XMIRR.
Please note that IRR and XIRR samples are included in the attached worbook available at the end of this Article.
IRR – Irrationally Reported Return?
1] IRR measures return, not return against specific project risk (or stated another way, with no reference point to purchasing the projects cashflows in the market). This is inherently accounted for when calculating NPV.
2] IRR is solved by an iterative process, it cannot be calculated algebraically.
3] Actual returns do not match the internal rate of return unless the liberated cashflows can be re-invested elsewhere at the IRR rate.
4] Can easily be distorted by making non-value changing quasi financing decisions such replacing up front capital spending with ongoing leasing payments.
5] Assorted Issues
- requires at least one cashflow change before a calculation is possible.
- has no scale for positive returns, and beyond a certain point (which seems to have increased in Excel 2010) can’t differentiate between the size of negative returns
- can provide as many answers as there are cash flow changes.
- can return very positive results even if total cashlow …. let alone NPV …. is negative.
Expanding on the first four of these points in more detail:
1] IRR does not inherently adjust for specific project risk . This is inherently accounted for when calculating NPV
Consider a word where management have two mutually exclusive opportunities to invest their shareholders last $100.
The first opportunity returns identical cashflows in every conceivable macro-economic state. It offers an 8% nominal IRR.
The second opportunity is a communications company looking to build a fibreoptic broadband network in central Africa. This project is unlikely to make a profit for at least 10 years, faces possible expropriation, and is reliant on massive spending elsewhere on new infrastructure and significant improvement in local health conditions. It offers a 10% nominal IRR on a weighted scenario basis.
Which opportunity would you pick?
IRR does not distinguish between the fundamentally different risk profiles of these two opportunities. The first project is akin to investing in treasury bonds, it is a risk free investment which would be recognised in a NPV analysis by use of an appropriate risk free discount rate (perhaps 5% nominal). The second project is clearly high risk and speculative and would command a far higher discount rate (perhaps 12% nominal in which case this project would actually be NPV negative. NPV compares the returns from each project to the cost of buying these cashflows in the market – IRR makes no allowance for the market costs.
2]IRR is solved by an iterative process, it cannot be calculated algebraically
This makes the IRR calculation inflexible whereas the NPV function can be readily adapted for part periods and mid-point discounting. It also means that IRR answers may vary – as they can do in xl2010 compared to earlier versions.
3] Actual returns do not match the internal rate of return unless the liberated cashflows can be re-invested elsewhere at the same rate as the original project IRR
The example below shows the re-investment assumption necessary for IRR to match annual returns.
This example also picks up on two of the comments from point 5 namely IRR:
- can return very positive results even if total cashflow …. let alone NPV …. is negative
- can provide as many answers as there are cashflow changes. The figure below was generated using Excel’s Table function to solve IRR’s “roots” for 1c graphically, it is apparent that a second discount rate of 190% also produces NPV equal to 0 (NB if users enter a “guess” in the IRR formula then other results may be shown in preference to the default result depending on how close the guess is to the other solution roots).
4] Can easily be distorted by making non-value changing quasi financing decisions such replacing up front capital spending with ongoing leasing payments
Whew – that was a little wordy, please explain!
Projects often contain sub project decisions. A common example is the initial capital spend on a new coal mine where the project team may either choose to purchase and then operate the mobile mine equipment (shovels and trucks), or pay a mining contractor a leasing charge and operating cost to run the mobile equipment instead.
Three quick points on the leasing decision:
- It should be NPV neutral or close to it unless the contracting company can bring operator expertise that the company cannot deploy internally.
- If the project is short life and/or marginal the company may wish to out source risk to the contractor who receives a commensurate profit margin in return (else the company bears the risk of re-deploying labour and equipment).
- It is quasi financing as the leasing charges will reflect the contractors cost of debt finance on the equipment. Please note that it is an investment decision should always be considered prior to any financing decision.
The snapshot below shows the pure owner/operate investment case for a simplified coal mine example (ignoring tax, depreciation, royalties etc). Initial capital of US$1000M halfway through FY12 delivers flat nominal profits of US$220M nominal for the next 8 years. This produces a project NPV of US164.6M at a 10% discount rate (mid-point basis) and a XIRR of 14.6%.
The management team have decided to use a mining contractor given the relatively short life of the deposit. Using the contractor saves US$200M up front but adds an incremental US$38M pa in operating costs. Assuming that the appropriate discount rate for evaluating the mobile equipment lease option is also 10% (note that in practice this sub-decision may have a different discount rate to the overall coal project) then this owner/operate versus lease decision is clearly NPV neutral to the mine management.
So how does our revised investment of US$800M (100M-200M) for annual cashflows of US$182M (220M-38M) look?. The NPV is the same as the initial owner operate decision at US$M164.6M – which is no surprise as the project has the same fundamentals as before, and we know management is indifferent to owner operator versus leasing. However the IRR has jumped from 14.6% to 15.7%
The IRR is higher in this case as the project returns are being evaluated against a smaller capital base.
A good question is what would have happened had the mobile equipment contractor received a 14.6% IRR on the lease (the same return as the project) rather than 10.0%. Under this scenario the final IRR for the revised investment for a $800M capital spend would still be 14.6% - but the overall NPV would have been reduced from US$164.6M to US$132.1M. The project has been reduced in value even though IRR has remained constant.
This example provides an example of how to negotiate with a counterparty that is IRR - but not NPV - focused. A credible case can be fashioned without resorting to multiple IRR solutions by:
- minimising the counterpary's initial investment by structuring the investment to return cashflow from the counterparty to you later in time (like the mobile equipment example)
- looking to return a large positive cashflow to the counterparty early in the investment lifecycle
Proponents of IRR
IRR has a significant supporter base. I have often heard comments along the lines of “I know IRR is flawed and we should use NPV for investment decisions but the Board/CFO/CEO/boss likes it”.
The other main defence of IRR is that it does have value in being used in conjunction with NPV. A Google search of “IRR criticism” produces this spirited defence of IRR
My brief responses to the author’s main points:
Criticism 1 This suggests we can turn around an unfavourable project by increasing our opportunity cost of capital. This is nonsensical, but incorporated then ignored in the negative-positive criticism.
Each project has an appropriate discount rate (cost of capital) that reflects the costs of buying that projects cashflows in the market. It is nonsensical to artificially increase those costs to make the project look better.
Criticism 2 A focus on NPV to the exclusion of IRR would build in a bias for large projects over smaller, perhaps more cost-effective ones.
A managers job is maximise NPV. If ten smaller projects contribute more NPV than a mutually exclusive larger one then the collection of smaller projects will be progressed – IRR offers no insights into this straightforward maximisation of NPV. Also as per an earlier example (the risk free project and the African IT venture) IRR does not take into account the cost of buying the project cashflows in the market.
Criticism 3 If we accept that both IRRs are mathematically correct, one solution is simply to take the smaller, more conservative 25 percent.
Would you have confidence in making a decision between two or more “correct” answers? Furthermore on what basis would you pick a conservative rather than aggressive IRR?
Criticism 4 Under some circumstances IRR is incalculable.
Yes it is.
Criticism 5Commonly, reinvestment at the IRR is an appropriate assumption.
So the better an project becomes then automatically the options for reinvesting the project cashflows also increase accordingly. Does this sound realistic?
Other Criticism “We cannot add IRRs for a meaningful number, but we can average them”
You can’t. The cashflows for these projects should be added together then a single IRR calculated.
If you found the article helpful please click on the "YES"
button after the question below.
If you didn't or otherwise want to vote No, please leave a comment and give me an opportunity to respond. It will give me a chance to improve this article and others I would like to write.
If you liked this article and want to see more from this author, please click here
Copeland and Antikarov, 2003,"Real Options a practioners guide"
Shockley, 2007, "An Applied Course in Real Options Valuation"