I want to prove ((1+IRR(range))^12)-1 is an accurate formula to produce an annualized IRR of monthly cash flows. To do so I used it to discount the cash flows to achieve a result of zero ...but got a different answer.

Here's the number set, beginning in cell A1:

A1 (100)
A2 10
A3 12
A4 14
A5 16
A6 18
A7 20
A8 22

This formula ((1+IRR(A1:A8,31%))^12)-1 results in 35.73%. I should be able to use this for my discount rate in this formula NPV(30.95%/12,A2:A8)+A1 to produce a NPV of 0.00. Instead, the only discount rate that works is 30.95%.

Why?

(Note - You may notice I tried to "guess" a result of 31% and trick it into getting closer to the "right" result.)

Incidentally, NPV(30.95%/12,A1:A8) also works, though this is against Microsoft instructions to leave the amount for Period 1 outside the parentheses. Odd.