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.