Am I beating the Dow?

July 8, 2005 5:19 PM

How would I get a good approximation of a portfolio return on an investment account which has regular incoming and outgoing cash-flows, so that I can compare it to mutual fund and stock index performance?

I have a trading account which I've deposited money in on an intermittent basis, to buy stocks, income trusts and mutual funds, and have on occasion withdrawn money when needed. Is there a simple and effective way to estimate the return in any particular stretch of time, or do we have to get into fairly complex mathematics? For instance, if I put in 40,000 and my portfolio goes up to 80,000, and then I take out 30,000, I obviously get a different "return" based on whether I assume I turned 40,000 into 80,000 or 10,000 into 50,000 (100% versus 500% return).

If there's no quick and dirty way to do this, can anyone recommend some freeware to calculate this? I'm assuming it's probably a function of Quicken or something, but I'd prefer something free.

posted by Big Fat Tycoon to work & money (8 comments total)

I used to do this for a living. It's a pain in the neck if cash flows are irregular. It's not complicated math, just involved.

If you have Excel (or OpenOffice) you can key in cash flows along with their dates and the balances on those dates, compute average annualized returns over each period bridging two cash flows and then do a day-weighted geometric average to get your average return.

If you just want to do it for one stretch of time (that is, between two adjacent cash flows,) use the following formula:

Annualized Return = ( (EB/SB)^(N/n) ) - 1

Where:

EB is the balance at the end of the period before you deposit/withdraw money

SB is the balance at the start of the period after you've deposited/withdrawn money

N is the number of trading days in a year (usually 250 or 252)

n is the number of days between those two adjacent cash flows (count trading sessions)

So, if I opened the portfolio on Monday morning, Apr 1 with $10,000 and took out $5,000 on Friday evening April 5 after a runup turned my $10,000 into $10,100:

SB = 10,000
EB = 10,100
n = 5
N = 252

and,

Annualized Return = ((10100/10000))^(252/5) - 1 = 0.65119 or 65.119% -- Not too shabby.

Note the $5,000 withdrawal doesn't enter into this calculation, though you'd use 6,100 = 10,100 - 5,000 as your starting balance for your next period.

If nobody comes up with an easier solution, drop me an email and I'll send you an example spreadsheet -- it sounds complicated but the most tedious part is getting the numbers into the worksheet.
posted by Opposite George at 6:31 PM on July 8, 2005

I'd guess calculate the interest on each inter-transaction period, and then work it out for your longer period on pro-rata basis. (Not super-confident on that though).

I'd use excel.
posted by pompomtom at 6:39 PM on July 8, 2005

Excel's Analysis ToolPak includes the XIRR function, for situations exactly like this. The Analysis ToolPak ships with Excel, but you have to go to the Add-Ins manager (Tools:Add-Ins... in the Mac OS X Office 2004 version of Excel) and check it to get it to load (and if you did a custom install of Office, you might have to install it the Analysis ToolPak first. If you don't have Office, I'm told other spreadsheets have the equivalent of an XIRR function, but I haven't used them so can't say for sure.)

Once you've got it loaded, you make two parallel ranges, one with a series of dates, each being a date on which cash moved in or out, and the other range having the corresponding change in the cash amount. You usually have a final value which is today's date and the negative of the current value (as if you withdrew the entire amount). You then apply XIRR and the result will be a percentage growth (hopefully!) or loss over the time period.

Excel's help explains it pretty well but a Google search shows lots of results too. I use Excel and XIRR for this very purpose.
posted by blm at 8:16 PM on July 8, 2005

Although it's appealingly easy to run, XIRR technically falls short of solving BFT's problem. Here's why:

An IRR is affected by cash flow timing and magnitude, because the calculation puts more weight on large cash flows. This measure tends to be used in capital planning and bond valuation, when cash flows are projected ahead of time. IRR is the answer to the question "If I pay X for a (predetermined or at least pre-modeled) set of cash flows today, what's my effective rate of return?" IRR is extremely useful for pricing and was the backbone of most of the models I built when I was getting paid to do such things, but philosophically it's an ex-ante measure and as such it's more suitable for calculating your returns on a basis comparable to fixed-income and fixed-income-like investments (e.g., bonds, asset-backed securities, CMOs.)

There are two reasons why a time-weighted measure is the better number for BFT to calculate:

