Category Archives: Helpful Hints

Cox-Ross-Rubinstein (CRR) option pricing spreadsheet

Here’s a spreadsheet which calculates call and put prices using the CRR framework for n = 1, 2, 3, and 4 timesteps.  For discussion of this method, see pp. 9-11 of lecture note which we covered in class yesterday.

The key idea here involves determining option payoffs without having to recreate the entire stock binomial tree which is shown on page 9 of the above referenced lecture note.  The way this is done involves determining for each call option the minimum number of up moves required in order to determine the nodes at which the option will be in-the-money.  By rounding up to the nearest integer “a” the value of \ln (K/S{d^n})/\ln (u/d) for n = 1, 2, 3, and 4 (see rows 11 and 12 below), we determine that the 1 timestep call will only be in the money at the u node, the 2 timestep call will only be in the money at the uu node, the 3 timestep call will only be in the money at the uuu and uud nodes, and the 4 timestep call will only be in the money at the uuuu and uuud nodes.  This greatly simplifies the calculations for call prices because we know that the call payoffs will all be zero at all other nodes (see row 13 below).  Having determined the arbitrage-free call prices, then we find the arbitrage-free put prices by applying the put call parity equation P = C + K{e^{ - rn\delta t}} - S (you can bring up the spreadsheet by clicking on the screenshot below):

Black-Scholes-Merton option pricing spreadsheet

Here’s a copy of the spreadsheet that I created during the section 1 (11-12:15) meeting of Finance 4335 today (you can bring up the spreadsheet by clicking on the screenshot below):In order to calculate the arbitrage-free price of a call option, we need to solve the following equation:

C = SN({d_1}) - K{e^{ - rT}}N({d_2}),

where {d_1} = \displaystyle\frac{{\ln (S/K) + (r + .5{\sigma ^2})T}}{{\sigma \sqrt T }} and {d_2} = {d_1} - \sigma \sqrt T .. The arbitrage-free price for the put may be obtained by applying the put-call parity equation.  By doing so, we obtain the put pricing equation for an otherwise identical (same underlying, same exercise price, same time to expiration) put:

P = K{e^{ - rT}}N( - {d_2}) - SN( - {d_1}),

where N( - {d_1}) = 1 - N({d_1}) and N( - {d_2}) = 1 - N({d_2}).

