Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
This works!
Thanks so much