Here is an updated interactive sheet. Six iterations are done now at each time step and I've produced all the plots. If you download the spreadsheet you'll see all the calculations are done on a single page now. Instead of parameter "gL" entered as a percentage (like I had before) I kept it as factor "g" so for example instead of entering 2% you'd now enter 1.02. I did this just to be consistent with Nick Edmond's blog post. I kept my original blog post here underneath this new spreadsheet. Enjoy! (There may be a bug: this one is not as robust as the old one: it doesn't handle changing in the inflation rate very well: but on the plus side you can try changing some of the other parameters). I corrected one bug: the solution always started from the same initial guess when it should have used the solution for the time step before as the first guess, thus it didn't always converge: Newton's method (in this case the vector version) is sensitive to where you start: you need to be close enough (in which case it converges rapidly), which you can get a feel for here. But I still have a problem with the auto-scaling of the plots. I think this is because I'm plotting values across hidden rows. The values in the hidden rows are not plotted, which is what I want, but I suspect they still do contribute to the axis auto-scaling solution. This problem is only apparent in the Price of Land plot with the default input parameters, but becomes apparent in other plots when you change some of the other parameters. The "eps" parameter only affects the Newton's method solution (it's the size of perturbations used to numerically calculate the Jacobian matrix) and has nothing to do with the model, but you can change that too (note in this case the Jacobian could well be calculated in closed form, but I wanted a more general approach). I have not tried changing all the different parameters, so it's quite possible you could break it! I've mostly just played with "g" and "alpha."
I never did end up using the circular reference iteration facility in Excel because of the nature of this problem: each time step depends on the prior one. Thus you can't really iterate at each time step independently. Thus the iterations are hardcoded in the spreadsheet, but in a fairly compact fashion. If you try downloading the spreadsheet, the "Results" sheet does all the work and looks fairly tidy, with the solution at each time step laid out one per row in a table. If you were to unhide all the rows and columns however, it would look worse, but not unmanageable. Between each pair of rows (save the 1st two), are the six Newton's method iterations laid out with the solutions in columns (I transpose to save vertical space). It's set up in such as way that if you want to add time periods, you can just copy and paste these unhidden blocks to the end (so, for example, it'd be easy to nearly double the number of time steps with a single copy and paste of the existing solution blocks appended to the end). Unhiding all the cells is not obvious, but only because I hid cell A1. Unhide that one and you're good (I'll let you figure that out... you may need to do some Googling). In fact, probably unhiding is not necessary to simply extend this solution to more time steps: simply copy paste rows of the existing table to the end. You'd need to adjust the plots of course. But if you want to see how it works or change the formulas, then you need to unhide.
Here's an update to the above. You can indeed keep the spreadsheet looking tidy and use a copy and paste procedure to extend the solution to more times, but doing this is not completely obvious, so I'll give some instructions here. The problem is a straight copy and paste leaves out some of the hidden cells at the top which allows the solution to propagate forward to each subsequent time. To get around this problem you need to unhide at least one of the hidden rows. First go to the "Results" tab of the downloaded spreadsheet. The cells implementing Newton's method are not present between times 0 and 1 (though unlabeled, time is in the 1st columns of the table on the left on the results page). You *could* just unhide all the hidden rows and columns, but to keep it looking tidy and to make it easier to work with we'll just unhide the first row after the time = 1 row, which at the time of this writing is row 4. Use the instructions here (I'm solving the puzzle I proposed you solve yourself with Google in the above paragraph, except on row 4 instead of cell A1). To do this for row 4, replace "A1" in those instructions with any hidden cell in the spreadsheet under row 1 that you'd be able to see if all rows (not columns) were unhidden. So for example the one on the far left of the unhidden columns in row 4 should work: as of this writing, that would be cell "E4." Following the instruction on the link, select "unhide row" in the drop down box on the format menu. Now you should see a blank row appear between the time = 1 and time = 2 rows: in this case row 4, starting with cell E4 on the left. Now copy the full rows starting with row 4 down to however many times you want to add. If you want to add 5 new times, copy from row 4 to row 108 (keep in mind there's a lot of hidden rows in between, which is why the end row is not row 9 = 4 + 5 here), then paste them starting in the first row beyond the bottom of the table (row 403). Be sure to select that whole blank row (row 403) to paste into, otherwise it won't let you do the paste operation, as depicted in Figure 1. Also note in Figure 1 the dashed green outline near the top showing the cells selected to copy. Column W separates the results table on the left from the charts data table on the right (only two columns of which are depicted).
You should see the block of rows get filled in with answers. The times in the far left column should now range from 21 to 25 in the new rows. This is depicted in Figure 2. Notice how the 1st column on the left (time) started not with 2 (the time in the copied row 4) but with 21 (the first time past the last time entry in the existing table).
Now you can continue to paste more cells until you get the table extended out to the time you want. Finish up by re-hiding all the blank rows in the results table. You'll notice that you get some bad values in the table to the right of the results table (in the otherwise blank rows): that's the chart data table for the charts on the "Parameters" sheet. Ignore those: once the row is hidden you can make new charts by either creating them from scratch or updating the data series in the existing charts to include the new rows: the bad values in the hidden rows should not get plotted. You'll need to reformat the x-axis of the chart(s) too because I fixed the maximum value at 20. Good luck!
I never did end up using the circular reference iteration facility in Excel because of the nature of this problem: each time step depends on the prior one. Thus you can't really iterate at each time step independently. Thus the iterations are hardcoded in the spreadsheet, but in a fairly compact fashion. If you try downloading the spreadsheet, the "Results" sheet does all the work and looks fairly tidy, with the solution at each time step laid out one per row in a table. If you were to unhide all the rows and columns however, it would look worse, but not unmanageable. Between each pair of rows (save the 1st two), are the six Newton's method iterations laid out with the solutions in columns (I transpose to save vertical space). It's set up in such as way that if you want to add time periods, you can just copy and paste these unhidden blocks to the end (so, for example, it'd be easy to nearly double the number of time steps with a single copy and paste of the existing solution blocks appended to the end). Unhiding all the cells is not obvious, but only because I hid cell A1. Unhide that one and you're good (I'll let you figure that out... you may need to do some Googling). In fact, probably unhiding is not necessary to simply extend this solution to more time steps: simply copy paste rows of the existing table to the end. You'd need to adjust the plots of course. But if you want to see how it works or change the formulas, then you need to unhide.
Here's an update to the above. You can indeed keep the spreadsheet looking tidy and use a copy and paste procedure to extend the solution to more times, but doing this is not completely obvious, so I'll give some instructions here. The problem is a straight copy and paste leaves out some of the hidden cells at the top which allows the solution to propagate forward to each subsequent time. To get around this problem you need to unhide at least one of the hidden rows. First go to the "Results" tab of the downloaded spreadsheet. The cells implementing Newton's method are not present between times 0 and 1 (though unlabeled, time is in the 1st columns of the table on the left on the results page). You *could* just unhide all the hidden rows and columns, but to keep it looking tidy and to make it easier to work with we'll just unhide the first row after the time = 1 row, which at the time of this writing is row 4. Use the instructions here (I'm solving the puzzle I proposed you solve yourself with Google in the above paragraph, except on row 4 instead of cell A1). To do this for row 4, replace "A1" in those instructions with any hidden cell in the spreadsheet under row 1 that you'd be able to see if all rows (not columns) were unhidden. So for example the one on the far left of the unhidden columns in row 4 should work: as of this writing, that would be cell "E4." Following the instruction on the link, select "unhide row" in the drop down box on the format menu. Now you should see a blank row appear between the time = 1 and time = 2 rows: in this case row 4, starting with cell E4 on the left. Now copy the full rows starting with row 4 down to however many times you want to add. If you want to add 5 new times, copy from row 4 to row 108 (keep in mind there's a lot of hidden rows in between, which is why the end row is not row 9 = 4 + 5 here), then paste them starting in the first row beyond the bottom of the table (row 403). Be sure to select that whole blank row (row 403) to paste into, otherwise it won't let you do the paste operation, as depicted in Figure 1. Also note in Figure 1 the dashed green outline near the top showing the cells selected to copy. Column W separates the results table on the left from the charts data table on the right (only two columns of which are depicted).
 |
| Figure 1: Screenshot of spreadsheet just prior to pasting the copied rows |
You should see the block of rows get filled in with answers. The times in the far left column should now range from 21 to 25 in the new rows. This is depicted in Figure 2. Notice how the 1st column on the left (time) started not with 2 (the time in the copied row 4) but with 21 (the first time past the last time entry in the existing table).
 |
| Figure 2: Screenshot of spreadsheet immediately after pasting the copied rows. |
Now you can continue to paste more cells until you get the table extended out to the time you want. Finish up by re-hiding all the blank rows in the results table. You'll notice that you get some bad values in the table to the right of the results table (in the otherwise blank rows): that's the chart data table for the charts on the "Parameters" sheet. Ignore those: once the row is hidden you can make new charts by either creating them from scratch or updating the data series in the existing charts to include the new rows: the bad values in the hidden rows should not get plotted. You'll need to reformat the x-axis of the chart(s) too because I fixed the maximum value at 20. Good luck!
Below is my original post which I'll leave intact for now:
From Nick Edmonds' Simulation Model for Samuelson and Inside Money post. You can edit the whole workbook. If you mess it up and want to start over, just reload the page. This is a first attempt at doing this kind of thing online iteratively with free online spreadsheets. I think I may be onto a better approach here, but have yet to connect all the dots. The "Growth rate for nominal loans" cell at the top changes the gL variable in Nick's write up (see this comment). Nick actually discusses the "Growth parameter for nominal loans (g)" in his table of variables, but the relationship is simple: g = 1 + gL. Enter it as a percentage in the long orange cell near the top (A2:E2). The workbook iteratively solves the equations, but only two iterations are used for each point in time. Check the columns "Cw err," "pa err" and "pc err" to see how close to a solution the two iterations get you. You may need to use the scroll bars. You should see the plot change when you change gL. It's definitely not perfect, but it should work reasonably well for values of gL near 2%.
Newton's method: a forward difference is used to calculate a Jacobian to solve the
problem and "delta_base" is the size of the perturbation to the Cw, pa,
and pc variables at each time (Each year is a row in the spreadsheet).
You can change "delta_base" by editing cell F2 (dark gray) just to the
right of gL. The solution shouldn't be too sensitive to delta_base. I
could improve the solution by adding more iterations to each of the
other tabs labeled 2 through 21. These sheets are a bit ugly... I was
interested in getting it to work online with minimal effort (Excel
"Solver" and macros are not available online). You can download the
workbook by clicking on the Green "X" Excel symbol on the right hand side of the black bar
across the bottom of the spreadsheet.
No comments:
Post a Comment