XIRR function for changing date (image included)

jht0005

New Member
Joined
Jun 15, 2017
Messages
1
I've built a dynamic model where the dates between an acquisition and the revenue producing go live change (acquisition date D58, go live E58). I am attempting to summarize the XIRR for every year for 5 years on another tab, so I know the XIRR range must start at D58 and go to wherever 12 months ends (and eventually 24, 36, etc). I was attempting to use address function plus an hlookup for the number 12 and then an offset to go from K57 to K58 in my formula (needing to read as xirr(D58:K58,D78:K78)) but I can't seem to get it to work. I've gone through ~200 Mr. Excel examples and can't seem to back into this function.

I'd like for the formula to know to start at D58 and then find 12 months out (for both the date and cash flows) and eventually 24, 36, and so on. Any help would be greatly appreciated as I'm under a MAJOR TIME CRUNCH!
Yf4bhwXgSdzNhXoixA7B1SXjD0EpQi5zRkWXTA1CcSLOBM32JWlC-PWeMVEdvsW5dAuVbd0u2SHwMqJc0CZ7KJzr1X7EHy3_Qk0ELRQhOD2FZEM3qhZoVexMct2A2aCM0K1m3W7tF3xDXcBSHGZDo_VqcoEWFvXLrfCAN_wmY_XMXjRFRw6-bfYkjh9FcZ32Ex3mIgZS_T-lLuR7BqNL3aVCU13-ACBQRLUEDc4QlGPAZBVYH2_cd6r8o_LxsrAYn-UFDEH28MV9o74MOYGqmLK4-v40uFnw_krledaVH-k9xWWXFV2P365xI4bqxoKmu_9GtddO5ubV261Xhjr_ZSodXAkoW91mFFYZDbzJHGjvcUz2HZd5Zvq_zbvtGOoiFJWWxG-dcj5wRhSx2PpbcdVaCaq2qvBEM42hA8nKPEYdAxRnVH8V4OoLAi7umPgFSmv0Fi5xcc2pepqxh0CXp_8uUYdvrcPOAsqFLt-oLx00fzX-oEZ2Yww112VlkadH-C8lnqxl2eeqmTiVH6zDxEZDC9xi0jwoKMeFbXpkLNhOHfbowkH1iRTUVlwYlsQf9BQEI3jf5JvkNCr9tCQVTw4D5KFbB8fDY_yT9_uKn3jB3iOQinEJmA=w1791-h329-no
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
No image seems to have been provided. For this forum, I believe you must upload the image to a file-sharing website, then insert the share/public URL as an image object by clicking Insert Image in the toolbar.

The example image should clarify one point: are the 12-month cash flows always in consecutive 12 cells; or could there be more or fewer cash flows over the range of dates specified in D58 and E58? If the latter, will D58 and E58 always match dates in column D (if that is where the 5 years of dates are); or are you looking for the "closest" actual dates to the dates specified in D58 and E58?

And the mock formula that you wrote -- xirr(D58:K58,D78:K78) [sic] -- should be XIRR(K[D58]:K[E58],D[D58]:D[E58]) in pseudocode. The point is: XIRR(values,dates), not XIRR(dates,values).

Finally, in addition to selecting the value and date range dynamically, another problem you will likely face is the fact that all of your values in column K (if that is where the 5 years of values are) are presumably positive, whereas XIRR requires that at least two values have opposite signs; for example, negative inflows (investments and initial valuation) and positive outflows (distributions and final valuation).

All of these factors might lead to an unduly complicated XIRR expression, if it is even feasible. Would you be amenable to using a VBA function instead?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top