Categories
Dividends

Dividend Tracker 0.6 update: Fixing yields

This is a quick update for the dividend tracker, bringing the version up to 0.6. This release includes a quick fix for an error I made when calculating the dividend yield on cost of the stock. I made the mistake of incorrectly calculating this metric, which makes it all look messy.

Messing up the yields

I was looking at my US market summary tab and I noticed something. “Why does the yield on cost look completely different to the yield on last price?” These two values should be different if you’ve made a loss or gain, but they were way off. Turns out I wasn’t calculating the yield correctly.

The yield on last price is perfect, but the yield on cost is where the issues arose. Put simply, it was calculating the cost (in your base currency) against the current cost in the market currency. This is incorrect since it’s two different currencies. The fix was simple enough, but I was annoyed I failed to spot this sooner.

Changelog

  • Fixed an error with the yield on cost columns.

How do I update my tracker?

Make sure you already renamed the previously copied template you’re currently using. With an updated release like this one, simply copy across a new version to your Google Docs. Then follow these steps:

  1. Copy this code into the cell L2 in the market tab with your base currency: =IFERROR((IFS(F2="Annually",E2,F2="Monthly",E2*12,F2="Quarterly",E2*4,F2="Semiannually",E2*2)/H2),"-") .
  2. Copy this code into the cell M2 in the market tab with your base currency: =IFERROR((IFS(F2="Annually",E2,F2="Monthly",E2*12,F2="Quarterly",E2*4,F2="Semiannually",E2*2)/J2),"-").
  3. Select the entire grey section of row 2 and drag down to copy the code to the rest of the tab and refresh all the data.
  4. For other market tabs, use the same code for M2, but use this code in the cell L2: =IFERROR((IFS(F2="Annually",E2*Data!H$15,F2="Monthly",(E2*12)*Data!H$15,F2="Quarterly",(E2*4)*Data!H$15,F2="Semiannually",(E2*2)*Data!H$15)/H2),"-"). Edit the *Data!H$15* with the reference to the cell with the correct currency conversion.
  5. Select the entire grey section of row 2 and drag down to copy the code to the rest of the tab and refresh all the data.

By Rich Edmonds

Rich creates content for the top Windows-focused publication, but by night he tries to make his money work for him and rambles far too much here.

Leave a Reply

Your email address will not be published. Required fields are marked *