MEMORANDUM
To: ACTEC Technology Committee
From: Noel C. Ice
File Name: ACTEC Technology Committee
File No.: N/A
Re: Excel and zCalc Spreadsheets
Date: Wednesday, September 25, 2002
==============================================================================
Accompanying this memo are several Excel Spreadsheets that can be used as templates, only two of which use zCalc macros. I will describe each template briefly below. If you don’t have zCalc you will get a message of some sort having to do with missing macros.
First a disclaimer. I have worked long and hard on these templates, but I don’t warrant that there are no bugs at all. The purpose for making them available to other Excel users is on the understanding that a seasoned Excel user can analyze the programming him or herself, and that the concepts employed should give the programmer a huge head start. If you want perfection, buy a commercial program. However, the advantage of writing your own, is that you will have a lot more control and flexibility to analyze data, rather than to merely have ready made calculations performed.
As far as I am concerned, these templates can be used by anyone, as he or she sees fit, provided only that I not be held liable if a template doesn’t perform correctly. In fact, it would be helpful to me to find out about any errors that others might uncover. That way I can correct things, and a corrected version can be circulated to those interested.
I would like to see an easy to use place on the ACTEC web site for ACTEC Fellows to post templates that others in the College can try out, use, improve upon, etc. I would also like to know who in the College uses Excel and would be willing to share information if another Fellow should call with a programming problem. I, for one, would certainly enjoy taking a call or two from someone who is genuinely stumped, and I would take pleasure in saving anyone some of the time I have wasted in figuring out some of Excels quirks. Others have helped me in the past. Years ago I collaborated briefly with Randy Glover, and he gave me a couple of fairly simple ideas that saved me a lot of time later. This sort of networking among Fellows could be a win-win proposition.
Don’t ask me about macros though. After hundreds of hours wasted on that subject I finally was able to produce function macros that were useful. Useful, that is, until Excel changed its macro programming language into something abominable. My old macros no longer work, and the new language appears to be intentionally designed to discourage dilettantes like me.
IceZMinDis
As an Excel user, I really like the concept of zCalc. The zCalc toolbox is fine, so far as it goes, but the formulas are basically invisible, and the templates are not meant to be modified. I preferred the old template approach to the Toolbox. Fortunately, the old templates are still available (for a while, anyway), and I used one of them to produce the template IceZMinDis. This template will only be of benefit to registered users of zCalc, since some of the functions are proprietary macros. Zcalc only costs $99, and I recommend it to all estate planning attorneys who can use Excel. Zcalc’s home page is http://www.zcalc.com/. (I will digress here and tell you that I just retrieved this URL by using a really nifty bookmark program, one I have selected after trying dozens as being far and away the very best and which is a “must have” program. Please try it out, by downloading a trial copy at http://www.softgauge.com/compass/index.htm. It will convert both Netscape and Favorites quickly and easily, and has a find feature that is blindingly fast.)
The zCalc ZminDis template is rudimentary. For one thing, it was not designed to calculate single life expectancy where there is no designated beneficiary, nor will it calculate life expectancy where death is prior to the RBD.
It also lacks one important feature that is easily added. It asks the user to enter the first distribution calendar year. This is usually the year prior to the required beginning date, if the participant is alive, but that presupposes that the user knows the RBD. I added a variable for date of birth, and then provided a formula to calculate the RBD.
IceZMinDis on Steroids
This is still somewhat a work in progress, but what I intended to do was to soup up the template to calculate the minimums where death was prior to the RBD. It was not as easy to do as I thought it was going to be, as is not unusual. For one thing, half years in excel are pesky. Excel does not have age functions. It has date functions, but if one adds six months to, say, August 31, Excel returns March 3, instead of the last day in February.
Ages, RBD, 59.5 etc
This template and the rest of the templates described below do not require zCalc. This is a handy little program that computes ages from birthdates, among other things. The two main purposes are to return the date the client reached or will reach 59˝ and 70˝. Again, half years are tricky, because when the last day of the client’s birthday is on the 30th, 31st or, in the case of August in non-leap years, the 29th, six months later means the last day of the sixth month following the birthday, which may or may not correspond with the birthday day.
MRDs D Prior to RBD
This is a simple little program that will give you the MRD factors where death is prior to the RBD. I think that this one is user friendly enough to benefit anyone even slightly familiar with Excel.
RBD Calculations
This is a not my behemoth. Instead, it is a scaled down one worksheet spreadsheet for use where death is after the RBD. Like the program that computes the MRDs where death is prior to the MRD, I think that this one is user friendly enough to benefit anyone even slightly familiar with Excel.
SDL (Computer Generated Significant Date List)
This is a jewel of a probate tax deadline calculator. Take a look.
FLP Investment Company Test
If you have ever formed a partnership with marketable securities, you know that gain will be recognized on funding if the investment company rules are violated. The regulations provide a safe harbor for those cases where no stock exceeds 25% of the portfolio value and the top 5 stocks do not exceed 50% of the value. This template allows you to play with the number of shares until the test is passed.
The interesting thing about this program for me was that I used the MATCH and RANK functions for the first time. Anyone who has programmed in the tax area has to be familiar with the VLOOKUP function. It is a staple. But VLOOKUP has its limitations. A table has to be created in ascending order, in order for the VLOOKUP to work. How do you get your table in the proper order. Well, you could manually sort, but that is a hassle. I figured out how to first rank the items in the table, and then use the MATCH function to order a new table. Then it was a simple process to use VLOOKUP to fill the table in. This program is small, and you can readily see how I used this technique, which is one that I imagine I will find use for again. In fact, I used it to order my list of potential half years falling in the first distribution calendar year in programming the RBD and MRD programs described above, although there may have been an easier way to do it.
707 & 709
These may need to be compared to the latest federal forms and updated accordingly, but that shouldn’t be too hard, and I think they are useful in any case.
These forms were created using a method I have employed many times. Simply take any federal tax form, and make a spread sheet out of it. Most of the calculations that the government form will have you do are simple addition and subtraction, and occasionally some multiplication and addition. The only seemingly hard part is applying a tax table, and that is really very simple once you understand the Vlookup function, which is really not that hard to grasp. Examples help, and by looking at my use of the Vlookup function in the 706 and 709 templates, you can see immediately how easy it really is.
NCI/ice