Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 1, Time value of money page 50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ABCDE
Loan principal 100,000
Loan interest 10%
Years to pay off loan 10
Annual payment 16,274.54 <-- =B2/PV(B3,B4,-1)
16,274.54 <-- =PMT(B3,B4,-B2)
Year
Principal at
beginning of
year Payment at end year
Part of
payment
which is
interest
Part of
payment
which is
principal
1 100,000.00 16,274.54 10,000.00 6,274.54
2 93,725.46 16,274.54 9,372.55 6,901.99
3 86,823.47 16,274.54 8,682.35 7,592.19
4 79,231.27 16,274.54 7,923.13 8,351.41
5 70,879.86 16,274.54 7,087.99 9,186.55
6 61,693.31 16,274.54 6,169.33 10,105.21
7 51,588.10 16,274.54 5,158.81 11,115.73
8 40,472.37 16,274.54 4,047.24 12,227.30
9 28,245.07 16,274.54 2,824.51 13,450.03
10 14,795.04 16,274.54 1,479.50 14,795.04
LOAN PAYMENT
Nomenclature
The table in rows 9-18 above is often called a loan amortization table (“amortize”: to
pay something off over time).
Here are two things to notice about the computation of this table:
•
Excel has a function, PMT, which does this calculation directly (cell B6).
PFE Chapter 1, Time value of money page 51
Dialog box for PMT function
Like some other Excel financial functions,
PMT generates positive answers for negative entries
in the
Pv box.
•
When we put all the payments in a loan table (rows 9-18 of the above spreadsheet) you
can see the split of each end-year payment between interest on the outstanding principal
at the beginning of the year and repayment of principal. If you were reporting to the
Internal Revenue Service, the interest column (column D) is deductible for tax purposes;
the repayment of principal column (column E) is not.
1.10. How long will it take to pay off a loan?
You’re getting a $1,000 loan from the bank at 10% interest. The maximum payment you
can make is $250 per year. How long will it take you to pay off the loan? There’s an Excel
PFE Chapter 1, Time value of money page 52
function that answers this question, which we’ll show you in a bit. But first let’s do this the long
way so we can understand the question. In the spreadsheet below we look at a loan table like the
ones considered in section 1.5:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ABCDE
Loan amount 1,000
Interest rate 10%
Annual payment 250
Year
Principal
beginning
of year
Payment
at end of
year
Interest
Return of
principal
1 1,000.00 250.00 100.00 150.00
2 850.00 250.00 85.00 165.00
3 685.00 250.00 68.50 181.50
4 503.50 250.00 50.35 199.65
5 303.85 250.00 30.39 219.62
6 84.24 250.00 8.42 241.58
Excel's
NPER
function
5.3596 <-- =NPER(B3,B4,-B2)
HOW LONG TO PAY OFF THIS LOAN?
Year 6 is the first year in which the
return of principal at the end of the year
is > principal at the beginning of the
year. Meaning--sometime during year
6 you will have paid off the loan.
As you can see from row 12, year 6 is the first year in which the return of principal at the
end of the year is bigger than the principal at the beginning of the year. Thus, sometime between
5 and 6 years you pay off the loan.
Excel’s
NPER function, illustrated in cell B24, provides an exact answer to this question:
PFE Chapter 1, Time value of money page 53
Dialog box for NPER function
Like the functions
PMT, PV, and FV discussed elsewhere in this chapter, the NPER
function requires you to make the amount owed negative in order to get a positive answer.
1.11. An Excel note—building good financial models
If you’ve gotten this far in Chapter 1, you’ve probably put together a few basic Excel
spreadsheets. You’ll be doing lots more in the rest of this book, and you’ll be amazed at the
insights Excel gives you over even complicated financial problems.
We’ve chosen this place in the chapter to tell you a bit about financial modeling (that’s
what you’ve been doing … ).
Here are three important rules for good Excel modeling:
PFE Chapter 1, Time value of money page 54
• Put all the variables which are important (the fashionable jargon is “value drivers”) at the
top of your spreadsheet. In the “Saving for College” spreadsheet of page000, the three
value drivers—the interest rate, the annual deposit, and the annual cost of college—are in
the top left-hand corner of the spreadsheet:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ABCDE
Interest rate 8%
Annual deposit 6,227.78
Annual cost of college 20,000
Birthday
In bank on birthday,
before
deposit/withdrawal
Deposit
or withdrawal
at begin. of year
Total
End of year
with interest
10 0.00 6,227.78 6,227.78 6,726.00
11 6,726.00 6,227.78 12,953.77 13,990.08
12 13,990.08 6,227.78 20,217.85 21,835.28
13 21,835.28 6,227.78 28,063.06 30,308.10
14 30,308.10 6,227.78 36,535.88 39,458.75
15 39,458.75 6,227.78 45,686.52 49,341.45
16 49,341.45 6,227.78 55,569.22 60,014.76
17 60,014.76 6,227.78 66,242.54 71,541.94
18 71,541.94 -20,000.00 51,541.94 55,665.29
19 55,665.29 -20,000.00 35,665.29 38,518.52
20 38,518.52 -20,000.00 18,518.52 20,000.00
21 20,000.00 -20,000.00 0.00 0.00
NPV of all payments 0.0000 <-- =NPV(B2,C8:C18)+C7
SAVING FOR COLLEGE
•
Never use a number where a formula will also work. Using formulas instead of “hard-
wiring” numbers means that when you change a parameter value, the rest of the
spreadsheet changes appropriately. As an example—cell C20 in the above spreadsheet
contains the formula
=NPV(B2,C8:C18)+C7. We could have written this as
=NPV(8%,C8:C18)+C7. But this means that changing the entry in cell B2 won’t go
through the whole model.
•
Avoid the use of blank columns to accommodate cell “spillovers.” Here’s an example of
a potentially bad model:
1
ABC
Interest rate 6%
PFE Chapter 1, Time value of money page 55
Because “Interest rate” has spilled over to column B, the author of this spreadsheet has
decided to put the “6%” in column C. He’s going to end up getting confused (don’t ask
why ... ); he should have made column A wider and put the 6% in column B:
1
AB
Interest rate 6%
An Excel Note: Making a Column Wider
Widening the column is simple: Put the cursor on the break between columns A and B:
Clicking the left mouse button will expand the column to accommodate the widest cell. You can
also “stretch” the column by holding the left mouse button down and moving the column width
to the right.
Summing up
In this chapter we have covered the basic concepts of the time value of money:
•
Future value (FV): The amount you will accumulate at some future date from deposits
made in the present.
•
Present value (PV): The value today of future anticipated cash flows.
•
Net present value (NPV): The value today of a series of future cash flows, including the
cost of acquiring these cash flows.
PFE Chapter 1, Time value of money page 56
• We’ve gone to great pains to point out the difference between the finance concept of net
present value (NPV) and the Excel
NPV function. The Excel NPV function calculates
the present value of the future cash flows, whereas the finance concept of NPV computes
the present value of the future cash flows minus the initial cash flow.
•
Internal rate of return (IRR): The compound interest rate paid by a series of cash flows,
including the cost of their acquisition.
We have also showed you the Excel functions (
FV, PV, NPV, IRR) which do these
calculations and discussed some of their peculiarities. Finally, we have showed you how to do
these calculations using formulas.
PFE Chapter 1, Time value of money page 57
Exercises
1. You just put $600 in the bank and you intend to leave it there for 10 years. If the bank pays
you 15% interest per year, how much will you have at the end of 10 years?
2. Your generous grandmother has just announced that she’s opened a savings account for you
with a deposit of $10,000. Moreover, she intends to make you 9 more similar gifts, at the end of
this year, next year, etc. If the savings account pays 8% interest, how much will you have
accumulated at the end of 10 years (one year after the last gift)?
Suggestion: Do this problem 2 ways, as shown below: a) take each amount and
calculate its future value in year 10 (as illustrated in cells C7:C16) and then sum them; b) use
Excel’s
FV function, noting that here the amounts come at the beginning of the year (you’ll need
to enter “1” in the
Type option as described in Section 1).
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ABC D
Interest rate 8.00%
Year Gift
Future value
in year 10
0 10,000 21,589.25 <-- =B7*(1+$B$3)^(10-A7)
1 10,000
2 10,000
3 10,000
4 10,000
5 10,000
6 10,000
7 10,000
8 10,000
9 10,000
Total (summing C7:C16)
Using FV function
PFE Chapter 1, Time value of money page 58
3. Your uncle has just announced that he’s going to give you $10,000 per year at the end of each
of the next 4 years (he’s less generous than your grandmother ... ). If the relevant interest rate is
7%, what’s the value today of this promise? (If you’re going to use
PV to do this problem note
that the
Type option is 0 or omitted.)
4. What is the present value of a series of 4 payments, each $1,000, to be made at the end of
years 1, 2, 3, 4? Assume that the interest rate is 14%.
Suggestion: Do this problem 2 ways, as shown in rows 11 and 12 below.
3
4
5
6
7
8
9
10
11
12
ABCDE
Interest rate 14%
Year Payment PV
1 1,000 877.19 <-- =B6/(1+$B$3)^A6
2 1,000
3 1,000
4 1,000
Total of C6:C9
Using NPV function
5. Screw-‘Em-Good Corp. has just announced a revolutionary security: If you pay SEG $1,000
now, you will get back $150 at the end of each of the next 15 years. What is the IRR of this
investment?
Suggestion: Do this problem two ways—once using Excel’s
IRR function and once
using Excel’s
RATE function (illustrated below).
PFE Chapter 1, Time value of money page 59
6. Make-‘Em-Happy Corp. (MEH) has a different security for sale: You pay MEH $1,000 today
and the company will give you back $100 at the end of the first year, $200 at the end of year 2, ...
, $1000 at the end of year 10.
a. Calculate the IRR of this investment.
b. Show an amortization table for the investment.
7. You are thinking about buying a $1,000 bond issued by the Appalachian Development
Authority (ADA). The bond will pay $120 interest at the end of each of the next 5 years. At the
end of year 6, the bond will pay $1,120 (this is its face value of $1,000 plus the interest). If the
relevant discount rate is 7%, how much is the present value of the bond’s future payments?
8. Look at the pension problem in Section 1.8, page000. Answer the following questions:
8.a. What if the desired annual pension is $100,000? How much does a 55 year-old have
to save annually? The CD-ROM which accompanies the book contains the following
template: