It won’t work if you manually enter the value in cell E1. Also, make sure that the value in cell E1 is dependent on Number of Monthly Payments. This is a construct that needs to be followed when you work with one variable data table in Excel.

Type =B4 in cell E1, which is one row above the values in the adjacent column to the right.

In this example, we are testing for 72, 84, 96… 240.

In a column, have all the different values of Number of Monthly Payments that you want to test.

Here are the steps to set up a One variable data table in Excel: Setting Up a One Variable Data Table in Excel However, if you want to test if for many different Number of Monthly Payments, then a One Variable Data Table in Excel would be the way to go. For example, you may want to keep the tenure of your loan (number of monthly payments) such that your Monthly Payment amount is not more than $1,000.Ī quick way is to change the data in this table and the formula would automatically update. Now you may want to do an analysis to see what number of monthly payments suits your condition, and how would the monthly payment vary based on it. The following formula is used to calculate the Monthly Payment: =PMT(B2/12,B3,B1) The Monthly Payment value is calculated based on these three variables (it is in red as it is an outflow of money). In the above data set, we have the Loan Amount, Interest Rate, and Number of Monthly payments. Suppose you have a data set as shown below:

When to use One Variable Data Table in Excel If you want to change two variables, use two variable data table, or Scenario Manager.

One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables. Watch Video – One-Variable Data Table in Excel Other articles in the Data Analysis series:

Irr to work in excel for mac