Pricing Spreadsheet

When the user, whether working on stand-alone software or through a spreadsheet, supplies only the values of the problem functions at a proposed point, the NLP code computes the first partial derivatives by finite differences. Each function is evaluated at a base point and then at a perturbed point. The difference between the function values is then divided by the perturbation distance to obtain an approximation of the first derivative at the base point. If the perturbation is in the positive direction from the base point, we call the resulting approximation a forward difference approximation. For highly nonlinear functions, accuracy in the values of derivatives may be improved by using central differences here, the base point is perturbed both forward and backward, and the derivative approximation is formed from the difference of the function values at those points. The price for this increased accuracy is that central differences require twice as many function evaluations of forward differences. If the functions are inexpensive to evaluate, the additional effort may be modest, but for large problems with complex functions, the use of central differences may dramatically increase solution times. Most NLP codes possess options that enable the user to specify the use of central differences. Some codes attempt to assess derivative accuracy as the solution progresses and switch to central differences automatically if the switch seems warranted. 

In addition, it is possible to reference data in another spreadsheet file, for example [First.xls]Result A3 refers to die address of cell A3 in worksheet Result and file First. This might be potentially useful, for example, if the file First consists of a series of spectra or a chromatogram whereas the current file consists of the results of processing die data, such as graphs or statistics. This dexibility comes with a price, as all files have to be available simultaneously, and is somewhat elaborate especially in cases where files are regularly reorganised and backed up. 

For quick estimates, this example can easily be coded into a spreadsheet and updated with the current prices of fuel and power. A sample steam costing spreadsheet is available in the online material at http //books.elsevier.com/companions. 

Manufacturing cost can also be comprehended by the use of a spreadsheet and some knowledge of the process. Figure 4.19 shows manufacturing costs that should be considered. The equipment purchase price is considered along with the amoritization rate, number of shifts, interest rate on the equipment, energy cost for the equipment, and floor space cost. Equipment time rate should be considered. The equipment time... 

The chapter starts by introducing the basic concepts of an economic analysis, as prices, breakdown of the capital and manufacturing costs, profit, as well as the formation of cash flow. Because the time-value of money plays a central role the next section presents some basic elements of financial methods. Two other sections deal with the detailed estimation of capital and operation costs. Simplified equations based on typically cost ratios can be used for quick estimations. These ratios are also helpful for the control of more detailed computations. The chapter ends with a more detailed description of the profitability analysis, both by traditional and modem methods. Note that the methods developed in this chapter can be applied using spreadsheets. 

The influence of different factors subject to uncertainty can be assessed by a sensitivity analysis. The most probable values are attributed to the base case. Then alternatives are generated by allowing errors in each factor, as for example variations in prices for raw materials, products or utilities, or different interest rates. The discounted cash flow analysis can determine which are the cost elements having the strongest influence on the NPV and DCFRR, and which are unimportant. This type of analysis is relatively simple to be done with a spreadsheet. The formulation of the problem in term of ratios can bring useful insights. 

Taking the same example as that developed to demonstrate the Vasicek model earlier, we now price the 3-year European call option on a 10-year pure discount bond using the CIR model for the short interest rates. Recall that face value is 1 and exercise price K is equal to 0.5. As in the example with the Vasicek model, we consider that o = 2% and tq = 3.75%. The CIR model overcomes the problem of negative interest rates (acknowledged as a problem for the Vasicek model) as long as 2a > o. This is true, for example, if we take a = 0.0189 and P = 0.24. Feeding this information into the above formulae is relatively tedious. A spreadsheet application is provided by Jackson and Staunton, After some work we get that the price of the call is... 

It is now possible to complete the price tree for the callable bond. Remember that the option in the case of a callable bond is held by the issuer. Its value, given by the tree in figure 11.11, must therefore be subtracted from the conventional bond price, given by the tree in figure 11.10, to obtain the callable bond value. For instance, the current price of the callable bond is 105.875 — 0.76, or 105.115. FIGURE 11.12 shows the tree that results from this process. A tree constructed in this way, which is programmable into a spreadsheet or as a front-end application, can be used to price either a callable or a putable bond. 

TABLE 10.6 CDS Price Calculation Excel Spreadsheet Formulae... 

We show at FIGURE 13.10 a basic spreadsheet for a convertible bond pricing model. The model parameters are shown at the top. We also show at Appendix III the cell formulae so that the sheet can be reproduced by... 

FIGURE 17.2 shows the spreadsheet used to calculate price, yield, and duration for a hypothetical bond traded forsetdement on December 10, 2005-It has a 5 percent coupon and matures in July 2012. Given the price, we can calculate yield, and given yield, we can calculate price and duration. We need to also set the coupon frequency, in this case semiannual, and the accrued interest day-count basis, in this case act/act, in order for the formulae to work. 

As of the trade date, this bond has three more coupons to pay plus its final maturity payment. The time to payment of each cash flow is shown in column G of FIGURE 17.4, which is a spreadsheet calculation of its yield. At our trade date, from Bloomberg page YA we see that the bond has a clean price of par and a redemption yield of 7.738 percent. This is shown at FIGURE 17.5. Can we check this on Excel ... 

Table 15-4 (constructed using worksheet Examplel5-3 in spreadsheet Chapter 15-examples) provides the outcome in terms of order sizes and profits for different wholesale prices and revenue-sharing fractions/ From Tables 15-3 and 15-4, observe that revenue sharing allows both the manufacturer and retailer to increase their profits in the absence of buybacks compared with the case in which the wholesaler sells for a fixed price of 5 without buybacks. Recall that when charging a wholesale price of 5, the supplier makes profit of 4,000 and the music store makes a profit of 3,803 (see Table 15-3). 

In Table 15-5, we show the impact of different quantity flexibility contracts on profitability for the music supply chain when demand is normally distributed, with a mean of jx = 1,000 and a standard deviation of a- = 300 (see worksheet Examplel5-4 in spreadsheet Chapterl5-examples). We assume a wholesale price of c = 5 and a retail price of p = 10. All contracts considered are such that a = (3. The results in Table 15-5 are built in two steps. We first fix a and j8 (say a = j8 = 0.2). The next step is to identify the optimal order size for the retailer. This is done using Excel by selecting an order size that maximizes expected retailer profits given a and j8. For example, when a = j8 = 0.05 and c = 5, retailer profits are maximized for an order size of 0 = 1,017. For this order size, we obtain a supplier commitment to deliver up to g = (1 + 0 05) X 1,017 = 1,068 and a retailer commitment to buy at least q = ( - 0.05) X 1,017 = 966 discs. In our analysis, we assume that the supplier produces Q = 1,068 discs and sends the precise number (between 966 and 1,068) demanded by the retailer. Such a policy results in retailer profits of 4,038 and supplier profits of 4,006. 

---