Dear all, I have made an Exel sheet that will calculate the annualized return from a stock, taking into account corporate actions like dividends, split, bonuses etc The reasoning associated with each cash flow entry and the beta version can be found here. I look forward to comments from you so that I can make this better. Once there is reasonable agreement wrt accounting practices, I can add automated stock price retrieval. Look forward to hearing from you.
NOTE: Many portfolio managers do not handle corporate actions correctly and this leads to skewed return values.
Ps I have no commercial angle in this. I develop stock analysis tools (previously shared here) purely from academic interest.
11 Likes
Click Here to access my google spreadsheet. This is not created by me but I have modified it completely to make it work for me. Frankly, I dont remember the source otherwise I would give full credit to them.
I am sharing a view only link and you have to be logged into a google account to save a copy for yourself. Once you save a copy for yourself, you can change it to suit your needs.
You can add buy and sell transactions, stock splits/bonus and dividends and works with any ticker that google finance provides a quote.
You can also make multiple portfolios work with this and you need to be little creative for this to make it work.
CAGR is calculated at stock level and if you can fill your capital intake and outtake under capital sheet, you can also track CAGR at portfolio level.
Google Spreadsheets is much better than Excel for tracking a portfolio.
I will do my best to answer any questions but most questions can be answered by a simple google search.
2 Likes
Dear @pattu,
A great tool. Thank you for working on this and sharing to wider audience. I was thinking of start to use XIRR to calculate returns and so I am trying this out.
In case of Dividend, it gets credited on a certain date and record date is different. Due to this number of shares on record date and credit date varies if any transaction between these dates. Which date is to be used in sheet for recoding dividend ?
Also with Libre Office, it looks like some times any data change from sample data takes a while to may be to process the calculations.
Is there a way to add Port folio xirr returns added to a single sheet from individual scrip sheets for a cumulative view ?
Thank you. The ex-div price date or the date on which the market price falls to the extent of the dividend can be taken.
I dont know why it should take longer with Libre office. It is a simple sheet and should work as is in all spreadsheets.
For portfolio XIRR, you can use the same sheet and simply add all your transactions across stocks in the same sheet. However, there can be some issues here. I think it is better to treat dividends as cash flows since they can be used to buy another stock. So a distinction has to be made between calculating instrument XIRR and investor XIRR. As of now, I have not made this distinction, but it is not hard to make those changes.
1 Like
For all those who are asking permission to give edit access, please make a copy using the below steps:
Make a copy of a file
On your computer, open a Google Docs, Sheets, Slides, or Forms home screen.
Open the file you want to make a copy of.
In the menu, click File and then Make a copy.
Type a name and choose where to save it.
Click Ok.