dtb80 wrote: ↑Tue Feb 26, 2019 9:22 am
Thanks for making this great spreadsheet!
I've been investing since 2005 and I'm in the process of transferring all of my data to this spreadsheet in Google Sheets. Whilst I've been very diligent in recording the fund that I bought, the amount and the price that I paid for any given month, I have not been recording the value of my pot at the end of each month and it looks like this is a key part of info this spreadsheet needs.
Can anyone give me some advice as to how to address this?
If you have the price and amount paid ,you can derive the # of shares purchased ($invested/$price-per-share)
From there, you will need to use a ticker lookup service (yahoo, Google, Vanguard website, etc. and get prices for the different dates. That can get pretty detailed, but you should be able to find the data.
You would then just create a formula for each month that totals the # of shares against published price to get the the balance. Of course you will have to be careful to be sure you time the additional of dividend reinvestment, but i assume you would have date from what you describe above.
I had a similar issue, what I did was only create annual answers to those questions and I hacked the spreadsheet to allow for annual entries only for years before 2017. Unfortunately my sheet is pretty customized to my situation, but I could share the idea.
What i ended up with is a dynamic worksheet that has manually entered balance/flow data for years before I started to track returns in longvest’s sheet. For years since the beginnning of tracking it pulls off the 12/31 balance and automatically adds each year to the bottom of the sheet. This allows me to do the same return calculations he has monthly but viewed annually. So basically I have annualized returns that I then look at from a 1, 3, 5, 10 and 20 year view and an IRR calculation as well. Because the flows are rolled up to an annual level, the overlapping periods do not get the same results as the monthly calculations. So for the more recent years, it is better to use the monthly calculations for any detailed analysis.
If there is interest in a system like that I can try to genericize my sheet and share with longvest as a new tab. I wouldn’t want to fork his great work.