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.
Re: How to create a table of assumptions in Excel 2007
|Tags: excel 2007, microsoft excel, office 2007, table|
|Thread Tools||Search this Thread|
|Similar Threads for: "How to create a table of assumptions in Excel 2007"|
|Thread||Thread Starter||Forum||Replies||Last Post|
|Need help to create cross Reference table/Matrix in Microsoft Excel||Eas!war||MS Office Support||2||15-02-2012 05:01 PM|
|How to create table and Modify tables in Microsoft Access 2007||Antonio1||Windows Software||7||27-11-2009 12:31 AM|
|Create html table from excel file||Laler||Windows Software||3||26-06-2009 09:44 AM|
|Pivot Table in Excel 2007||NIcaBoy||Windows Software||2||02-03-2009 03:16 PM|
|Importing HTML table in a Spreadsheet - Excel 2007||Aarif||Tips & Tweaks||1||01-12-2008 02:35 PM|