XIRR Formula for Different Text and Date Criteria

Tusharrm

New Member
Joined
Sep 24, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Dear Expert....

In attached excel sheet, i have data in NEW DATA sheet which will be updated on every month end. secondly, to calculate XIRR i have four date which are in different coloumn like investmet date, capital return date, dividend date and market date and also have 4 columns for their respective value... i tried xirr(index) formula. this formula is returning correct figures if all date and value are in same column.

can any one help on this... expected answer is given in the sheet, you need to give formula in yellow cell..

also note that this is dynamic data every time i add data in new data sheet formula of xirr to be calculated for new range....

I want ans. in sheet called ANS in cell E16.

I really appreciate if one can help me to solve this....

I tried to upload mini sheet but not able to .. pl help...

Regards,
Tushar M.
 

Attachments

  • ANS.png
    ANS.png
    10.5 KB · Views: 2
  • New data.png
    New data.png
    26.9 KB · Views: 2

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,148,011
Messages
5,744,334
Members
423,863
Latest member
teehexcel

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
Top