Questions and Answers of Financial Modeling

A pure discount bond with maturity N is a bond with no payments at times t = 1, … , N − 1; at time t = N , a pure discount bond has a single terminal payment of both principal and interest. What
“Duration can be viewed as a proxy for the riskiness of a bond. All other things being equal, the riskier of two bonds should have lower duration.” Check this claim with an example. What is its
What is the effect on a bond’s duration of increasing the bond’s maturity? As in the previous example, use a numerical example and plot the answer. Note that as N → ∞, the bond becomes a
Suppose that the market portfolio has mean μ = 15% and standard deviation σ = 20%.a. If the risk-free rate of interest is 8% calculate the 1-period state prices for an “up” and a “down”
Your company is considering the purchase of a new piece of equipment. The equipment costs $50,000 and your analysis indicates that the PV of the future cash flows from the equipment is $45,000. Thus
Although θ is generally negative, there are cases (typically of high interest rates) where it can be positive:• An in-the-money put with a high interest rate• An in-the-money call on a currency
Consider a structured security of the following type: The purchaser invests $1,000 and in three years gets back the initial investment plus 95% of the increase in a market index whose current price
Use the Excel Solver to find the stock price for which there is the maximum difference between the Black-Scholes call option price and the option’s intrinsic value. Use the following values: S =
Re-examine the X = 17.50 call for AMR in the previous exercise.a. Is the call correctly priced?b. What price would be necessary for this call in order for the implied volatility to be 60%?
Produce a graph comparing a put’s intrinsic value [ = max( X − S ,0)] and its Black-Scholes price. From this graph you should be able to deduce that it may be optimal to exercise early a put
Produce a graph comparing a call’s intrinsic value [defined as max(S − X , 0)] and its Black-Scholes price. From this graph you should be able to deduce that it is never optimal to exercise early
Use the data from exercise 1 and Data|Table to produce graphs that show:• The sensitivity of the Black-Scholes call price to changes in the initial stock price S.• The sensitivity of the
Use the Black-Scholes model to price the following:• A call option on a stock whose current price is 50, with exercise price X = 50, T = 0.5, r = 10%, σ = 25%.• A put option with the same
An investment bank is offering a security linked to the price 2 years from today of Bisco stock, which is currently at $3 per share. Denote Bisco’s stock price in two periods by S2. The security
Reconsider the above problem. Show that if the date 1 exercise price is X , the date 2 exercise price is X *(1 + r), and the date 3 exercise price is X*(1 + r )2, you will not exercise the option
A call option is written on a stock whose current price is $50. The option has maturity of 3 years, and during this time the annual stock price is expected to increase by 25% or to decrease by −
This problem is a continuation of the discussion of section 16.6. Show that as n → ∞, the binomial European put price converges to the Black-Scholes put price. (Note that, as part of the chapter
A prominent securities firm recently introduced a new financial product. This product, called “The Best of Both Worlds” (BOBOW for short), costs $10. It matures in 5 years, at which point it
A stock is currently selling for 60. The price of the stock at the end of the year is expected either to increase by 25% or to decrease by 20%. The riskless interest rate is 5%. Calculate the price
All reliable analysts agree that a share of ABC Corp., selling today for $50, will be priced at either $65 or $45 1 year from now. They further agree that the probabilities of these events are 0.6
In a binomial model a call option and a put option are both written on the same stock. The exercise price of the call option is 30 and the exercise price of the put option is 40. The call option’s
A stock selling for $25 today will, in 1 year, be worth either $35 or $20. If the interest rate is 8%, what is the value today of a one-year call option on the stock with exercise price $30? Use the
A share of Formila Corp. is currently trading at $38.50, and a 1-year call option on Formila with X = $40 is trading at $3. The risk-free interest rate is 4.5%.a. What should be the price of a 1-year
The current stock price of ABC Corp. is 50. Prices for six-month calls on ABC are given in the table below. Draw a profit diagram of the following strategy: Buy one 40 call, write two 50 calls, buy
Prove Proposition 6. Then solve the following problem: Three puts on shares of XYZ with the same expiration date are selling at the following prices:Exercise price 40: 6Exercise price 50: 4Exercise
A European call with a maturity of 6 months and exercise price X = 80 is written on a stock whose current price is 85 is selling for $12.00; a European put written on the same stock with the same
A share of ABC Corp. sells for $95. A call on the share with exercise price $90 sells for $8.a. Graph the profit pattern from buying one share and one call on the share.b. Graph the profit pattern
Consider the following two calls:• Both calls are written on shares of ABC Corp. whose current share price is $100. ABC does not pay any dividends.• Both calls have one year to maturity.• One
A put with an exercise price of 50 has a price of 6 and a call on the same stock with an exercise price of 60 has a price of 10. Both put and call have the same expiration date. On the same set of
A European call option is written on a stock whose current price S = 80. The exercise price X = 80, the interest rate r = 8%, and the time to option exercise T = 1. The stock is assumed to pay a
An American call option is written on a stock whose price today is S = 50. The exercise price of the call is X = 45.a. If the call price is 2, explain how you would use arbitrage to make an immediate
When you looked at the newspaper quotes for options on ABC stock, you saw that a February call option with X = 37.5 is priced at 6.375, whereas the April call option with the same exercise price is
Another analyst believes that HD will return only 0.5% per month over the next year. What are her recommended portfolio proportions?
You are an analyst investing in the high-beta DJ30 portfolio from the previous problem. You believe that the monthly return of MMM will be 1%. What are your recommended optimal portfolio proportions?
Using the efficient portfolio instead of the S&P 500:a. Compute the monthly returns on the efficient portfolio.b. Regress the average monthly returns of the stocks on their betas with respect to
Perform the second-pass regression: Regress the monthly average returns on the betas of the assets. Does this confirm that the S&P 500 is efficient?
Compute the GMVP using the constant correlation covariance matrix.
Compute the global minimum variance portfolio (GMVP) using the sample variance covariance matrix.
Use the function SIM defined in the chapter to compute the single-index variance-covariance matrix.
Compute the sample variance-covariance matrix and the correlation matrix for the six stocks.
In the previous problem find two portfolios whose standard deviation is 45%. (There is an analytical solution to this problem, but it can also be solved by Solver.)
The exercise disk for this chapter contains monthly data for stock prices of Kellogg and IBM. Compute the return statistics and graph a frontier of combinations of the two stocks.
Perform a sensitivity analysis (using Data|Table) on the certainty-equivalence factor in Section 7.5, showing how the IRR of the differential cash flows varies with the CE factor.
In the project finance pro forma of section 5.8 it is assumed that the firm pays off its initial debt of 1,000 in equal installments of principal over 5 years. Change this assumption and assume
Repeat the above exercise, but this time replace the terminal value by an EBITDA (earnings before interest, taxes, depreciation, and amortization) ratio times year-5 anticipated EBITDA. Show a graph
In the valuation exercise of section 5.4, the terminal value is calculated using a Gordon dividend model on the cash flows. Replace this terminal value by the year 5 book value of debt + equity. This
In the model of section 5.6, assume that the firm needs to have minimum cash balances of 25 at the end of each year. Introduce this constraint into the model.
Consider the model in section 5.6 (where debt is the plug).a. Suppose that the firm has 1,000 shares and that it decides to pay, in year 1, a dividend per share of 15 cents. In addition, suppose that
Suppose that in the model of section 5.1 the fixed assets at cost for years 1–5 are 100% of sales (in the current model, it is net fixed assets which are a function of sales). Change the model
Here’s a basic exercise that will help you understand what’s going on in the modeling of financial statements. Replicate the model in section 5.1. That is, enter the correct formulas for the
The files that accompany this book contains information for Kellogg. Use this information and the template of section 4.1 to value Kellogg. B C D E I F BASIC CASH FLOW VALUATION TEMPLATE A 2 Current
The template for exercise 14 gives the 10-year history of Intel’s quarterly dividends. Compute Intel’s cost of equity rE using the Gordon dividend model. Compare the cost of equity computed on
The template for exercise 12 gives the prices of the Vanguard Index 500 Fund (symbol: VFINX). This fund’s prices replicate the S&P 500 with dividends reinvested. Use these data to estimate the
Use the template for the ABC Corp. valuation in section 2.7 to value Cisco stock. Assume that the weighted average cost of capital for Cisco is 12.6%, the growth rate for years 1–5 is 4%, and that
Examine Cisco’s consolidated statement of cash flows (on the disk that accompanies this book) and transform this into a free cash flow.
Three years of balance sheets for Cisco are given on the disk with this book. Restate these balance sheets so that the accounting enterprise value is on the left side.
The disk for these exercises gives 5 years of monthly prices for two Vanguard funds—the Vanguard Index 500 fund (symbol VFINX) and the Vanguard High-Yield Corporate Bond fund (VWEHX). The first of
Continuing with the above example: Find the maximum rental which ABC will pay and the minimum rental which XYZ will accept.
Calculate the envelope set for the four assets below and show that the individual assets all lie within this envelope set. You should get a graph which looks something like the following: B A
In the example below, use Excel to find an envelope portfolio whose β with respect to the efficient portfolio y is zero. Notice that because the covariance is linear, so is β: Suppose that z = λx
A sufficient condition to produce positively weighted efficient portfolios is that the variance- covariance matrix be diagonal: That is, that ?ij = 0, for i ? j. By continuity, positively weighted
Consider the data below for six furniture companies. a. Given this matrix, and assuming that the risk-free rate is 0%, calculate the efficient portfolio of these six firms. b. Repeat, assuming that
Consider the data below and find the portfolio weights so that the expected return of the portfolio is 14%. What is the corresponding portfolio standard deviation? A B Standard deviation of return
Three assets have the following means and variance-covariance matrix: a. Calculate the statistics?mean, variance, standard deviation, covariance, correlation? for the portfolios. b. Create a chart
Consider the two random variables X and Y whose values are given below. Note that X and Y are perfectly correlated, though perhaps not linearly correlated. Compute their correlation coefficient. 25
Consider the two stocks below. Graph the frontier of combinations of the two stocks. Show the effect on the frontier of varying the correlation from ? 1 to + 1. в с TWO STOCKS Varying the
Hemp Airlines (HA, “we fly high”) is about to buy 5 CFA3000 commuter jets. Each airplane costs $50 million. A friendly bank has put together a consortium to finance the deal. The consortium
ABC Corp. is considering leasing an asset from XYZ Corp. Here are the relevant facts: ABC?s interest costs are 10% and XYZ ? s interest costs are 7%. Show that it will be advantageous both for ABC
Your company is considering either purchasing or leasing an asset which costs $1,000,000. The asset, if purchased, will be depreciated on a straight-line basis over 6 years to a zero residual value.
This problem introduces the concept of ?sustainable dividends?: The firm whose financials are illustrated below wishes to maintain cash balances of 80 over the next 5 years. It also desires to issue
Suppose that the S&P 500 price/earnings ratio is 17.5, the dividend payout ratio of the S&P is 50%, and that you estimate a future growth of dividends of 7%. What is E (rM)? COMPUTING E(rM)
At the end of June 2007, the price/earnings ratio of the S&P 500 was 17.5. Assume that the index proxies for the market, that it has a 50% dividend payout ratio, and that dividends are expected
It is 1 January 1997. Normal America, Inc. (NA) has paid a year-end dividend in each of the last 10 years, as shown by the table below: a. Calculate NA?s ? with respect to the S&P 500. b.
You are considering buying the bonds of a very risky company. A bond with a $100 face value, a 1-year maturity, and a coupon rate of 22% is selling for $95. You consider the probability that the
On the spreadsheet with this chapter you will find the following monthly data for Cisco?s stock price and the S&P 500 index. Compute the equation rCSCO, t = ?CSCO + ?CSCO rSP,t and include the R2
Consider a company which has βequity = 1.5 and βdebt = 0.4. Suppose that the risk-free rate of interest is 6%, the expected return on the market E(rM) = 15%, and that the corporate tax rate is
ABC Corp. has just paid a dividend of $3 per share. You—an experienced analyst—feel quite sure that the growth rate of the company’s dividends over the next 10 years will be 15% per year. After
The current stock price of TransContinentalAirways is $65 per share. TCA currently pays an annual per-share dividend of $3. Over the past 5 years this dividend has grown annually at a rate of 23%. A
Consider the following dividend and price data for Chrysler: Use the Gordon model to calculate Chrysler ? s cost of equity at end-1996 on the basis of dividends only. в с CHRYSLER CORPORATION
Dismal.Com is a producer of depressing Internet products. The company is currently not paying dividends, but its chief financial officer thinks that starting in 3 years it can pay a dividend of $15
Unheardof, Inc., has just paid a dividend of $5 per share. This dividend is anticipated to increase at a rate of 15% per year. If the cost of equity for Unheardof is 25%, what should be the market
ABC Corp. has a stock price P0 = 50. The firm has just paid a dividend of $3 per share, and intelligent shareholders think that this dividend will grow by a rate of 5% per year. Use the Gordon
Below are some year-end numbers for Cisco?s equity. Restate the enterprise value in market terms. в с A CISCO EQUITY DATA 27-Jul-12 29-Jul-11 30-Jul-10 2 3 Shares outstanding 4 Price per share 5
Identify the two internal rates of return of the investment in exercise 16. A Date Cash flow -500 30-Jun-07 14-Feb-08 100 14-Feb-09 300 400 600 800 -1,800 14-Feb-10 14-Feb-11 10 14-Feb-12 11
Use XNPV to value the following investment. Assume that the annual discount rate is 15%. A Date Cash flow -500 30-Jun-07 14-Feb-08 100 14-Feb-09 300 400 600 800 -1,800 14-Feb-10 14-Feb-11 10
Use XIRR to compute the internal rate of return of the following investment: A Date Cash flow 2 30-Jun-07 3 14-Feb-08 4 14-Feb-09 5 14-Feb-10 6 14-Feb-11 7 14-Feb-12 8 14-Feb-13 -899 70 70 70 70
You currently have $25,000 in the bank, in a savings account that draws 5% interest. Your business needs $25,000, and you are considering two options: (a) Use the money in your savings account. (b)
You have just turned 35, and you intend to start saving for your retirement. Once you retire in 30 years (when you turn 65), you would like to have an income of $100,000 per year for the next 20
A mutual fund has been advertising that, had you deposited $250 per month in the fund for the last 10 years, you would now have accumulated $85,000. Assuming that these deposits were made at the
Redo the previous calculation, this time assuming that you make 5 deposits at the beginning of this year and the following 4 years. How much will you accumulate by the end of year 5?
You are considering a savings plan which calls for a deposit of $15,000 at the end of each of the next 5 years. If the plan offers an interest rate of 10%, how much will you accumulate at the end of
You are considering buying a car from a local auto dealer. The dealer offers you one of two payment options: ? You can pay $30,000 cash. ? The ?deferred payment plan?: You can pay the dealer $5,000
You have just taken a car loan of $15,000. The loan is for 48 months at an annual interest rate of 15% (which the bank translates to a monthly rate of 15%/12 = 1.25%). The 48 payments (to be made at
Calculate the flat annual payment required to pay off a 13%, 5-year loan of $100,000.
An alternative definition of the IRR is the rate which makes the principal at the beginning of year 6 equal to zero. 9 This is shown in the printout above, in which cell E9 gives the principal at the
In this exercise we solve iteratively for the internal rate of return. Consider an investment which costs 800 and has cash flows of 300, 200, 150, 122, 133 in years 1?5. Setting up the loan table
The following cash-flow pattern has two IRRs. Use Excel to draw a graph of the NPV of these cash flows as a function of the discount rate. Then use the IRR function to identify the two IRRs. Would
You are offered an investment with the following conditions: ? The cost of the investment is 1,000. ? The investment pays out a sum X at the end of the first year; this payout grows at the rate of
You just took a $10,000, 5-year loan. Payments at the end of each year are flat (equal in every year) at an interest rate of 15%. Calculate the appropriate loan table, showing the breakdown in each
You are offered an asset costing $600 that has cash flows of $100 at the end of each of the next 10 years.a. If the appropriate discount rate for the asset is 8%, should you purchase it?b. What is

Showing 300 - 400 of 400