Building Models (lab)

Announcements/Discussion

• FC conference
• What majors do people have?
• Let me know if the math is too challenging or not challenging enough
• Questions from last time? (How the course works, or whatever)
• Lateness coupons.
• please remember the 1-minute emails
• reading includes the online notes!

Labs

It's my goal to make labs generally a self-paced, hands-on activity day. I may occasionally introduce new material, but I'll try to keep that to a minimum.

The goal for today is to build several of the models that we talked about last time. There may be elements of the models that you don't yet understand, but we'll get to that next time. Today, we're going to try to emphasize the "technical" skill of building the models.

Excel

Many of you are already very skilled with Excel, but there may be some formula and addressing tricks here you don't know. Feel free to skip or skim this if you're finding that it's not worth the time. You can always come back to it to review if you want.

Building the Compound Interest Table

We'll build a table for computing the value of 100 compounded monthly at an annual rate of 6%.

1. Start Excel. Create a new worksheet.
2. Starting in the first row of the first column (just for simplicity), create the first two rows of the table. It should look like this:

   period	old balance	simple interest	new balance
   1	100	0.50	100.50

   The 50 cents is actually a formula, of course. Instead of typing 0.50, we want to compute the simple interest on the old balance at 6% per year.
3. Formulas in Excel begin with an equals sign. When you want to use the value in another cell, you can just click on that cell and its address goes into your formula. Or, you can type the address yourself. So, for this formula, you'll have:

   = B2 * 0.06 / 12

   (The B2 is the cell address of the cell containing the number 100, assuming you started your table in the first row of the first column. In the cell, you'll see 0.5, but you'll see the formula up in the formula bar.)
4. Put a formula for the new balance in. The new balance is simply the sum of the old balance and the interest, so the formula is:

   = B2+C2

   Again, if you want, you can click on the desired cells instead of typing the cell address.
5. Okay, now we're ready to start taking advantage of copy/paste. Click and drag to select all four cells of row 2. Select Edit/Copy or use the keyboard shortcut (command c). You'll see your selection surrounded by vibrating dashed line. Then, click in the leftmost cell of row three and select Edit/Paste or use the keyboard shortcut (command v). Type ESC to stop the vibrating line.
6. Didn't work, did it? We need the new balance in the "old balance" column, not another copy of 100. So, change cell B3 to the following formula:

   = D2

   Strange formula, isn't it? It just "forwards" information in one cell to another cell.
7. Everything should be okay now. Well, almost. You can fix column A to increment the period. You would want a formula like "=A2+1"
8. Now, you can copy/paste the second line to the third. Does everything look good? If so, copy the desired line, click-and-drag to highlight lines 5–60, say, and then paste. Pretty easy, hunh?
9. But, what if we wanted to change the interest rate? We'd have to change each cell in column C. No good. We should have used another cell.
10. Start over on a fresh worksheet. Decide on a special cell someplace to the right, say cell F1, that holds the interest rate. Put the 0.06 in cell F2. Duplicate the first two rows, as before (as in step2 ). Then, in cell B2, where we had the formula

    = B2 * 0.06 / 12

    we will instead have

    = B2 * F2 / 12

11. Finish the first line and then copy/paste it to create the second line, as before. What goes wrong? You get a zero in the interest cell, C3. Why? Look at the formula. The formula has changed to:

    = B3 * F3 / 12

    The problem is that when we copy/paste a formula, the cell addresses get automatically adjusted to refer to different cells. For example, the cells in column D always refer to the cells in columns B and C of their same row (check this). But this adjustment is wrong in the case of the interest rate. We always want the formula to say F1, even when we copy it someplace else. To do that, we have to decorate the cell address with dollar signs. Change the formula in cell B2 to

    = B2 * $F$2 / 12

    and copy/paste it again. Does it work now? Good.

Cell addresses

The message here is that cell addresses can either be

• relative: they get adjusted when copied to new locations, so that the new references have the same relationship to the referring cell, or
• absolute: they don't get adjusted and always refer to the same cell

By default, all addresses are relative. You can make an address absolute by adding dollar signs.

Q: What is the return on $100 invested at 6% compounded monthly for three years? At 8%?

Q: How would you change this table to handle regular deposits, say of $10 every month? Have a cell where you can adjust all the deposits in a single swoop.

Q: Change the interest rate to whatever the rate on your credit card is. Change the initial balance to $1000. What is the monthly interest? Assume you pay just that plus $10. How long will it take you to pay off? How much will you have paid, total?

Extend

Extend models are built primarily out of blocks. These blocks live in libraries. These are grouped hierarchically, making it easier to find the one you're looking for, but only once you know what you're looking for and where it fits. So, this can be hard. Part of the point of this exercise is to practice finding blocks.

Another point to this lab is becoming comfortable with a few of the blocks, so read the dialogs and such that are presented to you. You can click on the "help" button in each block dialog to learn more about the block.

Start Extend by using the "Go" menu, as we did in class the first day. You can close the "Demo.mox" window that will automatically open.

You can create a new model by choosing File > New Model. This will give you a blank sheet to build your model on.

You'll have to open some libraries. Click on Library > Open. This will open a file system browser. Select "Generic Lib." Notice that this is now added to the end of the "Library" menu. Repeat this procedure to open "Plotter Lib."

Compound Interest

For this, we'll do two models. One an initial deposit of $1000 and the other a yearly deposit of $1000. We'll compare them over 45 years. We'll start with the initial deposit model.

1. Get a constant (Library > Generic Lib > Inputs/Outputs > Constant). Double-click it and make it $1000. This is our initial deposit. You can type a label in the box to the right of the "help" button; I find this helpful.
2. Get another constant. Double-click it and make it 0.1. This is our interest rate. (You can copy/paste the existing one, if you want.) Blocks are put wherever you last clicked in the model window, so keep that in mind. You can drag your blocks around to keep things neat.
3. Get an accumulator (Generic Lib > Holding > Accumulate). Accumulators look like tanks of water and they keep a running sum of the stuff sent to them. They are often used for state variables of a model, such as the number of dollars in this case, or the velocity of an object, or the amount of pollution, or the number of rabbits, etc.
4. Connect the StartingContentsIn port (the little square connector) to the appropriate constant.
5. Get a multiplier (Generic Lib > Math > Multiply). Connect one input port to the ContentsOut port of the Accumulator and the other to the interest rate.
6. Connect the output of the multiplier to the InputIn port of the Accumulator.
7. Get a "Plotter, I/O" from the plotter menu. Connect one of its input ports to the ContentsOut of the accumulator. Plotters are one of our most important output tools. This one plots values (up to four lines on one graph) with respect to time, but there are others, such as histograms.

Adjust the stop time to 45 years. Do this in the Run > Simulation Setup (command y) menu item. Click on the "continuous" tab and specify the end time as 45.

Run the model (Run > Run Simulation, or command r). What is the final value? What does the plot output look like when you take the log of Y1?

Compound Interest with Constant Contributions

Let's add some more stuff to the model.

1. Get another accumulator, another multiplier and an adder.
2. Connect the $1000 constant to the StartingContentsIn of the accumulator.
3. Connect the ContentsOut of the accumulator and the interest rate to the multiplier, as we did before.
4. Connect the output of the multiplier to one input of the adder. Connect the $1000 constant to another input of the adder. This represents the fact that, in addition to earning interest, we will make our own contribution of $1000 each year.
5. Connect the output of the adder to the InputIn port of the Accumulator.
6. Connect the ContentsOut of the accumulator to another input of the plotter.

Now run the model again. Look at the plot of both curves, both with and without logs. Notice how the slopes of the log curves are the same.

Experiment:

• What interest rate do you need to make $1,000,000?
• Delete one of the connections. (Double click on the line and press "delete.") Try running the model. What happens?
• What happens if you change the compounding period to monthly? Don't forget to divide the annual interest rate by the number of compounding periods per year. (Warning, this is complicated.)

Save your model to your desktop (say, as "myinterest.mox") and copy it to a zip disk or to your FC account. This may be useful later.

Lake Pollution

Open the Lake Pollution model. (Examples > Tutorials > Lake Pollution.mox). Explore the model. Here are some features:

• Notice the word "sum" connected to the output of the adder and to the red input of the plotter. You can use text instead of lines to connect values. This is really useful if the lines would end up making the model look messy and hard to read. Here's how:
  • Double-click somewhere in a blank area of your model. Type in the word or phrase you want.
  • You can now connect this to an input or output of a block.
  • Double-click somewhere else and type in the same word or phrase
  • You can now connect this to an input or output of a block.
• Don't worry about the details of the "input random number" block (factory 1). We'll look at random numbers next week.
• The input data (factory 2) block is interesting. The table gives the output during different time periods. Try plotting the data (there's a button to do that in the info window for that block).
• Try changing the output to "interpolated" and plot it.
• The holding tank block is very much like an accumulator, but with some subtle differences that we'll look at below.

For practice, replicate this model. For really good practice, close it and try to replicate it.

Accumulate vs Integrate w/ and w/o Delay

To understand the differences among different kinds of holding blocks, we'll do a comparison. Make a new model:

1. Get one "accumulate" block and three "holding" blocks.
2. Get one constant, say 1000, and feed it to the the InputIn of all the other blocks.
3. Get a plotter and connect all the ContentsOut ports of the blocks to different lines on the plotter
4. Open the holding blocks and make one of them "summed" (the default), another "integrate (delay)" and the last "integrate (no delay)"

Now we have to configure the simulation. Click on Run > Simulation Setup.

1. Click on the "Continuous" tab and make the end simulation time 1.
2. Make the time units "years."
3. Make the time per step 0.25. In other words, we'll advance time by quarters of a year.
4. Click on "run now"

What are the results? Look at the table of values below the plot as well as the plot. If you don't understand, ask!

Answers

The goal of lab, like the goal of science in general, is insight, not numbers. I want you to understand what you're doing and feel confidence in your skills. So, if you feel uncertain about what you're doing, you should ask for some help, even if you get the problem "right." That sense of uncertainty is important. So, I'm providing these answers more for those who are confident but wrong: to shake their mistaken sense of certainty. If, after solving the problems your own way and then exploring my answers, you still feel any uncertainty, please ask.

• The return on $100 at 6% compounded monthly for 3 years is $19.67. At 8% it is $27.02.
• Regular deposits can be handled several ways. One way is to add a new column between the "simple interest" and the "new balance" column. Call it "deposit." The formula in that cell should be just

  =$f$3

  where f3 is the cell that contains the regular deposit. Then, change the formula for "new balance" to be the sum of the old balance, the interest, and the deposit, so it would be something like

  =b2+c2+d2

• For computing the credit card payoff, I changed the interest rate to 12%. In that case, the monthly interest starts out at $10. I then changed the formula for the new balance to be just the old balance minus $10, since the interest is entirely paid. Obviously, it takes exactly 100 months to pay off the credit card. Here's the interesting thing, though. The total amount I paid is $1000 plus the interest. So, I need to add up the interest column. In my case, this turns out to be $505. (It's easy to sum a column in Excel: just click in the cell below the column and then click on the summation (Sigma) symbol in the tool bar.) So, the total amount I paid was $1000+$505 or $1505. That's a lot of money to pay for $1000. Worse, it takes 100 months, or over 8 years, to pay off this debt.
• The Extend simulation shows that $1000 compounded yearly for 45 years at 10% yields a total $72,890.48. Not bad for such a small investment, though it sure takes a long time. If we take the log of Y1, both the blue (interest) and red (balance) curves become straight lines. This is characteristic of exponential curves: their logs are straight lines, which can occasionally be easier to read or compute with.
• If you'd like to see one way to construct the model, you can look at this: interest-with-deposits.mox.
• The interest rate you need to make $1,000,000 is a bit less than 11%
• If you delete one of the connections to the plotter, it just deletes that one line. If you delete an input to the multiplier, you get an error message: "both connectors must be used in Multiply, block number ..." Various other error messages may apply. The point of this experiment is to purposely cause errors so that you can become comfortable with what Extend does.
• The key steps to switch to monthly compounding are to divide the interest rate by 12, but also to divide the $1000 deposit (since you're not depositing $1000 each month, but maybe you're depositing $1000/12 or $83.33 each month). So, we can just divide the sum. Also, we now need to run for 12*45 or 540 periods. Now the total after 45 years at 10% is $961,896. Here's my solution: int-deposits-monthly.mox
• We'll discuss the results of the hold tank comparison as a class. Here is my solution: holding-tanks.mox

This work is licensed under a Creative Commons License | | | |