nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Return by Type Not Calculating Correct Values

allen_car

I have a report of "Returns by Type - Last 12 Months" but I am getting a crazy Avg. Annual Return for one security. I modified the report to only include the single ETF which is causing the problem to troubleshoot but I am missing something.

The report shows a 79% return when the gain was $1479.91 on a $131,516.10 investment which should have been a return of 1.13%. See the screen shot of the problem.

The ETF was already owned at the start of the 12 month period and sold in the 1st moth of the 12 month period.

Does anybody have a suggestion what is wrong? I have been using Quicken Premier for over 20 years and run all my investments with it; so I am not a novice but I am at a loss what I am doing wrong on this report.

Find more posts tagged with

Investments

Investment Reports

Comments

Mark1104

that is the way Q calculates it.... it is not wrong... you owned the investment for 6 days and the annualzed return is 79%. it's not the return for just those 6 days. you made $1479.91 in 6 days, so it assumes that over 365 days you would have made $90,000! $90,000 divided by $131,000 is around 70%. close enough.

allen_car

Wow! Thanks. That makes sense because a week ago is was showing a MINUS return because my 12 month look back was starting before the big drop at the beginning of 2022.

I mainly ignore the Quicken calculated returns because I don't understand the underlying method or the method has quirks which don't work for me. My main use is to record all investment transactions and compare them with the brokerage 1099's at tax time.

q_lurker

@allen_car It is perhaps helpful to note that if you revise the setup to include the cash component the numbers are in line with your calculation. Using Excel's XIRR function the comparison becomes:

Date	ETF Only	ETF + Cash
1/24/2022	(131,516.10)	(131,516.10)
1/31/2022	77,779.60
1/31/2022	55,216.41
1/24/2023	-	132,996.01
XIRR =	79.22%	1.13%

That first column is essentially saying you started with $131K, took out $133K early on from the ETF, and it kept growing at that rate. It is more fundamentally a weakness in the math formulas when short term holdings are key. That 79.22% number is highly dependent on the 1/31/22 date. As that date might change toward the end of the period, the 79% moves to the 1% figure.
The second column essentially says you started with the $131K in Cash and ETFs and ended with $133K in Cash and ETF. Nothing was 'withdrawn from that mix. The picture doesn't care when or how often you made changes to the ETF-Cash mixture; only what you started with, what you ended with and whether you added to or withdrew from the aggregate.

You are right to be cautious about drawing conclusions. Without a reasonable understanding, the results can be misleading.