60-Second Skills: Annual IRR vs. Monthly IRR Formula And Other Non-Annual Cash Flow Increments
Bruce Kirsch, REFAI?
Founder of REFM, Author of RE Finance and Investments textbook and REFAI? Certification program
Did you know that REFM offers:
Take the first step toward mastering Excel and transforming your career.
There is a fundamental difference between solving for the IRR when cash flows are measured in annual increments vs. in monthly or other non-annual increments.
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.