12CP Cash Flow Bug



#17

I don't think that this has been noted here before.....

The latest version of the 12CP appears to have bug in the way the CF(j) function works. In the original 12C when you enter a cash flow using CF(j) it stored the cash flow in the j'th register and set the value of N(j) to 1. In the new 12CP it doesn't set the value of N(j) so whatever value was there previously is kept. This directly contradicts the summary of the CF(j) function given in the appendix of the manual. However, if you follow the standard of clearing the registers before entering any cash flows this will set the value of N(j) to 1 for all j's and so the examples in the manual work.

Actually, I like this "bug", it makes more sense for CF(j) to not set N(j) to 1 when editing the cash flows. But it's a significant change from the original 12C and should be documented.


#18

Thanks Katie - indeed the appendix should be changed. The manual uses STO to change a Cfj, which is difficult if j>20<G>, so should also be changed.

Cheers,
Tony

#19

I have to admit as one who has newly acquired a 12CP out of novelty and curiosity that I am not surprised to learn about stuff like this.

I am learning very quickly not to take for granted that when I store a value in any financial register that it will remain unmolested after a calculation. Fortunately, the examples in the manual seem to warn you about this more often than not, but I have been caught more than once over the past few days.

I have to confess--perhaps sacrilegiously in a forum that celebrates the handheld--that most of my recent forays into the mysteries of financial computation have been done in Excel. I have even gone so far not to trust the internal routines there and set up the computations myself.

For example, it occured to me that even though I know a lot about mortgages, loans, and leases--in general, TVM situations with regularly spaced payments of equal value--I really did not understand where my mutual fund manager got the quarterly, annual, 3-year, and 5-year returns reported my my quarterly reports.

I guessed it was a variant of the discounted cash flow analysis problems presented in the hp12cp manual, but of course I couldn't use the calc because my cashflows (contributions) were not evenly spaced. I discovered the Excel XIRR and XNPV functions, but even then at first didn't put my trust entirely in these, which are fortunately pretty clearly documented.

Rather, I set up a spreadsheet to compute the FUTURE (i.e., end-of-period) value of each cashflow, weighted according to when it was invested, summed up the terms, subtracted the actual end-of-period portfolio value, and adjusted my guess of IRR (manually or by Goal Seek) until this difference went to zero. Of course, this ended up agreeing with the XIRR result, since I was basically using a variant of the very formula that Excel uses.

Of course, these computations only roughly approximated my reported returns, and in some cases were off by over 100 basis points. That's about the point I learned about the difference between time-weighted and money-weighted rates of return, and figured my reports gave the former--it is, after all the industry standard. Computing these returns in a spreadsheet, either by determining the exact value of my portfolio at the time of a cashflow or estimating the return for a particular quarter using the computationally pretty simple Modified Dietz Method, my numbers are very close to or often the same as the reported ones (except for the fall quarter of 2002, which differs by 10 to 20 basis points for reasons that I can't figure out). As one devoted to a lifetime of mathematical curiosity, I found this very gratifying.

My point of sharing this little saga is to make this point: I felt more confident in my financial computing in a visually informative and highly user-controlled spreadsheet environment. I learned a lot, and if there was a mistake in data entry it was easily found. Which leads to my admittedly scandalous question: what is the advantage of handheld financial computing devices like the 12cp, 17BII, etc? I am coming at this from the angle of a fascinated amateur, but in the 21st century do people who do this for a living (investment and real estate professionals, bankers, accountants, etc.) routinely whip out a financial HP on in the car or cafe or whatever to make a mission critical decision? Or do they wait to get back to a laptop or desktop so they can make darn sure, based on the sort of visual affirmation I have observed in whatever spreadsheet or financial computing software they prefer, that their calculations are right?

I will still enjoy and use my 12cp, as I do all of my HPs, but I must admit that if I really want to work through another financial calculation like the one I just described, I will settled down on the couch with laptop and work it through in a spreadsheet.

Eager for comments. Please keep flames to a minimum--I burn easily :)

Les


#20

Depends on the task.

When I'm running mortgage payments, I use the 12cp.

When I'm adding up numbers from a set of journal entries to correct them, I grab a 12cp.

When I am modeling the purchase of a competitor over the next 5 years with expected cash flows, expenses, tax offsets, etc., I grab a spreadsheet.

Depends on the complexity of the calculation.

Given the huge sales the 12c and 12cp appear to still be commanding, business users (and test takers and students) appear to be buying and using them in droves.

Gene

#21

Quote:
Which leads to my admittedly scandalous question: what is the advantage of handheld financial computing devices like the 12cp, 17BII, etc?

That's a good question Les. One answer can be found at the Tales of the amazing HP 12c contest winners web page. I wish HP would release more of these stories.

#22

Quote:

That's a good question Les. One answer can be found at the Tales of the amazing HP 12c contest winners web page. I wish HP would release more of these stories.


The very first sentence of the story says:

"At Columbia Business School in 1999, we were required to have and taught to use a 12C."

The last sentence says:

