<< Back to all Blogs
Login or Create your own free blog
Home > ESPlanner vs. Sherman Hanna's spreadsheet

ESPlanner vs. Sherman Hanna's spreadsheet

February 4th, 2009 at 07:25 am

A comment to my recent post reviewing the ESPlanner software guided me to a finance professor named Sherman Hanna, who created a consumption smoothing spreadsheet, called LCS. The commenter was curious how this (free) LCS spreadsheet compared to the fairly expensive ESPlanner software ($149 for the basic, $199 for the premium with Monte Carlo simulation).

I did a basic test using my family as a test case. My family consists of myself (33), my wife (32), and my daughter (2), with another child expected in the next year. I started with $70K in retirement savings and approximately $120K/$100K in pre/after tax earnings. I assumed we paid for both childrens' college in full, at a price of $20K per year per child (today's dollars). I tried to put the same inputs into both software, although it's not always possible (ESPlanner wants pretax income, and LCS after-tax; ESPlanner calculates Social Security for you based on your earnings, and LCS wants you to input expected SSI, for example). I assumed early retirement at age 60, and taking SSI at age 70, with 75% of the projected benefit. The results? A huge variation between the programs. Here are my observations.

Inputs into LCS were much simpler. The program only needed a few basic numbers to give you a initial estimate. I'd venture that the estimate is only as good as the numbers you put in though. Digging deeper you find that most of LCS assumptions can be changed manually, which allows as much or as little detail as one would want. ESPlanner requires a thorough breakdown of most aspects of your finances. ESPlanner, while somewhat rough around the edges, is several orders of magnitude more streamlined in its interface. With LCS, you pretty much need to know Excel inside and out.

Outputs from LCS are pretty rough as well. Printing a pre-defined print-area will give you some basic summary info in both table and graph form. ESPlanner's output is better, starting with a formatted PDF summary document, along with supporting spreadsheets (ESPlanner uses Excel as the engine to do its calculations).

However, the biggest difference between the two is the handling and definition of "consumption". ESPlanner defines household consumption as gross after tax spending minus housing expenditures (mortgage, tax, maintenance, insurance). Housing expenses are entered in detail fashion (including loan info and schedule). Then ESPlanner calculates a household factor which is based on the number of adults and children. It assumes some economies of shared living. So 2 adults can live together as cheaply as 1.6 singles (by default). Children are handled in a similar fashion. It divides the household consumption by the household factor to give the "consumption per adult". Then it attempts to smooth the consumption per adult over the family's lifetime. The whole approach seems reasonable to me, and the economies of shared living data is based on studies that have been done.

On the other hand, the LCS approach is somewhat different. The LCS formula uses a complicated ratio of household size, likelihood of death, and "thriftiness factor" to approximate the change in spending from one year to the next. All expenses are lumped together, including housing, and no allowance is made for paying off loans. This causes spending to change relatively slowly from year to year, even if the household changes size drastically. This approach does not seem as intuitive or reasonable to me as the ESPlanner method.

The two programs produce drastically different spending plans in our case. Here is a chart detailing the 2 different savings plans.
Here is a chart detailing the 2 different spending plans.

ESPlanner recommends saving only $9000 this year (including all retirement contributions), with savings generally growing up to about $20K just before college, whereupon savings basically stops, resuming gradually as each child graduates, and peaking at $55K just before retirement. This produces a living standard per adult of $35880 in today's dollars up to age 100. Remember that ESPlanner does not include housing costs in consumption. Including housing, total household spending is in the range of $90-95K pre-college, and around $65K afterward (the "Empty Nest" phase).

LCS recommends saving $20K this year, increasing to $32K in 2 years, and then gradually decreasing to around $20K by the time the kids reach college. Household spending (including housing) starts at $80K and basically rises continuously throughout life, reaching a peak of $105K at age 75, and then gradually declining to $66K at age 100.

Since these plans are very very different, they obviously cannot be both be right. For me, it comes down to whether you think spending follows the ESPlanner model (non-housing spending is strongly correlated with household size) or the LCS model (overall spending grows throughout life and changes very slowly with household changes). Also, ESPlanner's treatment of housing expenses as separate from consumption seem more logical to me. Your housing expenses would not change much if a child moved out, but your food and entertainment costs would likely see big drops. LCS seems to make only a small allowance for this. Also, the fact that ESPlanner allows for loans to be paid off is a big plus for me. In LCS, it is probably possible to enter housing costs manually as line item expenses which could be drastically reduced when a loan is paid off, but I have not tried this as of yet.

