Skip to main content
Quantitative Finance

Return to Question

improved formatting
Source Link
edit approved May 2, 2018 at 1:02
lostinOracle
edit approved May 2, 2018 at 1:02
lostinOracle
  • 113
  • 2

Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of 10 on 1/1/2019.

Thus: 1/1/2000: -10 1/1/2001: +2 ... 1/1/2018: +2 1/1/2019: +10

Thus:
1/1/2000: -10
1/1/2001: +2
...
1/1/2018: +2
1/1/2019: +10

The XIRR of above would be 19.85%, close to IRR of 20%.

Is it possible to hit an XIRR of 10% by tinkering with the final payment on 1/1/2019? It looks like we can only negate by the total positive CF received, which is +26. Otherwise, (and please correct me if I'm wrong) if we go any lower we can't solve for 0 and we'll get a #NUM error.

So if we did -26 on 1/1/2019, we'll get an XIRR of 16.24%. If we use -27 or any lower, we'll get an #NUM error. Is there another method or shortcut I can use to get down to 10%? Or is hitting 10% IRR at this point no longer possible due to the hefty CF received?

Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of 10 on 1/1/2019.

Thus: 1/1/2000: -10 1/1/2001: +2 ... 1/1/2018: +2 1/1/2019: +10

The XIRR of above would be 19.85%, close to IRR of 20%.

Is it possible to hit an XIRR of 10% by tinkering with the final payment on 1/1/2019? It looks like we can only negate by the total positive CF received, which is +26. Otherwise, (and please correct me if I'm wrong) if we go any lower we can't solve for 0 and we'll get a #NUM error.

So if we did -26 on 1/1/2019, we'll get an XIRR of 16.24%. If we use -27 or any lower, we'll get an #NUM error. Is there another method or shortcut I can use to get down to 10%? Or is hitting 10% IRR at this point no longer possible due to the hefty CF received?

Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of 10 on 1/1/2019.

Thus:
1/1/2000: -10
1/1/2001: +2
...
1/1/2018: +2
1/1/2019: +10

The XIRR of above would be 19.85%, close to IRR of 20%.

Is it possible to hit an XIRR of 10% by tinkering with the final payment on 1/1/2019? It looks like we can only negate by the total positive CF received, which is +26. Otherwise, (and please correct me if I'm wrong) if we go any lower we can't solve for 0 and we'll get a #NUM error.

So if we did -26 on 1/1/2019, we'll get an XIRR of 16.24%. If we use -27 or any lower, we'll get an #NUM error. Is there another method or shortcut I can use to get down to 10%? Or is hitting 10% IRR at this point no longer possible due to the hefty CF received?

Source Link
IRRlost
IRRlost
  • 1
  • 1

question on XIRR (excel)

Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of 10 on 1/1/2019.

Thus: 1/1/2000: -10 1/1/2001: +2 ... 1/1/2018: +2 1/1/2019: +10

The XIRR of above would be 19.85%, close to IRR of 20%.

Is it possible to hit an XIRR of 10% by tinkering with the final payment on 1/1/2019? It looks like we can only negate by the total positive CF received, which is +26. Otherwise, (and please correct me if I'm wrong) if we go any lower we can't solve for 0 and we'll get a #NUM error.

So if we did -26 on 1/1/2019, we'll get an XIRR of 16.24%. If we use -27 or any lower, we'll get an #NUM error. Is there another method or shortcut I can use to get down to 10%? Or is hitting 10% IRR at this point no longer possible due to the hefty CF received?