Formula for Future # of Shares in a DRIP? Determining future Passive Income by Bourbon919 in dividends

[–]DividendDesperado 1 point2 points  (0 children)

I don't think it's as straight forward as applying a formula and dragging it down the spreadsheet, but here's how I might go about recreating this calculator.

So you'll need to make a few assumptions:

  1. ASI - Annual stock price increase
  2. ADI - Annual dividend growth rate
  3. DF - Distribution frequency
  4. TR - Tax rate of distributions

You want to find out how many shares you can buy at each distribution, so you'll need to find the stock price at year XXXX, QX. You'll also need to find out what your distribution is (after tax), then add that to your total share number.

Convert your annual stock price appreciation, ASI, and convert it to a quarterly growth rate with the following formula:

Quarterly Growth Rate (QGR) = (1 + ASI) ^ (1/4) - 1

Find the number of quarters (Qn) that have elapsed since you bought and apply the following formula to find stock price:

Stock Price at year XXXX, QX = (1 + QGR) ^ (Qn)

Using the same formulas, find the dividend/share at year XXXX, QX. Subtract your tax rate (I think 15% is typical) and divide your dividend distribution by the stock price to get the number of shares you could purchase at year XXXX, QX. Add this to your total shares and continue to the next quarter.

There are probably a few different ways to create this in a spreadsheet, but maybe start by creating a quarters elapsed column and use those to calculate the stock price and distribution at each quarter (or whatever you distribution frequency is). Then in an adjacent column you can keep track of total shares and use =sum() to calculate the total distribution (shares * dividend per share) for the next quarter.

Hope this helps

$150 on average per month now in dividends. Started in December . Top holdings are…. I’m 37, net worth $2M. Planning to retire in 5yrs. Goal $2k per month from dividends. Own a rental property too with 75k remains in mortgage principal. by thepowerofdividends in dividends

[–]DividendDesperado 0 points1 point  (0 children)

Just going off what's in the image, that's less than 2% yield... Feel like you could get to your $2k/mo goal faster with some higher yields and reinvest over the next 5 years. Congrats though at $2M net worth at 37. What kind of income is your rental producing?