As a follow-on to last week’s post on NPV, we note here that there is also a fundamental difference between solving for the IRR when cash flows are measured in annual increments vs. in monthly or other non-annual increments.
As the example spreadsheet embedded below shows, the IRR is by its nature an annual calculation, producing an annual discount rate as its result. Thus, when measuring non-annual cash flows, to be sure that the result it returns to us is meaningful, we must adjust for the different time period increment in the following way:
=(IRR(range of time zero and projected values)+1)^non-annual increment-1
If we don’t do this, then the cash flows will be discounted far too aggressively because Excel will think that each column represents 12 months, not something less than 12 months. To get the cells to light up in the example below, you’ll need to download the embedded file below by clicking on the download icon in the bottom of the embed border.