XIRR Question

[Paris0022]

I am trying to create an XIRR table that can give me a returns on a certain number of periods (months). The IRR works 12 months at a time. For example if I did a $1,333,500 investment and over 3 years (table on the right) I would get a IRR of 20.4%

But what I am trying to figure out if the 3 years ends in April. The IRR would not be accurate because it is calculating it as 12 months.
So I tried to use a XIRR formula to give give me a more accurate Rate of return. When I use the XIRR, it gives me a 0.4% return. Looking at the IRR, I should be getting something around 20%.

Why is it 0.4% and not close to 20%.

How would I calculate a more accurate IRR based on a return of 28 months.

Thanks for your help!


[TABLE="width: 808"]
<tbody>[TR]
[TD][TABLE="width: 808"]
<colgroup><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Initial Investment:[/TD]
[TD="align: right"](1,333,500)[/TD]
[TD][/TD]
[TD]0.4%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 1[/TD]
[TD] 11,080[/TD]
[TD="align: right"]1/31/2018[/TD]
[TD][/TD]
[TD]Initial Investment:[/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3 [/TD]
[TD]IRR (3 years)[/TD]
[/TR]
[TR]
[TD]Period 2[/TD]
[TD] 11,080[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD][/TD]
[TD="align: right"](1,333,500)[/TD]
[TD] 132,955[/TD]
[TD] 132,955[/TD]
[TD] 1,977,231[/TD]
[TD="align: right"]20.4%[/TD]
[/TR]
[TR]
[TD]Period 3[/TD]
[TD] 11,080[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 4[/TD]
[TD] 11,080[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 5[/TD]
[TD] 11,080[/TD]
[TD="align: right"]5/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 6[/TD]
[TD] 11,080[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 7[/TD]
[TD] 11,080[/TD]
[TD="align: right"]7/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 8[/TD]
[TD] 11,080[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 9[/TD]
[TD] 11,080[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 10[/TD]
[TD] 11,080[/TD]
[TD="align: right"]10/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 11[/TD]
[TD] 11,080[/TD]
[TD="align: right"]11/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 12[/TD]
[TD] 11,080[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 13[/TD]
[TD] 11,080[/TD]
[TD="align: right"]1/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 14[/TD]
[TD] 11,080[/TD]
[TD="align: right"]2/28/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 15[/TD]
[TD] 11,080[/TD]
[TD="align: right"]3/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 16[/TD]
[TD] 11,080[/TD]
[TD="align: right"]4/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 17[/TD]
[TD] 11,080[/TD]
[TD="align: right"]5/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 18[/TD]
[TD] 11,080[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 19[/TD]
[TD] 11,080[/TD]
[TD="align: right"]7/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 20[/TD]
[TD] 11,080[/TD]
[TD="align: right"]8/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 21[/TD]
[TD] 11,080[/TD]
[TD="align: right"]9/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 22[/TD]
[TD] 11,080[/TD]
[TD="align: right"]10/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 23[/TD]
[TD] 11,080[/TD]
[TD="align: right"]11/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 24[/TD]
[TD] 11,080[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 25[/TD]
[TD] 5,294[/TD]
[TD="align: right"]1/31/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 26[/TD]
[TD] 5,294[/TD]
[TD="align: right"]2/29/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 27[/TD]
[TD] 5,294[/TD]
[TD="align: right"]3/31/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 28[/TD]
[TD] 1,961,348[/TD]
[TD="align: right"]4/30/2020[/TD]
[TD] Ending Investment [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]XIRR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

Assuming the initial investment was made on 1/1/2018, the following formula returns 27.43%

=XIRR(B1:B31,C1:C31)

 

[Paris0022]

Why when I put that formula in =XIRR(B1:B31,C1:C31) I am getting 0.4%?

 

[Paris0022]

Oh I see the problem! I was missing a date on the initial investment!

Thanks!