Welcome. We are doing applications and I call them mega problems. They are mega for the context. What I want you to know is that they are 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 the first two capture a lot of complexity for this level. 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.5 percent per year. This is typically inflation that has been happening at least in the US, and I'm sure elsewhere too unfortunately, because tuition going up is not very good for families. Currently the average tuition of a state college is $10,000. State college meaning a college that a person is going within their state in the US. I use a lot of examples from the US, but I'm sure they're similar setups elsewhere. Emilia's daughter, Jessica, will begin college in five years. Notice the first complexity, I've taken you to the future and that's why I keep teasing watch Matrix and you'll get it, you'll get finance. Five years in the future, Emilia's portfolio of savings is making 5 percent annually. What Emilia, the mom, has done has been saving for Jessica to go to college among other things. How much does Emilia need to have set aside today to pay for four years of college for Jessica? 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 because you know when you are going to save money, what for, and so on. Let me first do what I recommend everybody to do is to draw a timeline, and that's really helps. I'm going to go slow in this segment. We start at time 0, which is now and then you keep going forward. Time 1, 2 and the next time that I will enter is 5. The reason I'm entering five is because five years from now is when Emilia's going to go to college. 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 or I will call it the investment, tends to happen one year before the cash flows occur later. For example, if you start a company, you tend to spend money buying equipment, stuff like that before you actually start making money. This is the nature of the beast. You pay tuition in this case in years 5, 6, 7, and 8. Typically, those will be at the beginning of the years of first starting college. That's the nature of the beast. I'm just highlighting what the real world looks like. What will I do with this problem is first of all, recognize the important elements here. Then you'll have cash flows here, here, and here. These will typically be negative numbers. You will have some negative number going out here and we call that tuition. The problem that we are trying to solve is over here. The first thing you have to recognize is there's a gap of five years and how do you deal with it? Things like that, you should be very aware of. The first step in doing this is first, draw the timeline. 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 and it will 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. First, understand what the problem is. The 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. That little reality makes this problem complex too, for our level. Let's keep going. We break up the problem, solve the problem in pieces using financial tools. Let's start. I will call this A, B, C, D, E, F, G, H, I. I am writing all these out. If you notice how many are there, 1, 2, 3, 4, 5, 6, 7, 8, 9. Open up your spreadsheet and you'll see these are the natural timeline that Excel provides you. What I'll do here is I'll write 10,000 as the tuition today. What will it be at time 0? That's the natural timeline, 1, 2, 3, 4, 5, 6, 7, 8. I'm sorry, I'm going slowly, but I think once you get this one example, it will be so useful doing everything else. What will the cash flow be at this time, the tuition? It's pretty simple, I'll just write it out. It will be 10250. 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. 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 number five corresponding to year five. How would you get that number? Notice what I've done on top. If this is for one, this is for two, this is for three, I just keep changing the number. This is for four and this is for five. 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. The discount rate in our problem, let me write it out, is five percent. Those are two distinct things. One captures the first one, discount rate is supposed to capture inflation plus more. 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. 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 till year five, inflation doesn't wait for her. Inflation is happening. Sorry, I laugh at my own jokes as usual. Now that I've gotten the first piece, first bite-sized piece is these four tuitions that I'll pay. Remember, they'll be typically at the beginning of the year. Even though she's doing college and so on, she'll end college in year nine. That's of interest, but the tuition will happen in year eight. That's the nature of the beast. We've gotten the first number. I would recommend very strongly is trying to populate all these cells in Excel. 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 2. This was number 1. Step number 2 is to figure out the value of these cash flows. The natural instinct is to come directly to today. 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, 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 want to change them, is that the first cash flow happens one year or one period ahead. 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, cannot be used here because it's typically assumed to be a fixed number. Here's another thing I will introduce, do NPV, and I'm reminding myself that it'll be in year 4. Why? Because if the first cash flow is in year five, if I simply use NPV, that's what will happen. Do equals NPV, the four reminded myself in Excel, write equal NPV, and you'll notice the first number is the rate. Press 0.05. Later in the course, we'll see what the N stands for. But Excel is not able to distinguish between PV and NPV. I use NPV as a way of doing cash flows that are changing over time. That's what I remind myself, so 0.05. Now here's the trick. The first number is sitting in which? F1, and the last number is sitting in which one? This is why I like Excel. Excel is just wants to know where the numbers are sitting. If the first number is 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 will get 41,586.22. This is basically the present value, but in which year? In year four. We have done step 2, but there's another step left. I didn't ask you, how much will Emilia need to have in her trust fund a year before her daughter starts college? I'm asking you now. So step 3 is, what is the value at time zero? 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. But then PMT is nothing and then the future value is 41,586.22 The cool thing about Excel is if this number is sitting in some cell, 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. Will be back in a second. Please look at it again.
