XIRR non-Contiguous Values

Hi all,

I'm not sure if I'm allowed to post a reward, but I have searched up and down this forum and have tried to implement people's solution that have solved this problem to no avail.

I'm trying to insert a sensitivity table for IRR. The dates are contiguous data (in cells above each other) but the cash out-flow and cash returns are in different parts of the sheet.

I've spent countless hours on this and am nearly giving up. Although it must be possible

Thanks everyone

Welcome to the forum!

Nobody is allowed to solicit payments of any kind, nor should you offer them (unless you buy points and offer them in the Commercial Services section). I have removed your offer of a voucher. Any help you get here will be completely free of charge.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

From what I have been able to gather, the XIRR() function is designed to work with contiguous ranges. Any solution I could see coming up with would involve taking your non-contiguous ranges and making them contiguous. As one who prefers to do this sort of operation in helper cells/ranges, I would probably propose a solution that uses a lookup function/filter/similar to extract the dates and values, and then feed that into the XIRR() function. Would you be interested in exploring that kind of solution, or will you only consider a single cell formula based solution?

Either way, as AliGW suggests, we probably need to have a better idea how your spreadsheet is currently arranged before we can offer anything concrete.

New thread started in the Commercial Services section. The OP is determined to pay for his help!

This thread is therefore closed.