From the course: Excel 2013 Essential Training

Unlock the full course today

Join today to access over 22,500 courses taught by industry experts or purchase this course individually.

Using Data Tables

Using Data Tables - Microsoft Excel Tutorial

From the course: Excel 2013 Essential Training

Start my 1-month free trial

Using Data Tables

“

In this worksheet called DataTable row 3 contains different interest amounts and they're incremented by an eight of a percent. The formulas in all of these cells except for cell C3, and in column B we've got amounts. Maybe we're considering borrowing money, and these are incremented by 25,000. And what we'd like to create here is ultimately a grid containing the monthly payments with these different interest rates and amounts being borrowed. In cells B1 and B2, we have got 3% and 200,000. Let's put in a formula here in B3. We're going to be using the Data Table feature. It's found on the Data tab under What-if Analysis. Let's first set up a formula in cell B3. Using the function PMT, this allows us to calculate the monthly payment based on an interest rate, in this case 3%. We want to divide this by 12 since we're paying our interest monthly, the term here 360 months. We'll show you later how we might want to change that. We're setting up here what might be called a master formula…

Contents