Simon Benninga. Financial Modelling 3-rd edition
Подождите немного. Документ загружается.
28 Chapter 1
1
2
3
4
5
6
7
8
9
10
11
AB C D
Interest 8%
Year Cash flow
Continously
discounted
PV
1 100 92.312 <-- =B5*EXP(-$B$2*A5)
2 200 170.429 <-- =B6*EXP(-$B$2*A6)
3 300 235.988
4 400 290.460
5 500 335.160
Present value 1,124.348 <-- =SUM(C5:C9)
CONTINUOUS DISCOUNTING
1.8.3 Calculating the Continuously Compounded Return from Price Data
Suppose at time 0 you had $1,000 in the bank and suppose that one year
later you had $1,200. What was your percentage return? Although the
answer may appear obvious, it actually depends on the compounding
method. If the bank paid interest only once a year, then the return would
be 20 percent:
1 200
1 000
120
,
,
%−=
However, if the bank paid interest twice a year, you would need to
solve the following equation to calculate the return:
1 000 1
2
1 200
2
1 200
1 000
1 9 5445
2
12
,,
,
,
%∗+
⎛
⎝
⎜
⎞
⎠
⎟
=⇒=
⎛
⎝
⎜
⎞
⎠
⎟
−=
rr
/
.
The annual percentage return when interest is paid twice a year is there-
fore 2
*
9.5445% = 19.089%.
In general, if there are n compounding periods per year, you have to
solve
r
n
n
=
⎛
⎝
⎜
⎞
⎠
⎟
−
1 200
1 000
1
1
,
,
/
and then multiply the result appropriately. If n
is very large, this converges to
r =
⎛
⎝
⎜
⎞
⎠
⎟
=ln .
1 200
1 000
18 2322
,
,
%
:
29 Basic Financial Calculations
1.8.4 Why Use Continuous Compounding?
All this may seem somewhat esoteric. However, continuous compound-
ing/discounting is often used in fi nancial calculations. In this book, it is
used to calculate portfolio returns (Chapters 8–15) and in practically all
of the options calculations (Chapters 16–24).
There’s another reason to use continuous compounding—its ease of
calculation. Suppose, for example, that your $1,000 grew to $1,500 in one
year and nine months. What’s the annualized rate of return? The easiest—
and most consistent—way to fi nd this answer is to calculate the continu-
ously compounded annual return. Since 1 year and 9 months equals 1.75
years, this return is
1 000 1 75 1 500
1
175
1 500
1 000
23 1694,,
,
,
%∗∗=⇒=
⎡
⎣
⎢
⎤
⎦
⎥
=exp[ . ]
.
ln .rr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ABC
Initial deposit 1,000
End-of-year value 1,200
Number of compounding periods 2
Implied annual interest rate 19.09% <-- =((B3/B2)^(1/B4)-1)*B4
Continuous return 18.23% <-- =LN(B3/B2)
Number of compounding periods Rate
19.09% <-- =B5, data table header
1 20.00%
2 19.09%
4 18.65%
8 18.44%
20 18.32%
1,000 18.23%
CALCULATING RETURNS FROM PRICES
Implied annual interest rate with n compounding periods
30 Chapter 1
1.9 Discounting Using Dated Cash Flows
Most of the computations in this chapter consider cash fl ows that occur
at fi xed periodic intervals. Typically we look at cash fl ows that occur on
dates 0, 1, . . . , n, where the period indicates an annual, semiannual, or
other fi xed interval. Two Excel functions, XIRR and XNPV, allow us to
do computations on cash fl ows which occur on specifi c dates that need
not be at even intervals.
7
In the following example we compute the IRR of an investment of
$1,000 made on 1 January 2006 with payments on specifi c dates:
1
2
3
4
5
6
7
8
9
AB C
Date Cash flow
1-Jan-06 -1,000
3-Mar-06 150
4-Jul-06 100
12-Oct-06 50
25-Dec-06 1,000
IRR 37.19% <-- =XIRR(B3:B7,A3:A7)
USING XIRR TO COMPUTE THE
ANNUALIZED INTERNAL RATE OF
RETURN
The function XIRR outputs an annualized return. It works by comput-
ing the daily IRR and annualizing it, XIRR = (1 + DailyIRR)
365
− 1.
XNPV computes the net present value of a series of cash fl ows occur-
ring on specifi c dates.
7. If you do not see these functions, add them in by going to Tools|Add-ins on the tool
bar and checking Analysis ToolPak.
31 Basic Financial Calculations
1
2
3
4
5
6
7
8
9
10
11
12
13
AB C
Annual discount rate 12%
Date Cash flow
1-Jan-06 -1,000
3-Mar-07 100
4-Jul-07 195
12-Oct-08 350
25-Dec-09 800
Net present value 16.80 <-- =XNPV(B2,B5:B9,A5:A9)
USING XNPV TO COMPUTE THE NET
PRESENT VALUE
Note that
XNPV
has a different syntax from
NPV
!
XNPV
requires all the cash flows, including the initial cash flow,
whereas
NPV
assumes that the first cash flow occurs one period
hence.
Exercises
1. You are offered an asset costing $600 that has cash fl ows of $100 at the end of each
of the next 10 years.
a. If the appropriate discount rate for the asset is 8 percent, should you purchase
it?
b. What is the IRR of the asset?
2. You just took a $10,000, fi ve-year loan. Payments at the end of each year are fl at
(equal in every year) at an interest rate of 15 percent. Calculate the appropriate
loan table, showing the breakdown in each year between principal and interest.
3. You are offered an investment with the following conditions:
• The cost of the investment is $1,000.
• The investment pays out a sum X at the end of the fi rst year; this payout grows
at the rate of 10 percent per year for 11 years.
If your discount rate is 15 percent, calculate the smallest X that would entice you
to purchase the asset. For example, as you can see in the following display, X = $100
is too small—the NPV is negative.
32 Chapter 1
4. The following cash-fl ow pattern has two IRRs. Use Excel to draw a graph of the
NPV of these cash fl ows as a function of the discount rate. Then use the IRR func-
tion to identify the two IRRs. Would you invest in this project if the opportunity
cost were 20 percent?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
AB C
Discount rate 15%
Initial payment 129.2852
NPV -226.52 <-- =B6+NPV(B1,B7:B17)
Year Cash flow
0 -1,000.00
1
100.00 <-- 100
2 110.00 <-- =B7*1.1
3 121.00 <-- =B8*1.1
4 133.10
5 146.41
6 161.05
7 177.16
8 194.87
9 214.36
10 235.79
11 259.37
4
5
6
7
8
9
10
AB
Year Cash flow
0 -500
1600
2300
3300
4200
5-1,000
5. In this exercise we solve iteratively for the internal rate of return. Consider an
investment that costs 800 and has cash fl ows of 300, 200, 150, 122, 133 in years 1–5
(see cells A8:B13 in the following spreadsheet). Setting up the loan table shows that
10 percent is greater than the IRR (since the return of principal at the end of year
5 is less than the principal at the beginning of the year).
33 Basic Financial Calculations
1
2
3
4
5
6
7
8
9
ABCD E FGH
IRR?
10.00%
LOAN TABLE
Year Cash flow Year
Principal
at beginning
of year
Payment
at end of
year
Interest Principal
0 -800 1 800.00 300.00 80.00 220.00
1 300 2 580.00 200.00 58.00 142.00
2 200 3 438.00 150.00 43.80 106.20
3 150 4 331.80 122.00 33.18 88.82
4 122 5 242.98 133.00 24.30 108.70
5 133 6 134.28 <-- Should be zero for IRR
Division of payment
between:
Setting the IRR? cell equal to 3 percent shows that 3 percent is less than the IRR,
since the return of principal at the end of year 5 is greater than the principal at the
beginning of year 5:
1
2
3
4
5
6
7
8
9
ABCD E FGH
IRR?
3.00%
LOAN TABLE
Year Cash flow Year
Principal
at beginning
of year
Payment
at end of
year
Interest Principal
0 -800 1 800.00 300.00 24.00 276.00
1 300 2 524.00 200.00 15.72 184.28
2 200 3 339.72 150.00 10.19 139.81
3 150 4 199.91 122.00 6.00 116.00
4 122 5 83.91 133.00 2.52 130.48
5 133 6 -46.57 <-- Should be zero for IRR
Division of payment
between:
By changing the IRR? cell, fi nd the internal rate of return of the investment.
6. An alternative defi nition of the IRR is the rate that makes the principal at the
beginning of year 6 equal to zero.
9
In the preceding printout cell E9 gives the prin-
cipal at the beginning of year 6. Using the Goal Seek function of Excel, fi nd
the rate that changes this fi gure to zero (the following picture shows how the screen
should look).
9. In general, of course, the IRR is the rate of return that makes the principal in the year
following the last payment equal to zero.
34 Chapter 1
(Of course you should check your calculations by using the Excel IRR function.)
7. Calculate the fl at annual payment required to pay off a fi ve-year loan of $100,000
bearing an interest rate of 13 percent.
8. You have just taken a car loan of $15,000. The loan is for 48 months at an annual
interest rate of 15 percent (which the bank translates to a monthly rate of 15%/12
= 1.25%). The 48 payments (to be made at the end of each of the next 48 months)
are all equal.
a. Calculate the monthly payment on the loan.
b. In a loan table calculate, for each month, the principal remaining on the loan at
the beginning of the month and the split of that month’s payment between inter-
est and repayment of principal.
c. Show that the principal at the beginning of each month is the present value of
the remaining loan payments at the loan interest rate (use the PV function).
9. You are considering buying a car from a local auto dealer. The dealer offers you
one of two payment options:
• You can pay $30,000 cash.
• The “deferred payment plan”: You can pay the dealer $5,000 cash today and a
payment of $1,050 at the end of each of the next 30 months.
As an alternative to the dealer fi nancing, you have approached a local bank, which
is willing to give you a car loan of $25,000 at the rate of 1.25 percent per month.
a. Assuming that 1.25 percent is the opportunity cost, calculate the present value
of all the payments on the dealer’s deferred payment plan.
b. What is the effective interest rate being charged by the dealer? Do this calcula-
tion by preparing a spreadsheet like the one that follows (only part of the
spreadsheet is shown—you have to do this calculation for all 30 months).
35 Basic Financial Calculations
Now calculate the IRR of the numbers in column F; this is the monthly effective
interest rate on the deferred payment plan.
10. You are considering a savings plan that calls for a deposit of $15,000 at the end of
each of the next fi ve years. If the plan offers an interest rate of 10 percent, how
much will you accumulate at the end of year 5?
Do this calculation by completing the following spreadsheet. This spreadsheet does
the calculation twice—once using the FV function and once using a simple table
that shows the accumulation at the beginning of each year.
2
3
4
5
6
7
8
9
10
11
DE FG
Month Cash payment
Payment
under
deferred
payment plan
Difference
0 30,000 5,000 25,000 <-- =E3-F3
1 0 1,050 -1,050 <-- =E4-F4
2 0 1,050 -1,050
3 0 1,050 -1,050
H
0 1,050 -1,050
0 1,050 -1,050
0 1,050 -1,050
7 0 1,050 -1,050
8 0 1,050 -1,050
4
5
6
1
2
3
4
5
6
7
8
9
10
11
12
ABC
Annual payment 15,000
Interest rate 10%
Number of years 5
Total va
D
$91,576.50 <-- =FV(B2,B3,-B1,,0)
Year
lue
Accumulation
at begining of
year
Payment at
end of year
Annual
interest
1 0 15,000 0.00
2 15,000 15,000 1,500.00
3 31,500
4
5
6
36 Chapter 1
11. Redo the previous calculation, this time assuming that you make fi ve deposits at
the beginning of this year and the following four years. How much will you accu-
mulate by the end of year 5?
12. A mutual fund has been advertising that, had you deposited $250 per month in the
fund for the last 10 years, you would now have accumulated $85,000. Assuming that
these deposits were made at the beginning of each month for a period of 120 months,
calculate the effective annual return fund investors got.
Hint: Set up the following spreadsheet and then use Goal Seek.
1
2
3
4
5
BA
Monthly payment 250
Number of months 120
Effective monthly return?
Accumulation <-- =FV(B4,B2,-B1,,1)
C
The effective annual return can then be calculated in one of two ways:
• (1 + Monthly return)
12
− 1: This is the compound annual return, which is prefera-
ble, since it makes allowance for the reinvestment of each month’s earnings.
• 12*Monthly return: This method is often used by banks.
13. You have just turned 35, and you intend to start saving for your retirement. Once
you retire in 30 years (when you turn 65), you would like to have an income of
$100,000 per year for the next 20 years. Calculate how much you would have to save
between now and age 65 in order to fi nance your retirement income. Make the fol-
lowing assumptions:
• All savings draw compound interest of 10 percent per year.
• You make the fi rst payment today and the last payment on the day you turn 64
(30 payments).
• You make the fi rst withdrawal when you turn 65 and the last withdrawal when
you turn 84 (20 payments).
14. You currently have $25,000 in the bank, in a savings account that draws 5 percent
interest. Your business needs $25,000, and you are considering two options: (a) Use
the money in your savings account or (b) borrow the money from the bank at
6 percent, leaving the money in the savings account.
Your fi nancial analyst suggests that solution (b) is better. His logic: The sum of the
interest paid on the 6 percent loan is lower than the interest earned at the same
time on the $25,000 deposit. His calculations are illustrated in the following spread-
sheet. Show that this logic is wrong. (If you think about it, it couldn’t be preferable
to take a 6 percent loan when you are getting 5 percent interest from the bank.
However, the explanation may not be trivial.)
37 Basic Financial Calculations
15. Use XIRR to compute the internal rate of return for the following investment:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EDCBA
F
Interest earned 5%
Interest paid 6%
Initial deposit 25,000
Year
Principal at
beginning of year
Payment at
end of year
Interest paid
Repayment
of principal
1 25,000.00 13,635.92 1,500.00 12,135.92 <-- =C8-D8
2 12,864.08 13,635.92 771.84 12,864.08
Total interest paid
2,271.84
Year
In savings
account at
beginning of year
End-year
interest
earned
In account at
end of year
1 25,000.00 1,250.00 26,250.00
2 26,250.00 1,312.50 27,562.50
Interest earned
2,562.50
THE 6% LOAN
Savings Account
=PMT($B$3,2,-$B$4)
EXERCISE 14, financial analyst's calculations
1
2
3
4
5
6
7
8
AB
Date Cash flow
30-Jun-07 -899
14-Feb-08 70
14-Feb-09 70
14-Feb-10 70
14-Feb-11 70
14-Feb-12 70
14-Feb-13 1,070
16. Use XNPV to value the following investment. Assume that the annual discount rate
is 15 percent.
4
5
6
7
8
9
10
11
AB
Date Cash flow
30-Jun-07 -500
14-Feb-08 100
14-Feb-09 300
14-Feb-10 400
14-Feb-11 600
14-Feb-12 800
14-Feb-13 -1,800
17. Identify the two internal rates of return of the investment in exercise 16.