Application II: Saving for College Part 1

Welcome, we are doing applications, and I call them mega problems. They are mega for the context, right? So what I want you to know is that they're not mega, mega as we go more and more advanced. Word problems become more and more complicated. The first one we did was very detailed. And I hope to do this one very detailed as well. The next two I will let you do a lot of stuff on your own and guide you. But first to capture a lot of complexity for this level. So up there on your screen you should be able to read the problem, and I'll read it slowly with you.

College tuition has been rising at a rate of 2.50% per year. And this is typically inflation that has been happening, at least in the US, and I'm sure as fair too unfortunately because tuition going up is not very good for families, right? Currently the average tuition of a state college is $10,000. So state college meaning a college that person is going within the state in the US. And I use a lot of examples from the US, but I'm sure there's similar setups elsewhere. Emilia's daughter Jessica will begin college in 5 years. So notice the first complexity. I've taken you to the future and that's why I keep teasing what matrix and you'll get it, you'll get finance.

So 5 years in the future, Emilia's portfolio of savings is making 5% annually. So what Emilia, the mom has done has been saving for Jessica to go to college, among other things. How much does Emillia need to have set aside today to pay for four years of college for Jessica? Now What happens in word problems that are written up in the context of how we are learning today is that they can only go so far in making the world real. So they can be sources of confusion. That's part of the reason why I'm doing the problems is that to clarify to see how the wording gets translated into reality.

In real life, things can be simpler in a way right, because you know when you're going to save money, what for and so on. So let me first do what I recommend everybody to do is to draw a timeline, and that really helps. And I'm gonna go slow in this segment. So we start at time zero, which is now, and then you keep going forward, so time 1, 2.

And the next time that I will enter is 5. And the reason I'm entering five, is because five is from now, is when Emilia's going go to college, right? So that's when the first tuition will be paid. Then, 6, 7, 8. Another interesting thing about tuition and you'll see this in later problems as well are I will call it the investment tends to happen one year before the cash flows occur later. So for example, if you start a company, you tend to spend money buying equipment, stuff like that before you actually start making money, right? This is the nature of the beast. So you pay tuition in this case in years five, six, seven, and eight.

And typically those will be at the beginning of the years after starting college, right? That's the nature of the beast. I'm just highlighting what the real world looks like, okay? So, what will I do with this problem is first of all recognize the important elements, here and then you'll have cash flows here, here and here.

These will typically be negative numbers, right? We'll have some number going out here, and we call that tuition. But I'm, the problem that we are trying to solve is over here. So the first thing you have to recognize is you, there's a gap of five years and how do you deal with it, right? Things like that you should be very aware of. The first step in doing this is first draw the timeline. And I'm going to highlight a few things when I solve this problem. Because I'll use Excel to do it, without using Excel. So that, you open up Excel, and you follow what I'm talking about, it'll be useful.

You have a handout about how to execute Excel, and my goal is to make you go there, but not do it for you kind of thing, right? First, understand what the problem is. So, question, the one thing I need to know which I can know is what is the amount of tuition I'll be paying and in which year in the future? The problem is tricky because the amount of tuition is changing every year because of inflation, and in that little reality makes this problem complex still for our level. So let's keep going. We break up the problem, solve the problem in pieces using financial tools. So let's start.

I will call this A, B, C, D, E, F, G, H, I, right? I'm writing all these out, and if you notice how many are there? One, two, three, four, five, six, seven, eight, nine. Open up your spreadsheet and you'll see these are the natural timeline that Excel provides you, okay? So, what I'll do here is I'll write 10,000 as the tuition today. What will it be at time zero? So that's the natural timeline. One, two, three, four, five, six, seven, eight. I'm sorry I'm going slowly but I think once you get this example it will be easy doing everything else. So what will the cash flow be at this time? The tuition.

It's pretty simple, I'll just write it out. It'll be 10250. So I already know this. How do I know this? Because I'll take 10,000.

