Data Tables in Excel

Data Tables in Excel - BMGT 301 Spring 2011 Dr. Z. karake...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
BMGT 301 – Spring 2011 Dr. Z. karake BMGT 301 What If Analysis -- Data Tables in Excel In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Assume you want to buy a car for $30,000; you will pay 20 percent as a down payment and finance the rest over five years (60 months). The interest rate offered by the dealer is 12 percent, but you know that banks outside might be offering better rates. By asking Excel to alter the cell containing the interest input, we can quickly see the different monthly payments from the various financing options. Given that other banks may be offering better deals, you will ask Excel to calculate how much you would pay each month if the interest rate was 8 percent a year, 9 percent a year, 10 percent and 11 percent a year. One-Variable Data Tables The formula you are going to use is the Payment: PMT( rate, nper, pv, fv, type ) We only need the first three arguments. So for us, it's just this: PMT( rate, nper, pv ) Rate means the interest rate. The second argument, nper , is how many months you've got to pay the loan back. The third argument, pv , is how much you want to borrow. Let's make a start then. On a new spreadsheet, set up the following labels: So we'll put our starting interest rate in cell B3 ( rate ), our loan length in cell B4 ( nper ), and our loan amount in cell B5 ( pv ). Enter the following in cells B3, B4 and B5 respectively: 12%, 60, $24000. Enter the following in Cell C2: Monthly Payment We'll enter our formula now. Click inside cell D2 and enter the following: =PMT( B3 / 12, B4, -B5 ) Cell B3 is the interest rate. But this is for the entire year. To find the monthly rate, in the formula, divide cell B3 by 12. Cell B4 in the formula is the number of months, which is 60 for us. B5 has a minus sign before it. It's a minus figure because it's a debt.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
BMGT 301 – Spring 2011 Dr. Z. karake When you press the enter key on your keyboard, Excel should give you an answer of $533.87. Having the PMT function in place, you can create an Excel Data Table. Do the
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 03/02/2011 for the course BMGT 301 taught by Professor Wang during the Spring '08 term at Maryland.

Page1 / 7

Data Tables in Excel - BMGT 301 Spring 2011 Dr. Z. karake...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online