SOFTWARE USER’S GUIDE*
*The Software User’s Guide also appears on the text website.
The software that accompanies International Financial Management, Canadian Perspectives, consists of three spreadsheets programs. All are written as Excel spreadsheets. The spreadsheet programs are FXOPM.xls, MPTSolver.xls, and TRNSEXP.xls.
FXOPM.xls
FXOPM.xls calculates the call and put premiums according to the Biger and Hull, Garman and Kohlhagen, and Grabbe European currency option pricing model. Sample output from FXOPM.xls follows. The sample output is from Example 9.8,page 219 in Chapter 9.
FXOPM.xls can be used to solve Problem 12 in Chapter 9.
European FX Option Pricing Model
Spot Rate (D) = / 63.86 / Forward Rate (D) = / 64.33Spot Rate (F) = / 156.59 / Forward Rate (F) = / 155.45
U.S. Interest Rate = / 5.13% / Foreign Interest Rate = / 1.05%
Exercise Price = / 67.00 / Option Volatility = / 10.70%
Days to Expiration = / 66 / Years to Expiration (T) = / 0.1808
d1 = / -0.8713 / N (d1) = / 0.1918
d2 = / -0.9168 / N (d2) = / 0.1796
Call Option Premium = / 0.30 /
Put Option Premium =
/ 2.95MPTSolver.xls
MPTSolver.xls is an Excel spreadsheet based Markowitz portfolio optimizer that allows the user to obtain the optimal tangency portfolio on the efficient frontier of a set of securities by finding the portfolio it is possible to construct that has the maximum Sharpe measure of portfolio performance. MPTSolver.xls is based on the Excel Solver capability found under the Tools applications. Thus, it is very versatile in solving a variety of optimization problems. For example, constraints can be established to restrict short selling or allow for unlimited short selling in the construction of the tangency portfolio. To use MPTSolver.xls, the user must input the risk-free rate, the expected returns, the standard deviations of returns, and the pairwise correlation coefficients for a maximum of seven securities. The user also needs to input an arbitrary set of investment weights to initialize the optimization. It is suggested that equal weights be used. The input areas are highlighted in blue. If less than seven securities comprise the investment universe, say five stocks, weights of zero should be input for Asset 6 and Asset 7. In this case, the optimization would be done by having Solver maximize the Sharpe ratio by changing the values of the investment weights of Assets 1 through 5, rather than for seven securities.
Sample output from MPTSolver.xls is presented below. The input data used in the sample are from Table 1 of Cheol. S. Eun and Bruce G. Resnick, “International Diversification of Investment Portfolios: U.S. and Japanese Perspectives, Management Science 40, January 1994, pp. 140-161. the output is consistent with Table 2 of the article.
MPTSolver.xls can be used to solve Problem 6on page 273 in Chapter 11.
MARKOWITZ PORTFOLIO OPTIMIZER
Return
/Std Dev
/Weight
/ R(p) / 1.726Canada / 1.390 / 6.140 / -6.041% / Rf / 0.000
France / 2.060 / 7.730 / 5.639%
Germany / 1.500 / 6.620 / 4.632% / Var(p) / 17.721
Japan / 2.130 / 6.440 / 39.504%
Switzerland / 1.330 / 5.900 / 0.585% / Std Dev(p) / 4.210
U.K. / 1.690 / 6.400 / 8.237%
U.S. / 1.340 / 4.600 / 47.444% / Sharpe(p) / 0.410
Sum W(i) / 100.000%
Canada / France / Germany / Japan / Switzerland / U.K. / U.S.
Canada / 1.000 / 0.400 / 0.270 / 0.220 / 0.370 / 0.600 / 0.710
France / 0.400 / 1.000 / 0.610 / 0.430 / 0.580 / 0.480 / 0.410
Germany / 0.270 / 0.610 / 1.000 / 0.370 / 0.760 / 0.440 / 0.300
Japan / 0.220 / 0.430 / 0.370 / 1.000 / 0.340 / 0.350 / 0.200
Switzerland / 0.370 / 0.580 / 0.760 / 0.340 / 1.000 / 0.450 / 0.380
U.K. / 0.600 / 0.480 / 0.440 / 0.350 / 0.450 / 1.000 / 0.530
U.S. / 0.710 / 0.410 / 0.300 / 0.200 / 0.380 / 0.530 / 1.000
TRNSEXP.xls
TRNSEXP.xls allows the user to obtain alternative hedging solutions for a foreign currency receivable or payable. The user must input spot, expected future spot, forward and/or futures exchange rates, along with foreign and domestic interest rates and the call option (if a FX receivable) or put option (if a FX payable) premium and exercise price. One may enter his own estimate of the expected terminal spot exchange rate or use the forward or futures FX rate as the market determined estimate. The user should input a value of zero for the value of a variable that he does not know. The output section provides the alternative hedging solutions to: an unhedged alternative, forward and/or futures hedge, money market hedge, and options hedge. The unhedged solution provides the (uncertain) expected receipt or cost. The options hedge alternative explicitly recognizes the opportunity cost of the option premium at the domestic interest rate. For a FX receivable, the options hedge yields the minimum receipt; for a FX payable, the options hedge shows the maximum cost. Sample input and output from TRNSEXP.xls follows.
TRNSEXP.xls can be used to solve Problems 2, 3, 4, and 6, pages 322 and 323.
in Chapter 13.
Transaction Exposure Hedging Analysis
Input Data
Amount of Exposure: / 5,000,000 / Receivable (1) / Payable (2): / 1Current Spot Exchange Rate: (American Terms) / $0.349800 / Length of Exposure: (Fraction of Year) / 0.50
Expected Spot Exchange Rate: (American Terms) / $0.357100 / U.S. Interest Rate: (Per Annum) / 10.67%
Forward Exchange Rate: (American Terms) / $0.357300 / Foreign Interest Rate: (Per Annum) / 6.25%
Call Option Premium: / $0.011500 / Futures Price: / $0.357200
Exercise Price: / $0.349800 / Contract Size: / 125,000
Put Option Premium: / $0.010500
Exercise Price: / $0.349800
Pg Down for Hedging
Alternatives Outcomes
Hedging Outcomes
#1 / Remain Unhedged: / $1,785,500.00#2 / Forward Hedge: / $1,786,500.00
#3 / Futures Hedge: / $1,786,000.00
#4 / Money Market Hedge: / $1,786,481.60
#5 / Options Hedge: / $1,693,699.13
SFT-1