How to calculate XIRR without projecting each cashflow

[HSR]

Hello Everyone,
I have a sample file with different possibilities. I've tried calculating XIRR without putting each cashflow of individual securities with no luck.
Few Points:

1. Interest Due date column indicates the 1st interest payment date. I want to put a formula wherein this will change every month/quarter/year depending on the frequency of the payments.
2. If the Interest due date is within the next 20 days from today, it should reflect the next due date
3. I have a big list of securities & for each of these I need to calculate XIRR separately. That's why looking for a way by which I can automate the whole process by putting in a fixed set of data for each of the security

Thanks in advance!

 

[Hui]

HSR

You could use a very complex Named Formula to develop a list of values based on your criteria, but you also need a Named Formula for the Dates, that would be a mess, but probably doable.

The easiest way is to setup 5 rows or columns, one for the date and one for each cash flow scenario
Add a fifth column/row to sum them at each date
The use the XIRR function on the Dates and Sum values column/row

It is a lot easier and auditable for you and the boss

 

[Peter Bartholomew]

Given that I have little knowledge of finance terminology, I would struggle to turn the descriptions into the sequence of dates and amounts that appear to be needed for the XIRR function. Given such sequences, I am confident that @Hui 's assertion that they can be re-packaged within a named formula. or several such formulas that reference one another, is correct.

There is also a new function LET that allows a series of names the be defined within a single cell formula. I have yet to try nesting LET functions within CHOOSE within another LET but I expect it is possible. By 'new' I mean Office 365 insider channel.

 

[HSR]

Hi,
Thanks for the reply. I will try out a couple of things & if something works out, I'll definitely post it here.

Thanks!