I know some people like the simulation models that I
sometimes put on my blog, so I thought I'd do one using the Samuelson / inside money model that I described last time.
The balance sheet and flow of funds for this are those set
out last time. The equation listing and
parameter values are given at the end of the post.
Most of the model is actually governed by accounting identities. The only assumptions are as follows:
1. Workers attempt to balance their consumption so that they
spend the same amount when working as in retirement.
2. The price of goods is sticky. Price adjustment responds to the rate of loan
growth (as an indicator of long term inflation) and the output gap.
3. The utility of holding land is such that at some point the
marginal utility is zero. This presumes
some level of disutility from holding land, perhaps from having to maintain
it. (This simply allows me to have a
zero interest rate on loans and deposits).
4. The price of land adjusts to equate the expected return
on land to that on deposits for retiring workers. The expected return on land for newborns,
exceeds their cost of loans, but they are assumed to be subject to credit
constraints. (The expected return on land for newborns is not modelled here  it's
not necessary because of the credit constraint assumption but for other
simulations, it might be necessary to include it.)
5. The simulations are run using adaptive expectations.
The graphs below show a simulation of a change in the nominal
growth rate in loan volume from zero to 2%.
Price stickiness allows a temporary increase in production. Rising prices start to increase the expected
returns on land relative to deposits, which makes it more attractive to hold land in
retirement. The balance of housing
ownership therefore moves towards retireds.
This requires that the real value of loans must fall, even though it was
initiated by a rise in the nominal value.
Model Listing
Retireds consume the full value of their assets:
Cr = ( Ar_{t1 }. pa + D_{t1} ) / pc
Workers consume an amount equal to what they expect to
consume in retirement
Cw = E[Cw_{t+1}]
subject to an intertemporal budget constraint.
Cw . pc + E[Cw_{t+1}] . E[pc_{t+1}] = C . pc
+ Aw_{t1} . pa  L_{t1 }+ Ar . ( E[pa_{t+1}]  pa )
These last two equations are rearranged to eliminate E[Cw_{t+1}],
and give an expression for Cw.
Output is total consumption
C = Cw + Cr
Deposits equals loans
D = L
Workers' housing is total housing less retireds' housing
Aw = A*  Ar
Retireds' housing is given by their budget constraint.
Ar = ( C . pc + Aw_{t1 }. pa  L_{t1 } Cw . pc  D
) / pa
The following equations then determine price setting. The price of goods adjusts at the nominal growth
rate of loans, adjusted for a measure of the output gap.
pc = pc_{t1 }. g . ( C / Cn )^{Ïƒ}
The price of land is determined by the following expression,
which equates the marginal expected return on land with the expected return on
deposits (zero). The first part of the
LHS is the assumed marginal utility of holding land, the second is the expected
capital gain.
( Î± . Ar^{Î² } Î» ) + ( E[pa_{t+1}] / pa  1 )
= 0
This is rearranged as an expression for pa.
Finally, expectations are adaptive, factoring in the growth
rate for nominal loans.
E[pc_{t+1}] = E[pc_{t}]_{t1} . g .
( pc / E[pc_{t}]_{t1} )^{Îµ}
E[pa_{t+1}] = E[pa_{t}]_{t1} . g .
( pa / E[pa_{t}]_{t1} )^{Îµ}
Variables and
Parameters
Variable

Description

Opening
Value

Ar

Land held by retireds

100

Aw

Land held by workers

100

A*

Total land

200

C

Total consumption

400

Cn

Stable price level of consumption

400

Cr

Retireds' consumption

200

Cw

Workers' consumption

200

D

Deposits

100

L

Loans

100

g

Growth parameter for nominal loans

1.00

Pa

Price of land

1.00

Pc

Price of consumer goods

1.00

The expression E[X_{t+1}] means the value that
agents in period t expect X to be in period t+1.
In the simulation the value of g is increased to 1.02 from
period 2 onwards.
Parameter

Description

Value

Î±

Land utility parameter

10.0

Î²

Land utility parameter

0.50

Î»

Land utility parameter

1.00

Îµ

Adjustment rate of expectations

0.75

Ïƒ

Adjustment rate of consumer prices

0.50

