It would be cleaner to hide the rows instead of deleting them. This would also allow you to un-hide them in the future if you were to add more positions.
I have been using 5.0 for a while so I think it’s time to finally transfer to the new one. Thanks Ryan your the best for giving this stuff for free keep it up!
The spread sheet is awesome, only problem im having as of now is that its not loading up the annual dividends for the tickers, and the dividend yeild, any help would be awesome
Thank you very much for the brilliant dashboard. I wanted to ask if you could please share a video where you show how to extract insights and how to analyze the data that appears in the dashboard? I'm asking this because some of the terms that appear in the dashboard are for people with advanced knowledge and prior experience in the world of dividends. Thank you again for helping so many people to understand the fascinating realm of dividends
Thank you Ryne, this looks great and I can't wait to get started. I also do covered calls and cash secure puts for my dividend-paying positions, do you have a spreadsheet that tracks that as well, or do you have any suggestions on how I can incorporate that?
Thank you so much! You could add a new page for your options trades if you want to. I'm not an options guy, so I probably don't have the best insight on how to incorporate that. Maybe that's something I can think about for a future version
I'm still new to dividend tracking. What is the dividend growth rate? Is it for the TTM or a different value? Its going to take me a while to understand what all of this data means.
No problem! The dividend growth rate, or the CAGR, is the compound annual rate that the company increases its dividend by. So, for example, KO has a 5 year growth rate of 3.5%, which means that the company has increased the dividend payments by that amount, on average, every year for the last 5 years.
Hi Ryne, was wondering if you could help explain something. I track individual purchase transactions for my stocks to know the average purchase price for that particular stock. But what I can't seem to wrap my head around is if I partially sold out of the position, what becomes of my average purchase price and how would it affect my market value? Example: I buy 10 shares at an average price of $1 averagePrice: 1, costBasis: 10, quantity: 10, I then sell 5 of these shares at market price of $5 averagePrice: ??? costBasis: ??? quantity: 5 Could you share how you would calculate this in the spreadsheet if you know how? Thank you very much!
You would have to recalculate the cost of your remaining shares, but you shouldn’t have to do this manually. Your brokerage account should tell you your average cost per share
Hi Ryan - thank you for the spreadsheet. I couldn't find this question already answered so here goes....What is the best way to handle DRIP? Would you just update the Positions tab with the updated number of shares/cost or is there a separate tab to add them?
One last question.... Is there a way to add EDIT in the positions (sector) for options style stocks like QYLD, CONY APLY ect.. or does it just get listed as an ETF ? Thank You for answering my questions. UPDATED... I found where you can add it. Thankx.. I like the new version 7.0
Thank you Ryne for this Spreadsheet, I am Brand New to Investing and Unfortunately starting late in the Game @ 48.. But have a Question you are entering Numbers in Sections, But not really explaining how and or where that is found. Especially the Dividend Growth Rate I know you said It doesn't populate. But how and where R U getting those Values for your Positions??? I know might be easy for most but us Beginners kinda Hard, Even the Google Fu isn't helping and Even More confusing. I think this will help the New Guy Trepidation in getting settled cause I feel im all over the place. So if you or Anyone else could help with this would be Great. Thanks in Advance and helping the new guy.
Hey there. Thanks for checking out the spreadsheet. So to find the 5 year dividend growth rate, just search a stock on seekingalpha.com, then go over to the "Dividends" tab, and then scroll down to the "Dividend Summary." There, you'll find the 5 year dividend growth rate. Hope this helps.
Hey Ryne, looks like finviz just change the format for "Dividend Est." from 'annual dividend' to 'annual dividend (%)' . This messed up Dividend Yield and Yield on cost calculation on the spreadsheet. Any workaround for that? TIA!
You know I’ve got the workaround! I updated the spreadsheet template a couple days ago. If you email me at ryne@retirewithryne.com I can share it with you
Hey Ryne! The spreadsheet is really cool - thx! When entering sold stocks - do you remove them from the positions list OR does it remove automatically once entered in Sold Positions? And if I only sold some shares of a stock, do I need to subtract that number of shares on the Positions page?
I’m glad you’re enjoying it! When selling a stock, you would manually remove it from the positions page. Also, if you only sold some shares, you would need to subtract the number of shares on the positions page
Ryne, I have noticed an issue with the spread sheet I am not sure how to fix. Recently Finviz has changed over the annual dividend from a single number to a number and a percent. So when the excel sheet pulls the info it provides 2 numbers into the cell. This causes an issue for dividend yield and yield on cost to generate numbers. Any idea on how to fix this issue?
Yes! I’ve actually updated the spreadsheet template to correct the issue. If you shoot me an email at ryne@retirewithryne.com I’ll send you the updated template
@@rynewilliams Hola Ryne muchas gracias por todo!! Yo tambien tengo el mismo inconveniente, puedo enviarte un email para que me ayudes a solucionarlo?? Saludos!!
I'm super new to purchasing and tracking my investments. Do you have a video explaining the basics of each column of your spreadsheet? Also, I'm trying to use your spreadsheet and I am confused as to how to find the dividend growth rate when looking on seeking alpha (or anywhere else)....any tutorials or step by steps??
Unfortunately, I don't have a video that breaks down all of the columns. All of these are terms you'll become familiar with as you continue along. You'll get to know them pretty quickly. As far as finding the dividend growth rate, on Seeking Alpha, if you go to the "Dividends" tab after searching for a stock, you'll see the 5 year growth rate on the main, dividend scorecard. That's the number you'll want to input in the spreadsheet
When I do dividend reinvestment, how should I edit the table? I need to add the amount paid to me in the Dividend Log section and change "# of shares" and "Avg. Cost Per Share" or should I make another change? I think you didn't mention this part in the video. I would appreciate it if you can explain.
You would need to input the dividend on the dividend log page for sure. Regarding the rest, I talk about this at about 11 minutes into the video. A dividend reinvestment should be treated the same as a purchase, so you would update the positions page in the same way.
Hey Ryne... Your channel has been very helpful to me. I have a small dividend portfolio of 12 stocks about 16K so far. I downloaded your spreadsheet to keep track and I was wondering how you find the growth rate. Can I find it with Stock Unlock?
Thank you for checking out the spreadsheet! I'm sure you can find it with Stock Unlock. The 5 year growth rate is also known as the CAGR...so maybe look for that
Hi, I'm curious as to why the Annual Dividend does not update automatically when I tried this. The cell seems to be configured to get the data from an external source but stays blank even when all of the other data does update. Thanks Edit: nevermind I solved it! Needed to adjustt the permissions for the forrmulas
Hey Ryan, been watching for a couple months now and just decided to download the tracker! How do I know what dividend growth rate to insert for each stock? When I research the growth rate for each one, it seems as if different websites say different growth rates for the stock. Is there a specific website I should use, or a certain way I should go about this? Thanks a lot!
I personally use the dividend growth rate from Seeking Alpha. If you search a stock and then go to the Dividends tab, you'll find the 5 year dividend growth rate there on the dividend summary
Ryne, your doubt about the row, has been more than 30, the question is, if we open a new position at the particular stock, I normally open a new row and not sum to the old buy. Or I should edit the last buy and insert the total amount?
Hey Ryne, thanks for this free resource . I have few questions : Will this this track Canadian or is only Us listed stocks ? Upon Add new shares by updating the average share price every time it won’t give non-accurate value if bought the other shares at lower or higher price ? What is best place to find the average dividend growth to input o that field? Thanks :)
Hi there, to answer your questions: 1. You can track Canadian companies. I demonstrate how to do this at 13:20 in the video. 2. If you update the average cost per share and the number of shares every time you purchase a stock, it will be accurate. This info is given to you by your brokerage account. 3. I personally use the 5 year dividend growth rate from Seeking Alpha Hope this helps!
@@JuanaMeloUnicEducation If you look up a stock on Seeking Alpha, you’ll want to go to the Dividends tab and look at the Dividend Scorecard. On the scorecard, it will show the 5 year dividend growth rate. That’s what you want to use
I have a question regarding the yellow box with the dividend growth rate. What exactly do I write in the column? The 1 year dividend growth rate or the 3 year dividend growth rate? Many thanks for your spreadsheet :)
Hi Ryne, quick question what period time should i put in the "dividen growth rate" column? I see in seeking alpha different options like TTM, 3Y, 5Y and 10Y. Thank you
Hey, I really enjoy your spreadsheet. For some reason ARCC doesn't populate annual dividend, dividend yield or annual income. Is there a formula change I can make? Thanks for all the information and keep up the great videos
Thank you for providing this excellent spreadsheet! I inputted my positions data (Ticker, shares, cost, etc) and everything automatically populated except the "annual dividend" was $0 for each ticker. Additionally, dividend yield was blank on all of them, and yield on cost was 0%. I thought I just needed to manually add the dividend growth rate so I entered the 5 year CAGR but it did not help. I also filled out the Dividend Log but still having the same problem. Any suggestions?
@@chrischaney1945 okay. There should’ve been an orange “allow access” button somewhere when you first open the spreadsheet. If you click that, the dividend data will load!
@rynewilliams is there any plan to add index funds to your spreadsheet? Also, I was considering slowly moving my taxable account over to my ROTH IRA. The problem is some of my stocks the cost basis is super low compared to where the shares are now. What are your thoughts on this?
Regarding index funds, that's outside of my control. The reason index funds don't work in the spreadsheet is because Google Finance doesn't have info for them. If that ever changes, then they'll start working in the spreadsheet. Also, if you're dead set on moving stocks from your taxable to your Roth IRA, then unfortunately you'll end up losing your cost basis. I'm sure it won't be that big of a deal across a long period of time though, but there's really no way around that
Hey Ryne, I'm having issues with ARCC, SWBI, and GAIN in the annual dividend row... any input on how to fix it? i have tried to shuffle around the stocks and the input is correct (if i move a stock like O in to the arcc ticker O will populate, but moving arcc to the O slot same issue as before). Thanks ahead of time.
On the positions page, in column “AC”, you want to go to the cell that corresponds to your ARCC position, and change the “7” in the formula bar to an “8.” That will fix the issue for ARCC, and probably the others as well
Also man one more thing how come my annual data for 2023 is equal to what it was in just december, am i supposed to add those up for annual data or how does that work ? @@rynewilliams
Curious, how can we add a new section? I want to split my tracking for UK stocks (I'm British) with USA ones to see how they compete but convert overall dividends to total as i tend to buy UK and US stocks
I'm from the Netherlands. I have TDIV in my portfolio (ETF) but it is the AEM version, so in euro. When i fill in the TDIV ticker, it gives me the Dollar one. How do i find the right Ticker so the spreadsheet gives me the right market? Thank you for your work
@@rynewilliams Wow that's quick. I missed that section. Can't find them on google, only on the euronext site. Has the same ticker. Maybe these ones i'll have to do manually. Thank you anyways. Keep up the good work
Is column B on Monthly Data sheet telling the date or the year.? For example, does "January 20" mean `20th January` or `January 2020`.? At time 11:49, when you are updating the dates, `20` seems to be date and not year. If that is case, why is the update required?
I had to reformat them, because it was showing day and not year. I first noticed when it didn't load February 30 correctly. If you click on Format in the ribbon when the cell is selected, you can remove Day and add Year.
Awesome spreadsheet Ryne! You earned my subscription. I came across the v6.0 video on my feed and noticed that that version had a Future Value Calculator. I was most excited about that feature on the spreadsheet but the newest version is missing this sheet. Any clarification on why, or how I can make my own sheet that would show me how my portfolio would grow over time? Much appreciated!
Thank you! I just felt there were better future value calculators out there for people to use. For example, this one here: www.marketbeat.com/dividends/calculator/
I’m glad you’re enjoying the spreadsheet! Toward the end of this video, at 13:20, I demonstrate how to find the right ticker symbol for stocks/ETFs outside of the US.
Hey Ryne, currently got a little bit in INVH, but I found another REIT called EQR, pays a higher dividend and it’s the same real estate sector. You think it would be a good idea to switch ?
@@rynewilliams I’m both an OldSchool gamer and retired Military/DoD (plenty of desk/computer time too) And when I heard Dragon Drop, I knew what you meant, but couldn’t stop thinking; awsum name for a move! Hahahahaaa 🐉 🥋 🐧🐧
I'm sorry for the trouble. FinViz just may not have the dividend information for some of those ASX holdings. It does pretty well with US stocks and Canadian stocks, but outside of that it can be hit/miss
Ryne, were you able to figure out an iferror equation if we do not have an allocation such as materials on the diversification tab? Right now I can't seem to figure out how to add the equation in to return a 0% if I don't have any allocation in my positions tab without just deleting it.
So I found a work around if you paste this =sumif(A22:L22,"#REF!") into the allocation total % it will ignore any #REF errors if you don't have a certain sector holding. I will add though, that if you have a cash position (which is weighted) it does affect the total percentage. so you may not have 100% allocation if you have a number entered in your cash position. You can add that into your allocations and change the equation to =sumif(A22:M22"#REF!") and create a "sector" allocation on the end if you like your numbers to all add up.
Some of the stock and ETF div shows zero. Based on previous suggestion tried replacing 7 with 8 in formula. I still do not get the div fixed. Any other way to fix it?
Hey there...this is an issue that's impacting all users of the spreadsheet. It randomly happens from time to time, and will work itself out in a short amount of time
Thanks so much for the free resource! I've been using version 6 for a while now. I'm trying to get the new version but I can't get the email to send to me. 😅
I’m not sure. I’d have to see your spreadsheet to diagnose the issue. If you didn’t change anything on the spreadsheet template, then everything should be working functionally. You can share your spreadsheet with me at ryne@retirewithryne.com and I can take a look
Hey Ryan, thanks so much for sharing your portfolio spreadsheet, I just started using it since I'm a new investor. I wonder when we input data on the dividend log page, is this after deducting taxes since this is the amount that is being reinvested back into the same stock / ETF?
Thanks for checking out the spreadsheet! The number you input on the dividend log page is the number that is given to you by your brokerage account. It’s pre-tax. With that said, it’s important to know that you’re taxed on your dividends whether you reinvest them or not.
That's great! Thank you very much. I'm very interested in V6.0 Future Value Calculator Tap, is it possible to download V6.0 as well? Cheers from Switzerland
Is anyone else having issues with "Annual Dividend & Dividend Yield" not showing up for certain stocks? For Me it's OXSQ, PSEC, GLAD, & PFLT. They're all dividend stocks, and the other 8 work fine.
In column AC on the positions page, just change the “7” to an “8” in the formula bars for the cells corresponding to the tickers that aren’t working. That should fix things.
@@rynewilliams unfortunately this isn't working for all of them. One that I haven't been able to figure out yet, is MSFT. It shows 0% for annual div and is blank for Div Yield. Any other thoughts?
Hey Ryne! Absolutely love the spreadsheet. It would be AMAZING if one day you would be able to allow multicurrency holdings for us European investors! (Most of my investments are in £ in my broker)
Thank you! Unfortunately that’s not really something that it’s in my control. At 13:20, I demonstrate how to find international companies to use in the spreadsheet, but not every stock will load. It just depends on what info the data providers have
Nice! I’m new to your account. Which brokerage account do you use? I currently have Webull, do you think this is a reliable company to invest in ETFs and hold my money for 10+ years?
I made a video on TSLY a while back. Outside of that, I talk about the yieldmax funds every now and again in my live streams. Is TSLY A Scam? (30.44% Dividend Yield) ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-P2b5RGFmoR0.html
You didn't really explain where you got your numbers for the dividend growth rate column. You said you were kind of just making them up. My spreadsheet is in a standstill.
You should be able to just copy/paste all of your info into the new spreadsheet. Seeing as it's just a spreadsheet, that's really the only way to do it
Thank you! Unfortunately the data provider for the spreadsheet doesn't have a lot of the dividend data for Canadian stocks. If you manually input the annual dividend per share, it will fix the issue in the other column