[slide 01]

Good to see you again. :)

At this point I am assuming you have watched the spreadsheet basics video and also maybe even checked out some documentation on the Spreadsheet Resources page.

So, let's apply what we know to solving a couple of simple problems from the first problem set, to get you started, and then maybe we'll try a more complex problem as well, just to show you how we can brute force anything we want with the raw computational power available to us, as long as we know how to set things up.

[slide 02]

These are the first couple of problems in the first problem set. These should be easy enough if you remember anything from principles of finance - if not, chapter 1 of the course textbook is calling your name!

Examine the first problem - feel free to pause the video here to give yourself some time to read it. As you can see there are 3 numeric inputs, so these are what we have to work with. Let's set up a spreadsheet to solve this problem.

[slide 03]

Problem 1

Initial value, or present value, is 177 thousand, rate is 2.4%, or 0.024, and time is 16 years.

Now, to calculate the result, we will reference these input cells in our result cell. It is very important to only use cell references and not to hard code any of these numbers in your calculations, just in case you have to do another attempt and have to work with new numbers.

So,  let's build the future value, start our formula with an equals sign , present * (1+r)^t, enter.
And there it is. Now, if we got something wrong in the first attempt, and try another, and get new numbers, we do not have to do anything other than update the inputs - assuming that we got this problem correct.
For example, imagine if our new present value is 120 thousand - just update that input cell, and voila, we're done.

[slide 02]

Now let's try the second problem. Take a moment to give it a read. As you can see, there are 2 numeric inputs, so we'll start with that.

[slide 03]

Problem 2

Initial value, 77 thousand, rate, 20.3% or 0.203.

For our calculation, we are going to accrue the daily rate, which is 1/365th of the APR, and compound 365 times. 

Enter the formula: = present * (1+r/365)^365

And there we are. Again, if in a future attempt we get different numbers - and you most certainly will! - just change the inputs and enjoy the power of your spreadsheet.

[slide 04]

Let's examine a more involved problem from the first problem set. Take a moment to give it a read.

We have several numeric inputs here: 14 years, 4 payments, 21 thousand each, 8% annual investment return. 

This problem can be solved with the use of present and future value of annuity formulas, but let's pretend we didn't know these and just use the power of the spreadsheet to set up the basic structure of the problem from first principles, and see how that goes. 

[slide 05]

Problem 9

So, let's enter our inputs, that's always a good place to start.
time, 14 years, number of tuition payments, 4, cash outflow 21 thousand, rate, 8% or 0.08.

We don't know what the size of each annual investment payment must be, let's take a guess, you'll see why. let's guess it's 5 thousand.

Let's build our timeline, which starts at time 0, the present moment, and goes forward.

So what happens with our cash flows? At time 0, we put in our first 5 thousand dollars. At time 1, we will have earned 8% on that first investment, and then on top of that, we throw in the next 5 thousand. So in total at the end of one year, we will have 10400.
Next year, we will earn 8% on the 10thousand 400, and throw in the next 5 thousand, and so on, and we will keep doing that until we reach the year 13, since the problem stipulates that we stop one year before we have to start making tuition payments.

Let's lock in the reference to the rate and the cash investment, so that these references don't shift as we fill down, and fill down to year 13. This is how much money we will have in 13 years!

Now, one year later, at time 14, we will have earned another 8% on this, but then instead of adding more money, we have to start shelling out the 21 thousand per year for college. Let's make that happen.

Fill this down for 4 years, and see what we are left with at the end!

Looks like we were investing too much! Because after we are done paying the 4 years of tuition, we still have a bunch of money left over! This is easy enough to fix, let's just tweak our investment amount, until such a time that we are left with just about 0 dollars in our investment account when we are done, and that will be the minimum that we must invest in order to afford college.

Try 2000. That's too little, we ran out of money and then some. Try 3000. Too much. And so on and so forth, you get the idea.

Doing this process manually is really not that bad. But this trial and error approach is so generally applicable and popular and useful, that most spreadsheets, including Excel and LibreOffice, have a special function called "goal seek" to do this trial and error search automatically. Let's give it a shot. 

There are three inputs required. 
The first is our final value cell, the second is the target value for it, and the third is the cell that we are going to change to achieve our target.
We are going to set our end value cell, to target value of 0, by changing the investment amount cell. 
Hit ok, and like magic, we are done, and that's our answer for how much we must invest every year at 8% in order to meet the college expense goals.

[slide 06]

Was that awesome or what? I hope now you are equipped to solve any problem that comes your way! See you on the discussion boards!