Is My Economic Model too Difficult for Excel to Handle?

[Ed Song]

I'm trying to run a mathematical simulation of an economic model. I'm wondering what is the best structure to solve this problem, or whether I should consider purchasing Matlab instead. (I'm very poor and buying a Matlab program may be out of my budget since I'm not a student or associated with a University). The model assumes that there are 40 time periods. The model is complicated, and probably because of simultaneous (circular arguments) equations, always breaks down after about 9-10 periods even with the iteration mode on with a maximum of 1000 iterations. Here is the lay out of the problem.

There are six groups of people each with population n(i) where i=1,2...6. I want to solve for both individual and aggregate variables, including aggregate output and tax revenue. There are many circular arguments, but the one giving me the most problem is the assumption that subsidies to the poor lowers their work effort, and thus before tax income. However, because I assume that government budgets are balanced, tax revenues depend on before tax income, but the size of the subsidy depends on tax revenues, hence a circular dependency. Initially, Excel broke down until I put the workbook in iterative mode, but works for only 9-10 time periods.

Let BTY equal before tax income, ATY equal after tax income, C(t) equal current consumption, I(ht) equal current investment in human capital, I(kt) equal current investment in capital, h(t+1) current human capital expenditures, h(t) human capital expenditures of the last period then for each household:

ATY = C(t) + I(ht) + I(kt)

I assume that

C(t) = mpc*ATY
I(ht) = mph*ATY
I(kt) = mpk*ATY

where mpc + mph + mpk = 1 and are given. Also

I(ht) = h(t+1) - (1-dh)*h(t) where dh is greater than zero but less than one.
I(kt) = k(t+1) - (1-dk)*k(t) where dk is greater than zero but less than one.

also K is the capital stock which equals k(t+1) plus the sum of all non-depreciated capital.
Likewise H is human capital stock which equals h(t+1) plus the sum of all non-depreciated human capital.

BTY is given by potential earnings (W) (which is given) and is reduced by a factor e times the subsidy s. So

BTY = W - e*s

the subsidy for each lower income group is given by

Total Tax Revenue*population share of the revenue/population of the lower income group.

The model is further complicated by the assumptions that consumption has a minimum expenditures requirement and satiation point. Also, both h(t+1) and H have satiation points, this creates long strings of if and then statements, which I have been able to shorten by creating new unsatiated intermediate variables. There is also a capital tax which reduces capital stock formation. However, I get around this circular problem by assuming that the wealth tax does not reduce work effort and that the proportion of the tax avoided goes to increase consumption and human capital accumulation as long as those variables are not satiated. However, I pretty much have to set up a second round of calculations which adds a few more tables to my program.

My general setup is to have a long table that first calculates each households BTY, ATY and subsidies. I then make long tables of my human capital accumulation, consumption expenditures, then capital accumulation. Because a certain percentage of the capital expenditures is avoided by the household, a second round of tables consisting of final human capital calculations, final consumption calculations, and what's left over goes back into final capital expenditures.

Is there a more efficient program setup? I've heard about the matrix option on excel. Would that work and how would that work? Or does the description of my program makes you believe that Matlab or another program may be more appropriate?

 

[jerH]

A few questions/thoughts. What exactly are you trying to solve for? Perhaps it's something that can be formulated for use with solver. Also, are you trying to do this via spreadsheet formulas or in VBA?

If you decide excel isn't right for you, Gnu Octave is a fairly powerful freeware alternative to MatLab.

 

[Ed Song]

I'm not familiar with VBA so I'm using spreadsheet formulas. I'm trying to see what happens to the distribution of wealth and economic growth rate when there is a redistribution of income from the wealthy to the poor under the assumption that the poor eats all of their income up to a minimum expenditures amount of income, and when the very wealthy become satiated in consumption and human capital (education, job training etc...) expenditures, the remaining income goes into wealth or capital stock accumulation. This research extends the work by Thomas Piketty, author of Capital in the Twentieth Century. I made a simpler model that worked fine. However, I avoided circular arguments by assuming that taxes and subsidies did not have any disincentive effects on work and investment.

 

[jerH]

It sounds very interesting...I wonder if it can't be formulated such that you can address it with Solver, but I'd need to understand it much better.

 

[Ed Song]

What is solver? To understand the program much better, you would need to to see the full excel worksheet, and I'm not sure whether anyone would understand it unless they read a draft of my paper, which will take some time to complete. If you want to take a look at the spreadsheet may be I can somehow send as an attachment a copy of the spreadsheet, but if I do that, then I would expect a lot of input from you or anyone who takes a look at the spreadsheet which consists of dozen of pages.

Nevertheless, the economic model is a system of equations with lots of discontinuities. Actually, the equations are not that complicated. For example, there is no calculus, just algebra. That's why I think excel might still work. The code gets complicated because you have to set up a system of If and Then statements when the economic state bumps up on one or more of the critical points. If you have engineering experience, its kind of like solving a chemistry problem with phase changes in the substrates and product. Its basically a big mass balance problem with conditional changes when critical points are reached.

 

[jerH]

Solver is an add-in that ships with Excel (you have to enable it) that allows you to solve constrained optimization problems. Quick overview here and here.


If you're using Excel 2010 or later the genetic algorithm engine in solver can deal with lots of discontinuities and other "non-smooth" problems.

 

[Ed Song]

Thanks for your suggestion. I've looked at solver and it doesn't appear to solve the problem that I'm looking at. Solver seems to solve very specific problems, like what quantities of certain products a store should buy and sell in order to maximize profits. It seems like the Solver requires a target to be specified, which does not make sense for the problem I'm trying to solve. The problem is that my program is just too big and complicated for Excel to solve, thus, midway through my calculations I get either a #NUM or #REF error. If I do a error check, its a circular argument error. The only way to fix the problem is to drop some conditions from the model, which unfortunately is the whole point of the study.

I've now have began to look at gnu octave. However, it's so difficult to figure out. The user manuals and books about the program is usually just how to install the program on your computer and a list of code instructions (syntax). It assumes that you already know computer basics, leaving you wondering how you get a file written in Notepad++ to run on the Octave compiler. I've struggled for a few days now, and made little headway. I suppose the writers of the manuals assume that you need the program for a college course, and the instructors of the courses are the ones that gives you instructions on how to write a program on Notepad++ and how to get the Octave compiler to recognize the written file.

 

[JackBean]

This sounds very complicated for matrix or array formulas. A Monte Carlo type simulation might work. Or you might see if VBA iteration would work. There is an example of this from Copenhagen at:

"Process and mass balance in Excel with VBA macros"
Process and mass balance in Excel with VBA macros

On this page there is a link for an example Excel file.

The example models an industrial process flow, where a substance flows through the stages of a process. The analogy to your problem could be cash flowing between the 6 groups of people and companies and the government with taxes and subsidies. The cash transfers should approach an equilibrium. If taxes to the government are paid once a year than each cycle of transfers between all of the groups would represent a year.

The example uses classes and collections. This reduces the number of variables as the variables for each group are isolated to each group by the class. So the same variable names can be used for all of the groups. "All process stages are classes in a class collection".

You have to learn some VBA with this approach but it seems you would have to learn some other program anyway. It is not clear that this problem is too difficult for Excel. Here is a quote I ran across while looking into this, by Stephen Hall:

"And with the use of Visual Basic for Applications (VBA), Excel can perform nearly any imaginable computing task."

An aid in programming is to start with an existing program and modify it. It may be possible to modify this, changing the stages and transfers, setting up rules for the transfers, to solve your problem which seems like an almost thesis sized project at first glance.

 

[Ed Song]

Jack Bean, thanks for your reply. For the last two weeks, because its free, I began learning how to use gnu octave. It looks like its going to be able to do the job, but there are a lot of problems with gnu octave.

Nothing really is free. Because Gnu Octave is nonprofit, there is a lot of deficiencies in their customer and user support. If you have trouble, you serf the net, and start hounding the few people you find that know the language. When they get tired of you, hopefully you can find another person to hound. The people who work at Gnu Octave are volunteers who are mostly interested in developing code, not on finding ways to make the product more user friendly. Thus, the user manual consists of 800+ pages of code, which is hard to read especially on a computer screen. Nevertheless, its a powerful tool, and you have to commend the people who are willing to work hard for free. Also, unlike excel, there is no iteration function on Gnu Octave. You have to write the iteration program yourself.

A big problem with Gnu Octave is dealing with output. The output is produced in the same way output was produced back in the 1970s when I worked with Fortran programs. It gives a long narrow list on the screen, one variable per line. You can get a printout, but with my program, that will take up literally hundreds of pages. Saving and printing plots on Gnu Octave is easy, though is initially hard to figure out. However, if its even possible, saving and printing tables is almost impossible. Thus, the method I've chosen is to run the calculations on Gnu Octave and transform the data by hand onto excel, which will be cumbersome and time consuming, but I think worth the effort. I would love it if I could find a way to download the output on Gnu Octave into an excel file.

I've learned that Matlab has a spreadsheet function and is easy to work with excel. However, I probably have to spend more than the basic $100 to buy a matlab program. May be we are talking about $500.

Combining VBA with Excel, may be the way to go. But I've heard that there are limitations on the amount of code VBA can handle. However, I'm not sure whether that rumor is true, as I really don't know much about VBA. If its easy to transform the Gnu Octave code, written on Notepad++ file, I would consider doing that. My guess, I'm writing my Gnu Octave code more like how someone would write a VBA code. Instead of using matrices and vectors, I'm using mostly loops with their While Statement. One interesting fact about Gnu Octave, is that it was initially developed to solve Chemical Engineering problems. Thus, the code should be able to handle my problem.

 

[mrmmickle1]

If you are just using loops then VBA will work fine. The mathematical functions that are native to MS Excel environment are also available when writing VBA code. I find that if you need to take logical steps to get to an answer VBA will work wonders. It is important not to focus on the larger scope of the project but one small aspect at a time. When creating code from scratch it can often be bewildering and/or discouraging/frustrating to think about the entire project at once. If you simply break the logic and/or steps down then things are infinitely easier. I have yet to run into an issue with VBA not being able to process the data or scenarios I present it. Just curious.... How many iterations are we talking about? How long are you looping things?

Your project sounds very interesting. Good luck with it. If you have any more specific questions feel free to post back. This community will be more then happy to assist you.