The magazine of the Melbourne PC User Group
Tracking Your Shares 
 Getting More Out Of Quicken
Deanna Ross
 deanna@mpx.com.au

Quicken's share tracking features are not as well known or utilised as its other functions. Accounting capability and GST readiness have stolen the limelight and the need for compliance in these areas is driving the current focus. However, the latest version of Quicken (version 8 - now called Personal, Personal Plus or Cashbook), as well as the previous version (version 7 - Standard, Deluxe or Quicken for Business), both contain a very useful module for monitoring share investments. The software not only tracks market values, but also has the ability to effectively evaluate performance. 

Performance

Performance is what it's all about! Let's look at several ways Quicken evaluates performance.

1. %Gain/Loss is a performance measure which compares the change in market value (since purchase) to the original purchase amount. Here we're really referring to the Percent unrealized gain or loss. (Figure. 1). You'll find this defined in Quicken's Help text together with an example. It's also the same thing as in Figure. 2.


Figure 2.

 


Figure 1.

This is not an annualised figure. That is, it is not a percentage per annum, but an absolute percentage change between purchase date and a subsequent date. It is a useful performance measure, but cannot be compared to any annualised figure such as a bank's interest rate on term deposits. It is readily accessible in Quicken's Portfolio View window, choosing the Holdings View. (If you do not find it there you can customize this view to include a %Gain/Loss column.)

2. ROI%, Percent Return on Investment, (Figures 3 & 4) is a performance measure which compares the growth in market value plus dividends received plus sale income from partial sales to the amount of the original investment. This calculation includes earnings. (You'll also find Quicken's definition of Percent Return on Investment in the Help text together with an example.) Figure 3 below which is the same as Figure 4


Figure 3


Figure 4

The ROI% calculation, like the %Gain/Loss, produces a percentage figure which is not annualised and which bears no relationship to the period of time being considered. ROI% is useful because it reflects dividend and sale earnings as well as any change in market value. This measure is also readily available in Quicken's Portfolio View window, choosing the Performance View. (Again, if you do not find it there you can customize the view to include a column for ROI.) 

Comparing these performance measures, %Gain/Loss reflects a change in the share price, whereas ROI% includes the effect of dividend earnings as well as a change in the share price.

I need to digress for a moment to explain why I do not recommend using any of Quicken's Reinvestment actions. Not using these will have an effect on the %ROI to the extent that Reinvestments will now not be excluded from the denominator of the above formula (Figure 3 or 4). I prefer to enter each Reinvestment as a dividend first and then a purchase, as this is what actually happens. I've found two inconsistencies in Quicken's Reinvestment actions: 

(i) The Reinvest Unfranked Dividend action can result in Uncategorized Income in Reports in both v7 and v8. If this happens the entry has to be deleted and replaced with two transactions - an Unfranked Dividend and a Buy.

(ii) In v8, you cannot create a single Reinvestment transaction when you want to Reinvest Franked Dividend with an Imputation amount. In this situation Quicken creates three transactions anyway - a Dividend, a Tax Credit expense and a Buy. So I pass reinvestments by!

3. Now let's look at the Average Annual Total Return, also called Average Annual Rate of Return (AARR). This performance figure is calculated as an annualised percentage for a share or group of shares between any two chosen dates. It takes into account: 
(a) the market value at the beginning date, 
(b) all dividends, interest, reinvestments, purchases and sales between the two dates and the timing of these, and 
(c) the market value at the end date. 

The formula (Figure 5) is complicated, but Quicken handles it well. (Again, you'll find a definition and an example in the Help text.)


Figure 5

Because it is referenced by two selected dates, the Portfolio View can't display the AARR. However, the Investment Performance Report (Figure 6) produces the AARR figure for us and shows the information used in performing the calculation. I have compared this calculation several times with Excel's XIRR function (the internal rate of return for a series of cash flows) using the same investment figures, and Quicken and Excel have returned the same answer every time, to two decimal places. (Be aware that Excel's XIRR rate is for a 360 day year. This implies that we should multiply Quicken's AARR percentage figure by 360 and divide by 365.)


Figure 6

I find this performance measure extremely valuable, because it is an annualised figure. This means I can compare the performance of a share, of all my shares, of a selection of shares, of a particular portfolio, with any other annualised figure like a bank's interest rate on term deposits, the All Ordinaries Index, the performance of a fund or funds, or a fund manager, or even with the performance of the same share over a different period. Also the periods compared can be different lengths of time. The annualised rate I get enables me to make meaningful comparisons and therefore decisions.

Example

I buy 2000 shares in XYZ Limited at $4.90 per share on 18/4/2000. Total investment including Stamp Duty and Brokerage is $9,844.10. On 4/10/2000 I receive a cash dividend of $380.00 (which, for the time being only, I will enter as unfranked). I record the daily closing price of XYZ: on 31/10/2000 the share price is $5.35. 

The %Gain/Loss for XYZ on 31/10/2000 is 8.69% since purchase.
The ROI% for XYZ on 31/10/2000 is 12.47% since purchase.
The AARR for XYZ for the period 18/4/2000 to 31/10/2000 is 24.77% per annum.

If I had bought the same number of XYZ shares for the same price but at an earlier date, say 15/11/1999, then on 31/10/2000 the %Gain/Loss would still be 8.69% and the ROI would still be 12.47%. However, the AARR would now be 13.12% per annum for the period 15/11/1999 to 31/10/2000, assuming there had been no other dividends in the period.

Each of these performance measures conveys useful, though different information. The Average Annual Rate of Return is the one that interests me most. If I look at it for several date ranges (say 1/7/2000 to today; 1/1/2000 to today; and the previous 12 months to today) on a regular basis (weekly, monthly and/or quarterly etc), it enables me to evaluate the ongoing performance of one share or of my whole portfolio in relation to what else I could do with my money. 

Provisos

I need to qualify my remarks so far. Firstly, the Investment Performance Report is not able to calculate large annualised rates of return for short periods. If your share produces a sizeable return over a few days or weeks, or even a couple of months, then if the annualised figure is greater than 10,000% or less than -99.9% the AARR will be returned as NA meaning "Not Available". Also, any figure over a short period, whether it be high or low, could be very misleading because annualisation assumes the share or shares are going to keep earning the same rate for a whole year. You need to be especially aware of this if you are using a calculation of the AARR from a short period for decision-making. The figure becomes more meaningful as the time interval being assessed lengthens and approaches 12 months or longer.

Secondly, since accurate share prices for the beginning and ending dates are fundamental for accurate Investment Performance Reports and therefore AARR figures, the more comprehensive the history of share prices you maintain the better. Share prices can be entered manually or easily downloaded from Quicken's Web site on a regular basis. When you "update" share prices once a week you download closing prices for the previous five working days into all Securities in your Quicken file for which you have entered an ASX symbol. Accurate and consistent dates are also important when recording purchases, sales and dividends. 

Some Important Issues

Now we specifically address three issues:

(A) Quicken provides a set of standard Investment Categories that are hard-wired to certain Actions. For example, Quicken places entries created by the Action Div. into a predetermined category called _DivInc which you cannot change. 

Suggestion: Design your own Investment Categories.

(B) Quicken grosses up Franked Dividends, so that once these are entered, reports are unable to show the franking component and the imputation component separately. 

Suggestion: Enter these components separately into the categories you have designed.

(C) Quicken also treats the Imputation Credit part of a Franked Dividend as an expense, whereas technically it should be transferred to an asset account where it stays until tax is assessed, at which time the imputation credit is taken up as a tax rebate. 

Suggestion: After entering this expense, transfer it to an asset account so that the Profit & Loss Statement, the Balance Sheet and Investment Performance Report are all correct.

In order to use these suggestions, you will need to record Buy, Sell and Dividend transactions as follows. Break each transaction into its components and enter them separately one at a time. Create Buy and Sell transactions as you normally do. Create all income transactions, one at a time as Miscellaneous Income (MiscInc or MiscIncX) and create all expense transactions as Miscellaneous Expense (MiscExp or MiscExpX). You can enter these directly into the transaction lines of the Investment Register if you are familiar with the way Quicken works.

Or use the Easy Actions drop-down menu and Record Income Event to enter each income component via the Miscellaneous Income field. Once you have tabbed out of this field a category box will be revealed that will allow you to choose your own category and even class. Similarly, use the Easy Actions drop-down menu and Miscellaneous Expense to enter an expense component (such as the Tax Credit "expense" part of a franked dividend) into the category of your choice.

Design your own Categories

(A) Design your own Investment Categories.
The way Categories are set up determines how useful your Business and Investment Reports are. So I suggest you create your own investment categories and leave Quicken's predetermined ones alone. In addition, by creating subcategories within categories, your reports will provide you with subtotals for each subcategory as well as with an overall total for each parent category. Figure 7 shows part of a Category List showing, as an example a selection of Investment Income and Expense Categories. Note the way each subcategory appears indented below its parent on the list. Keep the Category Name field as brief as possible to avoid report formatting problems, especially when nesting subcategories. The Category Description field can be longer. Now you can modify categories to suit the way you (or your accountant) want to see your reports.


Figure 7

Enter Components Separately

(B) Enter dividend and distribution components separately into the categories you have designed.

Let's go back to the example from the beginning. Enter the individual components of the dividend into categories of your own choice.


Figure 8

The dividend payment statement (Figure 8 above) shows the break up of the $380.00 paid on 4/10/2000.
Although Quicken tries to enter all this information in one box, you will soon see the benefit of entering each component of the distribution separately:

1. Record an Income Event: Miscellaneous Income of $133.00 Category _DivInc:1:FrDivC - credit the Cheque Account

2. Record an Income Event: Miscellaneous Income of $68.52 Category _DivInc:1:ImpAmtC

3. Miscellaneous Expense: Miscellaneous Expense of $68.52 Category _Tax Credit

4. Record an Income Event: Miscellaneous Income of $247.00 Category _DivInc:2:UnfDiv - credit the Cheque Account

It is important to understand why this "entry" requires four transactions:

1. The Franked Amount of the dividend component needs to be entered on its own so that you can keep track of this amount separately from the Imputation amount.

2. The amount of the Imputation Credit must be added to the Franked Amount to determine your Gross Income, so here you enter the Imputation amount separately as an income component. 

So far, Gross Income = Franked Dividend + Imputation Amount.

3. Since you don't actually receive any money for the Imputation amount at the time you receive the dividend, you need to deduct the Imputation amount now by creating a Tax Credit expense equal to the Imputation amount, so that the correct actual amount received is deposited in your bank account. Also, if you want to get a meaningful AARR from the Investment Performance Report, you will want to include only the amount that actually goes into the bank in the calculations. If you don't create the Tax Credit expense in Quicken, you will be overstating your share's AARR figure. Remember, you want to be able to compare your share's AARR figure with other rates (for example, a bank interest rate) which are all before tax rates. The Imputation amount itself is an after tax benefit which happens at a later date and is different for every taxpayer.

4. The Unfranked Amount of the dividend component needs to be entered on its own so that you can keep track of this amount separately from the other amounts.

So now,
Gross Income = Franked Amount + Imputation Amount + Unfranked Amount
and
Banked Amount = Franked Amount + Imputation Amount - Imputation Amount + Unfranked Amount.

  • Quicken will now report the Gross Income in the Income part of the Profit & Loss Statement as a total, and it will also show sub-totals for Franked Amounts, Imputation Amounts and Unfranked Amounts. (Section (c) following will deal with the Expense part of the Profit & Loss Statement.)

  • The relevant Bank Account will now show the correct Banked Amount.

  • Quicken's AARR and Investment Performance Report will use the correct Banked Amount in their calculations.

Figure 9 shows what the investment register now looks like.


Figure 9


Figure 10 shows the Investment Performance Report for the period 18/4/2000 to 31/10/2000 now that we've entered each component of the dividend separately.


Figure 10

At present, the Profit & Loss Statement still shows the Tax Imputation Credits as an expense. (Figure 11) and the Balance Sheet does not yet reflect the amount of the tax rebate (Figure 12).


Figure 11


Figure 12

Transfer the Imputation Tax Credit Expense

 (C) Transfer the Imputation Tax Credit Expense to an Asset Account so that it doesn't appear as an expense in the Profit & Loss Statement but so that it still forms part of the correct AARR calculation in the Investment Performance Report.

This is done simply by activating two memorised transactions. Steps 1 to 9 below set this up, and have to be done once only.

1. Add a new asset account with the name Shrs Imp Creds and description Share Imputation Credits.

2. Add a new cash account with the name Journals and description Holding Account for Journals. (If you dedicate this account to imputation transfers, you can easily find these transactions in the Register.)

3. Open Journals account.

4. Create a transaction in the Journals account as follows:

Date: As appropriate
Payee: Receive Tax Creds Into This Account
Receive field: Enter an amount of money, say $90,000.00, an amount easy to find and change later
Category: Select _Tax Credit expense category
Memo: Tfr Imp Tax Creds from Tax Credit Expense 
and click on Enter.

5. Memorise the transaction. (Highlight the transaction, click the Edit button, then select Memorise Transaction.)

6. Create a second transaction in the Journals account:

Date: As appropriate, same as in 4. (above) 
Payee: Transfer Tax Creds From This Account
Spend field: Enter same amount as in 4. above, say $90,000.00
Category: Select [Shrs Imp Creds] transfer category (the asset Account)
Memo: Tfr Imp Tax Creds to Asset Account 
and click on Enter.

7. Memorise the Transaction.

8. Make sure your Iconbar is activated. (Right click in tab area to reveal menu to Show Top Iconbar.) 
On the top menu bar click Edit > Options > Iconbar. 
Click New > locate and highlight Use a specific Memorised Txn... from the list that appears > click Change... button and enter text for the Icon Label, say FrTxCrd > click OK > then click OK again > in Assign Memorised Transaction to Icon box, select Receive Tax Credits Into item from drop-down list and also change Target Account to Journals account > click OK > click Done. 

You will now see a new Icon for the first memorised transaction on the Iconbar.

9. Create another new Icon as in 8. above, with the label ToSImpA, this time selecting the Transfer Tax Creds From item from the drop-down list in the Assign Memorised Transaction to Icon box, again changing Target Account to Journals account. 

You now have two memorised transactions you can use at the click of two buttons, whenever you need to transfer the Tax Credit Expense. This does not have to be for every transaction. You will need to transfer in order to produce a Profit & Loss Statement with zero Tax Credit expenses. 

Don't forget to go to the Journals Account and edit the two amounts to the required figures. After transferring the Imputation Tax Credit Expense, the Profit & Loss Statement (Figure 13) now looks much better. Also the Balance Sheet (Figure 14) now includes the Asset Account reflecting the amount available for tax rebate.


Figure 13


Figure 14

Now you have achieved your aim. The Profit & Loss Statement and the Balance Sheet are in line with what is required, and what's more, you can continue to rely on the Investment Performance Report and the AARR for any time interval you choose.

I have found these techniques very useful for tracking my own share portfolio, and I hope you will benefit from my experience.

About the Author
Deanna Ross, deanna@mpx.com.au is a Melb PC member and an Accredited Quicken Cashbook Trainer and Quicken Professional Partner.

Reprinted from the December 2000 issue of PC Update, the magazine of Melbourne PC User Group, Australia