This part has me confused:
ReplyDeleteRetireds' housing is given by their budget constraint.
Ar = ( W + Awt1  Lt1  Cw . pc  D ) / pa
but W is not defined anywhere that I can find. Please explain. Thanks,
TerryH
Thanks for the spot there  that's a mistake. I was originally using W as the wage bill and setting it as equal to total income / output C . pc. I then dropped the term W, but obviously missed it in that equation. I'll change it.
DeleteHi Nick,
ReplyDeleteAre the following equations identical to yours?
cR = ( pA•AR1 + D1 ) / pC
cW = ( pC•c + pA•AW1 – L1 + AR • ( E[pA+1] – pA ) ) / ( pC + E[pC+1] )
c = cR + cW
D = L
AW = A – AR
AR = ( pC•cR – L1 – D + pA•AW1 ) / pA
pC = pC1 • ( 1 + g ) • ( c / cNOM )^Ïƒ
pA = E[pA+1] / (1 – Î± • AR1^Î² + Î» )
E[pC+1] = E[pC+1]1 • ( 1 + g ) • ( pC / E[pC+1]1 )^Îµ
E[pA+1] = E[pA+1]1 • ( 1 + g ) • ( pA / E[pA+1]1 )^Îµ
With these equations, I find behavior which is quite different from what I see in your graphs.
NB. I suppose that you have to add starting values for E[pC+1] and E[pA+1], both equal to g (Growth parameter).
Anton
They look the same as mine. You're using 1+g, whereas I used g, so I presume you have a starting value of g = 0, going to g = 0.2? Yes, I should have specified starting values for E[pc+1] and E[pa+1]. Both are equal to 1, as for pc and pa.
DeleteYes, g=0.02. It is a minor detail, but it seems to me that the starting values of E[pc+1] and E[pa+1] should be equal to (1+g), as they are looking 1 period into the future.
ReplyDeleteAnton
OK. Well it depends whether you are assuming that the change in the growth rate of loans is expected or not. I had been assuming the latter. The problem if you don't assume this, you're not starting from a steady state, so some of what happens is a result of the starting position, rather than just being a result of the change. If the actual change happens only one period later, then the graphs will be different, but they should show the same general pattern.
DeleteThat's true. A further question. You give the following equation:
ReplyDeleteCw . pc + E[Cwt+1] . E[pct+1] = C . pc + Awt1  Lt1 + Ar . ( E[pat+1]  pa )
Shouldn't this be:
Cw . pc + E[Cwt+1] . E[pct+1] = C . pc + pa . Awt1  Lt1 + Ar . ( E[pat+1]  pa )
Anton
Yes. That's a mistake. Good spot. I've noticed I've made the same mistake in the equation for Ar. I'll do an edit. The equations used for producing the graphs are right though. I just copied them out incorrectly.
DeleteHi Nick,
DeleteIs it possible for you to list the complete equations you used for making the graphs above, because I am getting different results and can't find what's casuing it. Possibly I am overlooking some minor detail.
Anton
Hi,
DeleteThe following is a copy / paste from my excel model. Some of the variable names are slightly different, but I think it's selfexplanatory.
Cw=(Cx*pc+Aw.1*paL.1+Ar*(epapa))/(epc+pc)
Cr=(D.1+Ar.1*pa)/pc
Cx=Cw+Cr
L=L.1*IF(T>1,1+gL,1)
D=L
Aw=AtAr
Ar=(Cr*pc+Aw.1*paL.1D)/pa
pc=pc.1*IF(T>1,1+gL,1)*(Cx/Cn)^0.5
pa=epa/(1mua)
epc=epc.1*IF(T>1,1+gL,1)*(pc/epc.1)^epsilon_a
pa=epa.1*IF(T>1,1+gL,1)*(pa/epa.1)^epsilon_a
mua=(10*Ar^0.51)
So Nick, you use Excel to run these models? Those don't look like the normal cell formulas I'm used to which always start with an "=". Are you using cell formulas or Visual Basic, or some other trick I'm not aware of?
DeleteIf they are indeed cell formulas or their equivalent, how do you do the recursion? Just break it out on separate lines? I'm assuming that's what the lines are that look like:
Deleteecp = epc.1*...
What is the "epc.1" part? I haven't seen that before.
Yes. It's not a strict cut and paste.
DeleteI have two basic columns for each variable. The cells in the first column are named ranges for each variable name (Cw, Cr, etc.). The second column contains each of those formulas (but starting with the !=" as you say). The formula in the first column simply links to the value in the second column, except where it would result in a circular reference, in which case a hard coded value is used.
I then use a VBA macro which substitutes successive values into the hardcoded cells under it finds a solution. It usually takes a straight iteration between the existing hardcoded value and the calculated value in the second column, but for some complex models, I need to be a bit more creative to get it to solve.
Once it solves for each period, the solution is copied as hardcoded output to a table of results.
In excel, I use ".1" to indicate a value lagged by one period. Lagged values, if needed, are put into a third column, which just looks up the value from the results table. Again, the cell is a named range.
Ah, so ".1" is an actual operator in Excel then?
Delete"I then use a VBA macro which substitutes successive values into the hardcoded cells under it finds a solution."
Are you talking about Excel's built in "Finder" or "Solver?" or a custom VBA macro? It sounds like a custom one, but would the built in one's work?
This is interesting because I was thinking about doing something similar in Excel. I prefer Matlab, but don't have that at home. SciLab and other freebees are OK, but I keep stumbling over their plentiful differences with Matlab.
Also, you know there's a free version of Excel you can paste into blog posts to make a fully interactive spreadsheet? It doesn't have all the features though, so it probably wouldn't be able to run your VBA macros. I use it here:
https://sites.google.com/site/eggpuzzle/home/eggpuzzle
(a throwaway page I put up because one of my Dad's friends passed around this puzzle and then sent everyone into confusion by posting incorrect answers to it)
And here (middle of the post):
http://brownblog5.blogspot.com/2013/08/bankingexample11allpossiblebalance.html
You can download the spreadsheets too using the black tool bar across the bottom. I was amazed because Microsoft actually had a more useful free product than Google: I couldn't for the life of me figure out how to make a Google spreadsheet doc fully interactive like that... giving each user his own copy (you can make a Google spreadsheet available for everyone to edit on your blog, but there's truly just a single copy, so their edits remain after they're done: or if two people are editing at once I suppose there's chaos).
... plus the Microsoft one is way easier to use and add to your blog amazingly enough.
DeleteNo ".1" isn't an operator; it's just part of the range name I use. So the cell with the value of epa lagged one period will be named "epa.1". Just because you can't use "" in a range name.
DeleteI use custom built macros. I don't think you could use the built in ones for everything I use them for.
It took me a bit of time to relearn VBA, but it was worth it because I find excel very versatile for different types of models.
In your listing above, this
Deletepa=epa.1*IF(T>1,1+gL,1)*(pa/epa.1)^epsilon_a
should be
epa=epa.1*IF(T>1,1+gL,1)*(pa/epa.1)^epsilon_a
right?
Right. The only thing I had to add in to the cut and paste was the variable name at the start, and it appears I managed to make a mistake in doing so.
DeleteThanks Nick. I'm trying to duplicate your results in Excel and I'm not sure I'm getting close. I set up the spreadsheet like you described and I found that there were three variables at each point in time I had to vary simultaneously to arrive at a solution: Cw, pa and pc. I used solver to do this by squaring their errors (the named column I use as an input minus the formula column) and summing them and having solver minimize the sum. It gets close to zero each time. Can you tell me precisely what you plot in each of your charts above? I'll give you what I get for the Cw column for example:
DeleteT Cw
0 200
1 200
2 223.0458676
3 241.934268
4 248.8604498
5 247.4397933
6 242.0551392
7 235.4874282
8 229.1184922
9 223.5056777
10 218.7927572
11 214.9399555
12 211.8386731
13 209.3650153
14 207.402496
15 205.8502083
16 204.6243157
17 203.6568062
18 202.8932553
19 202.2904856
20 201.8143579
21 201.4380418
The T column is time.
I should add that I seem to start off OK matching your "Opening Values" ... I took "epsilon_a" to be a constant at 0.75 (none of the other parameters are labeled in your Excel listing), and "A*" in your Opening Values table I took to be At = 200 (a constant), and Cn I took to be a constant 400, and "C" in Opening Values I assume is Cx? And of course "g" in Opening Values is 1+gL.
DeleteI set the gL column to 0 at T=0, and then 0.2 for the rest of the time steps.
Deletehttps://docs.google.com/spreadsheets/d/16rcVIImIp7kc9_qTkzJOOJvvVGQRQ6SISDECrUb1Ng/pubhtml
DeleteThis comment has been removed by the author.
DeleteReplied to you direct to your email address.
DeleteThanks Nick!
DeleteNick, thanks. I see what I did wrong: I had the wrong value for gL. I used 0.2 in stead of 0.02. Now my inflation column (the only one I made in an attempt to match your charts) does indeed match yours.
DeleteAlso I found I can put the Excel solver commands into a macro and automate solving for each time step that way.
Thanks for your help with this. I'd like to be able to make my own simulations and this is a great help to me. I really appreciate it.
Here's my version, corrected now (I think.... I still need to check the other charts), but without the macros of course:
http://bankingdiscussion.blogspot.com/2014/04/blogpost.html
That's an interactive one that allows the user change values in some of the cells, but then you'd be on your own to iterate and find a solution manually. Ouch.
Nick, I have an interactive version now which automatically solves the equations. I only allow gL to be changed and it only plots the inflation rate now. I use two iterations for each solution at each time. The errors are printed in the spreadsheet. It's the same link as above, but here it is again anyway:
Deletehttp://bankingdiscussion.blogspot.com/2014/04/blogpost.html
That's very good. I'll have to see if I can work out how to use this facility some time to make the interactive versions.
DeleteI haven't yet dug into your VBA macros, but it is fairly straightforward to use Excel's Solver inside a macro. Probably their Goal Seek to, although I haven't tried that.
DeleteThe macroless version on my blog was more manually taxing than I'd hoped to set up, but that's partly do to the way I'd set up my sheet originally, which I was unwilling to redo for this exercise. I think I might be able to create a more general offline macro to set up a macroless spreadsheet for online interactive equation solving, in which the number of iterations is a parameter. The number of iterations will be fixed once the macroless spreadsheet is created though, of course.
This comment has been removed by the author.
DeleteJavaScript used to control the spreadsheet is another option.
DeleteDo you find that you often need to solve f(x) = 0 where x and f are vectors of the same dimension?
DeleteI don't think so.
DeleteThat's how I approached the simulation here: I solved h(x)  x = 0, where x was a dimension 3x1 input vector [Cw pc pa]' (the labeled ranges in your spreadsheet with these names but no formula), and h is 3x1 (the corresponding ranges in your spreadsheet containing the formulas for Cw, pc and pa respectively), so in this case f(x) = h(x)  x = 0. Perhaps w/ some further manipulation I could have got that down to dimension 2x1 x and f vectors, but it was not obvious to me how to reduce it further. I need to look at your macros and see what you did. I presume this was a typical case?
Delete"I presume this was a typical case?"
DeleteDo you mean was this a typical model in terms of solving? Yes, it's fairly typical of the sort of model I put on the site, with only a handful of equations. There's some stuff though that is a bit more complicated. Sometimes I need to fine tune the iteration process, or use a two stage process.
Or to put it more simply, I solved a nonlinear version of 3 equations 3 unknowns.
DeleteOK, I'll mail you the graps i found, for comparison
ReplyDeleteAnton
Am I understanding this that changing from a system where there is a constant amount of loans to one where loans expand at 2% per year gives a one off jolt of a decade of increased output but after that provides output no greater than if there had never been any change in the amount of loans? So we go to system where we need to run to keep still?
ReplyDeleteIt would be nice to also show the graphs for a subsequent change back out of a 2% loan growth scenario to a static stock of loans scenario. Would that give a one off decade of lost output followed by a reversion to things being just as they were? Was preAbenomics Japan an example of how a zero growth, zero inflation economy can provide just as good a level of affluence as a steady 2% inflation economy?
http://www.nytimes.com/2012/01/08/opinion/sunday/thetruestoryofjapanseconomicsuccess.html?pagewanted=1&_r=1&ref=opinion&
Sorry if I'm just in a stupid muddle.
I should have written:
DeleteAm I understanding this right, in that changing.....
stone,
DeleteApologies for the delayed reply  I've been away.
What we have in this simulation is a change from zero growth in nominal loans to 2% growth in nominal loans. But the growth in the real value of loans goes back to zero gain (after being negative for a bit). You couldn't get a long run result in this model with zero output growth and loan growth anything other than zero. Note that long run nominal output growth here is also 2%, because of what happens to inflation.
Don't read too much into these particular results though. I simply wanted a simulation where the rate of nominal loan growth provides the nominal anchor with all the other nominal values adjusting to it. But what actually happens depends very much on how you assume prices and expectations adjust to the change. I'm not suggesting that what I have included here is particularly realistic.
I'm just wondering whether it isn't possible to have a permanent steady state of increased output as a consequence of having loan growth. In reality isn't it true that in our neoliberal economy the intention is to have loan growth that is continuously greater than core inflation? So asset price inflation outstrips general price inflation. Isn't it true that loan growth in say the UK in the 1980 to 2007 period outpaced consumer price inflation without ever having core inflation begin to "catch up" with the loan growth?
ReplyDeleteIn order to ensure that consumer price inflation does not catch up with loan growth, unemployment is used to ensure wage constraint and capital flight from other countries is used to depress the ability of foreigners to compete for global commodities.
The "increased output" takes the form of highly paid work in the finance and real estate sector. It is "increased output" in that an hours work doing that pays enough to outbid the rest of the world for oil etc.
We had a hiccup in 20082011 when GDP devalued relative to the rest of the world but our neoliberal system seems now to have got back on track. We are now once again able to ensure that we get the oil etc and people in say India don't.
I don't think there is a problem with loan growth exceeding inflation, but I think there are issues when loan growth exceeds nominal GDP growth. The latter implies that the debt to GDP ratio is rising. This can happen for a bit  there is nothing wrong in itself with the ratio simply moving to a higher level  but it cannot grow forever, because there are limits on serviceability. So debt growing faster than GDP can only ever be a temporary situation and not the basis for sustainable growth.
DeleteI guess what I was intrigued by was the extent to which GDP growth could be a pure result of debt growth. So the debt to GDP ratio stays the same and is serviceable but the GDP comes in the form of the activity of administering the debt. So people work as loan officers or estate agents etc and their wages service the debt and the success of that economy enables all goods to be imported. Because the debt is itself the source of the means of servicing it can grow indefinitely. Wages increase ever more but a bigger and bigger proportion of wages go towards debt servicing so affluence does not increase as much as GDP.
DeleteIt depends on foreigners wanting to use the debt as a store of value such that their real goods and services get exchanged for a stake in the ever expanding debt based economy.
As an example, isn't it true, almost by definition, that if you had say an owner of a banana republic who decided to reinvest all of the proceeds from selling bananas to the UK back into loans that enabled the UK to buy more bananas, then the flow of "free" bananas to the UK would continue indefinitely and the debt dynamics would take care of themselves? The UK gets "free" bananas and the banana oligarch's wealth (held in the form of UK assets) becomes ever more spectacular in relation to what he pays those who work in the banana plantations.
DeleteOil, metal or manufactured goods work just like bananas in that way.
Well, there is no problem in itself with someone's ratio of wealth to income rising indefinitely  if my income is constant at $100, there's no limit on my assets. But there is a limit on my debt. You can come up with theoretical models where debt to income rises indefinitely, but these are generally ruled out by a noPonzi game condition. Which is reasonable, as such things do not happen in the real world.
DeleteI'm not sure exactly what you had in mind in your first reply there. Growth in measured value added from financial services such as the administration of loans can count as contributing to GDP growth, but I think you'd have to have some very strange numbers to be able to keep the debt to GDP ratio constant.
I guess I was wondering whether it is actually true that the real world conforms to the no Ponzi condition. I'm trying to get my head around a point that Paul Krugman pointed out in his blog when he wrote, "You often hear claims that we’ve only been able to run persistent trade deficits because of the special role of the dollar; this is just false, since other countries like Britain and Australia have been able to do the same thing." I'm trying to understand how Britain and Australia manage that especially how the capital flows that enable it actually are largely "invested" in stakes in residential mortgage loans for preexisting housing stock. Is the explanation that it is indeed a Ponzi system in that fresh loans fund the servicing of the existing loans? Nothing flows back to the creditors apart from account statements.
DeleteThere's no hard restriction against running a persistent trade deficit. For a start, you can finance a trade deficit with net financial income from abroad, so you might actually have a current account surplus. This is possible even with a negative net investment abroad, because you may earn a higher rate of return on assets than you pay on liabilities.
DeleteEven if you run a persistent current account deficit, it is not necessarily a problem, if your rate of growth is sufficient to ensure that GDP is rising at least as fast as the debt.
In fact, you can even have debt rising faster than GDP for ever, as long as the rate of change of the debt ratio is declining, so that the debt ratio is tending towards a finite number, rather than infinity.
The only one where you need to apply the noPonzi condition is where the ratio is tending towards infinity.
Thanks for the explanation.
DeleteI was looking at the graphs of UK debts versus GDP on page 23 of this
Deletehttp://www.mckinsey.com/insights/global_capital_markets/uneven_progress_on_the_path_to_growth
The really big growth in debt to GDP ratio seems to be down to the debts of financial institutions. They went from 47% of GDP in 1987 to 122% in 2000 to 209% in 2008 and 219% in 2011 (when the graph ends). That graph does look to have a sort of parabolic shape to it at least up until 2008. Was that growth doomed? I find it more difficult to understand who ultimately bears the servicing costs for that debt. It is easy to see in the case of household debt but in the case pf financial institution debt do the servicing costs ultimately fall more across the wider global economy outside the UK? Or is that debt a Ponzi system where financial institutions lend to each other to provide the funds to service the loans they have made to each other?
When I talk about debt, what I usually mean is net financial debt. So if I have borrowed $100, but I also hold $30 on deposit, my net debt is only $70. It's not quite that simple, but on the whole the net debt figure is what matters. If the financial liabilities of FIs is increasing, it's likely their financial assets are as well. I'm not saying this has no consequences, but it is a very different issue. It is misleading, I think, to lump FI debt levels in with debt levels for households.
DeleteOne of the developments in financial markets has been the fragmentation of credit intermediation into several distinct steps carried out by different entities. So before, you might have had a bank with $100 of loan financed by $100 of deposit. Now you might have a position where the $100 is loaned from A to B to C to D before going to the end user. Very little has changed other than organisational structure, but the apparent level of FI debt is much higher because of all the intermediate steps.
Some of that financial institution debt will be net debt though won't it? I mean some financial institutions borrow money for leveraged trading of equities, commodities etc. That is genuine debt isn't it? But is the issue that the extent of such debt is buried in the uncertainty due to the A to B to C to D situation you describe?
DeleteDoes expansion of the Financial Institution debt help to enable the UK to run a current account deficit?
Equities are still financial assets and, as far as economists are concerned, financial liabilities for the issuers. Commodities are a bit different, but on the whole FIs are not holding physical stocks, but rather financial instruments linked to commodity prices. There are certainly different issues that arise with financial assets which can vary in price, but that's a different issue. Even with debt funded equity holding, the intention would be that the income / gains on the asset pay for the debt. With household debt, it has to be serviced and repaid by consuming less than income.
DeleteThe current account deficit is equal by accounting identity to net accumulation of financial liabilities (i.e. net of accumulation of financial assets) by the public and private sector. This measure is equal to income less consumption less real capital investment. For the counterparts to a current account deficit, you'd be looking to public sector borrowing, net household borrowing and to a lesser extent fixed capital investment by PNFCs. FIs account for a relatively small amount. Even though their accumulation of liabilities may be high, so is their accumulation of financial assets.
Doing a quick analysis of this comment showing your Excel listing you have 23 different symbolic names used, 19 of which are not constants (the constants are At, Cn, epsilon_a, and gL). There are 7 of these 19 variables which are lagged and 12 equations for unlagged versions of variables (both unlagged versions of lagged and unlagged variables). Seven of the 12 equations depend on lagged version of variables. So if we take x to be a 12x1 vector representing the unlagged versions of variables, we could write this more generally as:
ReplyDeletex[n] = f(x[n], x[n1])
Where only 7 of the 12 elements of x[n1] are used to evaluate f, and of course f is also 12x1. If f was also a function of x[nm], where m is any set of positive integers, that could easily be rewritten in the same format by adding extra variables. I'm trying to come up with a general description for the kind of problem you typically solve (for which you need multiple iterations for solving the equations at each time sample). The iterations are required because x[n] is both an input and output of f() of course. Now in general by tracking down dependencies, the number of self dependent variables can be significantly reduced (as is the case here where 3 is sufficient, and perhaps it can be reduced even further). If it can be reduced to 0, then no additional iterations are required at each time step (because we have simply that x[n] = f(x[n1])) and the problem is much simpler (no fancy spreadsheets or macros required). But I'm guessing that's not the case in general. I'm hoping that in general an adequate first guess for a solution is f(x[n1], x[n1]). I went through the trouble of solving a series of 1st order approximations (two to be exact) to f (i.e. calculating the Jacobian and solving), but I suppose an even easier way to proceed is to just keep plugging the output of f back into the 1st argument until the output stops changing very much. In either case we're hoping for convergence!
The reason I'm asking all these questions is to try to make a macro that can generate the required sheets offline, so the sheets themselves can be used online for interactive simulations. It'd be great if my general purpose macro was useful for more than just one example. If I'm successful I'd be happy to share with you what I come up with.
DeleteTom,
DeleteI'm sorry, but I did my maths a long time ago and much of it I haven't used since. So, I work some stuff out for myself, but I'm not really fluent on the terminology. I know I did Jacobians at some stage, but I really couldn't say much about them right now. As I haven't had to use that stuff as of yet, I haven't bothered relearning it. Maybe at some point I will.
So, I don't think I can give you any sensible comments here, but don't let that put you off.
No worries... I'll try to keep abreast of what you're up to with your simulations and see what works best. "Jacobian" is not a word I've used much myself, but I was reminded recently that that was the name for a matrix of partial derivatives. It may also need to be square to have that name.
DeleteIt's nothing fancy, but it lets us approximate one of your nonlinear N equations in N unknowns as a linear problem, which can be solved in the usual algebraic way: adding scaled versions of the equations together to eliminate variables until there's just one left, and then doing a back substitution. In linear algebra speak, it just means we solve the matrix equation:
y = A*x
for x, namely by calculating:
x = inverse(A)*y.
Excel has a matrix inverse as well as matrix multiply functions built in. As an aside, solving the inverse is a bad way to go for efficiency and numerical stability (a prof once told us that if any of us ever found a legitimate reason to calculate a matrix inverse to let him know, because he's never seen one), so you normally solve a (fully determined in this case) least squares problem which is equivalent:
x = A\y [Matlab notation]
Which Excel can also do, but it puts the answer in a row vector with the elements reversed! It's a pain in the butt to get it back into the normal form again using built in Excel functions. So it's easier to just use the inverse. A properly designed least squares (AKA linear regression) routine essentially finds a pseudoinverse which is actually handier: a pseudoinverse can handle singular (noninvertible) and nonsquare A matrices.
Well there I go again: way more than you wanted to know! :(
Nick E: This you might actually find interestintg: I discovered yesterday that even the online version of Excel accepts circular references if you first check the box allowing those before uploading. You can also set the convergence test (the epsilon change threshold it checks to see if it's done) and the maximum number of iterations. I tested it out and it works. That *might* be handy for setting up interactive models in which the number of iterations required is not known ahead of time.
DeleteNick, I still don't have a macro that will create a sheet for you, but I was able to make use of the built in Excel circular reference iteration facility, so that I don't have to have each separate iteration repeated in it's own set of cells on the spreadsheet. So the sheet will iterate up to 10 times now. I had a lot of problems with this, I think from using the Excel built in matrix manipulation functions w/o writing out intermediate answers in cells: those functions work fine normally, w/o having to dedicate cells to intermediate answers, but for some reason they don't always work well with circular reference iterations turned on. As before you can download the sheet from the webpage if you want to try it:
ReplyDeletehttp://bankingdiscussion.blogspot.com/p/nonlinearequationsolverwithupto.html
(BTW, I'm not trying to solve your problem with this one: just a general nonlinear problem, which is defined on the second sheet)