Microsoft Money 2007: Performance Reports Flawed
wongp235
below)
#1. 1-Year Total Return Flaw
Whatever 1-year returns of a particular stock we wish to calculate, Money
always uses the start date a day earlier than it should be.
This is best illustrated by assuming we want to calculate the 1-Year Total
Return for the year to Dec 31, 2006. Strictly speaking, this period should
run from Jan 1, 2006 to Dec 31, 2006. However, since the opening price for
Jan 1, 2006 is the closing price for Dec 31, 2005, the correct dates to use
are Dec 31, 2005 to Dec 31, 2006. However, for unknown reasons, Money uses
the price at Dec 30, 2005 (rather than Dec 31, 2005) for its start date
One may think that a day’s difference will not much affect the performance
metric, but consider this:
A not unusual practice is for prices to be updated at the end of every
quarter i.e. as at Mar 31, Jun 30, Sep 30 and Dec 31 in line with the popular
gauging of investment performance on a quarterly basis. In the above example,
since Money cannot find the price as at Dec 30, 2005, it uses the latest
price available i.e. the one at Sep 30, 2005. Thus for the year to Dec 31,
2006, under the above circumstances, Money calculates the performance from
Sep 30, 2005 to Dec 31, 2006, a period of 15 months instead of one year.
At best, Money is a day out in computing 1-year total returns. At worst,
where prices are updated quarterly (or even half-yearly), the 1-Year Total
Return performance metric is completely misleading. Although not tested, this
flaw may also extend to the 3-Year Total Return metric. The Year to Date
Total Return and All Dates Total Return do not seem to be so affected.
#2. Foreign Stocks and Performance Report Flaw
(This flaw is also present in Quicken 2007)
Performance Reports generated by Money involving foreign stocks are
defective because they do not take into account the changes in exchange rates
between the beginning and the end of the period in question.
The performance report computes the percentage rate of return on the
investment. The method of computation is: “current market value plus income,
minus beginning market value, divided by beginning market value”.
To access performance of local stocks, the “beginning market value” is:
[beginning quantity x beginning price]. Note that historical stock prices
have to be maintained at the beginning date in order for beginning value to
be calculated and this Money does.
Where foreign stocks are concerned, beginning value should be: [opening
quantity x opening price x opening exchange rate]. Note therefore that not
only have historical prices to be maintained, historical exchanges rates have
to be kept too. Money does not keep historical exchange rates data; only one
rate is kept; the “current” rate. Money uses this one rate to calculate stock
values at the start as well as the end dates to which of course is plain
wrong. Value at the start must be computed based on the opening exchange rate
and that at the end at the closing rate the same way as opening and closing
prices are used.
To illustrate (pardon the lack of formatting. The data is in five columns;
email me if you want a properly formatted one):
Computation Method: Actual Actual MS-Money MS-Money
Year Start/End: Year-Start Year-End Year-Start Year-End
Date: 1-Jan-06 31-Dec-06 1-Jan-06 31-Dec-06
Quantity: 100 100 100 100
Price in foreign currency: 8 10 8 10
Exchange rate (Note 1): 2.00 1.50 1.50 1.50
Value in USD: 1,600 1,500 1,200 1,500
Gain/(Loss) in USD: n/a (100) n/a 300
Percentage Annual Return: n/a (6.25%) n/a 25.00%
Note 1: We assume here that the correct opening rate is 2.00 USD to one unit
of foreign currency and the closing rate is 1.50. However, Money uses the
same closing rate as the opening rate.
Money reports that you’ve made a healthy return of 25% when in fact you’ve
suffered a 6.25% loss.
The ridiculous situation of having lost money (1,600 at start versus 1,500
at end) and yet having Money report a positive return has come about because
although the share price has increased somewhat (8 at start versus 10 at end)
which is recognized by Money, the exchange rate has fallen, value wise, by
even more (2.00 at start versus 1.50 at end) which is not recognized by Money.
Where foreign stocks are involved, the performance report generated by Money
is fundamentally flawed and is hazardous for anyone to rely on it. It is
incumbent on Microsoft to fix the problem lest users, relying on the report,
suffer – and continue to suffer - grievous losses without realizing it as the
above example shows.
After all the problem is not so difficult to fix; if historical price data
can be maintained, it is equally simple for historical exchange rate data to
be maintained as well.
#3. Money Unable to Calculate Annual Percentage Rate (Sometimes)
The subtotal for stocks investment type does not show a figure for annual
percentage rate (shows “N/A” instead) if there are individual stocks that are
held for more than about 12 years. The individual stock itself however shows
the correct APR.
For example, if a stock bought on Sep 9, 1994 is included in the portfolio,
the APR for stocks sub-total shows “N/A”. Change the date to Sep 9, 2004 and
the APR figure appears.
Strangely enough, if you re-create the investment under “Other” investment
type, the problem disappears!
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
Wally
I use Money Deluxe 2007.
For complaint #1. If you set the dates from Jan 1 to Dec 31 you get the
correct performance figures. ie From the opening price on Jan 1 to the
closing price on Dec 31. This seems perfectly logical to me. If you use the
custom date of Previous Year these are the dates used. Just remember that it
goes from the opening price on the beginning date to the closing price on the
end date.
For complaint #3. I have extensive investment data on stocks, bonds, and
mutual funds dating as far back as 1992. This problem does not occur in my
money file. You might try running the file repair utilities.
wongp235
As to Flaw #1, I forgot to mention that the error occurs in Portfolio View,
not in the Performance Report. In Portfolio View, under the circumstances I
pointed out, the 1-Year Total Return computes a return amount (incorrect
amount) different from that of the Performance Report (correct amount). Both
were set to the same one-year period: Portfolio View set to "As of: Dec 31,
2006" and Performance Report from Jan 1, 2006 to Dec 31, 2006.
As for Flaw #3, I guess it must be me. I downloaded and installed the trial
Deluxe 2007 version from Microsoft, but the problem still persisted. I tried
to do a repair but that only made matters worse - certain columns from my
Portfolio View started disappearing.
Wally
portfolio view for investment reporting.
Dick Watson
exclusively user-to-user.
"wongp235" <wong...@discussions.microsoft.com> wrote in message
news:45B9E6F4-1C3F-49D8...@microsoft.com...
wongp235
they'd bother to read our comments. This is after all a Microsoft sponsored
site. Anyway, I'd much appreciate it if you could point me to the right place
to better get their attention.
Mark
information in the Price History for the investment in question.
If there is no price available for the exact "beginning" date, then it will
use the price that is available that is older than that date.
Also, as far as using the Dec 31 price instead of a Jan 1 price, it's
probably due to using the 'Closing Price' and a decision that the prior
day's closing price may be better than the first day's closing price. Of
course there are likely benefits and flaws with either decision.
For "Flaw 2" the issue is as you mentioned, Money does not maintain an
historical exchange rate record
But, it can be argued that your true gain should include the impact of the
exchange rate.
-Mark
"wongp235" <wong...@discussions.microsoft.com> wrote in message
news:11596DC8-10D5-46F4...@microsoft.com...
wongp235
As mentioned in one of my replies to Wally, the performance calculations in
Performance Report have got it right. However in Portoflio View, Money's
start date is not Dec-31 but Dec-30 and, if prices are updated quarterly,
since there is no price update on Dec-30, Money looks at the Sep-30 price,
one whole quarter ago.
Re: Flaw #2
There is no question in my mind that one's true gain/loss must take into
account changes in exchange rates between start and end dates.
Borty
International version of MS Money. I can also see that it has the potential
to give very misleading and inaccurate results.
Re flaw #2. As a New Zealander who holds much of my investment portfolio in
international equities, the value of which is markedly influenced by a
fluctuating exchange rate, this flaw has long been a source of considerable
annoyance frustration to me. Money's failure to record historical exchange
rates (except during buys and sells) means that my portfolio performance
reports lose all validity. I sincerely hope that Microsoft fixes this
problem and congratulate Wongp235 in bringing it to the attention of the
group.
--
Borty
Dick Watson
have to open a support case directly with them. In the US,
http://support.microsoft.com and look for the open an incident links.
Microsoft cares about user opinion in the aggregate as expressed in sales.
Individual user opinion expressed in support demands--which after all cost
them money--are probably less interesting to them. That having been said,
they do occasionally lurk and/or answer here. Generally, however, this is
the support folks not the people who more directly make product design
decisions.
"wongp235" <wong...@discussions.microsoft.com> wrote in message
news:3B81F951-6E60-4034...@microsoft.com...
wongp235
hard to get them to notice.
Dick Watson
issue, Money's handling of multiple currencies is pretty rudimentary.
"wongp235" <wong...@discussions.microsoft.com> wrote in message
news:25F35EE9-AD44-4618...@microsoft.com...