Annualized IRR

[bound4h]

Hoping there are some finance gurus/Excel gurus out there who can help. I understand what IRR is, but it's my understanding that Excel calculates IRR by assuming the cash flows are monthly (ie 12=1year). What if the cash flows are quarterly? See below example:

[TABLE="width: 621"]
<tbody>[TR]
[TD="align: center"]Q12014
[/TD]
[TD="align: center"]Q22014[/TD]
[TD="align: center"]Q32014[/TD]
[TD="align: center"]Q42014[/TD]
[TD="align: center"]Q12015[/TD]
[TD="align: center"]Q22015[/TD]
[TD="align: center"]Q32015[/TD]
[TD="align: center"]Q42015[/TD]
[/TR]
[TR]
[TD="align: center"] 1,542,658[/TD]
[TD="align: center"] (1,056,838)[/TD]
[TD="align: center"] 8,446,801[/TD]
[TD="align: center"] 7,761,898[/TD]
[TD="align: center"] 7,736,398[/TD]
[TD="align: center"] 8,235,359[/TD]
[TD="align: center"] (48,984,649)[/TD]
[TD="align: center"] (11,080,210)
[/TD]
[/TR]
</tbody>[/TABLE]

What's the IRR? When I type IRR(A1:A8) I get 23%. But since it's quarterly, should I multiply by 4? IRR(A1:A8)*4?

I'm ultimately trying to get an Annualized IRR, based on a set of quarterly cashflows.

Thank you

 

[bound4h]

Bump...

 

[Andrew Poulsom]

IRR assumes that the payments are annual. I think you need:

=(1+IRR(A2:H2))^4-1

 

[bound4h]

Thank you!!

I assume that when you refer to A2:H2 you just mean the stream of inflows.

Thanks again

 

[Andrew Poulsom]

I copied your sample data and pasted it into A1 of a worksheet and the values appeared in A2:H2. So yes, I mean the stream of flows.

 

[joeu2004]

What's the [annualized] IRR? When I type IRR(A1:A8) I get 23%. But since it's quarterly, should I multiply by 4? IRR(A1:A8)*4?

Perhaps.

You are correct that Excel IRR returns the quarterly IRR, since the frequency of your data is quarterly.

IRR(A2:H2)*4 is indeed one way to calculate annualized IRR.

(1+IRR(A2:H2))^4-1 is another way.

It depends on what the annualized IRR is intended to represent.

For example, if it is a (US) interest rate, the first form (*4) is the correct one to use.

If it is a compounded growth rate, the second form (^4) is the correct one to use.

PS.... Note that the second annualized IRR is close to what Excel XIRR returns if you use end-of-quarter dates. It is close, but not the same, because Excel IRR assumes equal frequency of data, whereas Excel XIRR uses the exact number of days, which varies by a few days depending the actual dates.