How to track one's portfolio effectively?

Hi,

I was not able to find XIRR returns in www.edelweiss.in. Does anyone know if they provide XIRR feature? If not can someone please share an excel to calculate XIRR

Hi Sunil,

I can’t find the file I saved a couple of months back but I think it’s fairly straightforward.

XIRR basically works on two variables - cash flows (inflow/outflow) and dates.

Both of these would be captured in one of the reports from your broker.

  1. So the first thing you need to do is take a spreadsheet dump of all your Buy/Sell transactions (almost all brokers would provide an option to export in spreadsheet format) for the period you want to calculate XIRR.

  2. Arrange your data into 3 columns of Buy/Sell, Amount, Date.

  3. Since all the buy transactions are cash outflows, show them as negative values. Ex: -1000

  4. Adding dividends may be difficult or you can choose to skip this step. You would have to manually
    add in line items with the amount of dividend and the date credited on for each stock.

  5. Add in the last line item - in that you put the present day market value of all the stocks bought during the time period for which XIRR is being calculated (assume this as a sell transaction)

  6. Apply the XIRR formula for the Amount and Dates columns.

Hope this helps!

2 Likes

Sunil, you can try the XIRR excel calculator created by @pattu sir
http://freefincal.com/xirr-returns-calculator/ &
http://freefincal.com/understanding-annualized-return-cagr-and-xirr/

Also you can use MProfit software(I use trial version) get the overall XIRR as well as individual XIRR returns

1 Like

@sunilsurana

Try valueresearchonline.com website,create PF, it will calculate all the things u r looking for.

Regards

I’ve create a sample PF with valueresearchonline.com but it is not showing CAGR returns. Can someone guide or post a screenshot?

trade data is not needed for calculating returns. Only deposits (amounts and dates) into your brokerage account, withdrawals (amounts and dates) from your brokerage account, opening balance of your holdings (including cash in brokerage account) and closing balance of your holdings (including cash in brokerage account) is needed to calculate returns.
Start with with opening balance (securities and cash). Enter all deposits and withdrawals in ascending order. Finally enter closing balance (securities and cash). All your trades during the period will eventually reflect in your closing balance of cash and securities account.

Here is a simple illustration.

Please note that all cash deposits and opening balances are entered as negative values and all cash withdrawals and closing balances are positive amounts. You can reverse the signs and your will get the same returns. You can use any arbitrary time period to calculate XIRR returns as you will always get annualized rate of return. This does not account for dividends. If your dividends are deposited directly in your bank account you can enter this as cash withdrawal.

6 Likes

Hi Abhishek,

https://www.smallcase.com has recently started providing an option similar to what you’ve suggested here. They let you create a basket of stocks of your choice and you can then buy/sell them as convenient.

This is my personal opinion and hence take it with a pinch of salt. Comparing our portfolio is like comparing metrics of individuals. Everyone has different priorities and requirements. It is better to not compare with other funds or investors. That could be the first and foremost risk. However we could look at long term returns and see if we match risk vs returns.

i believe you have to open a trading account with edelweiss . I don’t see any option to simply sign and create portfolio