Nate Oliver's blog

This entry is based on a thread I responded to, here:

http://www.mrexcel.com/forum/showthread.php?t=76494

Occasionally the question comes up as to how to derive Monthly Internal Rate of Return (IRR) values. The answer is, well, that would depend what format your data is in, in terms of timing.

If your Spreadsheet houses monthly data, IRR() will naturally do this. XIRR() will not, it wil annualize your data.

However, see the attached, it shows examples of flipping back and forth (with both), considering different data constructs. There's no VBA, this is a pure-play exercise in Worksheet Functions.

The 2nd example (Worksheet) is required to be array-entered as Transpose() won't fire, effectively, otherwise.

Also note that multiplying by 12 might serve as a decent gut-check, but won't be accurate, as you're looking at compounding growth, by month - a different mathematical paradigm, it's not linear growth.

Lastly, there's another important concept baked into the first Worksheet - tie your numbers out via different means, until you're really comfortable with various Excel functions and visualizing them.

Hope you had a good, long weekend (for those that had one)!