The big price tag for ESPlanner will likely discourage many but I believe the accuracy of its planning tools makes more sense for detail-oriented planners. On the other hand, LCS freeware nature, combined with a more conservative spending model, will probably suit many just fine.

LCS can be downloaded from http://hec.osu.edu/people/shanna/lcsprogram.htm, and an explanation is available at http://hec.osu.edu/people/shanna/lcs/overview.htm.

ESPlanner is available for purchase at http://www.esplanner.com.

8 Responses to “ESPlanner vs. Sherman Hanna's spreadsheet”

  1. scfr Says:

    Thank you taking the time to write up this review. It's very easy to follow, and extremely informative.

    Do these programs make adjustments for inflation?

  2. Broken Arrow Says:

    Superb entry!

    I didn't realize you were a guy though. Haha.

  3. Dave Says:

    @BA: Yep, last time I checked.

    @scfr: Both programs handle inflation, although in slightly different ways.

    In ESPlanner, there is an entry for current inflation, as well as an expected future change in inflation. You can enter all dollar amounts in today's dollars or inflated future dollars. Investment returns are entered in nominal terms. The spending and savings plans are in today's dollars.

    In LCS, inflation is handled by asking for everything in today's dollars, and asking for real returns rather than nominal. So implicitly you have to take inflation into account by reducing your expected returns.

  4. Sherman Hanna Says:

    Thanks for the comparison -- I created my LCS program (originally in Visual Basic) for classroom use with students creating hypothetical family scenarios. I have about 140 undergraduates each year in a course, so ESPlanner would not work for that. I see the main advantage of using it as making the students structure assumptions about the future -- clearly one of the key assumptions is projecting future household employment income --- the theoretical basis of my program includes an assumption about certainty of income projections, so I urge students to be very conservative -- this is difficult for 20 year olds. The income projection makes a big difference in the savings prescription. If you assume that your real household income will triple between age 25 and 60, the program will suggest running deficits for now, as you can do your savings later. Nobody can be certain that real household employment income will increase that much.

    I also use have a smaller number of students in my capstone financial planning course run the program for client households they find, typically older friends or family members. I tell them that the purpose is to inform their analyses but not to use the suggestions from the program directly in the comprehensive financial plan they deliver to their clients at the end of the quarter. If their client is middle aged, I suggest that it would be prudent to assume that real household employment income will not increase.

    I would appreciate suggestions and questions. I realize that there are many simplistic assumptions in my program.

  5. Dan Says:

    ESPlanner has an online version that, though quite scaled back and simplified compared to the full version, is appropriate for teaching situations such as you describe. Contact Kotlikoff.

  6. doyledh Says:

    I have been using Hanna's spreadsheet to project retirement spending. I am very impressed and it seems to be giving me an optimistic forecast of my retirement plan. Unlike Professor Hanna's students, I'm 66 and almost all the key numbers are known: salary, social security, pension incomes, age of retirement, and likely plans for future spending. One thing I like in Hanna's plan is that you just dump the net value of all investment assets into one cell and then give it one estimated growth rate.
    I have a couple of questions I do not find answers for in his instructions:
    1. Human Capital: it adds over 1 million in "human wealth" to my total wealth and I do not know what that means or how you take that to the bank
    2. Taxes: I'm not sure if the recommended spending amounts include what I will owe for taxes, or is this all "after tax" income and spending. When I estimate growth, let's say I expect to earn 6 percent, but do I want to discount for taxes and inflation and put in say 4 percent or 3 percent? I need to get my head around taxes after retirement; it seems to me they ought to be considerably less.

  7. doyledh Says:

    I now see that "Human wealth" refers to future earnings, which makes sense. This and several other questions I had about Hanna's spreadsheet can be answered by looking at the formulas in the cells to see how the number was derived.

  8. Sherman Hanna Says:

    Human capital is similar to what a forensic economist might estimate for a lawsuit for a wrongful death or injury -- what lump sum could replace the future stream of non-investment income? If I remember, we discount for the annual risk of death and for a real interest rate. If the household consists of a couple, it sonsiders both earnings streams, and whatever Social Security and defined benefit pensions are input.

    As for inflation and income taxes, it is up to the user to calculate those -- there are default values of the rates of return, but you can adjust those in sheet Input0

    of course in 2012, we have a situation (thanks to quantitative easing) that is unusual -- the annual real aftertax return on a safe investment is negative, whether U.S. Ibonds, 10 year treasuries, or German government bonds.

Leave a Reply

(Note: If you were logged in, we could automatically fill in these fields for you.)
Will not be published.

* Please spell out the number 9.  [ Why? ]

vB Code: You can use these tags: [b] [i] [u] [url] [email]