# Thread: How to create a table of assumptions in Excel 2007

1. Member
Join Date
May 2008
Posts
176

## How to create a table of assumptions in Excel 2007

With this trick, we'll show you how to create a table with two entries in Excel.

You want to compare loan offerings which vary in two parameters: the duration of the loan and the interest rate (the principle is, of course, for all types of double-entry table). Excel allows you to create the entire table while writing a single formula.

- Enter the term in the field B3: B7, interest rates in C2: F2, and the amount to be refunded in C9, negative because it is an amount received.

- You will now have two cells (B11 and B12 here) to be used for calculations but where you do not understand. In B2, enter the formula for the repayment of a loan. You will use for this the PMT function. The first argument, the interest rate, is B11/12 (cell B11, which contains the annual interest shall be divided by 12 because we have the monthly interest). The second argument (number of payments) is B12 * 12 or 12 times the number of years of repayment. The formula in B2 is =PMT(B11/12;B12*12;\$C\$9). Do not worry about the result (or error) that appears.

- Now select the field B2: F7 and pull down the Data menu, Table.

- In the field input cell line, type in cells B11 and input column, type B12, and click OK.

Your table is finished. You can hide (but not remove) the cell B2. To do this, right click on it, choose Format Cells, then Protection and select Hidden.

2. Member
Join Date
May 2008
Posts
4,834

## Re: How to create a table of assumptions in Excel 2007

You can hide (but not remove) the cell B2
How can you hide individual cells? I don't think it is possible or rather effective. However you can hide a full column or row. Simply right click the heading (column or row), then click Hide.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•