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.

© Copyright 2018