"After hearing my story, the professor made 12C's mandatory for students!"

What's the difference between being "...required to have..." and being "mandatory"?


#23

In real life, I'm a consultant to wall street investment advisers. Many of my clients give all their employees a 12C on their first day of work along with a stapler, tape dispenser and letter opener. They do basic math on them, TVM work and bond price/yield calculations; and use spreadsheets for anything more complicated. Pretty much the only program they use is a a few lines to "link" returns, something like:

01      1
02 0
03 0
04 /
05 1
06 +
07 x<>y
08 1
09 0
10 0
11 /
12 1
13 +
14 x
15 1
16 -
17 1
18 0
19 0
20 x
21 GTO 00


Edited: 8 June 2006, 9:24 p.m.

#24

Quote:
I guessed it was a variant of the discounted cash flow analysis problems presented in the hp12cp manual, but of course I couldn't use the calc because my cashflows (contributions) were not evenly spaced.

Actually, I have to correct myself in this regard. I think that one could creatively use the CF registers to store alternately a cashflow and the date on which it occured, and write a program that uses deltaDAYS to assist in calculating the requisite weights and then sums the weighted PVs (or FVs) in a register, given an inputted rate of return. Since each cash flow would take up two registers (an extra one to store its date), and since the program itself occupies memory, this would only be good for modest size problems. If only programming the 12c were not such a nuisance--no alphanumerics, and hard to edit!

An interested lurker has emailed me suggesting an HP12C program that computes Modified Dietz estimates. Using similar logic to the above, I am sure it is feasible. Also realistic for a modest size problem, since if one has a time-weighted return problem that spans a length of time and comprises many cash flows, you really want to break it down with a few portfolio valuations to improve the overall accuracy. In my portfolio, modified Dietz really goes astray if the periods in question are longer than a quarter, but it really does well vis-a-vis the true time-weighted return calculations for computing quarterly returns.

Les


#25

Modified Dietz with periods of one month plus whenever there is an asset flow of 10% (of current net value) is the current industry standard GIPS for performance calculations. This standard (formally AIMR) has been evolving toward greater accuracy and will soon require irregular time intervals to cover periods whenever there is any asset flow.

I've been working in the financial industry for 25 years and am still amazed at the glacial pace of acceptance of obviously better computation methods and other standards. There's no good reason why daily (or even continuous) performing measurements shouldn't be the norm for all assets -- there's certainly enough computing power on everyone's desktop to do this. Perhaps the limited cash flow ability in HP12C is the problem, it's been holding the whole industry back! ;)


#26

Thank you! That explains so much for me.

In my portfolio, my asset flows are so irregular that it is actually not onerous at all to look-up the actual values whenever I have had a cash flow. Actually, I have quite a few months where there are no cash flows, and in those cases modified Dietz reduces to a simple return calculation.

However, if my fund managers don't do this every time and instead follow the standard you note, this explains the very occassional quarter where their numbers and mine are out by up .1 or .2 percent.

That said, in a general sense I really think it is imperative for investors to know the difference between time weighted and money weighted return calculations. I can see why fund managers prefer the former, but I suspect investors want to know about the latter too, to answer the question "this is what happened to my actual money (not only the value of the underlying investments) while pursuing the path I did over the last 3, 12, 36 months, etc."

I agree with you that there is no reason not to calculate so-called true daily weighted returns, and I am surprised that my statements advise that modified Dietz is used, since it so easy for me as an endline user to look up daily values on the web thru the company's own database!

FWIW, the following page explains the calculation of returns by various means the most lucidly of anything I have found. This is old hat to you as a financial professional, but I found it very helpful:

http://www.dailyvest.com/products/calc_method.asp

Les

#27

Quote:
There's no good reason why daily (or even continuous) performing measurements shouldn't be the norm for all assets -- there's certainly enough computing power on everyone's desktop to do this.

There is on my desktop!

I am going to take it upon myself to start recording daily balances and cashflows, when they occur, in a spreadsheet, and start computing daily returns in a more dynamic way. Soon, I will have enough days to regularly generate monthly and quarterly returns, and, if I keep it up, will start generating annual rates each day.

I can, when I have time, expand my database of daily values retroactively as I get the time to look them up, but it is no major hardship to start recording my total portfolio balance each day starting now. If I were really keen I could track the balances of each fund (I think my portfolio is spread across 8 or 10) but one step at a time!

I am intrigued to learn that the mathematics of these computations is not hard--indeed, the modified Dietz calculation just uses sums, differences, products, and quotients, but no powers or roots--but the actual meaning and practical implications of the various return measures can be confusing for the lay investor and controversial among financial professionals.

Makes one smirk at those who opine that math is always an exact science ;)

Les

#28

Quote:
Modified Dietz with periods of one month plus whenever there is an asset flow of 10% (of current net value) is the current industry standard GIPS for performance calculations.

