How to Create a Two-Variable Data Table in Excel
Learn: The Quick & easy way on How to Create a Two-Variable Data Table in Excel
In this video, you will learn:
1. How to Create a Two-Variable Data Table in Excel.
2. How to use the PMT function in excel
In this video, I will show you HOW TO CREATE A TWO VARIABLE DATA TABLE IN EXCEL. Here I have some data to calculate the monthly loan repayment value. I have the loan amount, interest rate, and the number of monthly payments and to calculate the monthly payment, I will use the formula PMT function to that.
So first. I'm going to go over and click on cell C5 because I want the output result there and I'm going to create a simple little calculation here that will find out the current payment using these three (3) arguments here with help of built-in tools called Payment Function or PMT. So, I'm going to go to my formulas tab. I'm going to go into financial and I'll find PMT, which is called Payment function. Let me give it a click. It takes a handful of arguments from us to be filled in into the boxes, but I'm only going to use three of them, the three required ones.
The first one is the RATE, which is the PERCENT, this is in cell C3. Basically, an Interest Rate is an APR, i.e. Annual Percentage Rate. So, I'm going to divide that by 12. I'll say C3 divided by 12.
The second one is the NPer, which is the Number of Payments. So, I'm going to grab the No of Monthly Payment value, which is 340.
The third is the PV, which is the Present Value, which is $400,000. That is all. Now I'm going to go ahead and hit. Okay.
Roughly $1,968.21 is what I am going to be paying monthly for a $400,000 loan at 4% for the next 340 months.
As the monthly payment since this is an outflow. This is in red and in brackets. Now, if I want to identify the combination of the number of monthly payments and the loan amount, which would keep my monthly payment below $500. So I do not want to be more than$ 500 and I want to get the maximum possible loan and I want to know what would be the number of monthly payments. So to do that, I can use the two-variable data table. I would have to create a data table for it. So here I would first have to mention the two variables which are the number of monthly payments and the loan amount. Then I would have the number of monthly payments here in the column. So as of now, I have 340 months, I would want to check it for 352 months, 364 months, and so on. So this is 29 years, 30 years, and I would go up to 496 months which is 41 years.
Similarly, I would want to check for various loan amounts, so I would use $100,000 here, $150,000 here and I would go up to $400,000. I have intentionally left this cell E2 empty and I will show you how to create and construct that cell. In this cell, you need to refer to this cell's monthly payments value. And the idea is that this cell should be dependent on more than two variables that we have taken which are the number of monthly payments and the loan amount. If you manually type this value here, it would not work. It would have directly or indirectly made a bit dependent on these two cells. So I could manually either refer it here to the cell by pressing = in cell E2 and clicking on cell C5 and Press Enter Key or I could use the PMT function directly in this cell. Now that I have this value ready in cell E2, I can choose the variable data table. So I would select all the cells. I would go to DATA, WHAT-IF ANALYSIS and here I have the DATA TABLE option. When I click on it, it opens the Data Table dialog box. And here I have to give two inputs which are the row input cell and column input. The row input cell would be the variable which is varying across the row.
……………………………………………………………………………………………………………………….
Share this video: • How to Create a Two-Variable Data Tab...
For more simple and easy to follow How-to videos,
Click the Link Below to subscribe:
/ @learnexcelwitht
Join this channel to get access to perks:
/ @learnexcelwitht
#datatable #two-variable data table #What-if-analysis
LearnexcelwithT
Watch video How to Create a Two-Variable Data Table in Excel online without registration, duration hours minute second in high quality. This video was added by user LearnexcelwithT 01 January 1970, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,19 once and liked it 74 people.