Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 27, Excel introduction page 5
Release the left mouse button and:
1
2
3
4
5
6
7
8
9
10
11
12
13
AB C
Year Bank balance
01000
1 1150 <-- =B3*(1+15%)
2 1322.5 <-- =B4*(1+15%)
3 1520.875 <-- =B5*(1+15%)
4 1749.00625 <-- =B6*(1+15%)
5 2011.357188 <-- =B7*(1+15%)
6 2313.060766 <-- =B8*(1+15%)
7 2660.01988 <-- =B9*(1+15%)
8 3059.022863 <-- =B10*(1+15%)
9 3517.876292 <-- =B11*(1+15%)
10 4045.557736 <-- =B12*(1+15%)
COMPOUND INTEREST
Notice how Excel copied the cell formulas:
• The formula in cell B4 says: “Take the contents of the cell above and multiply by
(1+15%).
• When we drag down the cell formula in B5 says: “Take the contents of the cell above
and multiply by (1+15%).
PFE Chapter 27, Excel introduction page 6
This kind of copying is called relative copying in Excel: The cell formulas change in the
direction of the copy (that is, in the direction which you dragged the cell handle). There’s also
absolute copying, which we’ll explain in Section 27.4 below.
Excel hint
Instead of dragging cell B4, there’s an even simpler way to copy. If you put your cursor
on the handle and double-click with the left mouse button, the formula in cell B4 will be copied
from cell B5 through B13.
Entering formulas by pointing (a better way)
So far we’ve written the formula in cell B4. But it’s usually a better idea to use the
mouse and point at the relevant cells. Pointing and clicking formulas avoids a lot of mistakes. In
the previous example:
Put the cursor on cell B4
Type in “=”
PFE Chapter 27, Excel introduction page 7
With the mouse, point at cell B3
Write in the rest of the formula—*(1+15%).
Click the left mouse button or hit [Enter].
27.2. Formatting the numbers
The spreadsheet we’ve constructed so far is cute but ugly. Why do we need so many
decimal places? Why aren’t there commas in the numbers? How about indicating that these are
dollar amounts?
We can make all these changes by using Excel’s extensive formatting facilities.
PFE Chapter 27, Excel introduction page 8
FORMATTING NUMBERS IN EXCEL
Before: Mark the numbers to be formatted.
Go to Format|Cells|Number on the menu bar
and choose something appropriate.
After: Here’s what we chose:
Here’s how the spreadsheet looks now:
In other chapters we’ll use the Format|Cells command to change the way dates and text
and fonts appear in Excel. The important thing to note about this command is that it changes the
way cell contents appear, but not the actual cell contents. For example, suppose your cell
contents read 3287.65898992; now suppose that you made them look like dollars with a comma
PFE Chapter 27, Excel introduction page 9
and two decimal places, so that the cell reads $3,287.66.” The actual contents of the cell haven’t
changed—there are still eight decimal places, but it only shows two of them.
27.3. Absolute copying—building a more sophisticated model
The spreadsheet of the previous section is cute, but it doesn’t allow us to change the
interest rate at which the money accumulates. We fix this by writing the following spreadsheet;
in this spreadsheet we’ve got a separate cell (B2) to indicate the interest rate. By changing this
cell we’ll change all the accumulations.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AB C
Interest 7%
Year
0 $1,000.00
1
2
3
4
5
6
7
8
9
10
COMPOUND INTEREST
Go to cell B6 . Type the formula “ =B5*(1+$B$2) ” in this cell. The dollar signs on
$B$2 indicate that when we copy this formula, this particular cell reference will not change. In
the jargon of Excel: $B$2 is an absolute reference, whereas B5 is a relative reference—it will
change to B6, B7, … as we go down the column.
PFE Chapter 27, Excel introduction page 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AB C
Interest 7%
Year
0 $1,000.00
1 $1,070.00 <-- =B5*(1+$B$2)
2
3
4
5
6
7
8
9
10
COMPOUND INTEREST
Copying as we did in the previous section (click on B6, put the cursor on the B6 handle
and drag):
The result is a table much like that of the previous section:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AB C
Interest 7%
Year
0 $1,000.00
1 $1,070.00 <-- =B5*(1+$B$2)
2 $1,144.90 <-- =B6*(1+$B$2)
3 $1,225.04 <-- =B7*(1+$B$2)
4 $1,310.80 <-- =B8*(1+$B$2)
5 $1,402.55 <-- =B9*(1+$B$2)
6 $1,500.73 <-- =B10*(1+$B$2)
7 $1,605.78 <-- =B11*(1+$B$2)
8 $1,718.19 <-- =B12*(1+$B$2)
9 $1,838.46 <-- =B13*(1+$B$2)
10 $1,967.15 <-- =B14*(1+$B$2)
COMPOUND INTEREST
PFE Chapter 27, Excel introduction page 11
(We’ve formatted the numbers as currency.)
The difference between this spreadsheet and the previous one is that we can change the
interest rate simply by changing the contents of cell B2. In this example the interest rate is 10%:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AB C
Interest 10%
Year
0 $1,000.00
1 $1,100.00 <-- =B5*(1+$B$2)
2 $1,210.00 <-- =B6*(1+$B$2)
3 $1,331.00 <-- =B7*(1+$B$2)
4 $1,464.10 <-- =B8*(1+$B$2)
5 $1,610.51 <-- =B9*(1+$B$2)
6 $1,771.56 <-- =B10*(1+$B$2)
7 $1,948.72 <-- =B11*(1+$B$2)
8 $2,143.59 <-- =B12*(1+$B$2)
9 $2,357.95 <-- =B13*(1+$B$2)
10 $2,593.74 <-- =B14*(1+$B$2)
COMPOUND INTEREST
Excel hint
Never use a number if you can use a cell reference! Compare the previous example with
this one: If, as in the previous section, you “hard-wire” the 15% interest rate in cells B6:B15,
you have to change each of these cells in order to change the interest rate assumption. On the
other hand, if you put the interest rate in a cell (as in this section’s example), you need only
change the contents of that cell in order to recalculate the whole spreadsheet.
In Excel, numbers are always inferior to formulas!
Pointing and using the F4 key
Let’s go back to the stage in this example where we were putting the formula
“=B5*(1+$B$2)” into cell B5. We’ve already suggested that it’s better to enter formulas by
pointing and clicking than by typing. Now we’ll teach you another little trick, the use of the F4
PFE Chapter 27, Excel introduction page 12
key to “dollarize” cell references—that is, to make them absolute references instead of relative
references. Here’s what you do:
• Put the cursor in cell B6. Type “=”.
Now point at cell B5, the one that contains $1,000.00. You can point with either the mouse
(clicking when you’re on B5), or you can point with the arrow keys.
• Now type a star, the opening of a parenthesis, and a 1, and a + : *(1+ . Then point at
cell B2 containing the interest rate:
• Next hit function key F4. This puts the dollar signs into the cell reference B2 in cell B6.
PFE Chapter 27, Excel introduction page 13
• Finally, close the parentheses by typing a “)”. Hit [Enter].
• Copy cell B6 as before
Correcting errors—editing the cell
Suppose you made a mistake and forgot to “dollarize” the B2 cell reference, so that the
contents of cell B6 are “=B5*(1+B2).” This isn’t good—the cell contents should read
“=B5*(1+$B$2).” To make the appropriate change, we edit the formula in cell B6 and we use
the F4 key:
• Put the cursor on B6 and click the left mouse key twice. This opens the formula for
editing.
• Move the cursor until it’s somewhere on the B2 in the formula (it doesn’t matter where).
Hit the F4 key and your cell reference will be “dollarized.”
PFE Chapter 27, Excel introduction page 14
• Now hit [Enter] and copy as before.