Just wanted to clarify something about this--does this refer only to net asset flows into or out of the portfolio--i.e. net contributions or withdrawals? Or can it also refer to occasions when there is a substantial reallocation of monies within the porfolio (i.e. when I periodically "balance" by selling off fixed income investments to buy more equity units or vice versa) without there being a net inflow or outflow?

I ask because it seems feasible that the rate of return of the underlying investment mix can be altered significantly by such manoeuvres, making the Dietz assumption of constant return throughout the period harder to support. This could lead to inaccuracy in the estimate.

Let me give an example: On Jan 1 my portfolio is worth $100,000 and on Jan 31 it is worth $105,000. I make no contributions or withdrawals during the period and all earnings (interest, dividends, etc.) are automatically reinvested. HOWEVER, on Jan 21st I reallocated $18,000 from my conservatively performing bond fund to the somewhat more successful domestic equity fund. However, for the month of January my net asset flow is zero.

In this situation, Modified Dietz reduces to a simple return rate since the cashflow terms in the formula go to zero. So that estimate of my return for the month is simply 105/100 - 1 = 5%. That said, do the GIPS recommendations require a valuation on January 21st with a linking together of the subperiod returns, even though the net asset flow on that day is zero? Would it make a difference?

Something tells me I am overcomplicating this, but is not every day that a Wall Street consultant is so cheerfully helpful and available ;)

Les


#29

Actually, I think I have answered my own question. It occurred to me that a simple return rate is exactly that, and that if there are no net flows into or out of my portfolio in the period it doesn't really matter what goes on internally in the belly of the investment. In this case, my investment still grew by 5% in the month, and if that is due to 1% during a sluggish Jan 1 to 21 subperiod and 3.96% in the last 10 days, so be it. A 5% actual rate of return for that month is exactly that no matter where or if I choose to cut the pie smaller.

Am I getting this?

Les


#30

You've got it!

The only reason to re-evaluate the portfolio when there is a flow from one asset to another (within the same portfolio) is because you want to compute the performance of one of those assets (or class of assets). If there are no external flows there's no need to re-evaluate; it doens't matter when during the period your gain was recorded since, like you said, a 5% return is a 5% return.

#31

Just thought I'd let you know that I have gone back about three years so far and have computed monthly rates of return--simple returns for months with no net cash flow, Modified Dietz estimates for months with cash flows < 10% of total (I rarely have made more than one contribution in a month, so this makes for easy calculation), and midperiod valuation and computation of the true time weighted return in the one recent month that I made a sizeable deposit. I linked together returns and computed quarterly and, where possible, annual return rates, and compared them to my quarterly statements.

Save for occasional differences of at most 0.1%, likely due to slight differences in rounding in the many additions and multiplications that calculate a rate, I have been able to generate precisely the results that my statements report to me. Not just roughly close but pretty well bang on every time. This tells me that my investment folks compute and report my returns according to the minimum requirements of GIPS that you have turned my attention to.

Thank you again so much for educating me on this. My mission in the last few days has been to demystify and replicate for myself the computation of rates of return in my mutual fund portfolio. It may not radically change my saving and investment behaviour, but at the very least it has been a very intellectually satisfying experience.

--Les


#32

Les,

You're one of the very few people who bother to figure it out. The clients of my clients (mostly high net-worth individuals) never bother to do what you just did, they just argue that they don't understand how they could have performance of such-and-such. But then again, this is precisely the argument that they're paying for! :)

-Katie

Edited: 11 June 2006, 10:11 p.m.


Possibly Related Threads...
Thread Author Replies Views Last Post
  38E/C, 12C, 12CP date arithmetic bug Katie Wasserman 26 2,422 10-19-2011, 08:57 PM
Last Post: Miguel Toro
  12c+ vs 12cp Erturk Kocalar 7 983 03-08-2011, 11:32 AM
Last Post: Marcus von Cube, Germany
  10bII, 12c, 12cp, 17b2+ ... these are the ones I see...Poll...how about you? Gene Wright 23 2,322 05-30-2010, 09:14 AM
Last Post: Don Shepherd
  Gamma (and Factorial) for 12C/12CP Les Wright 9 1,201 02-02-2009, 06:18 PM
Last Post: Egan Ford
  Gamma and Factorial for 10C/12C/12CP (thread hijack) Les Wright 1 511 01-28-2009, 08:19 PM
Last Post: Egan Ford
  Received new 12CP Anniversary Edition today, but... Stephen Easterling 6 878 04-22-2008, 12:28 PM
Last Post: Stephen Easterling
  Cash Flow Analysis for the HP 35s *UPDATED* Miguel Toro 0 389 08-27-2007, 12:21 PM
Last Post: Miguel Toro
  Cash Flow Analysis for the HP 35s Miguel Toro 0 451 08-26-2007, 03:26 PM
Last Post: Miguel Toro
  What will replace the 25th anniversary HP 12cp? mk 5 729 03-31-2007, 05:23 PM
Last Post: Namir
  Cash for your trash? Palmer O. Hanson, Jr. 39 3,899 03-21-2007, 03:36 PM
Last Post: Trent Moseley

Forum Jump: