• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate XIRR without projecting each cashflow

HSR

New Member
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!
 

Attachments

  • Excel Queries.xlsx
    8.6 KB · Views: 5
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
 
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.
 
Hi,
Thanks for the reply. I will try out a couple of things & if something works out, I'll definitely post it here.

Thanks!
 
Back
Top