|
Listen to this post if you prefer
|
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.
Bruce – you seriously have to stop giving away all the secrets. You should only allow a blog post to be open for about a week or two. If the blog reader misses the post in that span then he/she can pay you a little something for the content. Call it “Limited Content Marketing”. I could be more creative and direct with my title but for the sake of your website I’ll keep it clean.
As for this post – I see this lack of precision in peoples’s underwriting all the time. For example – while calculating a quarterly IRR from a quarterly cash flow, simply multiplying your IRR calculation by 4 is just not going to cut it.
wait – does using XIRR negate this post?
It inputs cash flows AND dates…. XIRR(dates, cash flows)…. I assume it does negative your post…. but a confirmation response would be much appreciated….. i know the NPV does not work with negative outlays!! (ie you must do NPV of positive inlays and then subtract negative CF0)… thus “Net” present value is not totally “net”.
Thanks for the post.
Hi John, sorry for the delayed reply. No, XIRR does not negate this post, it’s simply an alternative way of solving for the IRR when you have known discrete dates for each cash flow.