locked
Annual Return in Portfolio Return Calculations view has a problem RRS feed

  • Question

  • The above view on one of my accounts within my portfolio does not display correctly - it shows N/A, which makes the grand total for the portfolio disply as N/A, although all the other accounts are fine.

    I have tracked down the date that the problem relates to, which is 1/3/2017.  If I change the "as of" date in portfolio view to 28/2/2017, the Annual return column works, but as soon as I change to a later date it stops working.  I have checked for any transactions on the 1/3/2017 and there are none except for price updates.

    There are 19 other accounts within the portfolio and none of them are affected.

    Does anyone know how to correct the issue

    Thanks

    Gareth


    • Edited by GDJH Friday, July 20, 2018 3:35 PM
    Friday, July 20, 2018 3:32 PM

All replies

  • I wish.

    I tried your same technique of trying to locate a corrupted transaction with the AsOf date. It looked promising, but the date did not get the results I thought I would get.

    Let me know if you end up fixing it.

    Hey, the Total Return percents including the last three years works. Total Return works. Cost Basis works. Appreciation works. They all work, other than the Annual Return for a few.



    Friday, July 20, 2018 9:54 PM
    Moderator
  • Thanks Cal

    I ran a report showing just 1 month up to 1/3/17 and I got an annualised return, so I think you are right the Asof date of 1/3/17 is not necessarily the date that is the issue, but there is a transaction somwhere that is rogue that stops the calculation woorking on the date of the 1/3/17.  If i had to guess it is somewhere between the start date of the portfolio and the 1/3/2017.

    Every investment within the account gives me an Annualised return when i run from the start date of the portfolio to 1/3/17, it is only the total annualised Return that does not calculate and as you say all the other columns give a result at the security/investment and the total level

    Scratch head time and try to find a way to systematically find the problem.

    It would be good to find someone who worked on developing Microsoft Money who may know the internal working better!

    Sunday, July 22, 2018 3:03 PM
  • Hi Cal

    Assume you tried a qif export and import into a new account?

    Sunday, July 22, 2018 6:53 PM
  • Hi Cal

    I tried a different method of tracking back as opposed to when it stopped working.  So i tried backtracking from the 1/3/2017 in Reports to see what date stopped the Annualised % stopped working,  I got to 1/12/2000 and everything worked fine, but when I went back to 1/11/2000 it stopped working.  Can't find anything between the 2 periods that i can see that would stop it working, but there must be somethig that I can't see.  I know at that time that automatic updating was working but not sure how to pinpoint what happened before, that stopped it working, must be something!?


    • Edited by GDJH Sunday, July 22, 2018 8:25 PM
    Sunday, July 22, 2018 7:57 PM
  • Hi Cal

    Assume you tried a qif export and import into a new account?

    I did not. That would have lost the specific lot info on sales. Yes, I could have re-done that.

    The symptom is not that bothersome. Maybe some day.

    Sunday, July 22, 2018 8:12 PM
    Moderator
  • Tried it and it did not solve problem, previous comment shows the next step i tried and it would appear to point where the problem maybe, but still can't see it
    Sunday, July 22, 2018 8:27 PM
  • Return calculations is likely to be calculated as

    (end of period value - beginning of period value) / beginning of period value

    value = shares * price

    N/A value probably is due to either

    * shares is zero (or somehow negative)

    * or price at the point of calculation is not available

    For example: if you have no  prices updates for the last week, then 'TR week' is very likely to be N/A

    I would try adding some price within the period in question and see if that will make any differences. 

    For example, if my 'TR week' value is NA, I would try add a price at the start of week and price at end of week.



    Monday, July 23, 2018 5:09 AM
  • Return calculations is likely to be calculated as



    I expect it is more like the XIRR function in Excel, but with prices included as elements in the calculation.
    Monday, July 23, 2018 4:14 PM
    Moderator
  • Thanks

    I wish it was a simple as the week but unfortunately it is the Annualised Return and for years this column worked on this account. The investments/securities are held in onther accounts over the same period and those accounts do not suffer from the same annualised calculation problem.  So i put it down to some rogue transaction in the account that is causing the problem, similar to if one was using Excel and had a Zero transaction then the Annualised return would not work. The strange issue is at the investment/security level the annualised return works, it is just at the total for the account that it does not work! Which suggests there is a rogue transaction or an issue with Microsoft money in some way.

    Monday, July 23, 2018 10:00 PM
  • I see ... I re-read the description a couple of time still not able to see it visually. If you don't mind, can you expand it with some actual number.

    Ideally, a screen capture will be best but if that is not possible due to privacy concern, something along the line with some made-up simple values to illustrate the point

    View: Return Calculation, Group by Account

    Account 0123

    SymbolA  then the column values ...

    SymbolB  then the column values ...

    so on

    Total then the column values ...

    Best,

    Monday, July 23, 2018 10:26 PM
  • The strange issue is at the investment/security level the annualised return works, it is just at the total for the account that it does not work! 

    So for a given account that has 5 investments: A, B, C, D and E. 

    For each of A, B, C, D and E

    the column Annualize return has a good (non N/A) values?

    But the very last line: Total for the account

    the column Annualize return has a a N/A value

    Something like

    A .... 1%

    B ... 2%

    C ... 3%

    D .... 4%

    E ... 5%

    Total N/A

    Monday, July 23, 2018 10:37 PM
  • The strange issue is at the investment/security level the annualised return works, it is just at the total for the account that it does not work! 

    So for a given account that has 5 investments: A, B, C, D and E. 

    For each of A, B, C, D and E

    the column Annualize return has a good (non N/A) values?

    But the very last line: Total for the account

    the column Annualize return has a a N/A value

    Something like

    A .... 1%

    B ... 2%

    C ... 3%

    D .... 4%

    E ... 5%

    Total N/A

    For me, E ... N/A. E has a lot of transactions.

    If I group by Account Name, only the account holding E totals to N/A.

    The problem is not as simple as a bad transaction it would seem. Using the Performance by investment type report, customizing to just the one stock, changing the date range, you can find a date where earlier dates show N/A, and after does not.

    The hope would be there is a transaction at or near that date. Nope. A split near that date? Nope.

    Monday, July 23, 2018 11:50 PM
    Moderator
  • Using Money 2000 International English.

    For what it's worth, I have had a problem with "Annual Return" when the latest share price (for BAe Systems  GB0002634946) is in a limited range ~ portfolio view returns a ridiculously high Annual Return figure, whilst ROI 3 year and % Gain figures are accurate. This happens to be the one investment in my portfolio which I had held from 1995 through till April 2002 ~ sold completely ~ and later bought again. My conclusion is that Money remembers the original holding and 'averages' over all time, so including the 'time out of the market'.

    Because I 'archived' just once I see no point in experimenting to try to understand what is going on ~ now I simply ignore the ridiculous figure.

    Teabag

    Tuesday, July 24, 2018 10:04 PM
  • For me, every investement/security has an annualised return, it is only at the total level that it displays N/A.  As per Cal i can focus in in different ways and get different results

    Very strange that we all seem to get varying symptoms that end up with the same issue.

    Can't be a rogue transaction, must be a programming issue.

    Yet there are other accounts with the same securities and thos do not have the same issue

    Even if i use a qif export from the problem account to another account I get the same problem

    Friday, July 27, 2018 10:46 PM
  • Even if i use a qif export from the problem account to another account I get the same problem

    Thanks! I was wondering.

    Friday, July 27, 2018 11:42 PM
    Moderator

  • Do you see similar problem when using Report -> Investment -> Performance by investment account? 

    Another approach is to use Report to "export" all the investment transaction into a spreadsheet and use XIRR to calculate and see if that point to anything (match result, not match, error)

    Saturday, July 28, 2018 3:30 PM
  • Inspired by GDJH's QIF discovery, I tried some experimenting. I distilled it down.  Here is how to reproduce the bug-- at least it worked for me on Money Deluxe Plus.

    1. Create hugeXZ3.qif Contents below.

    2. Import into a test *.mny file as a recovered account. I suggest File->New->NewFile to make a new test file. No password, and skip setup.
    2a. Have Money create a new investment account called hugeXZ3
    2b. Have Money make Acme Buggy Whips Corp to be a stock when asked.
    2c. No initial balance and don't add initial stocks etc

    3. After all import etc, go to the Portfolio. Select View: Return Calculations, which will display the "Ann. ret" (Annual Return) column.

    4. See that the Annual Return is 3.3%. Good so far.

    5. Put in a price for Acme Buggy Whips Corp of $40 on  today. See that the Annual Return now shows N/A.

    6. Put in a price for today of $30. See that the Annual Return now shows 15.6%. After a few more price tries, my crossover point was between $34 and $35.

    My takeaway is that this is a bug in the total return calculation that will not have a useful workaround. The problem is not some corruption of my file or data. This make me feel better.

    =========BEGIN hugeXZ3.qif===========
    !Type:Invst
    D2/19/1995
    CX
    MTest Memo 1234567890
    T455.00
    NBuy
    YAcme Buggy Whips Corp
    I1.25
    Q364
    ^
    D9/1/1997
    MTest Memo 1234567890
    T919.83
    NBuy
    YAcme Buggy Whips Corp
    I0.91983
    Q1,000
    ^
    D10/28/1997
    MTest Memo 1234567890
    T459.92
    NBuy
    YAcme Buggy Whips Corp
    I0.919833
    Q500
    ^
    D1/6/1998
    MTest Memo 1234567890
    T494.76
    NBuy
    YAcme Buggy Whips Corp
    I1.995
    Q248
    ^
    D2/19/1998
    MTest Memo 1234567890
    T1,271.67
    NBuy
    YAcme Buggy Whips Corp
    I2.78875
    Q456
    ^
    =========END hugeXZ3.qif=============

    I placed a copy at http://s000.tinyupload.com/index.php?file_id=42599716061741315488 which will be available for a while.








    Saturday, July 28, 2018 5:48 PM
    Moderator

  • Do you see similar problem when using Report -> Investment -> Performance by investment account? 

    I do when I customize that report to all dates.
    Saturday, July 28, 2018 8:37 PM
    Moderator
  • I have seen that come up before when a very large value increase is used.  I think i have also seen "Too Big" come up previously as well
    Monday, July 30, 2018 5:19 PM
  • As with Cal, if i set it to all dates and some other combinations, then i yes I still have the problem.

    Can select various dates and make it give me an answer, but it doesnt do the one I need which is all dates

    Monday, July 30, 2018 5:21 PM