1) BFT wants to compare his performance to mutual funds and stock indexes. Industry practice (i.e., the AIMR Performance Presentation Standards) is to present these returns on a time-weighted return basis -- an IRR number isn't directly comparable.

2) The reason the industry presents these numbers on a time-weighted return basis has to do with the need for a benchmark of investment manager performance. A manager of a mutual fund has little control over the cash flows in that fund because shareholders can deposit or withdraw money at will. So presenting portfolio performance in terms of IRR hides the information a mutual fund shopper wants -- that is, how did this manager do against the market and other managers -- by wrapping it in a layer of cash flow fuzz.

For example, take a look at two funds that allocate their investments identically in an exponentially rising market. Fund A is run by a large financial services group and is pushed hard by its salespeople to their clientele, resulting in increasing investments over time. Fund B is run by a sole proprietor managing a portion of a pensioner's assets, and is subject to steady withdrawals as money is needed to pay for retirement vacations or grandchildren's college funds and wedding gifts.

Even though both managers make the same investment decisions (and are therefore equally effective in the sense BFT wants to measure,) in a rising market with increasing rates of return over time Fund A will always have a higher IRR than Fund B. Both funds, however, will have identical time-weighted rates of return.

Another way of looking at this is that the choice between IRR and time-weighted returns is a choice of which simplifying assumption one makes in solving BFT's problem of "how do I allocate the returns I receive against my investment timing?" IRR chooses to concentrate only on cash flows, making the question of whether one allocates return to a dollar invested at time t0 or time t1 irrelevant. The return is completely specified by the cash flow vector. Even if IRR is an intuitively satisfying measure of return for BFT, and it may well be if he thinks more in terms of the fixed-income or capital markets than the equity market, it leads to an answer incompatible with the equity market numbers BFT wants to compare his results to.

The time-weighted rate of return calculation solves the allocation problem by assuming BFT is continuously rebalancing his portfolio. This assumption means the calculation allocates each day's returns to that day's starting value. By doing this BFT ends up with a more concrete measure of his effectiveness as an investment manager and gives him a number that's directly comparable to return measures available from historical fund and market data.
posted by Opposite George at 10:57 PM on July 8, 2005

There's really a few ways to calculate this: dollar weighted and time weighted. And it's a question of what you want to measure: the amount you were able to get out of the investments, or your ability to pick investments, independent of the amount invested. Dollar weighted will do the former and time weighted will do the latter (time weighted returns are what is required by professional performance standards, like GIPS).

Time weighted is like Opposite George said, except to get the total return for the portfolio over time you would need to geometrically link together a series of the [Ending Balance/Starting Balance] immediately before/after the cash flows occur, which is just multiplication.

In the example, if you had 10,000 at the start.
It goes to 10,100, then you withdraw 5,000.
You now have 5,100.
This drops to 5,025. Then you add 1,000.
The 6,025 balance climbs to 6,250.

There's 3 different cash flow periods. You need to calculate the return in each of the periods (End/Start), then just multiply them together.
1. 10,000 -> 10,100 (1.01)
2. 5,100 -> 5,025 (0.9853)
3. 6,025 -> 6,250 (1.0373)
Multiplied together: 1.01*0.9853*1.0373 = 1.0323 => 3.23%

Dollar weighted returns will be greatly impacted by the timing of cash flows in or out of the portfolio - this is what XIRR will calculate. However, it will be indicative of the performance of your money invested, since it will weight the periods with lots of money invested more heavily.

Very generally, fund managers that have no control over cash flows, like a normal mutual fund, are measured by time weighted. Fund managers that do have control (i.e. they're a private equity fund, they've found something they like and make a capital call on their investors) will be measured by dollar weighted.
posted by milkrate at 10:58 PM on July 8, 2005

Or what Opposite George said.
posted by milkrate at 10:59 PM on July 8, 2005

P.S.: BFT, after having put you through all this financial esoterica I hope it turns out Quicken or freeware will do what you want :)

(my offer of a sample spreadsheet still stands.)
posted by Opposite George at 11:03 PM on July 8, 2005

Thanks for all the responses, you've all been very helpful, particularly Opposite George. George, I've sent you an email requesting that spreadsheet. Perhaps I was a bit naive in thinking that there'd be software out there that would calculate these things -- I did find a few free IRR calculators, but none of them seemed to deal well with this irregular cashflow situation.
posted by Big Fat Tycoon at 4:49 AM on July 9, 2005