Report for YTD Investment Account Performance Summary
I am trying to generate a simple report to show "Last Year End Balance", "Current Balance", "Growth/Loss", and "% of change" by invesment account. The Investment Performance YTD report is the closest to what I want, but it shows an "Avg. Annual return" and I do not see an option to change this to YTD Return. Then the Income by Security and subtotaling by account report comes close, but the "Income as %" is a little low compared to the actual increase in the account and it includes the detail for each security. I do not see a way to summarize this by account. Does anyone know of a way to gerate the report I want in Quicken?
Answers
-
Use the Investment Performance Report but set the time period to Annual and Current Year, not YTD. This will give the annualized performance assuming that the share prices do not change between now an the end of the year. This is good an assumption as any for securities whare the prices vary, but not so good for money market funds and other fixed-price securities where you would want to include the full year's interest.
QWin Premier subscription0 -
Jim, Thank you. This gets me pretty cose. The Investments column has numbers close to year end, but not exact. Do you know what the Investment column consists of? The Avg Annual Return is really a YTD Return, which is what I want. I would like a column that shows the gain/loss in dollar amounts for each account. But I guess there is a limit to the customization of the reports. I will use this report.
0 -
In the Investment Performance Report, subtotal by Account and click on Details to see the description of each number that drives the results.
You may be able to get the gain/loss dollar amounts you want by manipulating the detail numbers. Depending on how you want to handle money added to or removed from the account, it may just be the total returns - total investments.
Note the annualized numbers in the IPR may still be misleading if the account did not exist at the start of the year. For example if you rolled one account into another during the year, you should include both accounts in the report an use the new Overall total number at the bottom for the percentage gain/loss.
There is also a $ gain/loss column in the Investing > Portfolio views but I am not sure how it is calculated.
QWin Premier subscription0 -
I believe I've accomplished what you are trying to do with 3 Quicken reports and an Excel workbook.
The Quicken inputs are 1) the Investing view which shows the current holdings of each investment account, 2) a report by year of all my investment account balances, and 3) a net withdrawal report which shows by year the net amount added to or withdrawn from each account (for accounts that had no deposits or withdrawals, the value is 0). I refresh #1 and #3 weekly or monthly and #2 annually. The investment return $ for each year = ending balance minus starting balance + withdrawals during the year (not including advisory fees) - deposits. For example, if your IRA is 1,000 larger at the end of the year and you made deposits of $500, then your investment return is $500.
In Excel, you can easily compute this number for each account for each year. The investment return percentage is the return divided by the starting balance for that year. This is not as good as an IRR, but it is pretty close to the same thing when your withdrawals and deposits are small relative to the starting balance. I compare each return % with either the S&P % for that year or the short term treasury return for that year, depending on what type of investments the account holds.
This approach doesn't care what kinds of assets you have in the account, how much trading you do, or who manages it. If you have an all equity account managed by an advisor, it is a fair comparison to ask, "would I have been better off with an S&P 500 ETF?" If you use the current version of Excel, you can also price every position in near real-time, and answer the question, "How has my account done from date x to this minute?"
Has anyone else done anything like this?
0 -
Thanks for that info. Here are a couple of comments.
- When comparing your returns to indexes, be sure you are using the total return versions of the indexes, which include reinvested dividends. For example in Quicken the S&P 500 TR index is index:spxt.
- I think all the data you need - starting and ending balances plus net deposits and withdrawals - is in Quicken's Investing Performance Report even if you don't use its IRR calculation.
QWin Premier subscription0 -
- Thanks for your comments, Jim.
- 1. I haven't used that index because I can't seem to get Excel to find it in its stock database, but you are correct that that would be more accurate. When I do the comparison I just mentally add 1-2% to the S&P 500 growth rate. I use the comparison only as a rough guide because there are other variables besides return to consider. I have one account that does a lot of tax loss harvesting, for example.
- 2. My version of the Investment Performance report does not display the starting and ending balances, nor does it show the change in value net of deposits and withdrawals. When I click on "show detail", I see more (actually too much more), but there are still some manual calculations to do to see what I'm looking for. Am I looking at the wrong report? I understand the IRR calculation and use it sometimes, but find it pretty useless for a YTD calculation, which is my leading indicator that tells me I've got to watch the account more carefully.
While the Excel spreadsheet calculates all of this continuously, it's taken me a while to realize that in the short term, the best thing to do after looking at it is nothing. I used to be very impatient, expecting a strategy to work quickly. I take a much longer view now, but I still find the more frequent reporting to be helpful. I guess it shouldn't be surprising that broker statements don't make it easy to do simple comparisons on a YTD or multi-year basis.
0 -
As I have said earlier, if you want to see YTD performance in the Investing Performance Report, set the Period to Annual and current year. This shows the full year performance assuming it will be flat for the rest of the year.
QWin Premier subscription0 -
See if this idea I just created offers any value to you:
0
