XIRR

[KC]

Hello, I am having a strange result from using the XIRR functions. The data
that I entered were:

Date Value
12/31/2009 1
12/31/2010 0
12/31/2011 0
12/31/2012 -504,039
12/31/2013 93,084
12/31/2014 76,112
12/31/2015 81,563
12/31/2016 68,969
12/31/2017 56,349
12/31/2018 63,568
12/31/2019 54,834
12/31/2020 55,548
12/31/2021 53,601
12/31/2022 51,243
12/31/2023 49,463
12/31/2024 48,080
12/31/2025 46,855
12/31/2026 47,164
12/31/2027 49,832
12/31/2028 47,162
12/31/2029 267,539
12/31/2030 266,813
12/31/2031 256,873
12/31/2032 148,881

The result that I got was 0.00%, when I input a guess value of 10%, the
result was 7,820.38%.

When I changed the first value (i.e. the value on 12/31/2009) to 0.00, the
value that I got was 0.00% no matter what guess value I input.

When I changed the first value to -1, I got the result of 13.95% without the
need of any guess value.

Since my series of values was yearly, I tried to calculate the result using
IRR and got the result of 13.95% no matter the first value was 1, 0 or -1.

Can anyone tell me what is going wrong with the XIRR function?

Thank you.

 

[Ron Rosenfeld]

Hello, I am having a strange result from using the XIRR functions. The data
that I entered were:

Date Value
12/31/2009 1
12/31/2010 0
12/31/2011 0
12/31/2012 -504,039
12/31/2013 93,084
12/31/2014 76,112
12/31/2015 81,563
12/31/2016 68,969
12/31/2017 56,349
12/31/2018 63,568
12/31/2019 54,834
12/31/2020 55,548
12/31/2021 53,601
12/31/2022 51,243
12/31/2023 49,463
12/31/2024 48,080
12/31/2025 46,855
12/31/2026 47,164
12/31/2027 49,832
12/31/2028 47,162
12/31/2029 267,539
12/31/2030 266,813
12/31/2031 256,873
12/31/2032 148,881

The result that I got was 0.00%, when I input a guess value of 10%, the
result was 7,820.38%.

When I changed the first value (i.e. the value on 12/31/2009) to 0.00, the
value that I got was 0.00% no matter what guess value I input.

When I changed the first value to -1, I got the result of 13.95% without the
need of any guess value.

Since my series of values was yearly, I tried to calculate the result using
IRR and got the result of 13.95% no matter the first value was 1, 0 or -1.

Can anyone tell me what is going wrong with the XIRR function?

Thank you.

HELP gives the answer.

From HELP for the XIRR function:

The first payment is optional and corresponds to a cost or payment that occurs
at the beginning of the investment. If the first value is a cost or payment, it
MUST BE A NEGATIVE VALUE.

(Emphasis mine)
--ron

 

[KC]

Thank you Ron. My main point was: the amount that I changed was very small,
only from +1 to -1 as comparied to other figures. Why did the result changed
so drastically.

Another query was that if the first payment / cost was zero, why did the
result be zero?

 

[Ron Rosenfeld]

Thank you Ron. My main point was: the amount that I changed was very small,
only from +1 to -1 as comparied to other figures. Why did the result changed
so drastically.

Another query was that if the first payment / cost was zero, why did the
result be zero?

The first payment needs to be negative. 0 and 1 are not negative values.
--ron