Wednesday, March 02, 2005

Last Steps...and: Your Excel Tutorial...and more reviews.

Finally got to write my test yesterday. That's all I'm going to say. Ever since I walked out of the CFA Level II and said to everybody and their dog that I passed the thing--and then failed--I've been gun-shy on exposing my unmitigated hubris. This afternoon the result should be faxed over here and you'll either get an "I told you I'd pass" or a "Gotta get a gun." Stay tuned for that.

Minaz came by yesterday...he's been stopping by for lunch a fair bit lately (if you'd like to know his antics in Seoul check my October postings) and today he had a job interview. He's a CGA (an accountant, a bit more practical/management-level than a CA/CPA, but he still knows his stuff) and out of work after about 8 years in a job I, somehow, introduced him to. I knew the owner and the business was just starting and needed a part-time bookkeeper...then it grew to over 200 people in a few years.

Anyhoo, we were fiddling around in Excel and he showed me a cool little function: Goalseek. Maybe you've already seen it, but I hadn't (which makes me, a finance guy, look like a bit of a chump).

Here was the problem: we had 2 annuities to calculate a rate on to get the IRR. The first was a cash inflow into an investment (it was more than that, as it has soft benefits, but we were looking for the hard numbers, remember: he's an accountant). Let's say $6,000 a year for 10 years. Then we had an output of $30,000 a year for 15 years starting in year 38. To do a normal PV in excel you need to enter =pv(rate, nper, pmt, (fv), (type)) where rate is the rate of return, nper is the number of periods, pmt is the payment per period, fv (optional) is the value at the end of the stream and type (optional) is normal annuity (payable at the beginning of the period) an an annuity due (payable at the end of the period).

What you can do is set rate as a certain cell and then enter both annuity formulae into 2 other cells and set a fourth cell as the difference between the two. By picking numbers and interpolating (basically making the rate cell number larger or smaller) you can arrive at the IRR (internal rate or return, or ROI: Return on Investment) of the investment. Minaz is a sly one..he went to "Tools" in the pulldown up top and hit "Goalseek". There are three questions the function asks: Which cells are we concerned with? What do you want it to be? and What cell should be changed to get what you want? As you know, my rate in the PV formulae were linked to the ratecell so all he had to do was "Set Cell: the one that is the difference between the two PV calculations; To Value: 0; By Changing Cell: the ratecell" Then it got the number: 5.13%

5.13% is not a fantastic rate of return (even if it is, as in this case, after tax) but perhaps the soft benefits help it make more sense. The cool thing now is that I can now compare other similar products and plans to this one to see which one makes sense! Yahoo!

Another little gem I found came from when one of the guys here asked how to price a bond. Well, I always got pre-formatted spreadsheets (either with formulae or macros) when I was doing the investment banking thing in Korea and when we were in brokerage the bond desk always gave us the numbers (plus our PCs were nothing like they are nowadays). I found a cool function: price (also in Excel is pricemat for notes that pay interest at maturity only and pricedisc that are discount instruments). The format is: =price(settledate, matdate, coup, rate, redamt, freq, basis) where settledate is the settlement date for a trade (3 business days after trade date, normally; all you need to do to put March 7, 2005 into this field is input DATE(2005,3,7) !), matdate is the maturity date of the instrument, coup is the coupon rate (you can even express it as a percentage, like 6.05%), rate is the current or quoted rate for that bond, redamt is the redemption amount (the convention is 100), freq is frequency (1=annual, 2=semi-annual, 4=quarterly) and basis denotes the daycount basis (these are in the help menu, we use '0' which is a 30/360 basis). The price it computes is the total price including accrued interest. Very cool. Again, I feel like a bum not knowing this already; but better to know a little later than never...right?

UPDATE: If there is a "NAME#" error in the cell then you have to update the formulae in your Excel software by going to "Tools" then clicking "Add-ins" and clicking "Analysis Toolpak" then having it do its thing to plop that function into your system. It's a little cumbersome (probably the reason I didn't find the thing before...was always on a Korean/Hanguel version in Seoul) but I guess it frees up RAM or something for those that don't use these more advanced functions.

A while back I burned through a good starter book on hedge funds: Hedge Funds for Canadians. It's quite a good overview of the industry and not too bookish and not a promo-piece-of-crap like many of these things are. If you want to bone up on these things this is my recommendation (although the profiles of funds in the back are a little dated).

I also went through Consultative Selling a little while ago (it was a skimmer, mostly). I got the 1995 version, there is a 2004 edition as well. I remember this was all the rage when I started in the business...basically it means: don't sell people on something, solve a problem of theirs. Pretty simple stuff. A few good pointers in there. It's really how I end up doing things normally. I give people a lot of information on how the world works (no one, it seems, knows a ton about estate and tax planning and how things like trusts or taxation on life insurance benefits work). After that the wheels are turning and there is usually some disconnect from where their plan is heading and where they'd like it to be...then comes the solution. That's about it. If they like the solution we have a sale, if not: we don't--and we look for another one or move on.

Oh, and if you're looking for a job take a look at Martin Yate's Knock 'em Dead. When I started really looking for a job (after university, in 1994) he had Resumes that Knock 'em Dead and Cover Letters that Knock 'em Dead; two books that gave some good pointers. Perhaps the best one was his one written for human resource people/interviewers. Here, in one place, were all of the major questions that these people are supposed to ask you...know thine enemy/opponent! So, you might have a browse in the books for interviewers (there are also good books by Vault and Wet Feet as well as Miriam Naficy's Fast Track for investment banking/finance interviews) and get in the zone that way as well. Interviewing and job-hunting can be learned and practiced.

Time to get to work, later.