Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator III

XIRR Help!!

Hi All,

I have the below data and want to calculate XIRR.  (note I have attached QVF which gives all this data with examples)

 

IDDateCash Flow TypeAmountPrevMarketValueAmountMonth End DateMonthYear
1A09/28/2018Buy-2054750.33009/30/2018Sep2018
1A09/28/2018Buy-1358974.4009/30/2018Sep2018
1A09/28/2018Buy-602442.83009/30/2018Sep2018
1A09/30/2018MktValue3998577.31009/30/2018Sep2018
1A10/31/2018MktValue3971498.363998577.3110/31/2018Oct2018
1A11/01/2018Interest13754.77011/30/2018Nov2018
1A11/30/2018MktValue39606623971498.3611/30/2018Nov2018
1A12/01/2018Interest13754.77012/31/2018Dec2018
1A12/31/2018MktValue4024849.77396066212/31/2018Dec2018

 

 

My rule for calculating XIRR is that for the beginning period for which I am calculating the Input Amount into my formula should be negative Prev MktValue Amount and then any intermediate  Amounts where Cash Flow Type = 'MktValue' should be 0 until my ending period, any Amounts where Cash Flow Type <> MktValue should be as is.

For example using the above if I were to calculate XIRR for October - December my inputs table would look like the below

Notice how my beginning MktValue is - September MktValue and my MktValue for October and November are 0.

IDInput DateCash Flow TypeInput Amount
1A9/30/2018MktValue         (3,998,577.31)
1A10/31/2018MktValue                                 0  
1A11/1/2018Interest                  13,754.77
1A11/30/2018MktValue                                 0  
1A12/1/2018Interest                  13,754.77
1A12/31/2018MktValue            4,024,849.77

 

My Formula in Qlik is 

XIRR(Aggr({<[Year],[Month]>}sum({<[Year],[Month]>}if([Cash Flow Type]='MktValue' and Date>'$(=min([Month End Date]))' and Date<'$(=max([Month End Date]))',0.0,
if([Cash Flow Type]='MktValue' and Date='$(=MonthEnd(Min([Month End Date])))',-PrevMarketValueAmount,Amount))),Date,ID),
if([Cash Flow Type]='MktValue' and Date='$(=MonthEnd(Min([Month End Date])))',Floor(MonthEnd(AddMonths(Date,-1))),Date)) 

 

 

which is working for the above example and giving me what I want.

 

The only scenario where I am running into issues is when I want to look at XIRR for just one month.  Say November. 

I am running into an issue because when looking at one month,  my min and max Month End Date is the same so when splitting up the above formula my inputs table looks like this 

 

IDDateCash Flow TypeInput Amount
1A10/31/2018MktValue         (3,971,498.36)
1A11/1/2018Interest                  13,754.77

 

instead of what I want which is this

IDDateCash Flow TypeInput Amount
1A10/31/2018MktValue         (3,971,498.36)
1A11/1/2018Interest                  13,754.77
1A11/30/2018MktValue            3,960,662.00

 

 

I realize this may be a little confusing to follow so I have attached a QVF which lays out the example I described here.

 

Any help is much appreciated!!! 

Thanks,
Mark

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

XIRR({<Month, Year>}Aggr(
Sum({<Date = {"$(=Date(MonthEnd(Min(Date), -1)))"}, Month, Year, [Cash Flow Type] = {'MktValue'}>} -Amount) +
Sum({<Date = {"$(=Date(Max(Date)))"}, Month, Year, [Cash Flow Type] = {'MktValue'}>} Amount) +
Sum({<[Cash Flow Type] -= {'MktValue'}>}Amount)
, ID, [Cash Flow Type], Date),
Date)

View solution in original post

3 Replies
m_perreault
Creator III
Creator III
Author

@sunny_talwar 

Would be very grateful if you could take a look!

 

 

 

sunny_talwar

Try this

XIRR({<Month, Year>}Aggr(
Sum({<Date = {"$(=Date(MonthEnd(Min(Date), -1)))"}, Month, Year, [Cash Flow Type] = {'MktValue'}>} -Amount) +
Sum({<Date = {"$(=Date(Max(Date)))"}, Month, Year, [Cash Flow Type] = {'MktValue'}>} Amount) +
Sum({<[Cash Flow Type] -= {'MktValue'}>}Amount)
, ID, [Cash Flow Type], Date),
Date)
m_perreault
Creator III
Creator III
Author

This works! 

 

Thanks so much