0

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.

Myles Collier
  • 23
  • 1
  • 6
  • I can answer this question, but will need to know (write equation in body of revised Q) how you derive the values -10000, 100, 100,...., 11000, etc. have put inserted in first place. This is clearly the function/array/(?!) that needs manipulating, so would be difficult to do without knowing that that function is, unless I'm missing something(?) – JB-007 Jun 13 '21 at 21:34

1 Answers1

1

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)

JB-007
  • 2,156
  • 1
  • 6
  • 22
  • First comment: One-Drive for limited availability (1 day, AM - expires, GMT time). pw = my name, lowercase, backwards. (not end of world if you miss it, you can still construct using screenshot I shared).... While you're reading this - consider closing by approving if you're happy with my proposed soln, for the benefit of other users/students. – JB-007 Jun 13 '21 at 21:52
  • Thanks this is what I was lookng for – Myles Collier Jun 17 '21 at 16:39
  • 1
    Glad to hear. PS - have revised above by updating link to OneDrive file w/ restrictions (no pw, no expiry). You've probably replicated by now, but all the same - sharing = caring. – JB-007 Jun 17 '21 at 21:40
  • 1
    :) (silly 15 char min requirement blah blig abl ignore) – JB-007 Jun 17 '21 at 21:41