• 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.

Please HELP to calculate XIRR Return on Multiple Stocks

jsmehta

New Member
Hi Team,
Sharing an excel with 2 stocks. Can you help me on
a) XIRR @ single stock purchase (Refer P14 - P23)
b) XIRR @ consolidated single stock (Refer N6-N7)
c) XIRR @ Portfolio level (Refer N9)

Thanks
Jignesh
 

Attachments

  • XIRR.xlsx
    19 KB · Views: 10
I know very little about finance calculations but I had expected to see an array of in- and out-flows together with the associated dates, not a lookup.
You may know otherwise.
73437
 

Attachments

  • XIRR.xlsx
    18.7 KB · Views: 17
Thank you Peter. This is a tracker of only in-flow basis active stocks as of date. The above helps.

Would you be able to help me a bit more... In my file, I have stocks sorted basis purchase date (not stock name), thus having the total below each set of stocks is a challenge. would you be able to help a alternate formula for "Consolidated Single Stock" (N6, N7).
 
Sorry but I develop methods for Excel 365 and I do not think they are of any use to you.
Code:
= LET(
  NetPurchase, FILTER(Purchase[Net Pur], Purchase[Stock Name]=@StockName),
  PurchaseDate, FILTER(Purchase[Pur Date], Purchase[Stock Name]=@StockName),
  CurrentValue, SUM(FILTER(Purchase[Curr Vale], Purchase[Stock Name]=@StockName)),
  TransactionCount, ROWS(NetPurchase),
  k, SEQUENCE(1+TransactionCount),
  values, IF(k<=TransactionCount, NetPurchase, CurrentValue),
  dates, IF(k<=TransactionCount, PurchaseDate, TODAY()),
  XIRR(values, dates) )

The combined value is actually somewhat easier because it only required stacking rather than filtering and stacking
Code:
= LET(
  TransactionCount, ROWS(Purchase),
  k, SEQUENCE(1+TransactionCount),
  values, IF(k<=TransactionCount, Purchase[Net Pur], SUM(Purchase[Curr Vale])),
  dates, IF(k<=TransactionCount, Purchase[Pur Date], TODAY()),
  XIRR(values, dates) )

I suspect you are stuck with a more manual process of applying filters and appending current values.
 
Back
Top