How-To’s

Improving Investment Spreadsheet Performance

September’s Dividend Meter update will be running a few days late; however, I’m excited to report the stock dividend portfolio’s annual income has broken through $6,600.00! Until I finish the update, I’d like to share with you a quick video for improving the performance of your dividend or investment spreadsheet.

Sometimes the import formulas in Google Sheets get stuck on “Loading..”. The reality is stock dividend figures do not change that frequently. Maybe once a year, if you’re fortunate, a company will increase the dividend. So, rather than all cells relying on an import column to finish loading, your spreadsheet can quickly calculate values if it has a dividend column with static numbers. Here’s the modification I made to my spreadsheet:

How a Kid Can Buy Stocks





While it’s never too late to start investing, it’s certainly better to start as early as possible. Consider this: a single $250.00 stock investment, earning an average of 7% per year, grows to approximately $8.000.00 in fifty years. Fifty years is a long time, but for an investor starting at the age of 9 years old, a $60k account balance at age 59 is possible by simply saving lawn mowing earnings and monetary birthday gifts during his/her adolescent years (just 10 years) and leaving it alone to grow.

Mowing Lawns to Retire Early Let’s look at a hypothetical example that I hope to make a realistic future my own 9-year old child.   By mowing lawns, completing household chores, and saving a portion of cash birthday gifts from relatives, he is currently able to save about $250.00.   If he invests the $250.00 per year in stocks for the next 10 years, and earns a 7% average annual return, he will have approximately $3,735.00 upon finishing high school.

10 Years Savings

$3,750.00 isn’t too exciting, but it’s a great start.   Now, let’s examine what happens if the investment is left alone for the next 40 years at an estimated 7% annual return.   Without any additional contributions, $3,735.00 becomes nearly $60,000.00…

40 Years of Savings

But how can a 9-year old child invest in stocks with savings of only $250.00?   Legally, the child cannot open a stock brokerage account by themselves, and commission charges would be prohibitive.   One possible solution is to open a custodian DRiP account for the young investor.   “DRiP” means “dividend reinvestment plan”    DRiPs allow anyone to get started investing with small sums of money, and they feature automatic reinvestment of dividends in additional fractional shares of stock at no or very low fees. To learn more about DRiP investing, visit The DRiP Investing Resource Center at DRiPinvesting.org – there is an abundance of great information and resources available at the website. It’s also important to understand the characteristics of a “custodian” account – essentially, an adult controls the account until the child reaches 18 or 21 (depending on which U.S. state you reside in).  Remember, when your kid reaches 18 or 21 years of age, assumes control of their custodian account, and is tempted to withdraw the hard-earned savings, send them back to this article – for the magic of compounding to work, the investment needs to be left alone!

If you and/or your child have larger sums to invest, consider opening a traditional custodian account with an online discount brokerage firm such as .   However, if you only have $250.00 to start with, then using a direct stock purchase plan administrator such as Computershare is an option to begin investing in a DRiP plan.

In next week’s article, I’ll post a detailed review of the account opening process at Computershare. Please know that I have no affiliation with Computershare, or financial incentive arrangement with the organization. I will provide an honest, unbiased review of the experience and will provide account updates in future blog articles.

At Computershare, you have to carefully review the investment plan for each stock – the fees and minimums do vary.   It appears the fees to sell shares are actually much higher than a traditional online brokerage firm.  However, the higher sell fees may be an acceptable trade-off for years of no-fee purchases and dividend re-investments (depending the stock purchased), and the whole point of this article is to buy and hold for fifty years.   I would love to hear comments from readers who have used Computershare or other DRiP plans, please Register and join the conversation…

 

How to Add Buy and Sell Alerts to Investment Spreadsheet

In last week’s post, When to Sell Stock, I described a strategy for using dividend yield as an indicator to make buy and sell decisions for dividend stock investments.  Today’s article will explain how to add an automatic buy sell alert column to your Dividend Meter spreadsheet.

When purchasing dividend growth stocks, my first preference is to hold the shares long-term; however, if a stock becomes significantly over-valued, I will consider selling some or all shares of the company, especially if an attractive buying opportunity exists in an alternative investment.

To measure value, I look at a position’s current dividend yield compared to a historical range for that particular stock.  Next week, I’ll explain how I determine the high and low dividend yield values that trigger a buy or sell alert.   But for now, let’s look at an example for COP, ConocoPhillips.  Currently, I’m willing to buy more shares if the dividend yield is greater than 5%, and I would consider selling shares if the dividend yield were ever to fall to less than 3.5%.

In reference to the Dividend Meter screenshot below, here is the formula you need for column J:

=IF(H2>0.05,“BUY”,(IF(H2<0.035,“SELL”,” “)))

Buy Sell Spreadsheet Alert

Next, add conditional formatting, so if the cell indicates “Buy”, it will be highlighted in green or whatever color you prefer to use.   For a “Sell” indicator, you can additional formatting to highlight the cell in red:

conditional_formatting

Now, when you open your Google Sheet, column J will automatically show a “Buy” indicator if the current stock dividend yield is greater than the value in your formula, or a “Sell” alert if the yield is less than your lower value.   If the dividend yield is somewhere in between the high and low values, the cell will be empty.

Disclosure:  This article is not intended to provide specific recommendations to buy or sell securities.  The author holds a long positions in COP.

How to Add Dividend Increase Alerts to Spreadsheet

Keeping a transaction journal in my Dividend Meter provides a history of dividend compounding at work over time, so it’s important to record new purchases of stock and dividend increases as they happen.   When new shares of stock have been purchased, it’s easy to determine why the needle on the Meter moved higher because I know what stock I just bought.   However, since Google Sheets automatically updates the cells with each opening of the spreadsheet, it can be difficult to quickly determine why a Dividend Meter reading is higher than the last recorded journal entry.  It’s either an error in the dividend amount, or a dividend increase has occurred, which is always a nice surprise!

Applying conditional formatting is a simple modification you can make to your Dividend Meter spreadsheet to easily determine if a company has increased or decreased their dividend.  Simply click the cell containing the annual dividend figure, select the Format tab, and choose “Conditional formatting…”

Dividend Increase Alert

In the Conditional Formatting pane, click the selection box under the heading “Format cells if…” and select Greater than.  Manually enter the current dividend figure for the stock.  Change the fill color to whatever color you desire – my preference is green for a dividend increase and red for a decrease…

Dividend Increase Value

To add an alert for a dividend cut, click “Add another rule” at the bottom of the Conditional Formatting window…

Add Dividend Cut Alert

To add a dividend cut alert, select Less than…, enter the same dividend figure (should already be filled in), and change the fill color.   Then click Done.   Apply the same steps for each security on your dividend tracking spreadsheet.   Google Sheets will remember the conditional formatting parameters.  Going forward, you’ll only have to change the dividend figure when/if a dividend increase or cut occurs.

Setting Dividend Cut Alert

Now, when you open your Dividend Meter, and the gauge chart shows a different value than your last recorded journal entry, you should be able to quickly identify which stock increased or decreased its dividend.