# Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Contributor 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)

 ID Date Cash Flow Type Amount PrevMarketValueAmount Month End Date Month Year 1A 09/28/2018 Buy -2054750.33 0 09/30/2018 Sep 2018 1A 09/28/2018 Buy -1358974.4 0 09/30/2018 Sep 2018 1A 09/28/2018 Buy -602442.83 0 09/30/2018 Sep 2018 1A 09/30/2018 MktValue 3998577.31 0 09/30/2018 Sep 2018 1A 10/31/2018 MktValue 3971498.36 3998577.31 10/31/2018 Oct 2018 1A 11/01/2018 Interest 13754.77 0 11/30/2018 Nov 2018 1A 11/30/2018 MktValue 3960662 3971498.36 11/30/2018 Nov 2018 1A 12/01/2018 Interest 13754.77 0 12/31/2018 Dec 2018 1A 12/31/2018 MktValue 4024849.77 3960662 12/31/2018 Dec 2018

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.

 ID Input Date Cash Flow Type Input Amount 1A 9/30/2018 MktValue (3,998,577.31) 1A 10/31/2018 MktValue 0 1A 11/1/2018 Interest 13,754.77 1A 11/30/2018 MktValue 0 1A 12/1/2018 Interest 13,754.77 1A 12/31/2018 MktValue 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

 ID Date Cash Flow Type Input Amount 1A 10/31/2018 MktValue (3,971,498.36) 1A 11/1/2018 Interest 13,754.77

instead of what I want which is this

 ID Date Cash Flow Type Input Amount 1A 10/31/2018 MktValue (3,971,498.36) 1A 11/1/2018 Interest 13,754.77 1A 11/30/2018 MktValue 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)

• ### XIRR

1 Solution

Accepted Solutions MVP

## Re: XIRR Help!!

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)``````
3 Replies
Contributor III

## Re: XIRR Help!!

Would be very grateful if you could take a look! MVP

## Re: XIRR Help!!

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)``````
Contributor III

This works!

Thanks so much