What-if Tables in Excel
Excel provides a simple process to deveelop tables that
ask, "What if...?" Using functions or equations its relatively
simple to determine a result for a given set of data. The spreadsheet
shown determines the monthly payment for a $75,000 mortgage with an
annual interest rate of 6.9% for 30 years.
You might want to try out a number of possibilities: "What
if the loan amount were $80,000?" "How about $90,000?"
The built-in "Tables" feature is a bit fussy - you have to
set it up just so, or it doesn't work. The values you want to try go
in a single column: here its A10:A17. The formula you want to display
for each value goes one cell above and one to the right of the first
value: in cell B9 I wrote "=B6". I then highlighted the range
A9:B17 as shown. Selecting "Data" and then "Table"
brings up the dialog box shown. Since this is a one-way table - you
only want to change one value, you only need to fill in the "Column
Input cell" - the items in the column should go where? Since we
want to try different values for loan amount, I wrote "B2"
in that box. Pressing "OK" cuased the screen to change as
shown:
If you wanted to find values when two variables change,
you need to lay the table out a bit differently. The figure below shows
a 2-way table - we want a payment amount asking What-if when we change
both the amount and the interest rate:
Note the amount still goeas in a column, now A10:A19, and
the interest rates to try go in the row above: B9:D9. The formula ("=b6")
moves to the upper-lefthand corner of the table: A9. Highlight A9:D19,
select "Data" and "Table". Again, its important that
the data in the row (interest rate) goes in cell B3, while the data in
the column (amount) goes in B2. Press "OK", and the table is
completed:
Note the answer in C10 corresponds to the original input:
$75,000 at 6.9% yields a payment amount of $493.95.
|
