Step-By-Step Guide on How to Make a Dividend Income Spreadsheet

As you know, recently I created a dividend income spreadsheet with Google Docs.  It took me probably 6 hours to do just because I am spreadsheet illiterate.  I thought it might be a good idea to immortalize a “step by step guide” on how to create a dividend income spreadsheet in case I need to do it again in the future.  And it might be helpful for those of you who want to create a spreadsheet too, of course.

For those of you (like me) who are pretty much excel illiterate, this might be a helpful post for you if you’re interested in creating a dividend income spreadsheet that is connected to Google Finance and will provide you with up to date information on the latest dividend stock price.  Also, like with other excel stuff, you can colour the title of your excel spreadsheet and make it all pretty (like mine, which is a pretty lilac shade, lol).

So without further delay, here’s a step by step guide on how to make a SIMPLE dividend income spreadsheet (because anything more complex than this, I can’t do).

  • Create a Google Account

If you don’t have one already, you should create a Google account so that you can use Google docs.  Then click on Spreadsheet to begin.  You’ll be taken to a blank spreadsheet and it will probably look overwhelming if you haven’t looked at a spreadsheet in a while.  Just look at the lettered columns and the numbered rows and you’re off to a good start.

  • Create Headings

Some of the headings that I like to use in my spreadsheet were Company (or stock), number of shares, annual dividend yield, dividend income, annual yield (%), current price, and quarterly or monthly dividend payment (this is probably overkill but I like to include it in my spreadsheet anyways).  I also have a column that says “DRIP” so I can remember which stock I drip and which ones I don’t.

This is where you can make your headings in BOLD and add a nice colour background.  Everything else after this isn’t as fun (and can give you a big headache if you don’t know how to maneuver around a spreadsheet like me!)

Note: when you type in the company, make sure you type in the share name (e.g. HSE.TO to indicate a Canadian share or BCE (without the “.TO” to indicate an American share) so that you can utilize Google Finance to update the latest share price.

Screen shot 2012-12-27 at 5.35.17 PM

  • Enter Data

Entering data for number of shares and annual dividend income yield is easy and straight forward.  To enter data for dividend income, you need to look at the column names.  For example, for HSE.TO enter in the function text box “B2*C2” which is “number of shares” x “annual dividend income yield” = “dividend income”.  For the respective rows, it will be “B3*C3″ and so on.

For current price, enter: =GoogleFinance(“HSE.TO”;”PRICE”).  Of course this varies according to the share name.  Some stocks do not have information in Google Finance, so you’ll just have to manually keep track of the share name then.

You can continue adding more attributes to your spreadsheet, like P/E (price to earnings ratio), 52 week high, 52 week low, earnings per share etc.  All you need to do is substitute “pe” for “price” and so on.  To see a list of attributes that you can add to your google dividend income spreadsheet, check out Google’s list here.

  • Add Up Your Dividend Income

This is probably my most favourite part of creating the spreadsheet.  To add up your dividend income (which will automatically change once you update the number of shares that you own or to update the annual dividend yield), just type in in the f(x) field:

=SUM(D2:D15)  (where D2:D15 is the column letter and the rows are the dividend shares you want to add up).

And voila, it will automatically add up all your dividend shares for you!  See below for an example.

Screen shot 2012-12-27 at 5.47.21 PM

Hopefully you will need to be updating your dividend income spreadsheet often because the company keeps on increasing the annual dividend yield!

I used to keep a dividend income spreadsheet on paper, but it just got too cumbersome and messy to update, and it was difficult to keep track.  I find this much more helpful and I can keep track of it more easily because it’s accessible anywhere.

Readers, do you have any other tips for a dividend income spreadsheet?  How do you keep track of your dividend income?

 

About

Young is a writer and former owner of Young and Thrifty and the main "twitter' behind Young and Thrifty's twitter account. She lives in Vancouver, BC and enjoys long walks on the beach, spending time with her anxious dog, and finding good deals. If you like what you read, consider signing up for email updates.

6 Responses to Step-By-Step Guide on How to Make a Dividend Income Spreadsheet

  1. Rob says:

    I actually use this free pre-made spreadsheet.

    https://spreadsheets.google.com/spreadsheet/ccc?key=0Ah2uvISuDwSedDMzNW9uRlp3MUp4WjNxdmhWRjcza2c&hl=en_US&authkey=COH_2fYO

    You can find more information on it from the guys over at Investment Moats. I’ve tweaked it a little for my own purposes, as I’m sure you can too; its not particularly complicated.

    http://www.investmentmoats.com/stock-market-commentary/portfolio-management/introducing-our-free-stock-portfolio-tracker-spreadsheet/

  2. Leigh says:

    Instead of doing:
    =GoogleFinance(“HSE.TO”;”PRICE”)

    you can do:
    =GoogleFinance(B2;”PRICE”)

    which means you can just copy/paste that formula into all the ones in the row instead of having to input the symbol in the formula on each line :)

  3. Brian says:

    We are most interested in our monthly dividend income, so we track our monthly or quarterly payouts. We put each stock across the top and the months down the page.

  4. Excellent post! This is pretty much how I track my dividend income. It’s great to show others how easy it is when you use some simple formulas.

Leave a reply

 Name: Email: We respect your email privacyEmail Marketing by AWeber