**John Houston**- Glasgow Caledonian University

Macroeconomic theory has been preoccupied with trying to understand the relationship between aggregate consumption and aggregate income. Researchers working in this area assume the existence of a rational, representative economic agent who wishes to maximise his total lifetime utility from consumption. Each period, the agent has to make a decision regarding how much of his wealth he should spend on goods and services and how much of current income should be saved or how much he should borrow. The author has taught elements of this theory on second- and third-level courses in economic modelling and macroeconomics, and has found the Solver to be an invaluable tool in bringing life to the concepts explained in the lectures. Students construct models similar to the one that will be described in this paper, then use the Solver to determine the agent's optimal period-by-period consumption. They can then alter the models' parameters and observe the impact of these changes on the pattern of consumption. Thus, for example, they can better appreciate the influence of the initial endowment and the rate of interest on the accumulation of wealth and the consumption pattern; the rate of discount applied to utility in increasing the agent's 'impatience' to consume earlier and; the importance of the assumptions made about the form of the agent's utility function.

- Utility is derived only from current consumption. The utility
function is assumed to belong to the
*logistic*class, and is given as , where a and b are parameters and c_{t}is consumption in period*t*. This is only one of several competing forms, but has the attraction of modelling diminishing marginal utility. Whilst the student cannot change the general*form*of the utility function, it is possible to alter its parameters. - It is assumed that utility has no 'time value' i.e. the present value of one 'util' this year is worth exactly as much as one to be realised in 50 years time. The student can however, model and observe the effect of time-discounting utility and its tendency to encourage consumption sooner, rather than later in life.
- The agent is given an endowment at the start of his life which is immediately available for consumption. It is also assumed that he will die with nothing, though, again, students can require him to bequeath some capital upon death.
- The agent's only income is interest from his accumulated wealth (measured at the start of each period). He may decide to consume only part of his current income, adding the rest to wealth for subsequent reinvestment and income generation. On the other hand, he may consume more in a period than he has income, thereby reducing his wealth (and future capacity to generate income, given the assumed real rate of interest). A single rate of interest is assumed to apply throughout the agent's life, though different rates can be incorporated if required. It is also assumed that the rate of interest on income and the rate of discount on utility are independent. It would be fairly simple to link the two by modelling the discount rate as a function of the interest rate, or both as a function of wealth.
- Consumption is constrained from above by the total wealth available and obviously, from below, by zero. In the interests of realism, the student can specify a minimum 'starvation' level of consumption per period, lest the Solver find it optimal to set consumption to very low levels in some periods.

The basic spreadsheet model is illustrated in Figure 1, with the logic for the first period displayed in full. This can then be copied across the columns for as many periods as the agent is assumed to live (with the proviso that from the second period on, the wealth brought forward is last period's wealth carried forward). This particular model assumes a 50-period lived agent, with no non-negative lower bound on consumption, who inherits £1000, with wealth attracting 10% interest per period. The logic is very simple, and can be quickly constructed and interpreted by the average undergraduate. In addition, it is very simple to alter any of the parameters of the model and observe the effect on consumption. Notice that row 15 (apart from A15) is left blank at this stage. It is into these cells that the Solver will trial various consumption levels in its effort to maximise this agent's utility given the constraints imposed on the model.

Figure 1 Structure and Logic of the Spreadsheet Model

The third and final constraint ensures that the agent consumes at least some minimum amount in each period (a 'starvation' level), as specified in B4.

Figure 2 The Solver Dialogue Box

Figure 3 Consumption, Income & wealth over the agent's lifetime

Figure 4 Intertemporal Consumption Function for the representative agent

Figure 5 Consumption profiles for 0% and 5% discounting

In this case, student can see that reducing the real value of future utility (in relation to the rate of interest) makes the agent more impatient to consume in the early stages of his life. As a result, he retains less of his wealth in the early stages, which lowers the amount of Capital on which later Income can be earned. As a result, the agent cannot consume at the same level in later life.

Problems will occur where the student unwittingly generates a scenario for which there is no feasible solution. For example, it is *obviously* impossible to insist on a minimum level of consumption (per period) that would result in all wealth being
consumed long before the end of the agent's life. In this example, the agent has an endowment of £1000 and a 10% return on wealth to last him say, 50 years. Roughly speaking therefore, average annual income will be £100. If the starvation level of consumption is set above £100, then no feasible solution can exist, given the assumptions of the model. This would be complicated further by insisting that some wealth be bequeathed upon death - the greater the bequest, the tighter will be the rein on consumption, and the greater the chance of there being no solution. There is no way of prechecking the feasibility of the problem as set before attempting to solve it (except where it is obvious), thus there is always the risk of waiting for a solution that never comes. The student will be informed if the Solver can't find a solution, in which case the student needs to go back to the model and look to relax some of the constraints.

The ease with which models can be set up and solved clearly makes the Solver a useful tool in teaching and research. That said, this author has experienced some of its shortcomings which presently limit its application to relatively small, simple models, such as the one described above. Two limitations in particular are worth mentioning, viz.,

- Time taken to solve larger models.
- Getting trapped at a local optimum.

Figure 6 Objective Function Surface with local and global optima

As can be seen from this rather exaggerated example, should the search
start with a low value for *Input 1*, then there is a good chance
that it will be drawn to the local peak. On the other hand, if the model
was seeded to start from a higher *Input 1* value, then there would
be a much better chance of finding the true global optimum. Of course, it
is only possible to plot the objective function surface for a model with
one or two inputs, thus the only practical solution is to run the model
several times using different seed values. In the event that most, if not
all, of the runs yield the same solution, you can be reasonably sure that
you have the true optimum.

- Houston, J. and Gasteen, A., 1997
*"From the general to the specific : estimating aggregate intertemporal consumption functions from survey data"*, Department of Economics Discussion Paper, Glasgow Caledonian University.

- MacDonald, Z., 1995
*"Teaching Linear Programming using Microsoft Excel Solver"*, Computers in Higher Education Economics Review, 9 (3), 7-10

- MacDonald, Z., 1996
*"Economic optimisation : an Excel alternative to Estelle et.al's. GAMS approach"*, Computers in Higher Education Economics Review, 10 (3), 2-5

**Address for correspondence:**

E-mail J.Houston@gcal.ac.uk

What's Related:· Other articles on the use of spreadsheets in teaching economics |