Is My Economic Model too Difficult for Excel to Handle?

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
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

Board Regular
Joined
Dec 3, 2008
Messages
168
Sorry I've been unable to spend much time looking at the spreadsheet you sent me. I'd echo the above comments about VBA's capabilities. I encounter a lot of people who underestimate the scope of problems that recent versions of Excel can handle.

In some of the MatLab work I've done for my dissertation, I use the fprintf command to send my output to a text file which I then import to Excel for easy manipulation. I assume Gnu Octave would have a similar if not identical command.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
Thanks mrmickle1. I've finished the first time period of 40 (I might reduce it to 30), and the Gnu Octave program functioned well. However, it took me more than an hour to transcribe the data into my excel file, and as the data list gets longer, its only going to get harder to transcribe the data. One big advantage Gnu Octave has over Excel is that its easier to spot the bugs and mistakes. Its hard looking into an excel cell and figure out what is what. My final program in excel was just a mess. I discovered many mistakes by writing the program on gnu octave. Programming for the second period will be somewhat more difficult than the first period, because now I'll have to account for depreciation and build up of capital and human capital stock. However, once the second period is finished, the second period program can serve as a template for the remaining periods.

In my test run for just the first period, I instructed the program to do up to 100 iterations until one of the aggregate variables changes by less than .1% between successive approximations. In the test run, it took three iterations for convergence to occur. However, I'm worried that convergence will be a lot tougher to get once stock quantities begin to accumulate. During the iteration, my first guess at the value of the variable is done by ignoring certain effects that make the calculation complicated. Because in the early periods, there is little accumulation of stock variables, the initial guess is quite good. Once we get in the later period, a guess too far from the actual value may not result in convergence. I suppose, I could learn how to do more complicated iteration processes.

Anyway, what downloads do I need to use VBA on Windows 8 and Office 2010? Is there a user manual? I've just spent two weeks learning Gnu Octave, do you think its worth it to change programs again? How easy is it to learn VBA? I can see myself doing 40+ hours of work just to transcribe the gnu octave data output into excel for a given test run. Thus, just conducting three or four runs with different parameters could take weeks. In a similar but simpler program in excel, I was able to do 20+ runs per day.
 
Last edited:

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
Your project and last post raise several issues.
I am not familiar with Gnu Octave. And why so long to get the output transferred to Excel. Are you using clipboard to transfer? Once in Excel VBA could again be helpful in reformatting the data.

There is a recent post of a list of Excel resources by hiker95. Hang around this forum and you will soon find helpful info like this.
http://www.mrexcel.com/forum/excel-...anging-columns-rows-advanced.html#post3925232

The list is long. If you prefer book over video, one book like Walkenbach's power programming with Excel book may be enough.
Excel comes with two help systems, one on the sheet, and a different one from the code window.

Convergence may not always happen, or could different starting numbers result in converging to different result? You may have to test to find out.

What exactly are stock quantities?

How easy is it to learn VBA depends. VBA is similar to some other programming. It has standard variable types, many functions corresponding to the worksheet functions, simple and array variables, loops. If you are already familiar with these concepts it would go much faster learning to transform cell type formulas into code. Some people revel in getting Cell Formulas to work. But this approach is not always practical.

There are various debugging techniques for VBA code explained in the references. Some may use one simple technique, while others may use a variety of techniques. Debugging Cell Formulas or VBA code involves first spotting a problem, then figuring out what is wrong, and how to fix it. It should get easier and faster with practice.

With such a project with no answer key how do you test it? Organization factors such as compartmentalization into modular parts factor into design. Start simple and add functionality. Could a cell formula and code version be built and compared, or an Excel and Gnu Octave version? With complex climate modeling, different models can be compared. What value is the result unless there is confidence it works as planned and how is that demonstrated? Similarly a math problem can often be solved by two different methods to show the same answer. So if you ask if it is worth it to learn another method these issues come to mind. Without some kind of testing or verification or validation you may just be building a house of cards.

Other books, not specifically on Excel, may also give you ideas on approaching complex projects.
The pragmatic programmer by Andrew Hunt published 1999.
This book discusses issues such as building a prototype, and adding complexity.
 

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
I meant to address this in your other post #9 also.

"But I've heard that there are limitations on the amount of code <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); font-variant: normal; cursor: help; color: rgb(51, 51, 51); font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 12.7272720336914px; font-style: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(250, 250, 250);">VBA</acronym><acronym> </acronym>can handle."

Macros do have a size limitation. Each module can have over 1000 lines of code. If the limit is exceeded the subs can be put in different modules.

Here is a link:
Maximum Length Limit for a Macro (Microsoft Excel)
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Edsong,

You have been referring to vectors and scalars. I am not too familiar with these, but here is a website that may prove of help with scalars:

VBA Internals: Getting Pointers | Byte Comb


This website discusses e :

MS Excel: EXP Function (WS, VBA)

As I mentioned before I will be glad to assist you in your project. I thought this may give you a little bit of VBA background on some of the subject matter your project encompasses.

Jackbean,

You bring up some good points. Early as I was learning VBA I recorded a few macros that exceeded the maximum limit. The funny thing is that I never knew what it was.... I just knew I had surpassed it. Thanks for the information!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,409
Messages
5,831,445
Members
430,069
Latest member
bubbleboom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top