For calls and puts, we need five parameter values: S (current underlying asset price), K (exercise price, \sigma (volatility of underlying asset return), T (time to expiration, measured in number of years), and r (the annualized riskless rate of interest). These parameters all show up in cells B1:B5. The first step to solving call and put prices requires determining the values for {d_1}, {d_2}, N({d_1}), N({d_2}),N( - {d_1}), and N( - {d_2}); these values are listed in cells B7:B12. Once we obtain this information, it’s simply a matter of coding the equations for the BSM call and put prices; these prices show up in cells E1:E2.

Finally, I also include the components of the replicating portfolios for the call and put options. Of course, the values of these portfolios must be the same as the call and put option values; otherwise, there would be riskless arbitrage opportunities. As we showed way back during class on Tuesday, October 23, one replicates a call option by buying delta units of the underlying asset on margin, whereas one replicates a put option by shorting delta units of the underlying asset and lending money. In the Black-Scholes-Merton pricing model, the call delta corresponds to N({d_1}), whereas the put delta corresponds to N({-d_1}).

Today’s class problem solutions for parts A-D applying risk neutral valuation

Today, we discussed the risk neutral valuation approach to pricing options, and today’s class problem assignment was to work parts A through D of the  Option Pricing Class Problem, relying solely upon the risk neutral valuation approach.

As I pointed out during today’s class meeting, the replicating portfolio and delta hedging approaches both imply that a risk neutral valuation exists between an option (both the call and put varieties) and its underlying asset.  This is analytically shown in sections 5 and 6 (located on pp. 8-9) of my Binomial Option Pricing Model (single-period) teaching note which was assigned for October 23.  A particularly useful advantage of the risk neutral valuation approach (compared with the replicating portfolio and delta hedging approaches) is that it is computationally simpler and particularly well suited for modeling multi-timestep option pricing problems.

Here are the solutions for parts A through D (click on the image for a full-size PDF version that you can print out):

Today’s class problem and solutions for parts A and B

Today, we worked on (among other things), parts A and B of the Option Pricing Class Problem. Here are the solutions for parts A and B (click on the image for a full-size PDF version that you can print out):

Be sure to bring your class problem with you to class next Tuesday. I will introduce a third method for pricing called the risk-neutral valuation approach which not only simplify the pricing problem for one timestep but also make it easier to calculate option prices for multiple timesteps. Spoiler alert – as we let the number of timesteps become arbitrarily large for a given discrete time interval, the famous Black-Scholes option pricing formula obtains.

On the role of replicating portfolios in the pricing of financial derivatives

Replicating portfolios play a central role in terms of pricing financial derivatives. Here is a succinct summary from yesterday’s class meeting:

  1. Buying forward is equivalent to buying the underlying on margin, and selling forward is equivalent to shorting the underlying and lending money. Like options, forwards and futures are priced by pricing the replicating portfolio and invoking the “no-arbitrage” condition. If the forward/futures price it too low, then one can earn positive returns with zero risk and zero net investment by buying forward, shorting the underlying and lending money. Similarly, if the forward futures price is too high, one can earn positive returns with zero risk and zero net investment by selling forward and buying the underlying with borrowed money. This is commonly referred to as “riskless arbitrage”; it’s riskless because you’re perfectly hedged, and it’s arbitrage because you are buying low and selling high.
  2. The replicating portfolio for a call option is a margined investment in the underlying. During yesterday’s class meeting, we priced a one timestep call option where the price of the underlying asset is $100, the exercise price is also $100, u = 1.05, d = .95, the interest rate r = 5%, and the timestep \delta t = 1/12. Given these parameters, the payoff on the call is $5 at the up (u) node and $0 at the down (d) node. The replicating value consists of half a share that is financed by a margin balance of $47.30; thus the “arbitrage-free” price of the call option is (.5(100) – 47.30) = $2.70.
  3. Since the replicating portfolio for a call option is a margined investment in the underlying, it should come as no surprise that the replicating portfolio for a put option consists of a short position in the underlying combined with lending. Thus, in order to price the put, we need to determine and price the components of the replicating portfolio. We will begin class tomorrow by reviewing our analysis of the replicating portfolio approach to pricing calls and puts, and move on to other pricing methods such as delta hedging and risk neutral valuation.

Rothschild-Stiglitz model (numerical and graphical illustration)

According to the Rothschild-Stiglitz model that we studied during yesterday’s class, insurers will limit contract choices such that there is no adverse selection.  In the numerical example that we implemented during class, there are equal numbers of high risk and low risk insureds; all have initial wealth of $125 and square root utility.  There are two states of the world – loss and no loss, and the probabilities of loss are 75% for high risk types and 25% for low risk types.  By offering high risk types full coverage at their actually fair price of $75 and offering low risk types partial (10%) coverage at their actuarially fair price of $2.50, both types of risks buy insurance and there is no adverse selection.

This illustrated in the figure below and in the spreadsheet located at  Clearly neither the B or C contracts would ever be offered because these contracts incentive high risks to adversely select agains the insurer.

Some observations concerning yesterday’s Rothschild-Stiglitz class problem

Yesterday, we considered the following problem in class:

  • Assume that consumers are identical in all respects expect for their loss probabilities; some are high risk, and others are low risk.
    • Members of the high-risk group have loss probability pH = 65%, whereas members of the low risk group have loss probability pL = 35%.
  • Each consumer has initial wealth of $100 and utility U(W)=W.5.
  • There are only two possible states of the world, loss and no loss. If a loss occurs, then consumers lose their initial wealth of $100.
  • Insurance contract offerings include the following:
    • Policy A provides full coverage for a price of $65.
    • Policy B provides full coverage for a price of $45.50.
    • Policy C provides 60% coverage for a price of $39.
    • Policy D provides 30% coverage for a price of $13.65.

The objective here is to identify the set of contract offerings that would prevent adverse selection. If you consider the pricing of these four insurance contracts, Policy A involves full insurance that is actuarially fair for high-risk consumers. We know from the Bernoulli principle that these consumers would like to purchase this contract. The challenge is to identify contracts that are favorable for the low-risk consumers but not for the high-risk consumers. Clearly we would not want to offer contract B, since everyone would select this contract and we would lose $19.50 on every high-risk consumer who purchased it (while breaking even on every low-risk consumer). High-risk consumers won’t want Policy C because it offers actuarially fair partial coverage, which provides lower expected utility than actuarially fair full coverage. However, low-risk consumers would be willing to purchase Policy C, so if A and C were offered, the insurer would break even on A and make $18 in profit from low-risk consumers who purchase Policy C. Given a choice between being uninsured, buying Policy A, or buying Policy C, low-risk consumers would purchase Policy C since it would offer higher expected utility than the other alternatives. Policy D would also be an acceptable alternative; if high-risk consumers purchased this contract, the insurer would lose $5.85 per high-risk consumer. However, if Policy A was also offered, none of the high-risk consumers would purchase Policy D. But low-risk consumers would prefer Policy D since it would offer higher expected utility than the other alternatives.

For what it’s worth, I have uploaded a spreadsheet consisting of expected utility calculations for this problem:

Problem Set 6 Hints and Spreadsheet

I just posted a new problem set on the course website; specifically, Problem Set 6, which is due at the beginning of class on Thursday, October 11.

In question 1, part C of Problem Set 6, I ask you to “Find the maximum price which the insurer can charge for the coinsurance contract such that profit can still be earned while at the same time providing the typical Florida homeowner with higher expected utility from insuring and retrofitting. How much profit will the insurer earn on a per policy basis?” Here are some hints which you’ll hopefully find helpful.

For starters, keep in mind that in part A, insurance is compulsory (i.e., required by law), whereas, in parts B and C, insurance is not compulsory. In part B of question 1, you are asked to show that the homeowner retrofits while choosing not to purchase insurance. However, in part C, the homeowner might retrofit and purchase coverage if the coinsurance contract is priced more affordably. Therefore, the insurer’s problem is to figure out how much it must reduce the price of the coinsurance contract so that the typical Florida homeowner will have higher expected utility from insuring and retrofitting compared with the part B’s alternative of retrofitting only. It turns out that this is a fairly easy problem to solve using Solver. If you download the spreadsheet located at, you can find the price at which the consumer would be indifferent between buying coinsurance and retrofitting compared with self-insurance and retrofitting. You can determine this “breakeven” price by having Solver set the D28 target cell (which is expected utility for coinsurance with retrofitting) equal to a value of 703.8730 (which is the expected utility of self-insurance and retrofitting) by changing cell D12, which is the premium charged for the coinsurance. Once you have the “breakeven” price, all you have to do to get the homeowner to buy the coinsurance contract is cut its price slightly below that level so that the alternative of coinsurance plus retrofitting is greater than the expected utility of retrofitting only. The insurer’s profit then is simply the difference between the price that motivates the purchase of insurance less the expected claims cost to the insurer (which is $1,250).

Midterm Exam 1 hints and formula sheet

I just posted the formula sheet for the exam at It is also linked as the first item on the formula sheets page on the course website.  This is identical to the formula sheet which will be attached to the exam booklet.

The exam consists of a total of 4 problems. The first problem is required, and you are also required to work 2 out of the 3 remaining problems on the exam (i.e., select two problems from Problems #2-#4). At your option, you may work all three of Problems #2-#4, in which case I will count the two problems with the highest scores toward your grade on this exam. Each of the graded problems is worth 32 points, so as a “bonus” I’ll add 4 points for including your name on the exam. Thus, the total number of points possible is 100.

Regarding content, the exam is all about stuff that we covered since the beginning of the semester; specifically, risk preferences, expected utility, certainty-equivalent of wealth, risk premiums, and stochastic dominance.

Guidelines for completing parts B and D on Problem Set 5

For parts B and D on Problem Set 5, you may solve these problems via either calculus or a spreadsheet model.

If you decide to implement a spreadsheet model, then you must email your spreadsheet model to “” prior to the start of class on Tuesday. In the problem set that you turn in at the beginning of class on Thursday, please reference your spreadsheet when you explain your answers for parts B and D problem. However, if you rely upon the calculus for maximizing expected utility, then no spreadsheet is necessary, although you might consider validating the result that you obtain via calculus with a spreadsheet model anyway. Or, you could validate your spreadsheet model with the calculus.

In order to solve this problem via spreadsheet, you’ll need to use the so-called Solver Add-in. The instructions for loading the Solver add-in into Excel are provided at the following webpage: