Chapter 10 Video: RAND

In this excel tutorial I’m going to be talking to you about economic simulation. An economic simulation is when you use random sampling from probability distributions for one or more of your variables. Basically, it’s miss..mixing statistics with econ. And it helps you with..with getting a more comfortable or a better idea about what your economic analysis might be by adding in probability or variability to your process. Now an example we have..I have here for you, our first cost is $25,000, our interest rate is 8%. Our life follows a uniform distribution between 8 and 15 years and our annual benefit follows a normal distribution with a mean of fifteen three.. $5300 and a standard deviation of $1200.

So, let’s go in and see how you get this to work when you have this type of simulation. In order to calculate your life, your life is the minimum of 8 up to 15, somewhere in between uniformly distributed. To do this it is equals C6, which is my minimum life of 8 years, and I’ve hardcoded this so I can just grab the corner and pull it down. Plus, I’m gonna do INT which will round to the ne..to a whole integer and inside there my number. It’s going to between..the reason I have 7 code there is because that’s the difference between 8 and 15 and its gonna be times a random number, which you just pull some random number between 0 and 1. So it calculates a life for me, somewhere between 8 and 15 following that uniform distribution. Notice I got a 14 there. Now, when I grab the corner and pull this formula down, notice what happens.

I get a different random number between 8 and 15 following a uniform distribution over my 20 iterations. Now to calculate my payment, remember this follows a normal distribution with a mean of $5300 and a standard deviation of $1200. To get the economic simulation working in there, this cell is calculated by typing in equals NORM INV, so N..O..R..M INV. Okay, it needs probability, the probability is, I wanted to draw a random number between 0 and 1, so my probability is RAND with an open and closed parenthesis comma. Then it wants my mean, which is in C6, and my standard deviation… I’m sorry, not C6, E6. My standard deviation is E7. I want both E6 and E7 to be locked because those will never change. I do want my random var..number to change. So simply hit return. And you can see that I calculated an A.

Now did you catch when I hit return that all my life values changed? This is what goes on whenever you’re doing a simulation or a random value in this. Anytime you click on these cells they’re gonna change over because it sees a new iteration going in. So just be aware that that’s going to happen. Now to calculate my present value, that’s gonna equal present value, remember where I need to know my rate? Which my 8% never changes, so notice I’ve hardcoded. My NPER is gonna be in column B, the..for the first one B10. I’m not hardcoding it because I wanted to iterate as I go down the..the number of iteration lives. My payment is in C10. Again, that’s gonna change with each iteration so I’ve not locked it down. And then I have to subtract off my first cost of B2. And so I just simply hit return and I can see my value. Now, I can just grab these both, come across, and you can see that it did something really weird.

What happened here? Oh, I did not lock my B2, did you notice how I got no values here? So, let me lock that B2 because it never changes. Okay, and hit return. Now when I grab that corner I should be in good shape. There, now we have values. Now, if I wanna calculate the mean and standard deviation of my iterations this is simply equals, equals average and I’m gonna average and I’m gonna highlight my numbers. And I can see that my mean, present worth is this..is there and my standard deviation is gonna equal, standard deviation, I gotta think about what the coding is, standard dev there it is. Standard deviation, okay S..T..T..D..E..V and I’m just gonna highlight my 20 different iterations and you can see I get a standard deviation. Now, what this has done for you is now over 20 iterations using different dis..dist..using different distributions for the information you have. I can get a mean and standard deviation over my 20 iterations, which hopefully will give me a better or more confidence in what my present worth is.