SOFTWARE USER’S GUIDE*
*The Software User’s Guide also appears on the text website.
The software that accompanies International Financial Management, fourth edition, consists of four spreadsheets programs. All are written as Excel spreadsheets. The spreadsheet programs are FXOPM.xls, CURSWAP.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 7.7 in Chapter 7.
FXOPM.xls can be used to solve Problem 12 in Chapter 7.
European FX Option Pricing Model
Spot 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.95
CURSWAP.xls
CURSWAP.xls solves for the all-in-cost (AIC) of a five-year currency swap from the perspective of a counterparty paying to a swap bank fixed-rated U.S. dollars and receiving from the swap bank fixed-rated foreign currency (FC). The spreadsheet requires the user to enter information into the cells highlighted in blue. The user must input several data items: the face amount of the FC bond issue, the bond coupon rate, the offering price (OP) of the bond expressed as a percentage of face value, the underwriting fee expressed as a percentage of face value, the current spot bid and ask foreign exchange rates in European terms, the FC bid and ask swap rates, and the U.S. dollar bid and ask swap rates. CURSWAP.xls calculates the AIC of the FC bond after underwriting fees, determines the FC cash flows needed to meet the FC debt service, calculates the FC notional value at the FC swap bid rate and converts it into the U.S. dollar equivalent notional value at the foreign exchange ask rate. From the U.S. dollar notional value, the dollar payments to the swap bank are calculated from the dollar swap ask rate. In the far right column, the actual dollar cash flows under the swap to be received and paid under the swap are summarized and the AIC is determined. The example numbers in CURSWAP.xls when it is first opened solve the currency swap example in the 1991 Harvard Business School “Note on Foreign Currency Swaps” prepared by William B. Allen, Jr., Scott P. Mason, and W. Carl Kester. The example analyzes a five-year Swiss franc-dollar currency swap of a SF100,000,000, 7.35% coupon, Eurobond. The offering price is 100% of par, the underwriting fees are 2.50%, the bid-ask foreign exchange rates are 1.99950-2.00005, the bid-ask SF swap rates are 7.70-7.80%, and the dollar swap bid-ask rates are 16.25-16.35%.
Cross-Currency Swap Analyzer
Year / FC Bond Cash Flow / FC Received / $Paid / Actual
$ Cash Flow
0 / -97,500,000 / -98,591,432 / 49,283,395 / 48,737,816
1 / -7,350,000 / 7,350,000 / -8,057,835 / -8,057,835
2 / -7,350,000 / 7,350,000 / -8,057,835 / -8,057,835
3 / -7,350,000 / 7,350,000 / -8,057,835 / -8,057,835
4 / -7,350,000 / 7,350,000 / -8,057,835 / -8,057,835
5 / -107,350,000 / 107,350,000 / -57,341,230 / -57,341,230
AIC / 7.98% / 7.70% / 16.35% / 16.69%
Face Value: / 100,000,000 / Bid / Ask
Coupon Rate: / 7.350% / Spot FX Rate: / 1.99950 / 2.00050
OP as % of Par: / 100.000% / FC Swap Rate: / 7.70% / 7.80%
Underwriting Fee: / 2.500% / $ Swap Rate: / 16.25% / 15.35%
MPTSolver.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 or 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 Problems 7 in Chapter 15.
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 in Chapter 8.
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-11