Data Table Calculationsĭata Tables are recalculated each time the worksheet containing them is recalculated, even if they have not changed. This means the tenure of the loan would be 24 years. If you can pay an EMI of 54,000, the interest rate of 12.2% and 288 EMIs are suitable for you. The Data Table gets filled with the calculated results for each combination of the two input values − You will see that the Row input cell is taken as $C$3 and the Column input cell is taken as $C$2.
Name the cells containing the values, so that the formula will have names instead of cell references. Analysis with Two-variable Data TableĪnalysis with two-variable Data Table needs to be done in three steps − You want to know how different combinations of interest rates and loan tenures will affect the monthly payment (EMI). You will understand this with the help of an example. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. Two-variable Data TablesĪ two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. If you can pay an EMI of 54,000, you can observe that the interest rate of 12.6% is suitable for you. The Data Table is filled with the calculated results for each of the input values as shown below − You can see that the Column input cell is taken as $C$2. Click the cell Interest_Rate, which is C2.Click the icon in the Column input cell box.select the range – E2:H13.Ĭlick What-if Analysis in the Data Tools group. Select the range of cells that contains the formulas and values that you want to substitute, i.e. The Data Table looks as given below − Step 3: Do the analysis with the What-If Analysis Data Table Tool Now, the two rows above the Interest Rate values look as follows − Type the other functions ( CUMIPMT and CUMPRINC) in the cells to the right of the first function. Type the first function ( PMT) in the cell one row above and one cell to the right of the column of values. This row is for the formulas that you want to use. interest rates that you want to substitute in the input cell down the column E as follows −Īs you observe, there is an empty row above the Interest Rate values. You can see that the cells in column C are named as given in the corresponding cells in column D. Set the calculations for EMI, Cumulative Interest and Cumulative Principal with the Excel functions – PMT, CUMIPMT and CUMPRINC respectively. Name the cells containing the values, so that the formulas will have names instead of cell references. Let us understand these steps in detail − Step 1: Set the required background
Analysis with One-variable Data TableĪnalysis with one-variable Data Table needs to be done in three steps − You also might be interested in knowing the amount of interest and Principal that is paid in the second year. You want to know the monthly payments (EMI) for varied interest rates. There is a loan of 5,000,000 for a tenure of 30 years. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. One-variable Data TablesĪ one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For details, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial. If you have more than two variables in your analysis problem, you need to use Scenario Manager Tool of Excel. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem. With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis.
0 kommentar(er)
