Transcript:
Microsoft Excel has a really cool feature called What-if analysis and today. I’m going to show you how to do a data table and there’s two different data tables you can use. So when you click on data at the top what-if analysis, there is data table. I’m gonna do two of them, so lets. Go ahead and get started. I’ve got in column a interest rate months finance amount, so this could either be a card note or a house note. If it was a house note, 30 years times 12 is 360 If you want to go 15 years, that would be 180 15 times 12 and then whatever your finance amount is for your house. So this could be a new house note a refi or it could be a car, new or used it doesn’t matter. Let me go back and leave it as a car to start off with your payment for five percent sixty years, which is five years 60 months. Twenty four thousand is the PMT function. What interest rate you’re looking for B one and you need to divide that by 12 because you pay your note monthly comma number of payments or number of periods. That’s the number sixty b2 comma. PV is present value. How much money do you have to borrow today to get that car? This is after a down payment. It’s after a trade-in the other two arguments. F of V and type are not required press. Enter, there is your answer. It always shows as a negative number. Some will double click and make it negative. B 3 So now it’s positive, so your total payments is simply your monthly payment and we’re going to multiply that times the number of periods you need to pay it 27000 175 so therefore, the interest paid is B 6 – how much you financed today for it, which is twenty four thousand three thousand one seventy-five remember the keyboard, shortcut control and the key that’s to the left of number. One on your keyboard. It will show your formulas. Instead of your answer, you just toggle back and forth. So these three right here. Those are input cells. Those are just numbers. I typed these three down. Here are dependent cells because they’re dependent on the input cells. So now let’s do the data tables. I’m going to do two of them. The first one is called a one input data table. What I’m trying to figure out is if I have a different interest rate. What’s my car note? Some will start off at zero point, two, seven, five and then zero point three. And let’s autofill those down, and I’m gonna stop at 7% a 6% works. Home tab what’s making percentages and increase the decimal? There’s my interest rates in f1 I’ve got to do this formula. A 3d reference equals and it must be a dependent cell. I’m trying to figure out what’s my payment going to be there? Those interest rates so it’s equals b5 I’m gonna hide that in just one. Second highlight II one two f-15 data tab at the top. What if analysis data table? I’ve got two options column and row notice. They both say input, which means it’s going to be one of these numbers over here. I’m running down column E with those percentages, two point, seven, five three. So it’s going to be com input b1 Watch what happens when I click, OK? That is my monthly car note. For those different interest rates, lets. Just make them look a little better home. Tab it’s up to you, comma. If you don’t like comma, go make it currency. Lose the decimals. If you want to hide that number right there, that’s that 3d reference control. One custom on your keyboard. One semicolon, two three. Just trust me, it’s three, don’t. Do one don’t do to do three? Click, okay. That number that 3d reference is still there. I can see in the formula bar, but that is hidden. Well, it’s nice about this is. I want to know how much interest I’m going to pay. At these different interest rates, so let’s just do equals. B Seven should be increasing, and it does. If I don’t know. The total payment equals B six, so that is called a one input data table. Now let’s do it to input if I do a two input. I usually don’t do the one input. I’m just showing you can do a one if you want. Here’s a two. Let’s do, I’m over in j2 So now it’s not just the interest rates, it’s the interest rates and the finance amount I’m gonna put the interest rates across the top highlight. Both of those autofill 6% works for madam. That is going to be empty. I’m gonna come right here. Actually, that’s that’s run will start so one down one to the left. I’m gonna do 22,000 Because maybe I don’t need a $24,000 car. Maybe I found something for 22,000 and it’s due 22500 I like both of those crosshairs and let’s take it out to 26,000 I’m gonna click and sell. I two equals. I still have to do the 3d reference, So I’m still doing a data table, but I’m not doing a one input. I’m gonna do it -. That’s why I got interest rates and the finance amount. I don’t know what the payment is highlight perfect data, what-if analysis data table so now. I’m going to use both of these boxes going across the row, right here or my interest rates and notice again, it says the word input. That’s when I put this over here to help you just learn how to do this. B 1 column input running down the column. I or the finance amounts. So this is a two input data table when I hit. Okay, perfect. Those are my monthly payments. Those interest rates there. We go what’s really nice about a data table Is you cannot delete just one specific cell youll. Get an error message. I click over here hit. Delete, can’t. Do it and again if I wanted to hide that I’d be control. One was my bad control. One custom, one, two, three, good to go. It’s still there. Hopefully that helps so now you know how to use. Microsoft Excel, Z– data, what-if analysis data table. I’m going to follow up with data. What-if analysis, goal seek and scenario manager this week.