After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 645193 - Request for enhancement to Advanced Portfolio Report
Request for enhancement to Advanced Portfolio Report
Status: RESOLVED NOTABUG
Product: GnuCash
Classification: Other
Component: Reports
2.4.x
Other Linux
: Normal enhancement
: ---
Assigned To: Christian Stimming
Depends on:
Blocks:
 
 
Reported: 2011-03-19 00:20 UTC by kodb2105
Modified: 2018-06-29 22:55 UTC
See Also:
GNOME target: ---
GNOME version: ---



Description kodb2105 2011-03-19 00:20:35 UTC
Add ability to track dividends from stocks/mutual funds that are not used to reinvest/ie buy additional fractional shares of the same security.  The Advanced Portfolio report does not allow for accurately accounting for/calculating rates of return or gain/loss that includes dividends unless said dividend is "reinvested".  Enhancement could be either adding a canned transaction to allow the accounting for the dividend in the stock account (not currently possible) or by including the ability to associate the Income:Dividend:Stock-Symbol account with the Asset:Investments:Brokerage:Stock-Symbol stock account.  I do not have any idea which would be easier to program but either would work.  If the second route was chosen the existing account hierarchy could be used and then users would need an individual subaccount for each dividend yielding stock under the Income:Dividend main account.  I'd be happy to help in any way I can including testing out any of the above permutations.  I have zero coding knowledge.
Bob
Comment 1 Chris 2011-03-27 02:58:43 UTC
What is it that you're trying to achieve?  What you're describing doesn't make complete sense to me.

Your stock is an Asset.  Its value, and hence gain or loss, does not change if the stock pays a dividend, which is strictly Income.  If you purchase more stock via a DRIP or using any other funds, the value and quantity of the Asset changes.  This is independent any dividend payment.

Now, by "rate of return" are you referring to yield?  Can you give some examples, with numbers, showing what you mean?

For example, given the following, what would your ideal report look like:?

2000/01/01 - Buy 100 shares ABC @ $10/sh, Debit $1020 from Brokerage
             $20 Credit to Expenses:Commissions
2000/02/01 - ABC pays $0.10/sh dividend
2000/02/01 - $10 Credit to Brokerage, Debit from Income:Dividend
2000/02/15 - Buy 400 shares ABC @ $20/sh, Debit $8020 from Brokerage
             $20 Credit to Expenses:Commissions
2000/03/01 - ABC pays $0.10/sh dividend
2000/03/01 - $50 Credit to Brokerage, Debit from Income:Dividend
2000/03/15 - Sell 250 shares ABC @ $25/sh,
             $6230 Credit to Brokerage, $6250 Debit from Income:CapGains
             $20 Credit to Expenses:Commissions
2000/04/01 - ABC pays $0.10/sh dividend
2000/04/01 - $25 Reinvested @ $25/sh, Credit 1 shares ABC,
             Debit $25 from Income:Dividend
2000/04/01 - ABC pays $0.10/sh dividend
2000/05/01 - $25.10 Reinvested @ $25.10/sh, Credit 1 shares ABC,
             Debit $25.10 from Income:Dividend
2000/05/15 - Sell 252 shares @ $15/sh
2000/05/15 - $3760 Credit to Brokerage, Debit from Income:CapGains,
             $20 Credit to Expenses:Commissions

If you could also provide the formula used to calculate your report, that would be immensely helpful.
Comment 2 kodb2105 2011-03-28 02:53:31 UTC
(In reply to comment #1)
Chris,

I am trying to acheive a calculation of the total return for a given stock or even the entire portfolio by having it calculated within the software instead of running reports and then using a secondary spreadsheet or calculator redundantly.

The format of the current report is fine overall. It just needs to account for the dividends to give a  more accurate in determination of  the "Total Rate of Return" for the stock in question: ie. factoring in not just the appreciation of the asset itself (stock price appreciation) but also factoring in the dividends that that particular stock returned.
For a pretty good explanation see :
http://www.usatoday.com/money/perfi/columnist/krantz/2006-05-03-figuring-returns_x.htm?loc=interstitialskip

The first method detailed in the link above would be:

Pi= initial or beginning price
Pf= final or price at time of computation
DP= dividends paid for the period in question

{[(Pf+DP) -Pi]/Pi} * 100 = % total return for the period in question including dividends

The second and more precise method calculates the uneven cashflow and IRR.  I cut and paste the Excel based example Mr. Krantz lists in his article below:

Quote begins: 
"You can also do this in Excel by following this template:

 Cell: Value
 A1: -51.26
 A2: 0.27
 A3: 0.29
 A4: 0.29
 A5: 56.46
 A6: =IRR(A1:A5)*4"
Quote ends

For the above A1= Pi, A2 thru A4 are the individual quarter 1,2,&3 dividends the stock in the example paid, A5=Pf+4thqtr dividend and A6 lists the IRR equation which gives the most precise calculation of total return for that single stock in question.

Gnucash currently only accounts for stock returns (so far as I can see) using only the appreciation in stock price itself and does not count the returns from dividends in that particular stock's statistics.  I.e. currently  [(Pf-Pi)/Pi]*100 = Return.

To be completely accurate the commission paid should be factored in as well for each individual stock in question.
C1= commission at purchase
C2= commission at sale (if applicable otherwise =0 i.e. if stock not yet sold)

The revised equation for method 1 would then be

{[(Pf+DP)-Pi)-(C1+C2)]/Pi} *100= Total return accounting for all dividends, comissions, and stock price appreciation in any given period. 
I am sure there would need to be a date/time function to calculate annuallized numbers based on the time interval in question.  I think the HPC-12 method or the IRR function presumes a 1 year period.

