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.
Reply With Quote
Bookmarks