excelexcel-formulafinanceaccountingirr

Dynamic IRR calculation. Is there a way to add zeros after the terminal amount is added?

I am building out a dashboard that allows you to add in a disposal date and amount which will add the data to the current modelled cashflows and recalculate the IRR.

The issue I am having is trouble zeroing all of the cells after the new disposal date.

For example say we have the below data in excel we can use the XIRR function to calculate the IRR:

30/04/2021 31/05/2021 30/06/2021 31/07/2021 30/09/2021 31/10/2021 30/11/2021 31/12/2021
-10000 100 100 100 100 100 100 11000

This would give an IRR of ~27%.

I want to make this dynamic so that I can say bring the disposal date forward as so:

30/04/2021 31/05/2021 30/06/2021 31/07/2021 30/09/2021 31/10/2021 30/11/2021 31/12/2021
-10000 100 100 100 11000 0 0 0

This gives an IRR of ~37%.

I am looking for information on how best to add the data in and how to add zeros after the disposal amount has been added. So if I change the cell for disposal amount and date it will bring the value forward, as seen in table 2. It will then add zeros to all cells after the disposal amount.

Solution

  • Workaround

    (no need to zero out anything with my proposed soln. - may save you duplicate resource in excel build... :)

    Plug this into excel per screenshot below (b13):

    =XIRR(B11:OFFSET(B11,0,MATCH(MAX(ABS(B11:I11)),ABS($B$11:$I$11),0)-1,1),B10:OFFSET(B10,0,MATCH(MAX(ABS(B11:I11)),ABS($B$11:$I$11),0)-1,1))

    Screenshot

    Application of proposed soln in Excel

    Shared OneDrive link: here (update no pw, no expiry)