Now, if we were going to go whole hog then we would want to add the ability to account for options by adding another variable that could be either a positive or negative number to account for any calls or puts.  Anything more complicated than that probably is outside the scope of the software although I'd be happy to see us pursue this to the nth degree.
Oi= Options income either + or -.  Selling Covered calls and writing/selling puts giving us + income and buying calls and puts giving us - income.

Adding this into the above would bring us to:
{[(Pf+DP+Oi)-Pi)-(C1+C2)]/Pi} *100

Assuming I haven't screwed up the algebra above (please please do check as I'm not a math or financial genius), I think the equations are relatively straightforward, the question is how to link the income account(s) to that particular stock or otherwise include either the individual quarterly dividends (more precise as noted above) or total dividends for any period into the equation.  I think the most straightforward way is to set it up so the program pulls amount of the dividends(DP) from Income:Dividends:Stock-SymbolXXX and uses that amount as "DP" above when calculating the return of Asset:Investment:Brokerage:Stock-SymbolXXX. Options could be handled from Income:Options:Stock-SymbolXXX to give us Oi. Alternatively, I suppose you could change the way the program handles dividends/stock income/options altogether but I think that would probably be a much much bigger programming headache (at least in my programming ignorance).

This concept could/should be extended to the global/summary data for the investment reports.
see: http://www.usatoday.com/money/perfi/columnist/krantz/2006-02-27-portfolio-return_x.htm

The above link  describes obtaining an accurate calculation of total portfolio return including any additions and withdrawals. Using that logic we could consider dividends, positive options income and any added cash as additions, and negative options income, commissions paid, and any cash withdrawals to be counted as withdrawals.   Mr. Katz again uses the HPC-12 as his tool of choice.  I suspect that the logic/equations are all easily duplicated for use in Gnucash.  Implementing these would put Gnucash light years ahead of any other software I've seen for tracking returns other than specialized brokerage stuff.

Hopefully this makes what I am aiming for somewhat clearer although it turned out much longer than I had anticipated!
Bob

> What is it that you're trying to achieve?  What you're describing doesn't make
> complete sense to me. <<snip>>
Comment 3 kodb2105 2011-03-28 03:32:01 UTC
Working the example you gave with the transactions leads to some further stratification as you need to consider the different purchase prices and the differential sales price.  Same algebra applies but you have to factor in FIFO for the stock gains.  So derived from my equations above:

Pi1 10*100=1000
Pi2 20*400=8000
Pi3=25 from drip
Pi4=25.10 from drip
Pf= 25*250=6250
Pf 15* 252=3780
DP 10+50+25+25.10=110.10
C1 20
C2 20
C3 20
C4 20


Gain 1 using FIFO
PiG1= (1000) + (150*20)=4000
PfG1=25*250=6250

Gain2
PiG2= (250*25)+25+25.10=6300.10
PfG2= 15*252=3780
(in this case Gain2 ends up negative or as a loss)

Aggregate cost basis 1000+8000+50.10= 9050.10=PiA

({[(PfG1-PiG1) + (PfG2-PiG2) +Dp]-[C1+C2+C3+C4]}/PiA) * 100
({[(6250-4000) +(3780-6300.10) + 110.10]-[80]}/9050.10) *100
({[2250-2520.10+110.10]-[80]}/9050.10)*100
-2.65% rate of return with all dividends, drip, and commissions included

So we will have to add operators to account for differential purchase price using FIFO methodology.  I do not know how to code that but I did show line by line calcs above.
You should probably check my math as it is late.
Bob
Comment 4 enginerd22 2011-07-17 18:27:56 UTC
This issue has been brought up before, apparently there was a workaround in 2.2 which no longer works properly in 2.4. See http://gnucash.1415818.n4.nabble.com/Advanced-Portfolio-Report-And-Dividends-td3394873.html and https://bugzilla.gnome.org/show_bug.cgi?id=581191. The reason I'm commenting is because I'm having the same issue, running 2.4.2. My dividends are showing up as negative when I use the fix which apparently worked in the bug report above.

Dividends definitely should be counted as part of the gain/loss of a portfolio, because in the end what you care about is how much money you made/lost as a result of buying, holding, and then selling the asset.
Comment 5 kodb2105 2012-01-15 02:58:32 UTC
(In reply to comment #4)
> This issue has been brought up before, apparently there was a workaround in 2.2
> which no longer works properly in 2.4. See
> http://gnucash.1415818.n4.nabble.com/Advanced-Portfolio-Report-And-Dividends-td3394873.html
> and https://bugzilla.gnome.org/show_bug.cgi?id=581191. T
<snip>
I went ahead and tried the logic/syntax as noted in the above bug 581191 and it is working in 2.4.7 on Ubuntu 10.04.3.  It appears to only work if you input exactly as shown in the example
I would think a hardcoded approach preferable but if this continues to work it helps dramatically!
Thanks,
Bob
Comment 6 Mike Alexander 2014-01-20 02:25:47 UTC
Dividends should show up properly in the Advanced Portfolio report if you use transactions as described in bug 581191.  This serves to link the dividend to the correct stock account so the report can find it.  I think this is a satisfactory way to deal with this so I'm going to close this bug.  If you disagree, say so.
Comment 7 John Ralls 2018-06-29 22:55:15 UTC
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=645193. Please update any external references or bookmarks.