And multiply it by 1.025. You can do it in Excel very simply, by taking item B as item A times 1.025. And if you keep doing it, you'll keep getting numbers for each row. But which is the number I'm really interested in? I'm really interested in, and I'm going to write that one out, is 11314.08. That's the first number I'm really interested in. How do I know that? Because it's in number five, corresponding to year five. And how would you get that number? Notice what I've done on top, if this is for 1, this is for 2, this is for 3, I just keep changing the number, this is for 4, and this is for 5.

So this effectively becomes the future value five years from now but future value of what? You have to be very careful. Future value of the tuition, because the inflation rate, is not necessarily, and hopefully not identical to the discount rate, right? So the discount rate in our problem, is, let me write it out, is 5%. Those are two distinct things, right? One captures the first one, discount rate is supposed to capture the inflation plus more, right? So, 113, 11596.93, 11886.86 and 12184.03. If I make a mistake, please figure it out and correct it. I hopefully have not made a mistake, right? So the first step is to figure out the tuition, and it seems to be okay, why?

Because it's not a flat number. The important thing to recognize is even though she will not go to college until year 5 inflation doesn't wait for her, right? So inflation is happening, sorry I laugh at my own jokes as usual. So now that I've gotten the first piece, first bite sized piece is these four tuitions that I'll pay, and remember they'll be typically at the beginning of the year, right? Even though she's doing college and so on. She'll end college in year nine. That's off interest, but the tuition will happen in year eight, that's the nature of the beast, okay? We've gotten the first number, I would recommend very strongly, trying to populate all these cells in Excel, okay?

And we are in which row? We are in row number one. We could be in any row, but I'm waiting for you to put all these down. Now comes the second step. Step number two.

This was number one.

Step number two is to figure out the value of these cash flows. Now the natural instinct is to come directly to the day, right? But more sensibly, you can do it using Excel or using the formula. The trick here though is you want to use the PV formula, right? And if you did a PV formula, when would it tell you the value? It would tell you the value at 0.4. It would be at 0.4 because remember the one convention built into all formulas unless you wanna change them is that the first cash flow happens one year, or one period ahead. So if it's happening in five, the formula will give you a number of at time four.

This is very important to remember. But there's another thing you want to know. Is if you use the PV function. You will not be able to execute this and the reason is the PV function allows for a FV or a PMT. And PMT by nature can not be used here because it is typically assumed to be a fixed number. So here is another thing I will introduce. Do NPV and I'm reminding myself that it'll be in year four, why? Because if the first cash flow is in year five, if I simply use NPV that's what will happen. So do equals NPV the four reminds myself in Excel, right?

Equal NPV, and you'll notice the first number is the rate plus 0.05. Okay so we'll get later in the course, we'll see what the N stands for but Excel is kind of not able to distinguish between PV and NPV. So I use NPV as a way of doing cash flows that are changing over time, that's what I remind myself, okay so 0.05. Now, here's the trick, the first number is sitting in which F1?

Right, and the last number is sitting in which one?

This is why I like Excel. Excel is just no wants to know where the numbers are sitting. So if the first number's sitting in column F row 1, I plug F1. And the last one F, G, H, I, I've done this and what do I get? I hit return and I'm staring here I'll get 41,586.22. Okay, so, this is basically the present value, but in which year? In year four, okay? So, we have done step two, but there is another step left. I didn't ask you, well, how much will the mom need, Emilia need to have in her trust fund a year before her daughter starts college, I'm asking you now.

So step three is what is the value at time 0? And this you should be able to do pretty easily. Say equals PV, what is the first number you press in PV? Rate, 0.05. Now, you need to press N, and the N is four. Again, please remember, I explained that and did it very slowly. Why is it four? Because of the nature of how formulas are typically set up. Okay, so four but then PMT is nothing, and then the future value is 41586.22. The cool thing about Excel is if this number is sitting in some cell, right? You can just reference that cell, instead of writing out the whole number here.

Turns out, when you press enter, you will get a number equal to, 34,213.09. Please take your time, pause this video, redo this problem. When I come back, I'll do something that I won't do in as much detail later. But like the first application, I want to take this application and squeeze out all the learning from it. We'll be back in a second. Please look at it again.