0
$\begingroup$

I have cash inflows and cash outflows for a 7 year period and my MIRR and inflation rate. That's all the information I have.

How do I calculation the IRR taking the inflation rate into account in Excel ?

$\endgroup$
5
  • $\begingroup$ Perhaps you could just subtract inflation from the IRR? It would be helpful to see how your spreadsheet is set up. Could you edit your question to include the fields in your spreadsheet, either by text or a screenshot? $\endgroup$
    – rajah9
    Jan 14 at 14:25
  • $\begingroup$ Is it that easy - just subtracting inflation from IRR ? I used discounted cash flow analysis - calculated the discount rate and then calculated the net flows times the discount rate to get the NPV for each year, then calculated the IRR that would bring me to a zero NPV for all years. $\endgroup$
    – user4434
    Jan 14 at 19:23
  • $\begingroup$ Is your inflation rate constant, or varying during the 7 years? $\endgroup$
    – rajah9
    Jan 14 at 20:41
  • $\begingroup$ Constant - so is my technique correct ? $\endgroup$
    – user4434
    Jan 14 at 23:30
  • $\begingroup$ yes, sounds correct. But when I use discounted cash flow, I don't take inflation into account. Come to think of it, I don't recall any stock or bond analyses using inflation either. $\endgroup$
    – rajah9
    Jan 15 at 13:37
1
$\begingroup$

OK, you need to create a price series based on your "inflation" rate. Deflate all your cashflows by this, to give you a series of real cashflows. And then you can do a normal IRR calculation, based on these real cashflows.

Even if your inflation rate is constant rather than time-varying, this can make a difference. Imagine a series of cashflows that goes -100, +100, -100, +100 ad infinitum, versus the opposite that does +100, -100, +100, -100 ad infinitum. IRR'ing these will give you the same nominal 0% IRR. Subtracting any constant inflation rate will thus give you the same real returns. Except if inflation is running at, say, 100% a year, the real returns will be VERY different ;-)

So this would in reality become:

-100, +50, -25, +12.5, -6.25...

against

+100, -50, +25, -12.5, +6.25...

Not the same thing at all. Obviously, an extreme example. But hopefully the point is obvious. Just discount all the cashflows by your inflation price series, to give you a real-terms IRR.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.