Please check my math on Quicken investing calculations

Jay Gourley

I was skeptical about the Average Annual Return and IRR calculations on the Investment tab of Quicken for Windows mainly because the documentation didn't make any sense to me. It looked like someone cut and pasted it from a finance book without looking at the code or explaining the variables.

So I checked results on Mathematica, which is more clearly documented. I used Cisco over a five-year period ending 12/28/2021 (today). So it's a straightforward example. You buy some Cisco shares on 12/28/2016 collect the dividends for five years and sell the shares on 12/28/21. What's the value of that cashflow?

For Cisco stock my Quicken (Ver R37.62, Build 27.1.37.62) Investing tab shows, "Average Annual Return (%) 5-Year" = 13.5 and "ROI (%) 5-Year" = 82.3. But Mathematica shows the effective rate of return is 0.195499 or just under 20 percent.

I tried to attach the Mathematica notebook, but this web site wouldn't allow that. So it's published at https://www.wolframcloud.com/obj/jgourle2/Published/cisco example.nb. That way you don't have to have Mathematica installed to test the definitions. The web site will run it for you.

There is plenty of room for error in my calculation. So I did something wrong, please let me know. On the other hand, if my reasoning is correct, you should probably make note of it before basing an investment decision on Quicken's math. It would be fairly easy to turn the notebook into a single function that returns the effective rate of return on any listed stock.

q_lurker

Your Quicken data appears to be off


OR


I didn't bother to sell it today, but that should not make any real difference.  Took closing price 12/28/16 as purchase price and dividend records from Yahoo Finance.  All based on a single 100-share lot.

At other times I have checked Quicken's math vs Excel's XIRR function for the same cash flow sequence and gotten highly comparable if not complete agreement. 

Edit:  The ROI 5Yr per Quicken came in at 132.02%

Jim_Harman

One small correction to @q_lurker's example:

Quicken uses the closing price on the day prior to the start of the analysis period as its starting value. So for the 5 year Avg. Annual return calculation above, the Bought should be on 12/27/2016, using that day's closing price.  

q_lurker

@Jim_Harman is correct.  It can be challenging to keep straight what dates really apply.

Frankly, I was trying to parallel Jay's opening scenario where he suggested the buy on 12/28/16.  The more accurate or generic scenario is to already own the shares on the beginning date of the 5-year period (or any other defined period).  Quicken establishes the beginning value as the closing value from the prior day.  Note in my Performance Report snip that the Beginning Market Value on 12/28/16 is $0.  That is followed immediately by the purchase on 12/28/16 of $3,042 of value.

I did the report for the user specified dates of 12/28/16 to 12/28/21.

Perhaps a snip will help with a shorter time period.



The Investment Performance Report for the Last 12 Months runs from 12/30/2020 through 12/29/2021.  But that Beginning Market Value dated 12/30/20 is the closing value from 12/29/2020.  The portfolio view 1-Year Average Annual return calculation uses exactly those same dates and values and computes that same 47.88%.  (That Ending Mkt Value of $6,387 is actually a mid day price on 12/29/2021, so later calcs may vary slightly).

In the same fashion, for a 5-year period ending on 12/28/21 (Jay's original question), the key dates would have been 12/29/2016 through 12/28/2021 with the beginning value from the close on 12/28/2016.

Now to further cloud the picture, when I take that info into Excel, 



To get the same result, I need to back the starting date off one day.  Quicken's 12/30/20 Beginning Market Value date (which happened to use a 12/29/20 closing value) needs to be 12/29/20 in the Excel calculation.  It can be challenging to keep straight what dates really apply.

Jay Gourley

Thanks, q_lurker for checking my arithmetic. You and Harmon are both right. My mistake, or at least the major one, was in configuring the Quicken investment page so that a transfer of shares between accounts caused unexpected results. The unexpected results disappear when I change display options to group by security instead of by account.

Even with the one-day change of date, there's a minor but curious difference between Mathematica's result and Quicken even when the start date is the previous day. I pasted the cash flow and calculation below.

I was a little confused by the Excel representation. If I read it right, it shows a diminishing value as a positive return.

Also, thanks for pointing out that the Quicken investment performance report calculates the internal rate of return. I didn't realize that.

In[69]: quote5yrago = FinancialData["CSCO", {{2021 - 5, 12, 27}, {2021 - 5, 12, 27}}, Method -> "Legacy"];
cost5yrago = {{quote5yrago[[1, 1]], quote5yrago[[1, 2]] (-1)}};
ciscodiv = FinancialData["CSCO", "Dividend", {{2021 - 5, 12, 28}, {2021, 12, 28}}, Method -> "Legacy"];
quotenow = FinancialData["CSCO", {{2021, 12, 28}, {2021, 12, 28}}, Method -> "Legacy"];
cashflow = Join[cost5yrago, ciscodiv, quotenow]
result = FindRoot[TimeValue[Cashflow[cashflow], r, {2021, 12, 28}] == 0, {r, 0}]

Out[73]= {{{2016, 12, 27}, -30.68}, {{2017, 1, 4}, 0.26}, {{2017, 4, 4}, 0.29}, {{2017, 7, 5}, 0.29}, {{2017, 10, 4}, 0.29},
{{2018, 1, 4}, 0.29}, {{2018, 4, 4}, 0.33}, {{2018, 7, 5}, 0.33}, {{2018, 10, 4}, 0.33}, {{2019, 1, 3}, 0.33},
{{2019, 4, 4}, 0.35}, {{2019, 7, 3}, 0.35}, {{2019, 10, 3}, 0.35}, {{2020, 1, 2}, 0.35}, {{2020, 4, 2}, 0.36},
{{2020, 7, 2}, 0.36}, {{2020, 10, 1}, 0.36}, {{2021, 1, 4}, 0.36}, {{2021, 4, 5}, 0.37}, {{2021, 7, 2}, 0.37},
{{2021, 10, 4}, 0.37}, {{2021, 12, 28}, 63.53}}

Out[74]= {r -> 0.193159}

q_lurker

I was a little confused by the Excel representation. If I read it right, it shows a diminishing value as a positive return.

Using the Excel, I simply created the cash flow column plus Investment less Return columns.  That produced the 12 month 47.88%.  Oddly (?), if i reverse those signs, it still computes a positive 47.88% figure.  Is one saying diminishing value and the other saying increasing value?  Beyond my comprehension level.  All in all, as I presented it initially, the $4,446 initial investment paid off $147 in cash dividends and still became $6,387 through the course of 12 months.  Sounds like a positive return to me.

a transfer of shares between accounts caused unexpected results. 

Yep, one needs to be careful about what one is doing especially with things like transfers, spinoffs, mergers, and the like.  Both getting the transactions right, and making sure performance figures make sense.  Multiple accounts and multiple securities when needed.  Quicken doesn't always make that part easy. 

Reviewing my data, I saw I had a 5th $36 dividend recorded in error in 2020.  Deleting that dividend brings the 19.70% down to 19.54%.  Adjusting the opening value or price to your 30.68 drops the number to 19.32%.  Getting pretty close.

Jay Gourley

Thanks again, q_lurker.

Jim_Harman

For a further discussion of the ins and outs of Quicken's Avg. Annual Return and Excel's XIRR function, see this discussion
https://community.quicken.com/discussion/7637790/irr-calculations-wrong-within-q-2018-upgrade

The interesting part is near the end.