I will start with thanking @Prdnt_investor as i was inspired by his VP public portfolio tracking spreadsheet that he created and shared in the VP portfolio thread. I also learned calculating NAV from his blog. I also have to credit http://www.investmentmoats.com. I have taken his portfolio tracking spreadsheet as baseline to built this. I have customized on top of his spreadsheet.
The spreadsheet is built to track my portfolio performance in the way i like. The spreadsheet has provisions for adding buy/sell transactions, corporate actions like dividends and splits. you can enter the cash flow movements in and out of your portfolio. The spreadsheet will summarize the performance and calculates CAGR for each stock as well as portfolio. Nav calculation is also built in along with CAGR calculation. you can take a snapshot of Nav every quarter.
You can see the spreadsheet in action by making a copy to your google account from the below link.
light yellow cells are where you need to put in your inputs and light blue cells are auto calculated.
The transactions listed in the spreadsheet are not my actual transactions. While the stocks are actual stocks in my portfolio i took pains to modify the quantities and cash amounts.
I would be happy if this is of any help to the folks in the VP community.
A preview of the spreadsheet is being shown instead of the link. If you want to copy the spreadsheet to your account you can add https:// at the start to the below link and make a copy of the sheet to your account.
Thanks @pidugu for sharing this sheet. I see there in sheet REF there is section for price alert. Can you please throw some light on this âŚhow does this workâŚHow do you get alert when your expected price is triggered
Ref sheet is only for Specifying the different transaction types(buy,sell,div,split,fee) and portfolio types( you can choose to have your own here). Ignore the price alert tolerance. I am not using it but what you asked can be done. you can google for it. You will have to put notifications on cells and write a script to open the spreadsheet every hour or so. this will update the sheet and the notifications on cells get triggered for you to receive alerts.
Please read the below blogpost on understanding the nav calculation.
If you want to start nav calculation starting today you can take nav as 10 and calculate units based on that.
If you want to calculate historic nav it will take a little effort. if you notice in the cash flow sheet i am calculating units based on the nav on that day. so you have to add all transactions till that day, calculate the PF value on that day based on prices on that day and then use the nav to put the units.
Some folks are requesting write access to this spreadsheet. You donât need write access to this sheet. You would have to make a copy of this sheet to your google account. File -> make a copy and edit your copy of the spreadsheet.
Thanks Harsha. After checking with you on the other thread, I started tracking my portfolio in it. I also added some more features based on my need. Thought I will contribute back, from where I got this sheet. I can also try adding any more interesting features if there is any request.
Here is what I have added.
Added charts for different category/classification.
Added a standard brokerage calculation. This might need more improvement if you have multiple brokers.
Noticed that the date format in transaction sheet was in US format which caused flux in CAGR calculation. Fixed it by going to File->Spreadsheet Settings->locale
Changed cash calculation automatically.
Moved the results in holdings page to a separate tab called history and it gets recorded automatically every day which will allow tracking at a later stage. Again this might need more tweaking.
How to use it:
Update the transactions sheet(area in yellow) with actuals.
Make sure âcodeâ column is correct in both transactions and holdings. The sheet uses google finance so better check if the code returns the right company. I noticed that including âNSE:â or âBSE:â before the symbol gives correct result. In my case for ITC it gave me a US company with the same code. So be specific to make it accurate.
If the price is still not accurate use the âMan. priceâ, which is manual price, to enter right price.
Update the brokerage charge in ârefâ sheet as percentage of transaction value. This will update brokerage in the transaction calculations.
Update the holdings sheet with stock name and code which you are currently holding.
Update the âPast holdingsâ sheet with stocks which you have already exited.
Update âCash registerâ sheet with cash flow details.
If required add items to watch list for stocks which you would like to monitor.
First of all, I would like to sincerely thank Harsha Devulapalli and Sharmi, who have done the fantastic job in coming up with this google sheet.
I have made slight modification and it is working very well. I am facing a challenge with one particular formula in the sheet - relating to CAGR calculation. As per my sheet, CAGR calculation is only throwing up 0% and is not calculating the actual CAGR. I have attached a link to my amended google sheet.
I would highly appreciate, if you can guide me as to what error I am making in the CAGR calculation (Column W) in Holdings sheet.
I am not able to see the sheet fully with view access. But in general I have noticed that in the transactions sheet the date is not set as date(probably its text data type). Try changing it.
Dear Sharmi
I checked the format of date column (Column A) in âTransactions 2â sheet. Its format is set as âdateâ. I redefined the format as âdateâ by selecting the range and then using the menu (Format -> Number -> date). Still the same error persists.
I am reattaching a new link to this message, as you had faced challenge in accessing the file. Thank you in advance.
@pidugu, @sharmi
Thanks for Excels. I have a question with ref to https://docs.google.com/spreadsheets/d/1x9x6U1L3_fv-i8vHsDh2x_qc7swr9aNS6qZiksHWKMk/edit?usp=sharing
Shouldnât the âAmountâ column in âCash Registerâ tab and âcash flowâ column in âtransactionsâ tab match ? The amount invested on 6/9/2014 (MM/DD/YYYY) as per âtransactionsâ is 66,076 (row2,3,4) where as in âcash flowâ it is shown as 1L (row 1). Am I missing something here ?
Amount in cash register is how much money you got into your bank account
for allocating to equities where as cash flow is how much of it you used.
you might have bought 2 lakhs but only used 1 lakh of it. 1 lakh will be
your cash. next time you might get only 50K and buy 1.5L. now your
remaining cash is 50K etcâŚ
