How do I change the IRR formula for MONTHLY periodic payments?

[Jon]

The IRR formula is set up for annual payments. How can it be changed to
account for monthly payments? I know the NPV formula will work if you divide
the discount rate bay 12 for monthly payments. Do I multiply the solution by
12? I tried this and the result did not look correct. I do not want to use
the XIRR formula for this because it will be a template.

Thank you

 

[Bernard Liengme]

None of the financial function have specified periods: you can use days,
months, quarters, years. But the results have to be interpreted. IF you have
a series of monthly payments, then IRR returns a monthly rate. Generally one
converts that by multiplying by 12 to get the nominal yearly rate. But you
might consult a finance book to learn about 'nominal' vs 'effective' rates
best wishes.

 

[joeu2004]

The IRR formula is set up for annual payments. How can it be changed to
account for monthly payments? I know the NPV formula will work if you divide
the discount rate bay 12 for monthly payments. Do I multiply the solution by
12? I tried this and the result did not look correct.

Why not?

It might be helpful if you clarified exactly what you have to begin
with. You say that "the" IRR formula (I assume you mean __your__
formula) "is set up for annual payments". To me, that means that you
have annual cash flows, not monthly cash flows. Is that right? Or do
you have monthly cash flows, and you are making the incorrect
assumption that IRR() always produces an annual rate, just as XIRR()
does?

If you have monthly cash flows, the IRR function gives you a monthly
rate. Are you trying to convert that to an annual IRR?

If you have annual cash flows, but you want to report a monthly IRR,
there are two schools of thought. One school says: simply divide the
annual IRR by 12. Another school says: find the monthly rate that
compounds to the annual IRR. The latter can be accomplished as
follows:

=rate(12, 0, -1, 1+IRR)

Conversely, if you have monthly cash flows and a monthly IRR, one
school says simply multiply by 12; the other school says compound the
monthly rate for 12 periods. The latter can be accomplished as
follows:

=fv(IRR, 12, 0, -1) - 1

If you compute the NPV discount rate simply by dividing the annual rate
by 12, you probably subscribe to the multiply/divide school of thought
for the IRR conversion.