1. finance
2. personal finance
3. financial planning
4. retirement and pension

FINANCIAL MANAGEMENT SOLVERPACK
PART 1 OF 3
Compound Interest Calculations
Filename: Compound
This model is designed to solve compound interest problems, including present and future values of lump
sums and annuity streams. The model can compute values for any frequency of compounding, including
continuous compounding. Figure 1 illustrates the growth of a lump sum investment made at time zero with
annual, frequency t, or continuous compounding. Figure 2 illustrates the present and future value of an annuity.
Reference: Weston, J. Fred, and Brigham, Eugene F. Managerial Finance. 7th ed. Hinsdale, Illinois: The
Dryden Press, 1981. Chapter 4.
Figure 1: Future Value of Lump Sum
Figure 2: Future Value of an Annuity
Essential information
This model finds the present and future values of two types of investments. The first is a lump sum of money
deposited at the beginning, or received at the end, of an investment period. The second type of investment
involves a stream of annuities, which are equal periodic payments or investments. Many classes of investment
can be analyzed with this model, including bonds, bank savings certificates, and insurance company annuity
programs. The UNEVEN and LEVEL models explore some more complicated applications in more detail.
There are several considerations to keep in mind when using the model:
•
•
•
•
•
•
•
•
Interest is compounded at equal intervals. Continuously compounded interest may be computed for
lump sum investments by entering 'y for the variable cont. When continuous compounding is specified,
you must blank any frequency of compounding which may have been entered. If no input is given for
cont, the model assumes compounding at the intervals specified.
For annuity valuation problems, the payments or deposits are equal throughout the entire period of the
investment. The model is only accurate if there is at most one annuity payment per compounding
period. This means that t, frequency of compounding, should be a multiple of t2, frequency of annuity
payments.
The interest rate is assumed to be constant over the entire investment period.
The model assumes reinvestment of all interest payments not removed through specified annuities.
For lump sum investment problems, the interest is received at the end of each payment period. For
annuity problems, the model assumes payment either at the beginning (annuity due) or end (ordinary
annuity) of the payment period. To evaluate a stream of ordinary annuities, enter 2 for type, the annuity
type. To evaluate a stream of annuities due, enter 1 for type.
When the interest rate, r, is set to zero, the model ignores inputs for t, frequency of compounding; cont,
continuous compound message; and type, annuity type message.
Iterative solution is necessary to find r, interest rate, from present or future value of an annuity stream.
For lump sum compound interest valuation problems, use the variables fv and pv. For annuity streams
use fut_val and pre_val.
Sample Solutions
Example 1 examines alternative investments involving annuity streams. Example 2 finds present and future
values for a "clip and strip" bond, for which all payments to the investors are made in a lump sum at the
maturity of the bond.
Example 1: Individual Retirement Account with annuity due
An investor is examining an insurance company Individual Retirement Account (IRA) that requires periodic
investments up to age 60. The annuities paid each year after the account comes to maturity are to be
compared with annuities from alternative investments.
The investor has $2000 available per year starting at age 40 to invest in the insurance company-administered
IRA. He estimates the insurance company will pay about 9% annually on the investment over the 20-year
investment period. How much money will be accumulated at the end of this time?
To solve this problem, enter the following input values:
Input Value
9
20
1
1
1
2000
Variable Name
r
n
t
type
t2
annuity
Solve the model. The solution is shown in Figure 3.
Figure 3
Variables
St Input
Name
Output
Unit
Comment
** COMPOUND INTEREST CALCULATIONS **
9
20
1
fv
pv
r
n
cont
t
rate
1
type
1
2000
t2
annuity
fut_val
pre_val
r2
$
$
%/yr
yr
'n
9
111529.06
19900.23
9
times/yr
%/yr
times/yr
$
$
$
%/yr
future value of invested amount
present value of invested amount
annual nominal interest rate
length of time invested
continuous compounding: 'y or 'n
frequency of compounding
annual effective interest rate
annuity type: 1 - annuity due
2 - ordinary annuity
frequency of annuity payments
amount of each annuity payment
future value of annuity
present value of annuity
actual effective interest rate per
annuity payment period, not annualized
The future value of the annuity stream is over $100,000, as shown by fut_val.
The annuity contract specifies that the investor will begin to receive an annuity from the insurance company at
age 60. The company assumes it will be able to pay an annual interest rate of 14% on the balance of the
investment and estimates a remaining life expectancy of 15 years. The annuity is guaranteed for life. What is
the annual annuity payment to the investor?
To solve this problem, change the following input values and status:
Status Variable Name
r
n
type
B
annuity
pre_val
Input Value
14
15
2
fut_val
Entering fut_val for pre_val puts the full investment from the first model solution in as present value for this
solution. Solve the model. The solution is shown in Figure 4.
Figure 4
Variables
St Input
Name
Output
Unit
Comment
** COMPOUND INTEREST CALCULATIONS **
14
15
1
fv
pv
r
n
cont
t
rate
2
type
1
t2
annuity
fut_val
pre_val
r2
111529.06
$
$
%/yr
yr
'n
14
18157.931
796087.51
14
times/yr
%/yr
times/yr
$
$
$
%/yr
future value of invested amount
present value of invested amount
annual nominal interest rate
length of time invested
continuous compounding: 'y or 'n
frequency of compounding
annual effective interest rate
annuity type: 1 - annuity due
2 - ordinary annuity
frequency of annuity payments
amount of each annuity payment
future value of annuity
present value of annuity
actual effective interest rate per
annuity payment period, not annualized
The value of annuity indicates a yearly payment of over $18,000. The investor wishes to compare this with
depositing the money himself.
He estimates that he could deposit the IRA at 14.5% annually over the years after age 60. Taking the same
annuity stream as the insurance company would have paid in the previous solution, how long would the
investment last?
To solve this problem, change the following input values:
Status Variable Name
r
B
n
I
annuity
Input Value
14.5
Solve the model. The solution is shown in Figure 5.
Figure 5
Variables
St Input
Name
Output
Unit
Comment
** COMPOUND INTEREST CALCULATIONS **
14.5
1
fv
pv
r
n
cont
t
rate
2
type
1
18157.931
t2
annuity
fut_val
pre_val
r2
111529.06
16.342687
'n
14.5
1019595.1
14.5
$
$
%/yr
yr
times/yr
%/yr
times/yr
$
$
$
%/yr
future value of invested amount
present value of invested amount
annual nominal interest rate
length of time invested
continuous compounding: 'y or 'n
frequency of compounding
annual effective interest rate
annuity type: 1 - annuity due
2 - ordinary annuity
frequency of annuity payments
amount of each annuity payment
future value of annuity
present value of annuity
actual effective interest rate per
annuity payment period, not annualized
The solution for n indicates that if the investor lives beyond the age of 76 (16 years beyond age 60), the
insurance company plan is the better opportunity because the insurance company guarantees payments for
life. The three solutions for this example show that if the investor can put $2000 per year into an IRA at the
specified interest rates, he can expect a yearly income of more than $18,000 at the maturity of the account. If
he expects to live beyond age 76, he should stay with the insurance company offer. Otherwise, he should
invest the money himself.
Example 2: A "clip and strip" bond
A new bond issue is offered in which the face value of the bond is sold independently from the right to the
coupons (interest payments) on the bond. Both components are sold at a discount and will be payable at
maturity. The bond matures in 10 years and bears interest at 16.5% compounded semiannually. The face
value (value at maturity) of each bond is $1000, and the coupon stream is at an annual rate of 16.5% of the
face value, paid semiannually. Determine the fair price of both the face value and the right to the coupons.
To solve this problem, enter the following input values:
Variable Name
fv
r
n
t
type
t2
annuity
Input Value
1000
16.5
10
2
2
2
.5 * r * fv
Solve the model. The solution is shown in Figure 6.
Figure 6
Variables
St Input
Name
Output
Unit
Comment
** COMPOUND INTEREST CALCULATIONS **
1000
16.5
10
2
fv
pv
r
n
cont
t
rate
2
type
2
82.5
t2
annuity
fut_val
pre_val
r2
204.8528
$
$
%/yr
yr
'n
17.180625
3881.554
795.1472
8.25
times/yr
%/yr
times/yr
$
$
$
%/yr
future value of invested amount
present value of invested amount
annual nominal interest rate
length of time invested
continuous compounding: 'y or 'n
frequency of compounding
annual effective interest rate
annuity type: 1 - annuity due
2 - ordinary annuity
frequency of annuity payments
amount of each annuity payment
future value of annuity
present value of annuity
actual effective interest rate per
annuity payment period, not annualized
The value of pv, present value of invested amount, represents a fair price for the face value of the bond. The
right to the coupon payments is given by pre_val, the present value of the annuity. These two sum to $1000,
the amount of the face value. The future value of the annuity, fut_val, is the payment that the investor
purchasing the coupon stream will receive at maturity.
Level Debt Service Analysis
Filename: LEVEL
This model is designed to solve a variety of problems involving loans that are repaid by a stream of regular
equal payments over the life of the loan. Each payment contains a return of principal and an interest
component. Mortgages and installment sales obligations are the most common types of debt that use this
payment structure, known as level debt service.
Reference: Alonso, J.R.F. Simple Basic Programs for Business Applications. Englewood Cliffs, New Jersey:
Prentice-Hall, 1981. Pages 187-201.
Essential information
The model uses the basic discounting methods of investment valuation. Each of the equal payments to the
lender include some principal and some interest on the remaining unpaid balance. Toward the end of the term
of the loan, the interest portion of the payment declines significantly and the buyer's equity approaches the
total value of the asset. This is illustrated in Figures 1 and 2.
The model includes the purchase price, total amount of the loan, down payment, nominal interest rate, periodic
level payment, and total amount paid over the term of the loan. The model can also determine the principal and
interest payments involved in any one payment or span of payments, and the equity and remaining balance at
any point in the loan.
Figure 1
Figure 2
There are several considerations to keep in mind when using the model:
•
•
•
The model assumes that the periodic monthly payment and nominal interest rate remain constant over
the term of the loan.
Enter 0 for down payment, down, if there is no down payment.
The units in the model are set up for payment on a monthly basis, with "%_as_decimal/mo" as the
Calculation unit for r, the nominal interest rate; and "$/mo" as the Calculation unit for A, the periodic
payment amount. If payments are made at some other frequency, the Calculation units for these
variables should be changed so their denominator reflects this frequency, and the appropriate
conversions added to the Unit Sheet.
•
•
•
•
•
The variable f represents the beginning, and l the end, of a span of payments to be analyzed. The
variable k represents a single payment to be analyzed. All three of these variables are measured in
time from the beginning of the loan.
For f, l, and k to represent the number of a payment in sequence, their Display units must be the same
as the payment frequency (for example, units of "mo" for monthly payments). This would mean, for
example, that when k is 50 the model is examining the fiftieth payment.
When attempting to determine the payment, k, that will have a particular proportion of interest to
principal, the model must be solved iteratively. Iterative solution is also necessary when solving for
interest rate, r.
Values computed when n, the term of the loan, is not a multiple of the payment period are inaccurate.
Situations in which nis not a whole number of payment periods do not reflect an actual level debt
service situation. However, you can use this to backsolve for an approximate value of n. Then re-run
the model with a nearby integer value of n as an input and backsolve the other direction.
The variables k, payment to be evaluated; f, start of period to be considered; and l, end of period to be
considered, must lie between zero and n, the term of the loan. Values outside this range represent
payments either before the loan has started or after it is over. This condition can be recognized by the
fact that i_period, p_period, accum_i, or rg_ loan are negative. Values computed under such
circumstances do not reflect actual loan payments.
Sample solutions
Example 1 analyzes various factors involved in a home mortgage. Example 2 examines a car loan, in which
the desired monthly payment is too low to be possible.
Example 1: Home mortgage
A $75,000 home is purchased with a down payment of $10,000 and a 25-year mortgage. The mortgage
payments are based on a 14% annual interest rate. What are the required monthly payments, and how much
will the borrower pay over the entire term of the mortgage? Also examine the composition of a single payment
and span of payments.
To find the monthly payment, enter the following input values:
Variable Name
Price
down
n
r
Input Value
75000
10000
25
14
Solve the model. The solution is shown in Figure 3.
Figure 3
Variables
St Input
Name
Output
Unit
Comment
** LEVEL DEBT SERVICE ANALYSIS **
75000
10000
25
14
Price
down
dp
Loan
n
r
13.333333
65000
$
$
%
$
yr
%/yr
purchase price of asset
down payment (after closing costs)
down payment as percent of price
beginning balance of loan
term of loan
nominal interest rate on loan
A
T
Ti
782.44468
234733.4
169733.4
$/mo
$
$
periodic loan payment amount
total of payments made over loan term
total interest paid over loan term
The payments come to almost $800 per month, totaling over $234,000 for the entire mortgage.
When will the payments be half principal and half interest?
This problem requires iterative solution to find which payment has a given mix of interest and principal. To
solve this problem, enter the following additional input values and guesses:
Status Variable Name
G
k
pk
Input Value
15 * 12
A/2
The guess given for k represents a point 15 years into the mortgage. Solve the model. The solution is shown in
Figure 4.
Figure 4
Variables
St Input
Name
Output
Unit
Comment
** LEVEL DEBT SERVICE ANALYSIS **
75000
10000
25
14
'y
391.22234
Price
down
dp
Loan
n
r
A
T
Ti
table
k
accum_i
rg_Loan
E
pk
ik
13.333333
65000
782.44468
234733.4
169733.4
241.24148
156900.23
33142.121
41857.879
391.22234
$
$
%
$
yr
%/yr
$/mo
$
$
mo
$
$
$
$
$
purchase price of asset
down payment (after closing costs)
down payment as percent of price
beginning balance of loan
term of loan
nominal interest rate on loan
periodic loan payment amount
total of payments made over loan term
total interest paid over loan term
generate amortization table? 'y or 'n
(See table and Plot Sheets)
Analysis of a Specific Payment
payment to be evaluated
total interest paid after k payments
loan balance after k payments
equity after k payments
principal part of kth payment
interest part of kth payment
The 241st payment contains nearly equal proportions of interest and principal. Enter yr as units for k to see
that this is in the twentieth year of the mortgage.
The model can also be used to evaluate a span of payments. Suppose, for tax purposes, the homeowner
wants to see how much principal and interest is paid in the first tax year of the mortgage. The first mortgage
payment was for June, so the last payment in the calendar year is the seventh month of the mortgage.
To solve this problem, enter the following additional input values:
Variable Name
f
l
Input Value
1
7
Solve the model. The solution is shown in Figure 5.
Figure 5
Variables
St Input
1
7
Name
f
l
pperiod
iperiod
Output
Unit
Comment
174.80291
5302.3098
mo
mo
$
$
Analysis of an Interval of Payments
start (1st payment) of period
end (last payment) of period
principal paid during period
interest paid during period
The homeowner pays $5302.31 in interest payments in the first year of house payments.
The Table Sheet includes a complete amortization schedule for the loan.
Example 2: Term of car loan
A car buyer wishes to buy a $10,000 car, and can afford a down payment of $2000 and monthly payments of
$100. Available financing is at a 16% annual rate. What is the term of a loan under these conditions?
To solve this problem, enter the following input values:
Variable Name
Price
down
r
A
Input Value
10000
2000
16
100
Solve the model. The solution is shown in Figure 6.
Figure 6
Variables
St Input
Name
Output
Unit
Comment
** LEVEL DEBT SERVICE ANALYSIS **
10000
2000
16
100
Price
down
dp
Loan
n
r
A
20
8000
$
$
%
$
yr
%/yr
$/mo
purchase price of asset
down payment (after closing costs)
down payment as percent of price
beginning balance of loan
term of loan
nominal interest rate on loan
periodic loan payment amount
You will get an error message, "Infinite loan terms." At this interest rate, such a low payment is impossible.
Net Present Value/Internal Rate of Return Analysis
Filename: UNEVEN
This model is designed to determine present and future values of a stream of cash flows of uneven amounts at
an assumed discount rate. The analysis includes computation of internal rate of return. The model can also be
used to analyze cash inflows, including the depreciation tax shield over time, and cash outflows, allowing for
assumed rates of taxation and inflation. The growth of net cash flow in relation to net present value and future
value following an initial $100,000 investment is shown in Figure 1.
Reference: Van Horne, James C. Financial Management and Policy. 5th ed. Englewood Cliffs, New Jersey:
Prentice-Hall, 1980. Chapter 5.
Figure 1
Essential information
This model can be used to compare alternative investment opportunities, each of which would result in a
stream of cash flows over the span of the investment. These cash flows can be all inflows, all outflows, or a
combination of the two. The investments can be evaluated by the net present value method or the internal rate
of return method.
This model can be used for these three types of problems:
•
To determine the present or future value of a series of cash flows, first enter the initial investment as an
input for the variable invest0. Then switch to the table and enter the series in the columns inflow and
outflow. For each time period, enter income in the inflow column and disbursements in the outflow
column. Use the Direct Solver when solving for present value, pv, or future value, fv. Use the Iterative
Solver to find the interest rate r. In both cases, TK computes the net cash flow in the table along with
the present and future values at each time period.
•
•
To find the internal rate of return of a series of net cash flows, enter the series as in the previous case.
Then enter zero for pv and solve iteratively for the internal rate of return, r.
To find the value of a series of inflows, outflows, and depreciation adjusted for inflation and tax
considerations, use the table and the variables a, b, infl, tax, and pv_infl. The columns, inflow, outflow,
and depreciation start from the end of the first cash flow period after the initial investment. It is
necessary to use iteration when solving for r, interest rate; infl, inflation rate; or tax, tax rate. To find the
internal rate of return, set pv_infl to zero and solve iteratively for r.
There are several considerations to keep in mind when using the model:
•
•
•
•
•
•
•
•
The discount rate, r, represents rate per period of cash flow. If the cash flows are more frequent than
one per year, r should be the annual interest rate divided by the frequency per year of the cash flows.
Internal rate of return analysis can have multiple solutions in situations where the cash flow stream
changes back and forth between positive (net income) and negative (net disbursements). The model
finds only one of these solutions, depending on the guess provided to the Iterative Solver. In such
situations, simple internal rate of return analysis is not an accurate measure of the worth of the
investment (in contrast to net present value analysis, which always gives a single conclusion). Where
the net cash flows alternate in sign, plotting present value against interest rate will reveal when
multiple internal rates of return exist (since the present value curve will be zero for more than one
interest rate). The model can be used to make this plot by making r and pv into lists, using the List
Solve command to solve over a range of values of r.
Internal rate of return analysis may have no solution. This situation is characterized by nonconvergence of the Iterative Solver. (A series of cash flows with no internal rate of return can still be
analyzed by the net present value method.) Again, this can be confirmed using plots.
Several of the other models in this SolverPack are designed for analysis of certain cash flow problems
without inflation adjustments. For a single cash flow or a stream of equal periodic cash flows (annuities)
see the COMPOUND model. For a single inflow followed by periodic level outflows with a future value
of zero, use the LEVEL model. These models concern more specific applications in more detail and
alleviate the need for extensive use of the Iterative Solver.
The models CAPM or DIVGROW may be used to estimate discount rate when either weighted average
cost of capital or cost of equity capital is the appropriate basis for discounting.
When no value is given for a, the proportion of the inflows affected by inflation, or for b, the proportion
of the outflows affected by inflation, these variables are set to 100%. That is, the model assumes that
the inflation rate for the inflows or outflows is the same as the general inflation rate.
Inflows represent payment from the investment to the investor. Outflows represent payment from the
investor to the investment.
Inflows, outflows, and the depreciation tax shield benefit can be evaluated without taking inflation into
account by setting infl, the inflation rate, to zero.
Sample solutions
The example below finds the net present value and internal rate of return of a series of cash flows before
inflation. The model is then used to analyze the same investment with inflation effects, tax considerations, and
depreciation tax shield benefits.
Example: Analysis of an investment
A corporation is considering installing machinery that will require an initial investment of $100,000. In the five
years following the investment, returns of $20,000, $40,000, $40,000, $30,000, and $20,000 are expected.
Assume capital is discounted at 12%. Find the net present value of the investment.
To solve this problem, enter the following input values:
Variable Name
Input Value
invest0
r
100000
12
Switch to the table. Enter the following entries in the column inflow.
20000
40000
40000
30000
20000
Return to the Variable Sheet and solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** VALUES OF UNEVEN CASH FLOWS **
100000
12
invest0
r
pv
fv
$
%
$
$
8630.1873
15209.339
(alt-h for help)
initial investment (at time 0)
discount rate per cash flow interval
present value of cash flow series
future value of cash flow series
The net present value is given by pv.
We can analyze the same investment by the internal rate of return method. What is the internal rate of return of
this investment?
To solve this problem, change the following input values and status:
Status Variable Name
G
r
pv
Input Value
14
0
Solve the model. The solution is shown in Figure 3.
Figure 3
Variables
St Input
Name
Output
Unit
Comment
** VALUES OF UNEVEN CASH FLOWS **
100000
0
invest0
r
pv
fv
15.44
0
$
%
$
$
initial investment (at time 0)
discount rate per cash flow interval
present value of cash flow series
future value of cash flow series
The internal rate of return is between 15% and 16%.
With more information, the investment can be analyzed in greater depth. Suppose the machinery represented
by the $100,000 initial investment is depreciated by the straight line method in equal amounts of $20,000 each
year over the 5-year period of the investment. Also, assume a corporate tax rate of 50% and projected annual
inflation of 7% as measured by the Consumer Price Index over the span of the project. Assume also that the
specific inflation rate applicable to the revenues from the investment is 95% of the general inflation rate. What
is the net present value of the project at a discount rate of 12%? Find the net present value of the project in the
intermediate years.
In order to use the inflation-adjusted formula, it is necessary to enter the cash flow information into the
appropriate columns of the table. To solve this problem, enter or change the following input values and status:
Status Variable Name
r
B
pv
infl
a
tax
Input Value
12
7
95
50
Go to the table and make the entries indicated below.
Column:
inflow
outflow
depreciation
Entry
1
2
3
4
5
20000
40000
40000
30000
20000
0
0
0
0
0
20000
20000
20000
20000
20000
Lists outflow and depreciation can easily be filled using the copy command.
Solve the model. The solution is shown in Figure 4.
Figure 4
Variables
St Input
Name
Output
Unit
Comment
** VALUES OF UNEVEN CASH FLOWS **
100000
12
7
95
50
invest0
r
pv
fv
8630.19
15209.34
infl
a
$
%
$
$
%
%
b
100
%
tax
pv_infl
1135.63
%
$
(alt-h for help)
initial investment (at time 0)
discount rate per cash flow interval
present value of cash flow series
future value of cash flow series
INFLATION AND TAX CONSIDERATIONS
expected inflation rate per period
inflow inflation rate as % of the
general inflation rate
outflow inflation rate as % of the
general inflation rate
tax rate
inflation-and-tax-adjusted npv
After inflation and taxes are considered, the investment is somewhat less lucrative. The present value
computed when revenues do not keep pace with the general rate of inflation, pv_infl, is quite a bit smaller than
pv, the first present value that was computed (in Figure 2). The table displays the intermediate values of the
inflation-adjusted net present value.
Figure 5
TABLE: worksheet
Title:
Net Present Value / Internal Rate of Return Analysis
Element
inflow
outflow
depreciation
1
20000
0
20000
2
40000
0
20000
3
40000
0
20000
4
30000
0
20000
5
20000
0
20000
net cash flow
18450.89
26106.86
24386.46
18688.01
13503.41
adjusted npv
-81549.11
-55442.25
-31055.79
-12367.78
1135.63
The investment becomes profitable in the fifth year.
Bond Swap Analysis: Anticipated Spread Swap
Filename: SPREAD
This model is designed to compute bond yields and to analyze bond trading. The yield to maturity or the
expected realized compound yield over a specified holding period can be computed for a bond. Proposed bond
swaps can be evaluated based on assumptions about future interest rates.
Reference: Homer, Sidney, and Leibowitz, Martin L. Inside the Yield Book: New Tools for Bond Market
Strategy. Englewood Cliffs, New Jersey: Prentice-Hall and New York Institute of Finance, 1972. Chapter 6.
Essential information
This model can analyze two types of bond swaps that are intended to take advantage of unusual market yield
spreads. These swap types are known as intermarket spread swaps (for bonds of comparable quality in two
distinct bond market segments) and substitution swaps (for two otherwise equivalent bonds). Both involve a
gain that is expected to occur only if yield spreads readjust. The magnitude of the expected gain is dependent
upon assumptions about future interest rates and on the length of the workout period required for readjustment
of the yields.
For either a single bond investment or for comparing alternative investments or swaps, the model computes
yield to maturity or current market price of each bond, based on face value, coupon rate, and a reinvestment
rate for the coupons. Realized compound yield and future market value are computed, based on a workout
period and prevailing yield to maturity at the end of the workout period. Monetary gain and percentage return
on amount invested are provided.
The model can be used to analyze a single bond or to evaluate a bond swap. In each case, all values are
entered on the Variable Sheet.
•
The workout period, out, and reinvestment rate, invest, are assumed to be constant from bond to bond.
The value of the swap in basis points (hundredths of a percentage point) over the workout period is
represented by value.
FINANCIAL MANAGEMENT SOLVERPACK
PART 2 OF 3
There are several considerations to keep in mind when using the model:
•
•
•
•
•
•
•
•
•
•
Assumed reinvestment rate, yield to maturity at the end of the workout period, and length of the
workout period all have a significant impact on the values computed by the model.
The model does not take into account the transaction costs of bond trading or tax considerations.
Solving for y1, yield to maturity at present; yw1, yield at end of workout period; invest, reinvestment
rate; or out, workout period, requires the Iterative Solver.
To evaluate pure yield pickup swaps, for which the benefits accrue over the full period to maturity, use
the PICKUP model.
The model assumes non-zero yield to maturity and end-of-workout period yield. In cases where the
coupon payments are not reinvested during the workout period, the reinvestment rate, invest, should be
set to zero.
The reinvestment rate is assumed to be the effective rate over the entire investment period. Interest on
coupons is compounded at the same frequency as the coupon payments.
The model is not designed to analyze situations in which the workout period is not an exact multiple of
the coupon payment period. It is assumed that the first coupon payment occurs one full payment period
after the bond is purchased.
The assumed workout period cannot exceed the shorter of the two bonds' terms to maturity.
The expected value of the swap computed by the model is achieved during the workout period. The
computed value of the swap would be achieved by the investor (if his expectations were to prove true)
whether or not the purchased bond is sold at the end of the workout period.
The total monetary gain, gain, is the difference between the current market value of the bond and the
value of the bond plus interim coupons and accrued interest at the end of the workout period. The
proportional gain, return, is the amount of gain per dollar invested, expressed as a percentage rate. The
realized compound yield, real, is the percentage rate at which the money would have to be invested,
compounded as frequently as the coupon payments, to earn the same total return. Gains and realized
compound yield are computed based only on the gain per dollar initially invested. The model does not
take into account discrepancies between the amount of money needed to buy the purchased bond and
the amount obtained from selling the held bond. Reinvestment of excess or borrowing in order to
complete the swap is not considered.
Comparisons between bonds with different coupon frequencies (for example, semiannual and quarterly
coupons) are subject to inaccuracy.
Sample solutions
The first example finds the yield to maturity and expected realized compound yield of a bond at a given price.
The second compares two bonds being considered for an intermarket spread swap.
Example 1: Yield to maturity
A bond with a $1000 face value, 12 years remaining to maturity, and an annual coupon rate of 10% paid
semiannually is trading at a bond price of 75, or $750. The investor wants to hold this bond for 5 years and
expects an effective coupon reinvestment rate of 15% and a 15% yield to maturity for the bond at the end of
the 5 years. Find the bond's yield to maturity at present and its realized compound yield over the holding
period.
This problem requires Iterative solution to determine yield to maturity. To solve it, enter the following input
values and guesses:
Status Variable Name
out
invest
fvb1
r1
t1
n1
G
y1
yw1
val1
Input Value
5
15
1000
10
2
12
12
15
750
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
* BOND TRADING: SPREAD SWAP & YIELD *
5
out
yr
15
invest
%/yr
1000
10
2
12
fvb1
r1
t1
n1
y1
yw1
val1
c1
int1
14.44
50
707.35
$
%/yr
times/yr
yr
%/yr
%/yr
$
$
$
end1
Tend1
gain1
return1
real1
787.77
1495.13
745.13
99.35
14.28
$
$
$
%
%/yr
15
750
workout period for spreads
to readjust
reinvestment rate assumed during
workout period
ANALYSIS OF HELD BOND:
face value of bond
annual coupon rate
frequency of coupon payments
term to maturity
yield to maturity at present
exp. yield at end of workout period
market value of bond
amount of each coupon payment
coupons and accrued interest at end of
workout period
market value at end of workout period
total amount accrued
total monetary gain
proportional gain on investment
realized compound yield
The bond's yield to maturity at present is about 14.4% and the investor anticipates a realized compound yield
of about 14.3%, assuming interest rates behave as predicted.
Example 2: Intermarket spread swap
A 10%-coupon, 22-year industrial-company bond with a yield to maturity of 15% is to be swapped for a 15%coupon, 20-year utility-company bond of comparable quality with a yield to maturity of 17%. Both bonds pay
semiannual coupons and have face values of $1000. Over a 2-year period the yields to maturity for the two
bonds are expected to be 15.5% and 17%, respectively. The assumed coupon reinvestment rate is 16%.
Evaluate the basis point gain involved over the workout period.
To solve this problem, first enter the input values for the two bonds, as indicated:
Variable Name
out
invest
fvb1
r1
t1
n1
y1
yw1
fvb2
r2
t2
n2
y2
yw2
Input Value
2
16
1000
10
2
22
15
15.5
1000
15
2
20
17
17
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
* BOND TRADING: SPREAD SWAP & YIELD *
2
out
yr
16
invest
%/yr
1000
10
2
22
15
15.5
1000
15
2
20
17
workout period for spreads
to readjust
reinvestment rate assumed during
workout period
fvb1
r1
t1
n1
y1
yw1
val1
c1
int1
680.5
50
225.31
$
%/yr
times/yr
yr
%/yr
%/yr
$
$
$
end1
Tend1
gain1
return1
real1
663.08
888.39
207.89
30.55
13.78
$
$
$
%
%/yr
ANALYSIS OF HELD BOND:
face value of bond
annual coupon rate
frequency of coupon payments
term to maturity
yield to maturity at present
exp. yield at end of workout period
market value of bond
amount of each coupon payment
coupons and accrued interest at end of
workout period
market value at end of workout period
total amount accrued
total monetary gain
proportional gain on investment
realized compound yield
$
%/yr
times/yr
yr
%/yr
ANALYSIS OF BOND TO BE PURCHASED:
face value of bond
annual coupon rate
frequency of coupon payments
term to maturity
yield to maturity at present
fvb2
r2
t2
n2
y2
17
yw2
val2
c2
int2
886.85
75
337.96
%/yr
$
$
$
end2
Tend2
gain2
return2
real2
888.59
1226.55
339.7
38.3
16.89
$
$
$
%
%/yr
exp. yield at end of workout period
market value of bond
amount of each coupon payment
coupons and accrued interest at end of
workout period
market value at end of workout period
total amount accrued
total monetary gain
proportional gain on investment
realized compound yield
value
3.11
%/yr
VALUE OF SWAP:
value of swap over workout period
The swap creates a total gain of 310 basis points, or 3.1%, if the spreads readjust as assumed over the twoyear period.
Bond Swap Analysis: Yield Pickup
Filename: PICKUP
This model is designed to analyze pure yield pickup bond swaps. Such swaps are undertaken to improve
current income, yield to maturity, or both.
Reference: Homer, Sidney, and Leibowitz, Martin L. Inside the Yield Book: New Tools for Bond Market
Strategy. Englewood Cliffs, New Jersey: Prentice-Hall and New York Institute of Finance, 1972. Chapter 7.
Essential information
This model evaluates the gain achieved by a bond swap in terms of change in yield to maturity, expected
realized compound yield to maturity, and current coupon yield. It can also calculate the book loss that may
have to be recognized when the old bond is sold, and the number of years it will take to recoup the loss
through the higher cash flow from the new bond. The model accounts for the total amount invested, using
proceeds from bonds to be sold, amt, and number of bonds to be purchased, nbonds. The bond investment
must be held to maturity.
Analysis of a single bond can be accomplished by entering the necessary values for that bond
and solving. The model computes yield to maturity. Based on the user's assumption about
reinvestment rate earned on coupons paid, expected monetary gain to maturity and realized
compound yield to maturity are computed. Use the SPREAD model to calculate the expected
realized compound yield when the bond is not held to maturity. To evaluate a swap of two
bonds, enter information for both the held bond and the alternative and solve.
There are several considerations to keep in mind when using the model:
•
•
The assumed reinvestment rate has a significant impact on the returns computed by the model.
The model does not account for the transaction costs of bond trading or tax considerations.
•
•
•
•
•
•
Solving for y, yield to maturity at present, or invest, assumed reinvestment rate, requires the Iterative
Solver.
The SPREAD model is better suited to analysis of swaps motivated by the expectation that yield
spreads will readjust over a workout period shorter than the term of the bond.
The model assumes non-zero yield to maturity. In cases where the coupon payments are not subject to
a reinvestment rate, invest should be set to zero.
Assume one reinvestment rate, invest, and one frequency of coupon payments, t, for both the held
bond to be purchased. Interest on coupons is compounded at the same frequency as the coupon
payments.
Variables with units of "$/bond" or "$*10/bond" represent values for one bond. Variables with units of
"$" represent values for the entire investment made in a bond issue.
The total monetary gain, gain, is the difference between the current market value of the bond and the
bond value at maturity, including bond value, coupons, and accrued interest. The realized compound
yield, real, is the percentage rate at which the money would have to be invested, compounded as
frequently as the coupon payments, to earn the same total return. The total monetary gain from the
swap, Tgain, is the difference at maturity between gain on the purchased bond and gain on the held
bond. This gain from the swap is translated into realized annual yield as value, expressed in basis
points (hundredths of a percentage point). For these gain figures to be accurate, the money obtained
from sale of the held bond should be the amount invested for the purchased bond. The gain in coupon
yield, intgain, is the gain in basis points of coupon yield per dollar invested.
Sample solutions
This example compares two bonds that an investor desires to swap for yield pickup and computes the time
necessary to recoup the book loss.
Example: Pure yield pickup swap
An 8%-coupon, 30-year bond with a yield to maturity of 13% is to be swapped into a 14%-coupon, 30-year
bond with a yield to maturity of 14%. Both pay coupons semiannually. Assume a 14% coupon reinvestment
rate. What is the annual yield pickup value of the swap? Assuming the held bond has a book value of 90, how
many years will it take for the higher coupon cash flow to recover the book loss from the swap?
To solve this problem, enter input values for the first bond and solve.
Variable Name
invest
t
fvb1
nb1
r1
n1
y1
bval1
Input Value
14
2
1000
1
8
30
13
900
As shown in Figure 1, the first bond has a realized compound yield of approximately 13.7%.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** BOND SWAP ANALYSIS: YIELD PICKUP **
14
2
invest
t
%/yr
times/yr
assumed reinvestment rate
frequency of coupon payments
1000
1
fvb1
nb1
amt1
r1
n1
y1
val1
c1
int1
amgain1
gain1
real1
bval1
bloss1
$
bonds
$
%/yr
yr
%/yr
$
$
$
$
$
%/yr
$/bond
$/bond
ANALYSIS OF HELD BOND:
face value of bond
number of bonds
total value of bonds
annual coupon rate
term to maturity
yield to maturity at present
current market value of bond
amount of each coupon payment
coupons & accrued interest at maturity
gain from amortization to face value
total monetary gain
realized compound yield
book value of bond
loss from book value per bond
8
30
13
900
624.18
624.18
40
32540.82
375.82
32916.64
13.73
275.82
The value of the swap is determined by reinvesting proceeds from sale of the first bond. To solve this problem,
enter the following input values for the second bond:
Variable Name
fvb2
amt2
r2
n2
y2
Input Value
1000
amt1
14
30
14
Solve again. The overall solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** BOND SWAP ANALYSIS: YIELD PICKUP **
14
2
invest
t
%/yr
times/yr
assumed reinvestment rate
frequency of coupon payments
1000
1
fvb1
nb1
amt1
r1
n1
y1
val1
c1
int1
amgain1
gain1
real1
bval1
$
bonds
$
%/yr
yr
%/yr
$
$
$
$
$
%/yr
$/bond
ANALYSIS OF HELD BOND:
face value of bond
number of bonds
total value of bonds
annual coupon rate
term to maturity
yield to maturity at present
current market value of bond
amount of each coupon payment
coupons & accrued interest at maturity
gain from amortization to face value
total monetary gain
realized compound yield
book value of bond
8
30
13
900
624.18
624.18
40
32540.82
375.82
32916.64
13.73
bloss1
1000
624.18
14
30
14
fvb2
amt2
nb2
r2
n2
y2
val2
c2
int2
amgain2
gain2
real2
bval2
bloss2
275.82
.62
1000
70
35544.58
0
35544.58
14
$/bond
loss from book value per bond
$
$
bonds
%/yr
yr
%/yr
$
$
$
$
$
%/yr
$/bond
$/bond
ANALYSIS OF BOND TO BE PURCHASED:
face value of bond
total value of bonds to be analyzed
number of bonds to be analyzed
annual coupon rate
term to maturity
yield to maturity at present
current market value of bond
amount of each coupon payment
coupons & accrued interest at maturity
gain from amortization to face value
total monetary gain
realized compound yield
book value of bond
loss from book value per bond
Tgain
value
intgain
2627.95
.27
1.18
$
%/yr
%/yr
k
13.52
yr
VALUE OF SWAP:
total gain from swap at maturity
annual yield pickup value of swap
gain in coupon yield on investment
from swap
time to recoup book loss from cashflow
The swap picks up about 27 basis points in annual yield. It will take over 13.5 years to recoup the book loss.
Bond Refunding Decision
Filename: REFUND
This model is designed to determine whether a firm should refund an existing bond issue with a new one when
interest rates drop below the coupon rate on the existing issue. Tax considerations are included.
Reference: Van Horne, James C. Financial Management and Policy. 5th ed. Englewood Cliffs, New Jersey:
Prentice-Hall, 1980. Pages 620-626.
Essential information
This model takes into account a number of cost and benefit factors associated with refunding: the lower
interest expense over the life of the new issue, the call premium, extra interest paid if the new bonds are
issued before the old ones are called, the flotation costs of the new bonds, and the tax savings arising from the
amortization of flotation costs. Refunding decisions can be based on a comparison of the after-tax cost of
refunding to the after-tax interest savings over the period that the old bond issue is replaced.
There are several considerations to keep in mind when using the model:
•
•
•
•
•
•
•
•
•
The model applies only when lower interest rates justify refunding. It does not consider accounting
practices that are sometimes used to justify refunding when interest rates have risen. Therefore, the
annual interest rate on the old bonds, oldrate, must be higher than the interest rate on the new bonds,
newrate, for the net present value of the refund decision, npv_at after taxes or npv_bt before taxes, to
be positive.
The model assumes bonds are due at maturity and pay periodic interest annuities. Serial or sinking
fund bonds that provide for principal payments over time are not analyzed.
Bond-refunding decisions are often complicated by the fact that the two issues mature at different
times. The model assumes that the cash savings should be analyzed over the shorter of the two bond
maturities, which is calculated as n. This ignores the interest rate refunding risk associated with the
issue with the shorter maturity.
The model assumes that both issues would have the same interest payment dates during the period
they were both in effect. Enter a value for t, frequency of annuity, to find either npv_bt or npv_at, which
represent the net present value of the refund decision. For example, for semiannual payment dates, the
value of t should be 2.
The user must enter zero for any cost factors not relevant to a particular application.
The flotation costs, oldflot and newflot, should include any costs amortized over the life of the issue
associated with the issuance of the bonds. These include initial discount or premium, underwriting
spread, and legal costs.
The model allows consideration of the net present value of the refunding decision based on both the
before-tax and after-tax costs of debt, which are calculated as npv_bt and npv_at, respectively.
Although the cash flows are after-tax, the investor can earn the before-tax rate by buying the bonds
directly. Because of this, some analysts argue that the before-tax discount rate is appropriate. Others
argue that the after-tax cost of debt is relevant for the owners of the firm, especially if it finances any
direct refunding costs with additional debt.
The variables cashin, ts_am, and int_sav represents cash savings per annuity period, taking place in
each annuity period until the end of the time considered. These figures should not be compared directly
to callcost, extra, or any other variable that measures front-end costs of the refunding decision.
Iterative solution is necessary in cases where the interest rate on the new bonds, newrate, is being
determined.
Sample solutions
Example 1 analyzes the costs and benefits of refunding a bond issue. Example 2 determines at what interest
rate a refund decision becomes profitable to the issuing firm.
Example 1: Evaluation of decision to refund
A large corporation is considering calling a $30 million issue of 17% coupon bonds with a remaining life of 12
years. Unamortized flotation costs $400,000 remain on the old bonds. The bonds must be called at a 5%
premium (a price of $105 for every $100 of face value). The company could market a similarly sized issue
today at 14% with a 10-year maturity for estimated flotation costs of $600,000. The old bonds, like the new,
would have semiannual coupon payments. It is estimated that the old bonds would be retired about 2 months
after the issuance of the new bonds. Assume a corporate tax rate of 46%. Should the corporation refund?
To solve this problem, enter the following input values:
Variable Name
bond
prem
oldmat
oldrate
oldflot
newmat
Input Value
3E7
5
12
17
400000
10
newrate
newflot
tax
overlap
t
14
600000
46
2
2
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** BOND REFUNDING DECISION **
3E7
5
12
17
400000
10
14
600000
46
2
2
bond
prem
oldmat
oldrate
oldflot
newmat
newrate
newflot
tax
t
overlap
cashout
callcos
extra
cashin
1685000
810000
459000
249133.33
$
%
yr
%/yr
$
yr
%/yr
$
%
times/yr
mo
$
$
$
$
ts_am
6133.3333
$
int_sav
243000
$
ts_unam
184000
$
npv_bt
npv_at
954322.08
1767729.4
$
$
principal amount of bonds
call price premium over face value
remaining term of old bonds
interest rate on old bonds
old bond unamortized flotation costs
term of new bonds
interest rate on new bonds
new bond flotation costs
tax rate
frequency of annuity for npv
duration both issues are outstanding
initial cash outflow to refund
after-tax cast of call premium
old bonds interest during overlap pd.
total cash savings from
refunding (per annuity pd.)
tax savings from amortizing new
flotation costs (per annuity pd.)
after-tax interest savings from
refunding (per annuity pd.)
tax savings from immediately deducting
unamortized flotation cost of old bond
npv of refund: before-tax cost of debt
npv of refund: after-tax cost of debt
Since both npv_bt and npv_at are positive, the refunding decision is profitable, whether evaluated at the
before-tax or after-tax cost of debt.
Example 2: interest rates for profitable refund
As interest rates drop, a company with a $20 million, 14% bond issue outstanding is considering calling the
bonds at the call price of $105 per $100 of face value. The company pays federal income taxes at a 46% rate,
and has decided to evaluate the refund decision in terms of before-tax cost of debt. Flotation costs of $550,000
are required on the new bond, versus $300,000 of unamortized flotation costs remaining on the old bond. The
old issue has 20 years remaining to maturity, the new bonds will mature in 20 years, and their overlap time on
the market will be 3 months. Both bonds have semiannual coupons. At what interest rate for the new bonds is
it profitable for the company to call the old issue?
At the break-even point, the net present value of the refunding decision should be zero. This problem requires
Iterative solution to determine newrate, the interest rate on the new bonds. To solve this problem, enter the
following input values and guesses:
Status Variable Name
bond
prem
oldmat
oldrate
oldflot
newmat
G
newrate
newflot
tax
t
overlap
npv_bt
Input Value
2E7
5
20
14
300000
20
10
550000
46
2
3
0
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** BOND REFUNDING DECISION **
2E7
5
20
14
300000
20
550000
46
2
3
0
bond
prem
oldmat
oldrate
oldflot
newmat
newrate
newflot
tax
t
overlap
cashout
callcos
extra
cashin
1330000
540000
378000
90503.646
$
%
yr
%/yr
$
yr
%/yr
$
%
times/yr
mo
$
$
$
$
ts_am
2875
$
int_sav
87628.646
$
ts_unam
138000
$
npv_bt
npv_at
651028.08
$
$
12.377247
principal amount of bonds
call price premium over face value
remaining term of old bonds
interest rate on old bonds
old bond unamortized flotation costs
term of new bonds
interest rate on new bonds
new bond flotation costs
tax rate
frequency of annuity for npv
duration both issues are outstanding
initial cash outflow to refund
after-tax cast of call premium
old bonds interest during overlap pd.
total cash savings from
refunding (per annuity pd.)
tax savings from amortizing new
flotation costs (per annuity pd.)
after-tax interest savings from
refunding (per annuity pd.)
tax savings from immediately deducting
unamortized flotation cost of old bond
npv of refund: before-tax cost of debt
npv of refund: after-tax cost of debt
When the new bonds can be sold at an annual interest rate below 12.4%, the refunding decision becomes
profitable to the company.
Convertible Debt Analysis
Filename: CONVERT
This model is designed to analyze convertible debentures, including risk and return measures useful in
determining the value of these securities.
Reference: Weston, J. Fred, and Brigham, Eugene F. Managerial Finance. 7th ed. Hinsdale, Illinois: The
Dryden Press, 1981. Pages 878-889.
Essential information
This model deals with the valuation of debt securities convertible into a specified number of shares of common
stock in the issuing company. The risk and return measures computed in the model aid in deciding whether to
invest in the common stock of a company or in a convertible debt issue with the potential to convert the debt
into common stock on a favorable basis in the future. These risk and return measures compare the convertible
debt issue to similar straight debt bond issues without the option to convert to stock, quantifying the extra
premium paid and the reduced yield received with the option to convert. This model evaluates several
parameters which are useful in analysis of convertible issues but does not solve for theoretical fair market price
of the convertible issue.
Both potential investors and issuers of securities can use this model. Investors can analyze an existing market
price and potential issuers can analyze the combinations of conversion price, premium over current stock
price, and interest rate that may be acceptable to the investor.
There are several considerations to keep in mind when using the model:
•
•
•
•
The model does not take into account the impact of a call provision, the tax bracket of the investor,
limitations on the conversion time period, or the volatility of the stock price. The conversion ratio,
cr, and the conversion price, cp, are assumed to remain constant over the span of the investment.
Quarterly, semiannual, or annual coupon payments can be specified by entering the appropriate value
for t, frequency of coupon payments. The model assumes interest compounding at the same rate as
the coupon payments.
When the yield to maturity of the convertible issue, y2, or the straight debt issue, yl, is set to zero, a
division-by-zero error will result.
Iterative solution is necessary when finding y2, the yield to maturity of the convertible issue, or yl, the
yield to maturity of a comparable straight debt issue.
Sample solutions
Example 1 determines some of the risk and return factors applicable in purchasing a convertible debt issue.
Example 2 analyzes the characteristics of a convertible debt issue might be considered by the issuer.
Example 1: Comparison of convertible debt and straight debt
A 12%, 20-year convertible debt issue is selling at a price 90. The coupon payments occur annually. The yield
required in the market on non-convertible debt issues of comparable quality and time to maturity is 16%. The
issue is convertible into 20 shares of common stock per $1000 of face value. The current price of the common
stock is $42 per share. Determine the downside risk from the conversion price and the premium of the parity
price over the stock price.
This problem requires Iterative solution to determine y2. To solve this problem, enter the following input values
and guesses:
Status Variable Name
fvb
dp
t
r
n
G
y2
y1
cr
sp
Input Value
1000
900
1
12
20
12
16
20
42
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** CONVERTIBLE DEBT ANALYSIS **
900
1
12
20
16
20
42
fvb
dp
t
r
a
n
y2
y1
bv
risk
cr
cp
par
sp
sv
prem
120
13.463348
762.84636
17.979195
50
45
840
7.1428571
$
$
times/yr
%/yr
$
yr
%/yr
%/yr
$
%
shares
$/share
$/share
$/share
$
%
face value of convertible bond
current convertible bond price
freq. of coupon payment (conv. issue)
coupon rate
(conv. issue)
coupon payment (conv. issue)
time to maturity (conv. issue)
yield to maturity (conv. issue)
yield to maturity (straight debt issue)
bond (downside) value of convertible
downside risk from convertible bond
price to bond value
conversion ratio (shares per bond)
conversion stock price
conversion parity price
current stock price
total stock value (current stock price)
parity price premium over stock price
The downside risk from the current conversion price, risk, represents the percentage by which the current price
exceeds the bond value of the issue. If the bond were suddenly to trade at its bond value only, its price could
be expected to fall about 18%. The premium of the conversion parity price of the stock above the selling price,
indicated by prem, is about 7%. The stock price does not have far to rise before it may be profitable to convert.
(An analysis based on expectations about stock prices is beyond the scope of this model.)
Example 2: Interest rate on a convertible debt issue
A company is interested in estimating the required interest rate, or coupon, on a planned 10-year convertible
debenture issue with a $1000 face value. The debentures are expected to be issued at par ($1000 per $1000
of face value). The yield to maturity required in the market for non-convertible debt of comparable quality is
14%. The acceptable bond value premium of convertible debt price over straight debt price appears from
market indicators to be 15%. At what rate could the company market a convertible issue conforming to the
above constraints?
This problem requires Iterative solution to determine y2. To solve this problem, enter the following input values
and guesses:
Status Variable Name
fvb
dp
t
n
G
y2
y1
risk
Input Value
1000
1000
2
10
12
14
15
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** CONVERTIBLE DEBT ANALYSIS **
1000
1000
2
10
14
15
fvb
dp
t
r
a
n
y2
y1
bv
risk
11.537576
57.687879
11.537576
869.56522
$
$
times/yr
%/yr
$
yr
%/yr
%/yr
$
%
face value of convertible bond
current convertible bond price
freq. of coupon payment (conv. issue)
coupon rate
(conv. issue)
coupon payment (conv. issue)
time to maturity (conv. issue)
yield to maturity (conv. issue)
yield to maturity (straight debt issue)
bond (downside) value of convertible
downside risk from convertible bond
price to bond value
The issue can be marketed at a coupon rate of approximately 11.5% per year.
Financial Statement Ratio Analysis
Filename: RATIO
This model is designed to compute a wide range of financial ratios that are used to analyze corporate financial
statements. These ratios concern profitability, liquidity, leverage, and operating activity.
Reference: Weston, J. Fred, and Brigham, Eugene F. Managerial Finance. 7th ed. Hinsdale, Illinois: The
Dryden Press, 1981. Chapter 7.
Essential information
The variables in this model are divided into five groups. The first group contains values from the financial
statements (assets, liabilities, equity, and income). The remaining four groups contain ratios and other
parameters that are useful in analysis of profitability, liquidity, leverage, and operating activity. The model
would typically be used to derive the variables in the last four groups from those in the first group.
There are several considerations to keep in mind when using the model:
•
•
•
•
•
•
•
Study of financial ratios is only a cursory means of reviewing a company's financial condition. The most
meaningful ratio information is obtained by reviewing the company's performance over several years to
identify cycles and trends. To reach a reliable conclusion on the company's prospects, supplementary
analysis of the text and footnotes of financial reports and information from third parties is necessary.
There are no absolute standards that define ratios as good or bad.
Several of the variables in the section "FROM THE FINANCIAL STATEMENT" can be computed, rather
than entered. Current assets, c_asset, can be computed from cash, cash, marketable securities, and
cash equivalents; acc_r, accounts receivable; inv, inventory; and oth_c_a, other current assets. Total
assets, t_asset, can be computed from c_asset, current assets; and f _asset, fixed assets. Total
capitalization, capital, can be computed from lt_debt, total long-term debt; and equity, total equity. Net
income, net_inc, can be computed from sales, sales; oth_inc, other income; cgs, cost of goods sold;
opr, operating expense; depr, depreciation expense; int, total interest expense; and inc_tax, income
taxes.
The variables in the section labeled "ACTIVITY ANALYSIS" all require an annual sales level. When a
period shorter than a year is being reviewed, one possible way to calculate these ratios is to convert
interim period sales to an annual rate. This approach can only be used when sales are not seasonal.
Another approach is to sum quarterly sales over the four quarters preceding the balance sheet date.
The variables oth_inc, other income, and oth_c_a, other current assets, are automatically set to zero if
not given.
Variables given in units of "$/yr" are measured over the year previous to the financial statement.
For easier entry, it is possible to enter all dollar amounts in thousands of dollars instead of dollars.
Results for ratios and percentages will remain the same, and any derived dollar amounts will also be in
thousands of dollars. Calculation units should be changed from "$" to "$*1000" or from "$/yr" to
"$*1000/yr", but the same answers will appear whether or not the units have been changed.
This model can easily be altered to account for factors not included or to analyze financial statements in
more detail. Appropriate variables can be added to the Variable Sheet and entered into the appropriate
rules on the Rule Sheet. Several years can be reviewed and compared by making the variables into
lists.
Sample solutions
The example analyzes a hypothetical annual report. All figures below are as they would appear in a corporate
annual financial report and are specified in thousands of dollars.
Example: Ratio analysis of annual report
The asset portion of the balance sheet of JDB Industries shows cash assets of $8,200, along with marketable
securities of $61,400. The balance of accounts receivable is $203,700. Inventories are valued at $110,500,
and other current assets and prepaid expenses are $41,900. Adding fixed assets brings the total amount of
assets to $1,204,200.
On the liability side of the balance sheet are current liabilities of $234,000. There is also long-term debt of
$358,500 and stock-holders' equity totaling $611,700.
Sales for 1982 comprised $1,202,458, while the cost of goods sold totaled $560,212. Operating expenses
were $429,586, exclusive of $62,844 in depreciation and amortization. Interest expense for the year was
$35,850, and income tax expense was $30,000.
Based on this information, evaluate the financial position of JDB Industries.
Since the numbers given are consistently in thousands of dollars, they can be entered directly. To solve this
problem, enter the following input values:
Input Value
8200 + 61400
203700
110500
41900
1204200
234000
358500
611700
1202458
560212
62844
429586
35850
30000
Variable Name
cash
acc_r
inv
oth_c_a
t_asset
c_liab
lt_debt
equity
sales
cgs
depr
opr
int
inc_tax
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** FINANCIAL RATIO ANALYSIS **
69600
203700
110500
41900
1204200
234000
358500
611700
1202458
560212
62844
cash
acc_r
inv
oth_c_a
c_asset
f_asset
t_asset
c_liab
lt_debt
equity
capital
sales
oth_inc
cgs
depr
425700
778500
970200
0
$
$
$
$
$
$
$
$
$
$
$
$/yr
$/yr
$/yr
$/yr
cash, marketable securit., cash equiv.
accounts receivable
inventory
other current assets
total current assets
net fixed assets
total assets
total current liabilities
total long-term debt
total equity
total capitalization
sales
other income
costs of goods sold
depreciation expense
429586
35850
30000
opr
int
inc_tax
net_inc
83966
$/yr
$/yr
$/yr
$/yr
operating expenses
total interest expense
income tax expense
net income
ebit
np_marg
roi
roe
g_marg
op_mar1
op_mar2
149816
6.983
6.973
13.727
53.411
12.459
17.685
$/yr
%
%/yr
%/yr
%
%
%
PROFITABILITY ANALYSIS:
earnings before interest and taxes
net profit margin
return on investment
return on equity
gross margin
pre-tax operating margin
pre-tax pre-depr. operating margin
1.819
191700
1.168
39300
times
$
times
$
net working capital
quick ratio or acid test
net quick assets
dc_rati
de_rati
int_ear
36.951
58.607
4.179
%
%
times
LEVERAGE ANALYSIS:
debt to capital ratio
debt to equity ratio
pre-tax interest times earned
inv1_tu
inv2_tu
as_turn
wc_turn
fa_turn
coll_pd
10.882
5.07
.999
6.273
1.545
60.985
times/yr
times/yr
times/yr
times/yr
times/yr
days
ACTIVITY ANALYSIS:
inventory turnover (related to sales)
inventory turnover (related to cgs)
asset turnover
working capital turnover
fixed asset turnover
average collection period
LIQUIDITY ANALYSIS:
c_ratio
wk_cap
q_ratio
q_asset
All computed dollar values are in thousands of dollars. The investor would now compare the ratios for
profitability, liquidity, leverage, and operating activity to industry averages and to the same ratios for JDB
Industries in earlier years as an aid in investment decisions.
Analysis of Operating and Financial Leverage
Filename: LEVERAGE
This model is designed to examine a firm's performance in terms of leverage, the degree to which a given
change in sales affects profits. The model is also concerned with breakeven analysis using assumptions about
a firm's production cost structure and capital structure.
Reference: Weston, J. Fred, and Brigham, Eugene F. Managerial Finance. 7th ed. Hinsdale, Illinois: The
Dryden Press, 1981. Pages 555-574.
Essential information
Leverage is a concept which can be used to quantify (as a ratio) the extent of magnification, positive or
negative, in earnings due to a change in the level of sales. High leverage represents a condition in which an
increase in sales will cause a relatively high increase in earnings, while low leverage means that an increase in
sales causes only a small increase in earnings.
Two distinct types of leverage are operating leverage and financial leverage. Operating leverage depends on
the proportion of fixed costs to variable costs in the total production costs of the firm. Financial leverage
depends on the proportion of capital that bears fixed charges (debt and preferred stock) to equity in the
capitalization of the firm. The model analyzes operating, financial, and total leverage using units sold, price per
unit, variable cost per unit, total fixed costs, interest charges, preferred dividends, and amount of common
stock outstanding. The model can evaluate various parameters at the breakeven point or compare the impact
on earnings per share of two financing alternatives.
The model can be used to examine one capital structure or operating cost structure, or to compare two
proposed changes in capital structure. These uses of the model are described below:
•
•
•
To examine one alternative, enter the known values and solve.
To compare the impact of leverage on two alternative financing proposals, enter both alternatives on
the Variable Sheet. Then solve to find the indifference ebit, i_ebit. For ebits above this value, the
financing alternative with the higher total leverage, Tlev, is more profitable. For ebits below this value,
the reverse is true.
The model need not be solved completely. Financial leverage and operating leverage can be examined
independently of each other. For example, the financial leverage variables need not be entered for
breakeven analysis of the firm's production cost structure.
Sample solutions
The example finds the level of financial and operating leverage for a firm and explores financing alternatives.
Example: Leverage and financing alternatives
A manufacturer of screws is examining financing alternatives. The total volume sold by the company is
100,000,000 screws per year. Variable costs for the screws come to 1.5 cents apiece, and they sell for 3.5
cents per screw. The company must also cover fixed costs of $1,000,000. It has 200,000 shares of common
stock outstanding and debt that requires $750,000 of annual interest payments. No preferred stock is
outstanding. The company is taxed at a 46% annual rate. Find its degree of total leverage and breakeven unit
sales.
To solve this problem, enter the following input values:
Variable Name
price
vc
fc
units
tax
avgsh1
iexp1
pdiv1
Input Value
.035
.015
1E6
1E8
46
200000
750000
0
FINANCIAL MANAGEMENT SOLVERPACK
PART 3 OF 3
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** OPERATING AND FINANCIAL LEVERAGE **
.035
.015
1E6
1E8
46
200000
750000
0
price
vc
fc
units
tax
ebit
oplev
be_unit
avgsh1
iexp1
pdiv1
fixch1
finlev1
earn1
eps1
Tlev1
U_be1
R_be1
1000000
200
5E7
$/unit
$/unit
$
units
%
$
%
units
750000
400
135000
.675
800
87500000
3062500
shares
$
$
$
%
$
$/share
%
units
$
unit sales price
variable cost per unit
total fixed costs
total number of units sold
tax rate
earnings before interest and taxes
degree of operating leverage
breakeven unit sales
(no financial leverage)
FINANCING ALTERNATIVE #1
average # of common shares outstanding
total interest expense
total preferred dividend requirement
total interest & preferred dividends
degree of financial leverage
net income
earnings per share
degree of total leverage
breakeven unit sales (total leverage a)
breakeven revenues
Total leverage is 800%. Breakeven unit sales of 50 million screws would provide earnings before interest and
taxes of $750,000, just enough to cover the annual interest expense.
Now the company must raise $1,000,000 to modernize its equipment. Two alternatives are being considered:
selling common stock at $50 per share or selling preferred stock with a 7% dividend. Taking into account only
earnings per share and leverage, which alternative is preferable?
Enter the common stock alternative by altering the following input value:
Variable Name
avgsh1
Input Value
200000 + 1E6/50
This value represents the old stock, plus $1 million of new stock at $50 per share.
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** OPERATING AND FINANCIAL LEVERAGE **
.035
.015
1000000
1E8
46
220000
750000
0
price
vc
fc
units
tax
ebit
oplev
be_unit
avgsh1
iexp1
pdiv1
fixch1
finlev1
earn1
eps1
Tlev1
U_be1
R_be1
1000000
200
5E7
$/unit
$/unit
$
units
%
$
%
units
750000
400
135000
.61363636
800
87500000
3062500
shares
$
$
$
%
$
$/share
%
units
$
unit sales price
Variable cost per unit
total fixed costs
total number of units sold
tax rate
earnings before interest and taxes
degree of operating leverage
breakeven unit sales
(no financial leverage)
FINANCING ALTERNATIVE #1
average # of common shares outstanding
total interest expense
total preferred dividend requirement
total interest & preferred dividends
degree of financial leverage
net income
earnings per share
degree of total leverage
breakeven unit sales (total leverage a)
breakeven revenues (total leverage ana)
The total leverage, Tlev1, will be used to compare the two alternatives. Earnings per share total a little over
$0.61 per share.
Now enter the preferred stock alternative values:
Variable Name
avgsh2
iexp2
pdiv2
Input Value
200000
750000
.07 * 1E6
The value for pdiv2 represents a 7% dividend on $1 million of preferred stock. Solve the model. The solution is
shown in Figure 3.
Figure 3
Variables
St Input
Name
Output
Unit
Comment
** OPERATING AND FINANCIAL LEVERAGE **
.035
.015
1000000
1E8
46
price
vc
fc
units
tax
ebit
1000000
$/unit
$/unit
$
units
%
$
unit sales price
Variable cost per unit
total fixed costs
total number of units sold
tax rate
earnings before interest and taxes
oplev
be_unit
220000
750000
0
200000
750000
70000
200
5E7
%
units
750000
400
135000
.61363636
800
87500000
3062500
shares
$
$
$
%
$
$/share
%
units
$
FINANCING ALTERNATIVE #1
average # of common shares outstanding
total interest expense
total preferred dividend requirement
total interest & preferred dividends
degree of financial leverage
net income
earnings per share
degree of total leverage
breakeven unit sales (total leverage a)
breakeven revenues (total leverage ana)
avgsh2
iexp2
pdiv2
fixch2
finlev2
earn2
eps2
Tlev2
U_be2
R_be2
879629.63
830.76923
65000
.325
1661.5385
93981481
3289351.9
shares
$
$
$
%
$
$/share
%
units
$
FINANCING ALTERNATIVE #2
average # of common shares outstanding
total interest expense
total preferred dividend requirement
total interest & preferred dividends
degree of financial leverage
net income
earnings per share
degree of total leverage
breakeven unit sales (total leverage a)
breakeven revenues (total leverage ana)
i_ebit
2175925.9
$
i_unit
i_eps
158796296
3.5
units
$/share
avgsh1
iexp1
pdiv1
fixch1
finlev1
earn1
eps1
Tlev1
U_be1
R_be1
degree of operating leverage
breakeven unit sales
(no financial leverage)
COMPARISON OF FINANCING ALTERNATIVES
indifference ebit for the two
financing alternatives
unit sales at indifference ebit
earnings/share at indifference ebit
The total leverage, Tlev2, is higher for alternative 2. With preferred stock financing, the earnings per share
have dropped to $0.325 per share. The common stock alternative is preferable because it causes less dilution
in earnings per share.
Both financing alternatives have earnings per share, ind_eps, of $3.50 at the indifference ebit indicated by
ind_ebit. The unit sales at this level are indicated by ind_unit. (Only the first seven characters of these last two
variable names show up on the screen above.) Since these values are higher than the ebit and unit sales
originally specified, the company is presently operating under the point of indifference. This indicates that the
alternative with the lower total leverage, in this case financing by common stock, is preferable. This is
confirmed by the higher earnings per share in the common stock case.
Cost of Equity Capital: Capital Asset Pricing Model
Filename: CAPM
This model is designed to estimate a firm's cost of equity capital by relating the "beta coefficient," a measure of
the systematic risk of the firm's equity capital, to the expected market return and the expected risk-free interest
rate. An alternative method for determining a firm's cost of equity capital is given in Cost of Equity Capital:
Dividend Growth Model (DIVGROW).
Reference: Van Horne, James C. Financial Management and Policy. 5th ed. Englewood Cliffs, New Jersey:
Prentice-Hall, 1980. Chapter 3.
Essential information
The model includes the security market line formula, which provides a description of the expected return-risk
relationship for a firm's equity. In addition, the model relates a firm's beta coefficient to its leverage and
includes the necessary calculations to estimate beta and cost of equity capital under different leverage
assumptions.
Several points should be kept in mind when using the model:
•
•
•
Bankruptcy costs are assumed to be zero for purposes of the model. A firm's assets can be sold quickly
without disposal or legal costs. Transaction costs, information costs, and borrowing and lending rate
differentials are not considered.
The values for rf, risk-free interest rate; rm, expected market rate of return; and beta1, the firm's beta
coefficient, must be provided by the user. An estimate for a firm's beta can be obtained from various
financial services. The value for beta can also be calculated by statistical methods from market
information.
The variables in this model can be divided into three groups: The first group consists of rf, rm, tax, and
b_unlev, all variables that are assumed to be constant as the firm's leverage changes. The variables
with "1" in their variable name represent values from the current capital structure of the firm. The
corresponding variables with "2" in their name represent values for a proposed capital structure.
Sample solutions
The example that follows is divided into three parts. The first part estimates a firm's current cost of equity and
its unlevered beta. The second part analyzes how a shift in the leverage affects the cost of equity capital. The
third part of the example demonstrates how to use the model to determine the appropriate leverage to achieve
a specified beta.
Example: Cost of equity and leverage
The capital structure of Insolvent, Inc. is 35% debt, and the firm's stock has a beta coefficient of .9. The
corporate tax rate is 46% and the firm's cost of debt is 14%. The risk-free interest rate estimated by treasury
bills is 12%, and the estimated market return over the next year is 18.5%. Determine Insolvent's theoretical
cost of equity capital, weighted average cost of capital, and unlevered beta coefficient. Then, by reducing
Insolvent's financial leverage to zero, find the minimum cost of equity capital in the described economic
environment. Finally, if the company were to mirror the market in performance, it would have a beta of 1. What
is the required debt-to-capitalization ratio to achieve a beta of 1?
To solve the first part of the problem concerning the unlevered beta, enter the following values for the indicated
variables:
Variable Name
rf
rm
tax
Input Value
12
18.5
46
beta1
debt1
kd1
.9
35
14
Solve the model. The solution to this portion of the problem is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
CAPITAL ASSET PRICING MODEL **
12
18.5
46
.9
35
14
rf
rm
tax
b_unlev
%/yr
%/yr
%
.69725864
beta1
debt1
derat1
kd1
ke1
wacc1
%
53.846154
17.85
14.2485
%
%/yr
%/yr
%/yr
short-term risk-free interest rate
expected return on market portfolio
tax rate
unlevered beta
CURRENT CAPITAL STRUCTURE
current beta of the firm's equity
existing debt percentage of capital
(market value basis)
existing debt to equity ratio
current cost of debt
current cost of equity capital
current weighted avg cost of capital
The estimated cost of equity capital is about 18%, and the weighted average marginal cost of capital with a
35% debt ratio is near 14%. The unlevered beta is approximately 0.70.
If, given the conditions above, Insolvent, Inc. reduces its financial leverage to zero, what would be the
minimum cost of equity capital?
To solve this problem, enter the following value:
Variable Name
debt2
Input Value
0
Solve the model. The solution to this portion of the problem is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
CAPITAL ASSET PRICING MODEL **
12
18.5
46
rf
rm
tax
b_unlev
%/yr
%/yr
%
.69725864
short-term risk-free interest rate
expected return on market portfolio
tax rate
unlevered beta
CURRENT CAPITAL STRUCTURE
.9
35
14
0
beta1
debt1
derat1
kd1
ke1
wacc1
%
53.846154
17.85
14.2485
beta2
debt2
.69725864
derat2
kd2
ke2
wacc2
0
%
%/yr
%/yr
%/yr
%
16.532181
%
%/yr
%/yr
%/yr
current beta of the firm's equity
existing debt percentage of capital
(market value basis)
existing debt to equity ratio
current cost of debt
current cost of equity capital
current weighted avg cost of capital
PROPOSED CAPITAL STRUCTURE
beta estimated for proposed leverage
proposed debt percentage of capital
(market value basis)
proposed debt to equity ratio
cost of debt for proposed leverage
cost of equity capital for proposed leverage
weighted average cost of capital
for proposed leverage
The solution shows that with all-equity capital structure, the estimated cost of equity capital is about 16.5%.
Insolvent's management would like their stock to perform as close to the market as possible. A beta of 1
requires what amount of leverage for the firm?
To solve this problem, enter or revise the following input values:
Status Variable Name
beta2
B
debt2
Input Value
1
Solve the model. The solution is shown in Figure 3.
Figure 3
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
CAPITAL ASSET PRICING MODEL **
12
18.5
46
.9
35
14
rf
rm
tax
b_unlev
%/yr
%/yr
%
.69725864
beta1
debt1
derat1
kd1
ke1
wacc1
%
53.846154
17.85
14.2485
%
%/yr
%/yr
%/yr
short-term risk-free interest rate
expected return on market portfolio
tax rate
unlevered beta
CURRENT CAPITAL STRUCTURE
current beta of the firm's equity
existing debt percentage of capital
(market value basis)
existing debt to equity ratio
current cost of debt
current cost of equity capital
current weighted avg cost of capital
PROPOSED CAPITAL STRUCTURE
1
beta2
debt2
derat2
kd2
ke2
wacc2
44.569223
%
80.405192
%
%/yr
%/yr
%/yr
18.5
beta estimated for proposed leverage
proposed debt percentage of capital
(market value basis)
proposed debt to equity ratio
cost of debt for proposed leverage
cost of equity capital for proposed leverage
weighted average cost of capital
for proposed leverage
The degree to which leverage would have to be increased is indicated by the variable debt2. To find the
resulting weighted average cost of capital, it is necessary to estimate a value for cost of debt with this capital
structure.
With the above capital structure, what is the weighted average cost of capital for a cost of debt of 15%?
To solve this problem, enter the following input value:
Variable Name
kd2
Input Value
15
Solve the model. The solution is shown in Figure 4.
Figure 4
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
CAPITAL ASSET PRICING MODEL **
12
18.5
46
.9
35
14
1
15
rf
rm
tax
b_unlev
%/yr
%/yr
%
.69725864
beta1
debt1
derat1
kd1
ke1
wacc1
beta2
debt2
derat2
kd2
ke2
wacc2
%
53.846154
17.85
14.2485
%
%/yr
%/yr
%/yr
44.569223
%
80.405192
%
%/yr
%/yr
%/yr
18.5
13.864801
short-term risk-free interest rate
expected return on market portfolio
tax rate
unlevered beta
CURRENT CAPITAL STRUCTURE
current beta of the firm's equity
existing debt percentage of capital
(market value basis)
existing debt to equity ratio
current cost of debt
current cost of equity capital
current weighted avg cost of capital
PROPOSED CAPITAL STRUCTURE
beta estimated for proposed leverage
proposed debt percentage of capital
(market value basis)
proposed debt to equity ratio
cost of debt for proposed leverage
cost of equity capital for proposed leverage
weighted average cost of capital
for proposed leverage
At this cost of debt the weighted average cost of capital is about 13.9%.
Cost of Equity Capital: Dividend Growth Model
Filename: DIVGROW
This model has several applications for valuing the equity of a firm based on an expected perpetual dividend
stream that grows at a constant rate. These applications include estimation of the firm's cost of equity capital,
the fair market price of its common stock, the equilibrium price-earnings (p-e) ratio, and the weighted average
cost of capital. Also, given a specified p-e ratio and stock price, either the cost of equity capital or the expected
dividend growth rate may be calculated.
Reference: Weston, J. Fred, and Brigham, Eugene F. Managerial Finance. 7th ed. Hinsdale, Illinois: The
Dryden Press, 1981. Pages 594-599.
Essential information
This model relies on the constant dividend growth model of common stock valuation as a theoretical base. For
an alternative method of estimating cost of equity capital and weighted average cost of capital, see Cost of
Equity Capital: Capital Asset Pricing Model (CAPM).
Take the following considerations into account when using the model:
•
•
To ensure accuracy in the weighted cost of capital estimate, wacc, the debt and common equity
proportions of capitalization should be based on their total market value. For a firm with privately held
debt or equity, this requires an estimation of fair market value. The marginal cost of debt can be
determined from recent debt issues by the firm or similar firms.
The model provides the option of discrete or continuous compounding. For the variable type, the user
should enter 1 for continuous or 2 for discrete.
Sample solutions
The example first estimates the cost of equity capital using the dividend growth model. Then it examines the
effects of changes in the dividend payout ratio on cost of capital.
Example: Dividend growth and cost of equity capital
The XYZ company has a capital structure of 70% common equity with a current dividend of $.90 per share.
This represents a 30% payout ratio at the current earnings level. The company estimates 10% per annum
dividend growth for the foreseeable future. With a 46% tax rate, stock selling at $25 a share, and a recent debt
issue marketed at rate of 10%, what is XYZ's cost of equity capital and the weighted average cost of capital?
Also find the earnings per share and the price-earnings ratio.
Enter the following values for the specified variables to determine the costs:
Variable Name
type
common
Input Value
1
70
kd
tax
div
payout
g
stock
10
46
.9
30
10
25
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
DIVIDEND GROWTH MODEL **
1
type
debt
common
30
70
%
%
deratio
kd
tax
div
payout
eps
peratio
g
stock
ke
wacc
42.857143
%
%/yr
%
$/share
%/yr
$/share
10
46
.9
30
10
25
3
8.3333333
13.6
11.14
%/yr
$/share
%/yr
%/yr
type of compounding assumed:
1 - continuous; 2 - discrete
debt % of capital (market value basis)
common equity percentage of capital
(market value basis)
debt to equity ratio
marginal cost of debt
tax rate
current annual dividend
payout ratio (dividends % of earnings)
earnings per share
price-earnings ratio
expected growth rate of dividends
current equilibrium stock price
cost of common equity
weighted average cost of capital
The estimated cost of equity capital is 13.6%, and the weighted average cost of capital is 11.14%.
Another firm, ABC Inc., has characteristics identical to XYZ Inc., with the exception of a 40% payout ratio. How
does ABC's cost of equity capital compare with that of XYZ?
To solve this problem, enter the following input values and status:
Status Variable Name
B
div
payout
I
eps
Input Value
40
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** COST OF EQUITY CAPITAL:
DIVIDEND GROWTH MODEL **
1
type
debt
common
30
70
%
%
deratio
kd
tax
div
payout
eps
peratio
g
stock
ke
wacc
42.857143
%
%/yr
%
$/share
%/yr
$/share
10
46
40
3
10
25
1.2
8.3333333
14.8
11.98
%/yr
$/share
%/yr
%/yr
type of compounding assumed:
1 - continuous; 2 - discrete
debt % of capital (market value basis)
common equity percentage of capital
(market value basis)
debt to equity ratio
marginal cost of debt
tax rate
current annual dividend
payout ratio (dividends % of earnings)
earnings per share
price-earnings ratio
expected growth rate of dividends
current equilibrium stock price
cost of common equity
weighted average cost of capital
The cost of equity capital is 14.8% and the weighted average cost of capital is 11.98% for firm ABC. Both costs
are greater than those for firm XYZ.
Black-Scholes Option Pricing
Filename: OPTIONS
(The AMOPT and BINOPT models offer alternative methods for evaluating options values.)
The OPTIONS model is designed to determine the market value of a call or put option which entitles the holder
to purchase (call option) or sell (put option) a particular common stock at a fixed price at some future date. It
can also be used to calculate the hedge ratios for both types of options. Figure 1 is a graphic representation of
the value (net after option purchase price) of call and put options at maturity in relation to a fixed current stock
price. Figure 2 illustrates the call and put prices for a fixed exercise price.
Reference: Van Horne, James C. Financial Management and Policy. 5th ed. Englewood Cliffs, New Jersey:
Prentice-Hall, 1980. Chapter 4.
Figure 1
Figure 2
Essential information
The equations in the model are taken from the widely accepted model of options valuation developed by
Fischer Black and Myron Scholes ("The Pricing of Options and Corporate Liabilities," Journal of Political
Economy, May/June, 1973). Some modifications to the original theory concerning real-world assumptions,
supported by empirical research, are included in the model.
There are several considerations to keep in mind when using the model:
•
•
The Black-Scholes pricing model uses the Cumulative Standard Normal Density Distribution.
The Black-Scholes theory of option value assumes a European option, which can only be exercised on
its maturity date, rather than an American option, which is exercisable at any time up to the expiration
date. An American call option usually should not be exercised until its expiration date, in which case the
•
•
•
•
•
•
•
Black-Scholes valuation model is valid. However, the model does not handle some special
circumstances where an American option on a dividend-paying stock may be worth more than a
European option.
Valuation of the put option in this model is applicable to a European option only.
In computing the current value of the stock from the viewpoint of the option holder, all expected
dividends up to the option expiration date are assumed to have been paid.
Transaction costs or market imperfections associated with option and stock trading are not used in the
determination of market value. Solutions of this model can be used in conjunction with the OPWRITE
model, which does account for some transaction costs.
The model assumes that stock prices behave randomly over time and the historical variance of the
return on the stock will remain the same over the life of the option.
The model assumes that the short-term risk-free interest rate for borrowing and lending will remain
constant over the life of the option.
If no values are entered for the dividend payments, the model assumes none exist and sets the values
to zero. If there is to be no dividend payment at all, pvdiv, the present value of the dividends, must be
set to zero.
Estimation of the market value of a firm's common equity is also possible with this model.
Sample solutions
Example 1 involves call and put options. Example 2 examines an equity evaluation for a corporation.
Example 1: Call and put options
Tordo Machine stock is currently selling at $82 per share with a quarterly dividend of $1 expected in 3 months.
Historically, the return on the stock of the company has had a standard deviation of .275. The risk-free interest
rate is 10%. Determine the price and hedge ratio of a call option to buy shares of Tordo Machine Company
stock at $90 with a maturity of 3.5 months. What are the price and hedge ratio for a put option with the same
exercise price and expiration date?
To solve this problem, enter the following input values:
Variable Name
stock
exercis
rate
sigma
n
div1
t1
Input Value
82
90
10
.275
3.5
1
3
Solve the model. The solution is shown in Figure 3.
Figure 3
Variables
St Input
82
90
10
.275
Name
stock
exercis
rate
sigma
Output
Unit
$/share
$/share
%/yr
Comment
** BLACK-SCHOLES OPTION PRICING **
current stock price
exercise price
short-term risk-free interest rate
standard deviation of continuously
compounded annual return on stock
3.5
1
3
n
div1
div2
t1
t2
pvdiv
stock1
c_hedge
c_optio
p_hedge
p_optio
c
d
0
0
.97530991
81.02469
.33115697
2.426733
.66884303
8.8149547
.27919397
-.4367207
mo
$/share
$/share
mo
mo
$/share
$/share
$/share
$/share
length of option contract
1st dividend per share
2nd dividend per share
time before 1st dividend is to be paid
time before 2nd dividend is to be paid
present value of dividends
current stock price excluding dividend
call option hedge ratio; area under
normal curve up to d
call option price
put option hedge ratio
put option price
area under normal curve up to d2
normalized argument of distrib. fun.
The call option has a theoretical price of $2.42 per share. A hedged position requires that 33 shares of Tordo
stock are owned for every call option of 100 shares written.
The put option has a theoretical price of $8.81 for which the put option writer can establish a hedged position
by shorting 67 shares of Tordo stock for every put option of 100 shares written.
Example 2: Debt and equity
The total current value of the Hottop Corporation is $5,000,000 and the company pays no dividends. The face
value of the corporate debt (assumed to be a discount bond, on which no interest payments will be made
before maturity) is $3,750,000. The standard deviation of the return on Hottop Corporation is .25 and the riskfree interest rate is 8 percent. The corporate debt matures in 2.5 years. Estimate the market value of the firm's
equity.
To solve this problem, first change the units for n to yr. Then enter the following input values:
Input Value
5000000
3750000
8
.25
2.5
0
Variable Name
stock
exercis
rate
sigma
n
pvdiv
Solve the model. The solution is shown in Figure 4.
Figure 4
Variables
St Input
Name
5000000
3750000
8
.25
stock
exercis
rate
sigma
2.5
n
div1
Output
Unit
$/share
$/share
%/yr
0
mo
$/share
Comment
** BLACK-SCHOLES OPTION PRICING **
current stock price
exercise price
short-term risk-free interest rate
standard deviation of continuously
compounded annual return on stock
length of option contract
1st dividend per share
0
div2
t1
t2
pvdiv
stock1
c_hedge
0
0
0
c_optio
p_hedge
p_optio
c
d
2009734.5
.07615906
79974.776
.84992378
1.4313913
5000000
.92384094
$/share
mo
mo
$/share
$/share
$/share
$/share
2nd dividend per share
time before 1st dividend is to be paid
time before 2nd dividend is to be paid
present value of dividends
current stock price excluding dividend
call option hedge ratio; area under
normal curve up to d
call option price
put option hedge ratio
put option price
area under normal curve up to d2
normalized argument of distrib. fun.
The theoretical value of the equity of Hottop Corporation is $2,009,734.50.
Two other option pricing models are included with this SolverPack.
AMOPT includes formulas for American call and put options. The model is based on the paper "Efficient
Analytic Approximation of American Option Values" by Giovanni Barone-Adesi and Robert E. Whatley,
published in the Journal of Finance, June, 1987. The AMOPT model allows for the possibility of early exercise
of the options. It also allows for the effects of any number of known dividends on the value of call options.
BINOPT provides another alternative for options pricing. The method used is based on the Binomial Option
Pricing algorithm discussed in the book, Options Pricing, by Robert A. Jarrow, PhD and Andrew Rudd, PhD.
Homewood, Illinois: Richard D. Irwin, Inc., 1983. The BINOPT model essentially divides the option duration into
a finite number of intervals and uses the binomial probability distribution to estimate the likelihood that the
stock will increase or decrease during each interval. The BINOPT model also allows for any number of known
dividends.
Option Investment Performance
Filename: OPWRITE
This model is designed to determine the maximum potential profit and return for option strategies that involve
purchasing stock or selling it short in order to hedge the option position. Any strategy that involves buying stock
and writing call options or shorting stock and writing put options can be analyzed by this model. Standard
applications involve fully covered option writing, in which 100 shares of stock are bought or shorted for each
option written; and ratio writing, in which a ratio of shares per option is bought or shorted to achieve an
expected return approximating the risk-free interest rate.
Reference: Riley, William B., Jr. and Montgomery, Austin H., Jr. Guide to Computer-Assisted Investment
Analysis. New York: McGraw-Hill, 1982. Chapters 2 and 6.
Essential information
The model can be used in conjunction with the OPTIONS model (Black-Scholes Option Pricing), which
estimates a fair market price for a put or call option and specifies the hedge ratio for a fully hedged position.
The user should be familiar with financial investment valuation methods and option theory.
The model returns the dollar amount of investment required to implement a particular strategy; profit potential
for the strategy at the exercise price; and the range of stock prices within which the position will be profitable.
Some considerations to keep in mind when using the model to formulate a strategy are:
•
•
•
•
•
•
•
•
•
•
Only strategies that involve buying stock long and writing call options, or shorting stock and writing put
options, can be analyzed. In the analysis of call options ns represents the number of shares of stock
bought, while in the analysis of put options it represents the number of shares of stock shorted.
Enter 'put or 'call for the variable type as appropriate.
Stock commissions are entered as a percentage of share price.
Option commissions are entered as dollar amounts.
The number of options, nopt, is given with a Display unit of "options" and a Calculation unit of "shares".
The Unit Sheet contains a conversion of one option to 100 shares, and for calculations on the Rule
Sheet, the Calculation unit of "shares" is used for nopt.
When nopt, the number of shares of options written, equals or exceeds ns, the number of shares of
stock bought or shorted, the profit potential at the exercise price, eprof, represents the maximum profit
potential of the investment strategy. This includes partially or fully covered options.
When ns, the number of shares of stock bought or shorted, equals or exceeds nopt, the number of
shares of options written, the range of profitable stock prices is open-ended. For call options this means
there will be no value for uprice, upper limit of profitable stock price, while for put options this means
there will be no value for lprice, lower limit of profitable stock price.
The Black-Scholes model (OPTIONS) can be used to determine a fair option price and the required
hedge ratio.
Option commissions and maintenance costs are automatically set to zero if not given.
The variable margin represents the percentage of margin required on short stock. Since the model only
considers shorting stock in put option strategies, margin is only used for these strategies. The model
does not explicitly take into account interest that must be paid on the debit balance of the margin
account or margin requirement resulting from stock price fluctuations. These values can be entered as
maintenance costs for the variable maint, or the user may wish to add equations to the Rule Sheet to
account for them.
Sample solutions
The first example involves analysis of a call option strategy. The second analyzes a put option strategy.
Example 1: Hedged call option
An investor wants to establish a hedged position by writing a call option and purchasing stock. Analysis of call
options for Tordo Machine stock, using the Black-Scholes option pricing model (OPTIONS), indicates that a
hedged position requires a purchase of 33 shares per 100-share option written at a fair option price of $2.42
per share. The current price of the stock is $82 per share, and the exercise price is $90 per share. There is a
commission of 1.75% of the stock price, and the option is sold with a commission of $16. During the life of the
option a $1 dividend will be paid per share. Determine the profit range and investment requirements for this
stock option strategy.
To solve this problem, enter the following input values:
Variable Name
type
nopt
popt
Input Value
'call
1
2.42
eprice
comm
ns
ps
div
c
90
16
33
82
1
1.75
Solve the model. The solution is shown in Figure 1.
Figure 1
Variables
St Input
Name
Output
Unit
Comment
** OPTION INVESTMENT PERFORMANCE **
'call
1
2.42
90
16
33
82
1
1.75
type
nopt
popt
eprice
comm
maint
ns
ps
div
c
margin
0
invest
eprof
return
uprice
lprice
2527.355
423.67
16.763
93.119
76.933
options
$/share
$/share
$
$
shares
$/share
$/share
%
%
option type - 'put or 'call
number of options written
price for option on a single share
exercise price per share
total commission on options
total maintenance requirement
number of shares bought or shorted
current stock price per share
dividends per share during option life
commission on stock transaction
margin required on shorted stock
(put option only)
$
$
%
$/share
$/share
required investment (options & stock)
profit potential at exercise price
return potential at exercise price
upper limit of profitable stock price
lower limit of profitable stock price
The required investment to establish this partially covered option position is approximately $2,527. The
maximum profit potential, which occurs at the exercise price, provides a return of about 16.8% over the life of
the option. The strategy would not be profitable if the stock price rose above $93.12 per share or fell below
$76.93 per share.
Example 2: Return on put option
Consider writing four 100-share put options with an exercise price of $25 per share. The options are written at
a price of $5.25 per share. At the same time, 175 shares of stock are shorted at $25 per share, with a
commission of 1.5% and 50% margin required. The total commission for selling the options is $128. This
strategy also involves $1000 for maintenance costs and an expected dividend of $0.75 per share during the life
of the option. What is the potential return for the given strategy?
To evaluate this strategy, enter the following input values:
Variable Name
type
nopt
popt
eprice
Input Value
'put
4
5.25
25
comm
maint
ns
ps
div
c
margin
128
1000
175
25
.75
1.5
50
Solve the model. The solution is shown in Figure 2.
Figure 2
Variables
St Input
Name
Output
Unit
Comment
** OPTION INVESTMENT PERFORMANCE **
'put
4
5.25
25
128
1000
175
25
.75
1.5
50
type
nopt
popt
eprice
comm
maint
ns
ps
div
c
margin
invest
eprof
return
uprice
lprice
1281.125
1709.5
133.437
34.624
18.048
options
$/share
$/share
$
$
shares
$/share
$/share
%
%
option type - 'put or 'call
number of options written
price for option on a single share
exercise price per share
total commission on options
total maintenance requirement
number of shares bought or shorted
current stock price per share
dividends per share during option life
commission on stock transaction
margin required on shorted stock
(put option only)
$
$
%
$/share
$/share
required investment (options & stock)
profit potential at exercise price
return potential at exercise price
upper limit of profitable stock price
lower limit of profitable stock price
The maximum potential profit of $1709.50, which occurs at the exercise price, yields a return of just over 133%
over the life of the options.