Has anyone seen (or written) a Modified Dietz program for the 12C? I've searched for one on the Web and on these boards  but haven't turned one up. Thanks!
Modified Dietz program for 12C


« Next Oldest  Next Newest »

▼
06142006, 11:58 AM
▼
06142006, 12:54 PM
I am still new to the HP12C, having just gotten one, and the absence of alphanumerics and some limitations on indirect referencing of registers and restricted options for looping makes it a bit of challengethis would be much easier to do on an HP41 series or the HP42S. That said, I have thought about the issue and I think it is doable using the cash flow registers creatively. Basically, CF0 holds the beginning market value of the portfolio, CFj holds subsequent cash flows, and each Nj stores the number of days into the period (month, quarter, year, whatever) the cashflow took place., with the last entrysay it ends up in register CFnreflecting the ending market value of the portfolio. The entries into the calc would have to be made according to an agreed upon sign convention which is perhaps a little different from the usually NPV/IRR problem. This would mean CF0 and CFn are both positive, reflecting a positive balance in the account at the beginning and end of the period (I hope this would be true!), and CF1 to CF(n1) is positive or negative according to whether it is a contribution or withdrawal. Given this input (and the user may need to use the deltaDays function of the calc to compute the values for the Nj registers), the program would then sum CF0 thru CF(n1) and subtract it from CFn. This is the numerator of the Modified Dietz formula, and would be stored someplace safe. Then, the program would need to loop thru the various Nj entries, compute each weight, multiply it by its corresponding CFj value, sum this into some other register, and on completion the total added to the CF0 value. This is the denominator of the formula. Take its reciprocal, recall the numerator to the stack, multiply the two together, and you have estimated timeweighted rate of return for the period in question. The issue I have right now is learning more about the data storage system on the calc so I can ensure that crucial information that is modified when the CF registers are accessed can get safely stored someplace else so it can be used in the routine. For example, the value in the n financial register will be needed so the program knows how many loops to take in the additions, yet it is modified each time the CF registers are recalled. (Also, the n register is needed for the indirect addressing of CF20 and beyond.) So, finding a place to store that value safely is a consideration. It could be that the resulting program will accommodate somewhat less than 80 cashflows, since I may need some registers to store summations and calculations. The alternative is to construct a program that doesn't store each value in a cashflow register, but rather takes the values from the stack and computes the weights, sums, etc., on the fly, and then does the final division and reports the result. The problem with this is that there is no way to check if the data entry was correct, or to modify the problem by adjusting just one or two cashflow values. It is a worthy academic exercise. I love the calculator, but I think this problem is much more easily programmed in a spreadsheet or, at the least, a calculator that more comfortably handles matrices and vectors, like the HP42S. I find it is a lot easier to keep track of arrayed data in a vector. Les
▼
06142006, 02:57 PM
Quote: Another option is to temporarily "cannabilize" the PV, FV, PMT, and i registers and recruit them to use as regular data registers, since they aren't needed in the calculation proposed and aren't modified during the cashflow entry process unless you execute NPV or IRR. The darn beef I have with hp12c/cp programming is that in the absence of DSE and ISG types of commands, loop counting and tracking is a little more cumbersome. I don't have much time to trouble shoot this now. Anyone else there able to help Matt out? Les ▼
06142006, 03:56 PM
I wonder if this Geometric return program could be modified to do a Modified Dietz?
[f][P/R] This program is pretty slick. You enter the first return you would like to include in the geometric return, and hit [R/S]. The screen then shows you the number of entries so far (1). You then enter the next return, hit [R/S], and the counter increases again. When you've entered all the returns, you press [i] (twice if you're using a 12C gold). And it generates the return. I believe it is borrowing the cash flow keys as you describe, Les. ▼
06142006, 08:47 PM
Matt, you could also use the 12c stats program: clear sigma, enter each return as a decimal then: 1 + LN Sigma+. When you are finished: xbar e^x 1  gives the geometric mean.
To do a modified dietz: A ENTER B ENTER C ENTER D R/S > (BCA)/(A+D*C) where this is the program: 01 RDN 05 X<>Y 09 RDNFor more than one cashflow in the period you'd need to get a weighted dietz weight (D) for the total cashflow(C).
e.g. 10 ENTER 20 ENTER 5 ENTER .75 R/S >.3636 1+LN sigma+ (see 1.0000) 20 ENTER 30 ENTER 5 ENTER .25 R/S >.2353 1+LN sigma+ (see 2.0000) xbar e^x 1  >.2979
Cheers,
Edited: 14 June 2006, 11:01 p.m. ▼
06152006, 02:46 AM
That's a very streamlined approach. Just to make sure, A is the beginning value and B is the ending value? Thanks! ▼
06152006, 07:17 AM
Matt  yes A & B are the opening and closing fund values. When sigma+ is pressed the interest (BAC) for the period is in "Y" so
% LASTX + + > 100*((1+X/100)*(1+Y/100)1)eg 4 ENTER 5 R/S > 9.2
And, a 6 liner for comparison with a benchmark:  1 LASTX % + / > 100*((1+Y/100)/(1+X/100)1)Speaking of + +, my favourite routine is   > Z  Y + XI use it for timezone conversion. For example now it is 11pm (23:00) here in NZ where we are 12 hours ahead of GMT (tz = +12). What is the time in New York (tz=4)? 23 ENTER 12 ENTER 4 CHS then   gives 7. So, its now 7am in NY, today Thurs. The other way  when it is 4pm in New York, what time is it here? 16 ENTER 4 CHS ENTER 12   wow this gives 32  anything over 24 means tomorrow and we subtract 24 to get 8am tomorrow. Under 24 is yesterday and we add 24. The routine is time1 zone1 zone2  . RPN is a lot of fun!
Cheers,
Edited: 15 June 2006, 3:14 p.m.
06142006, 09:19 PM
Les, What you propose is just barely possible on a 12C and pretty reasonable on the new 12CP. The major limitation is the use of Nj's as the time periods because each Nj can only store values between 0 and 99. Therefore the program would be limited to calculation periods of just over 3 months. An alternative might be to encode the number of days as a fractional amount in each CFj register or better still use alternating CFj registers for: actual cash flow amounts; dates (stored in the usual m.dy or d.my format). The new 12CP would allow for 40 cash flows using this method and data entry would be simple and editable. As you suggest using PMT, PV, FV and i as general purpose registers is the way to go in order to allow all 81 CFj registers to be used for data. But not being able to use storage register arithmetic with these does lengthen the programs. Still I'm not sure if such a program would be that useful. You'd really want to have the ability included periodic market values and maybe income, fees, etc.  not just cash flows. Also, you'd probably want to store these data in a more permanent form to avoid having to enter it again. But it does sounds like a programming challenge and someone may have to take it up... :) Katie
Edited: 14 June 2006, 9:22 p.m. ▼
06152006, 10:51 AM
Katie: You make an interesting point. Wondering what you would suggest to investors looking to get a quickanddirty tally of their yeartodate portfolio return (if the portfolio has maybe three or four inflows or outflows). The portfolio return would be compared to benchmarks, such as the Russell 2000, as a measure of how the portfolio is progressing thus far for the year. It would be cool to do this with the 12C, rather than "cheating" and using Excel. There's something elegant about being able to punch this up on the 12C. Any suggestions? ▼
06152006, 01:11 PM
When you start looking for comparison indexes and historical data you really should consider "real" portfolio accounting software. I agree that it would be cool to do this on a 12C, or even a 42S, but they're not really up to the task, it would even be a pain to do this in Excel. Software that'll let you edit transactions, view holdings, measure performance, etc., is what I'd recommend. There are many sharewaretype packages out there that will allow you to do all sorts of stuff and fill in security prices for you too. I'm used to using professional software for this so I don't have any experience with lowend packages but something like Fund Manager is what I have in mind.
Edited: 15 June 2006, 1:16 p.m.
06152006, 04:02 PM
I am starting to conclude that probably the least complicated approach IS to use the stats registers. Manually, I would store MVB in PV and MVE in FV (easy to remember, since that is how the values are ordered in time), and for the cashflows do: weight ENTER cashflow sigma+ I would leave it to the user to estimate or compute the weights on his or her ownthe formula is 1  (days into period)/(total days in period) for each cash flow. At the end of this all of the requisite quantities are stored in the calculatorR2 holds the sums of the cash flows, R6 the sums of the weighted cash flows. The program would then calculate the modified Dietz estimate for the period thus: RCL FV RCL PV  RCL 2  RCL PV RCL 6 + / 100 * This isn't as sexy as storing each individual cashflow and weight in the calculator for editing and review, but for small problems it is good enough. There is progress on the horizon. I just got back from seeing my financial guy, and showed me an internal email advising that that firm is is moving to a true dailyweighted approach (mid period evalutions whenever there is a cash flow) effective immediately. Les
▼
06162006, 07:12 AM
Matt  I've now added a program  see below
Les, Here is an example using your Sigma+ design. The MVB=1000 on 12.312005 Ref: HPCC DataFile V23N2 P11 for documentation of the depreciation functions. SL doesn't use i or PMT.
The program below automates the above, for any number of periods.
1. key in program Cheers, Tony
Edited: 16 June 2006, 6:39 p.m. ▼
06162006, 06:56 PM
Tony: This is absolutely remarkable. It is truly amazing how quickly you created such a complex program that works so well and efficiently. Thank you so much for sharing your talents and intelligence. ▼
06162006, 09:25 PM
Thanks for your comments Matt, and for your request :)
If we are given market values just before each cashflow then we can easily do the true time weighted returns as they, somewhat ironically, do not involve time explicitly.
This 15 liner is all we need as a program (here I tack it on behind the previous one): [RCL][PV] 42 45 13  I hope this might be useful, and that I haven't included any mistakes :) To come in under the Dietz for the first quarter we need to give the market a slower start:
1000 PV 0 PMT 1010 FV 0 i Let's tack on the mid point dietz as well, just 20 lines. This runs just like the 14 liner, but is happy to take quarterly valuations only :)
[RCL][PV] 57 45 13 
Cheers,
Edited: 17 June 2006, 12:04 a.m. ▼
06172006, 03:56 PM
Quote: I am going to try to tweak this so that values entered into the PMT and FV registers at each valuation point are the cashflow and the end of day balance INCLUSIVE of the cashflow, since that is what my information reportsi.e., I go into my investment firm's website, and if I made a $500 contribution on May 13 the balance reported to me for that day is inclusive of that amount. So far I am wondering how to adjust things so that the former subperiod's ending value becomes the new period's beginning value so the linking can be done. It can't be that hardI am still new to the calculator and want to be sure to preserve all of the desired interim values so it all works out. I will tell you how I do, unless tony beats me to it first ;) Les ▼
06172006, 04:34 PM
I only had to make a couple of adjustment to Tony's routine after all:
[RCL][PV] 42 45 13  In Tony's example, the opening quarter balance is 1000 and the close of day balances on Jan 15, Feb 15, and March 15, and March 31 are 1150, 1350, 1600, and 1600 respectively. There were cash flows of 100, 150, and 200 on Jan 15, Feb 15, and March 15, and the above balances include these. Proceeding:
1000 PV 100 PMT 1150 FV 0 i GT0 042 R/S see 5.00 R/S see 5.00 One can carry on with the next quarter just as in Tony's example. The results are the same. I prefer this version since it allows me to use the numbers that I have on hand rather than having to compute the precashflow endofsubperiod balance since the routine takes care of this. Les ▼
06172006, 05:58 PM
Les, I admit I did wonder about whether the valuations at cashflow points would normally include the said cashflows or not. Thanks for the correction!
Cheers, ▼
06172006, 08:28 PM
It is not a correction so much as a matter of preference. I just find it a lot simpler. Other users may prefer the other way. The formula provided by my investment firm for calculating a subperiod return on a valuation day (whether a cashflow takes place on that day or not) is simply (MVE  MVB  CF)/MVB, where MVE is the ending value INCLUDING the cash flow, MVB is the beginning value including cashflows that have gone before, and CF is the total cashflow on the valuation daytypically a single transaction for long term savers like me, but if several transactions took place that day it would be the net sum. Obviously, if there is no cash (such as on the end of quarter dates in our example), CF = 0 and the formula reduces to the simple return quotient we all know. Of course, by this point we have figured out that you geometrically link together the subperiod returns to get the overall return, and this is ingeniously done by the x ENTER y % LastX + + sequence that I have been raving about. A bit of a digression, but it has been niggling at me: True timeweighted calculations are considered the "standard" since they purport to be independent of investor intervention since they are supposed to "control" for the timing and size of cashflows by snipping the timeline at key points. I am not sure I agree with this assumption as pure "law". Indeed, I think it's fair if one is talking about the return of a homogeneous portfolio consisting of one investment. If fund Q grows in value 2% between date x and date y, it is going to do this no matter how much I bought of it on date x and or date y. HOWEVER, in a mixed portfolio, investor behaviour CAN influence the underlying "true" timeweighted rate of return by altering the characteristics of the investment mix. For example, let's say my portfolio of bond and equity securities earns 2% between date x and date y. For my date y cash flow, I contribute a good chunk and choose to allocate all into the higher earning equity securities in such a way that for the next subperiod the rate of return for my portfolio is slightly improved even if the rates of the individual investments remains constant. I know I should come up with a concrete mathematical example, and I will work on it, but in the meantime I am wondering if I am on track here. The bottom line for me is that I am as a longterm investor interested in the overall performance of my whole portfolio, rather than each investment specifically, and it is important to appreciate that even with the goldstandard timeweighted return calculation that strives to eliminate the impact of investor actions, it is possible for the perfomance numbers to be altered according to how my cashflows adjust the underlying asset mix. I will come up with a simple twosecurity portfolio example and present it when I get the time. Eager for feedback on this point. Les
▼
06182006, 05:30 PM
Les, my take on returns is that they are only useful for comparison with other "similar" returns. So fund managers compare their time weighted returns on a single asset sector fund with a benchmark return for that sector, and with other fund managers returns for that sector. The origin of the Dietz formulae was in the context of comparing investment performance of pension fund managers. Peter Dietz worked for Frank Russell at the time (1966) and they were pioneers in this business. As far as the individual investor goes what has always mattered is simply the net cashflows invested and the end value. The investor might want to compare this end value with what would have been achieved if the cashflow had been invested in a bank savings account, for example. Indeed it might be a good idea for investors if fund managers gave an alternative end value assuming investment at risk free rates. This would avoid the minefield of "returns" which after all are just numbers, not unlike statistics, and we know their reputation<G> What I'd want to know is simply my "IRR"  my internal rate of return on the investment. This would give me a quick comparison with other investments I could have made, for example. I'd be quite interested in the time weighted return on my invetment because that's what I would have got by investing a single lump sum at the outset. It might help me in analysing why I have got such and such a return. For example, if the day to day time weighted return were in fact constant over the period then theoretically the cashflow timing itself would have no effect on the actual return achieved. You are right that varying the asset mix in a fund will vary the return. Your fund manager might even publish a time weighted return on a composite fund  which may be of some interest but the relative proportions in the asset mix vary (even from day to day as the different parts grow differently) and unless the mix is rebalanced frequently the return should be viewed with some scepticism.
Cheers,
n=1 PV=10000 PMT=1500 FV=12000
Edited: 19 June 2006, 9:54 p.m.
06172006, 03:29 AM
I am using a 12cp and at step 4b I am getting 9.375%, not the correct answer of 12.58% I don't think I have keyed in the program incorrectly and I have followed the data entry and initialization instructions precisely. Will try to find the problem. Les ▼
06172006, 03:41 AM
The sign of the contents of register 6, the sum of weighted cashflows, is reversed (so to is the sum of the weights, register 4, though the program doesn't use it). If the program as written gives the right answer for you, are the 12c classic and the 12cp different in some way? I see from your code that the location of LASTX on the keyboard differs, but save that when I key in the program as give and run it the sign on those register contents is reversed, and this gives wrong results after line 23 where the desired return quotient is computed. Les
06172006, 04:17 AM
On my 12cp, the problem is the ENTER at line 28. I take it out, and all seems to work. The beginning part of the program sums everything correctly in the stats registers. Must've been doing something wrong before. Is that ENTER needed on the 12c classic, or is it an error in Tony's original code? Les ▼
06172006, 05:23 AM
I had to take the ENTER out at step 64 of the midpoint Dietz code to get that to work too. Are there differences between the classic 12c and the 12cp in the relationship between ENTER, LastX, and stack lift? My understanding from the 12cp manual is that when ENTER preceeds LastX there is no stack lift and that register X is simply overwritten. But, in this code on my 12cp LastX after ENTER seems to induce stack lift, and in both cases in tony's code this is not desired. For example, I do this.
[f]CLEAR[REG] So the TZYX stack order is 0, 900, 900, 36. It would be 0, 0, 900, 36 if the X register were overwritten by LstX after that ENTER, but that's not what happened. It isn't what happens with tony's code on my 12cp, so I had to take those ENTERs out. Confusing, but at least I got the programs too work. Neat work :) Les ▼
06172006, 05:44 AM
Les, humble apologies, I keep forgetting about stack lift on the new 12cp. On the old 12cp and the 12C gold stack lift is disabled after TVM entry, so I needed that extra ENTER. Also the quick stats cotrection I used (lastx sigma) doesn't work on the new 12cp as it doesn't disable the stack after a stats entry. Gee you did well to get them to work  I'll go back and have another look at your comments. Thanks so much for testing! Yes, I fear you are indeed right about LASTX on the new ones  ENTER is *supposed* to disable stack lift  so a LASTX after ENTER should overwrite X. This is separate to the TVM thing I mention above, but it looks similar!! On the 12cp ENTER normally does disable stack entry but LASTX seems to have the power to overcome this. I should have written it on the 12cp I guess  but I was using one of my old gold ones<G>
Cheers, Edited: 17 June 2006, 6:02 a.m. ▼
06172006, 07:06 AM
Thanks, nice to know I wasn't completing losing my mind :) As an aside, I should comment that the keystroke sequence % LastX + + to geometrically link two percentage returns in X and Y registers is really quite elegant. For years I have doggedly punched on calculators 100 / 1 + x<>y 100 / 1 + * 1  100 *, or something equally convoluted, to link two percentage returns. Les ▼
06172006, 12:32 PM
I agree with Les, Tony's [%] [LASTx] [+] [+] is a masterpiece!  I'm also troubled by the stack lift differences on the new 12Cp vs the older models. I spent considerable effort to write some recent code (posted here) so that it would work on both versions. It's probably slightly longer than needed because of this and having to debug on two calculators that are suppose to be compatible is no fun. I think that HP made a big mistake on this.
Edited: 17 June 2006, 12:33 p.m. ▼
06172006, 01:56 PM
Quote: This is one of those situations where a little Grade 9 math is helpful indeed. The FOIL mnemonic for multiplying binomials in particular. If X and Y are the returns we want to link (as %), the linked result Z(as a percent) is given:
Z = 100 * [(1 + X/100) * (1 + Y/100)  1] The third term just magically happens to be the formula used by the calculator for X ENTER Y %. The little routine works unaltered, and best I believe, on my hp calcs with a standard 4 register stack and Last X registerhp12cp, hp41cv, hp41cx, hp42s. In the hp28s, hp48g, and hp49g+, both X and Y arguments get eaten by % command, so they have to be brought back by LAST (in the 28S), ARG (in the 48G), or ANS (in the 49G+ in RPN mode). Of course, on these last three machines, the flag that preserves stack arguments needs to be set. Alternatively, X and Y can be duplicated by DUP2, so they will be there irrespective of whether that flag is set or not. (To really confuse me, the 48G ARG command recalls the last arguments to the stack, if the flag to allow it is set. On the HP49G+, ARG computes the argument of complex number. But I digress.) Clever and fun! Thanks! Les 