Calculating position returns

[foglifter]

It's easy to track your personal PP returns (portfolio as well as specific positions) when you don't add new money. Things are not that simple when one adds money periodically into all or some positions. Does anybody have an idea how to calculate the total return %% for a position in this scenario? I think brokerage firms do that using some formula. Perhaps it's a proportionally-weighted average of returns of individual lots or something similar.

Any thoughts?

[MediumTex]

Track your contributions for the year.  That will allow you to come up with a rough estimate of the performance of the assets.

I'm sure there is a complex formula that will give you an exact performance number, I just don't know what it is.

I just take the beginning of year balance and end of year balance, and subtract contributions, and the difference gives me a ballpark performance number for the year.

[foglifter]

Thanks MT, your hint fortified my vague idea of tracking individual lots and I think I figured the way to get to the numbers that Fidelity shows me as an average for each position:

1. CB = sum of cost basis for all lots for a specific position
2. G = sum of gains for all lots for a specific position
3. Average return (%)  = G / CB * 100

I checked the formula for a couple of actual positions and it works pretty well.

[fnord123]

Why not just use the XIRR function in Google Spreadsheets (I assume Excel has something similar) across all positions/dates?  This works fine for me.

[foglifter]

Why not just use the XIRR function in Google Spreadsheets (I assume Excel has something similar) across all positions/dates?  This works fine for me.

Do you have an example by chance? The documentation doesn't list any... what the heck are "payments" and "dates"?

Thanks!

[HB Reader]

The internal rate of return (IRR) is the best performance measure since it adjusts for additions and disbursements of funds to a portfolio.  There are a number of IRR calculators on the web and IRR functions in most spreadsheet and database programs.   

If you've had a lot of additions and disbursements from your portfolio it can be a bit tricky to calculate on your own.  I haven't done it using the basic formula and a calculator since I was in school a gazillion years ago.

I have used Quicken since 1998 to track both my PP and VP.  It works pretty well.  Since it keeps a history of prices and all your transactions it will calculate the IRR for any period you've held the asset(s) as well give you a snapshot of your portfolio's value at any point of time in the past.   

[MCSquared]

Why not just use the XIRR function in Google Spreadsheets (I assume Excel has something similar) across all positions/dates?  This works fine for me.

Do you have an example by chance? The documentation doesn't list any... what the heck are "payments" and "dates"?

Thanks!

Here is a google doc that I quickly created that has the XIRR function and a CAGR calc as well.  Your contributions are negative (you are writing a check) and any withdrawals/current portfolio balance are positive.

https://spreadsheets0.google.com/pub?ke ... utput=html

[MCSquared]

Why not just use the XIRR function in Google Spreadsheets (I assume Excel has something similar) across all positions/dates?  This works fine for me.

Do you have an example by chance? The documentation doesn't list any... what the heck are "payments" and "dates"?

Thanks!

Here is a google doc that I quickly created that has the XIRR function and a CAGR calc as well.  Your contributions are negative (you are writing a check) and any withdrawals/current portfolio balance are positive.

https://spreadsheets0.google.com/pub?ke ... utput=html

Sorry, not sure I linked the document the proper way.  Try this:

https://spreadsheets.google.com/ccc?key ... y=CMva1q0M

[foglifter]

Thanks Clive, it makes sense now!

[fnord123]

Here's another XIRR tutorial: http://www.experiglot.com/2006/10/17/ho ... d-returns/

(Clive's was good, but this is a complex enough topic that having multiple lessons is a good thing imo)

Note: If using GoogleDoc's Spreadsheet XIRR function rather than Excel's, the description at the above link may not be exactly right - see my follow-up post a couple posts down.

[foglifter]

Here's another XIRR tutorial: http://www.experiglot.com/2006/10/17/ho ... d-returns/

(Clive's was good, but this is a complex enough topic that having multiple lessons is a good thing imo)

That's great article, thanks for sharing.
If I understood it correctly, if I want to see an up-to-date return of a portfolio I need to put the current balance as a negative value into the last row, correct?

[fnord123]

Here's another XIRR tutorial: http://www.experiglot.com/2006/10/17/ho ... d-returns/

(Clive's was good, but this is a complex enough topic that having multiple lessons is a good thing imo)

That's great article, thanks for sharing.
If I understood it correctly, if I want to see an up-to-date return of a portfolio I need to put the current balance as a negative value into the last row, correct?

That's what the article says, but I think it is actually the opposite on GoogleDocs - at least, the opposite is what I do in my PP GoogleDocs spreadsheet. Here's a simple example that I just tried:

1/1/2009 Buy -$1,000   <-- Negative # for money spent buying an investment
1/1/2010 Today's value $1,100    <-- Positive # for current value of the investment

Assume the dates are cells A1 and A2 respectively, and the money is cells C1 and C2 respectively.  The XIRR formula I used was: =xirr(C1:C2,A1:A2).  The output was the expected CAGR of 10%. When I changed it to two years, the CAGR dropped to 4.88%, which seems correct given compounding.

And if you had multiple transactions:

1/1/2009 Buy -$1,000   <-- Negative # for money spent buying an investment
1/1/2010  Buy    -$1,000  <-- More negative $$ for buying the investment
1/1/2011 Today's value $2,200    <-- Positive # for current value of the investment

Google's XIRR results in 6.52% CAGR, that seems about right.

[foglifter]

I think in Google Docs it doesn't matter: I just tried both ways (positive and negative investments) and the result is still the same.

[foglifter]

1. CB = sum of cost basis for all lots for a specific position
2. G = sum of gains for all lots for a specific position
3. Average return (%)  = G / CB * 100

I just realized that I asked about a different metric - a total return, not an annualized return. But in fact I also realized that CAGR would be my next question, so now I have both my questions answered.  I will be tracking the number of shares and the share price for each transaction - this will  allow me to calculate both total return (or what is the correct name?) and CAGR.

Both metrics are useful, although I think total return might be more useful for tracking short-term performance (i.e. variable portfolio), while CAGR is important for both PP and VP.

Thank you all for your help.

[Coffee]

I don't understand why the online brokerages can't provide this data, with the click of a button?  Dang-it!

[foglifter]

I don't understand why the online brokerages can't provide this data, with the click of a button?  Dang-it!

From what I see Fidelity only provides the total average return, not the annualized return. When it comes to 401(k) the picture is even more vague: usually you only get YTD return for the whole portfolio.

It looks like when it comes to investing do-it-yourself is the best approach. And Excel and Google are the helpers.