Question

I am looking for someone to build a spreadsheet in excel that can quickly answers these type of questions by inputting the data. Thanks

1.Suppose that on Monday, 16 November, you assume a long position in one yen futures contract at the futures price of $0.0083. The initial margin is $4,590, and the maintenance margin is $3,400. The contract size is ¥12,500,000. For simplicity, you do not withdraw excess money from your margin balance. All margin requirements are met with cash. You short the same contract (i.e., a reversing trade) at the opening price on Monday, 23 November. Make sure you can calculate all the numbers in the following table except the settlement prices that are given.

Trading Day	Settlement Price	Marking to Market	Deposit/withdrawal	Account Balance
Nov. 16	$.0085	$2,500	$4,590a	$7,090
Nov. 17	.0081	-5,000	1,310b	3,400
Nov. 18	.0078	-3,750	3,750	3,400
Nov. 19	.0077	-1,250	1,250	3,400
Nov. 20	.0080	3,750		7,150
Nov. 23	.0081c	1,250		8,400
			-8,400d	0
Total		-2,500	2,500e (loss)

Notes:       

a.$4,590 initial margin deposit.

b.$1,310 deposit to meet $3,400 maintenance margin.

c.$.0081 opening futures price on 23 November.

d.Entire account balance withdrawn after reversing trade.

e.Note that -$2,500 = ($.0081-.0083)(12,500,000).

Now assume that Nov. 23 is also the maturity date for the contract. Instead of doing the reversing trade described above, you take the delivery of the yen and pay the closing (settlement) price of $0.0078. Recalculate the numbers for Nov. 23 and the total gain/loss.

Trading Day	Settlement Price	Making to Market	Deposit/withdrawal	Account Balance
Nov. 16	$.0085	$2,500	$4,590	$7,090
Nov. 17	.0081	-5,000	1,310	3,400
Nov. 18	.0078	-3,750	3,750	3,400
Nov. 19	.0077	-1,250	1,250	3,400
Nov. 20	.0080	3,750		7,150
Nov. 23	.0078	-2,500		4,650
			-4,650	0
Total		-6,250	6,250 (loss)

You take the delivery and pay $97,500 (= $.0078(12,500,000)), but you lose $6,250 from the daily settlements. Therefore your total cost is $103,750. If you instead enter into a forward contract to buy the same amount of yens at the forward price of $.0083 on Nov.16, assuming that the forward contract matures on the same day as the futures, your cost for taking the delivery is also $103,750 (= $.0083(12,500,000)).

2.On Monday, November 16, you assume a short position in one March Euro futures contract at futures price of $1.20/€. The initial margin is $2000, and the maintenance margin is $1500. The contract size is €125,000. You enter the reversing trade (long position) at the opening price ($1.19) on Monday, November 23. Make sure you can calculate all the numbers in the following table except the settlement prices that are given.

Trading Day	Settlement Price	Marking to Market	Deposit/withdrawal	Account Balance
Nov. 16	$1.18	$2,500	$2,000	$4,500
Nov. 17	1.21	-3,750	750	1,500
Nov. 18	1.22	-1,250	1,250	1,500
Nov. 19	1.24	-2,500	2,500	1,500
Nov. 20	1.18	7,500		9,000
Nov. 23	1.19	-1,250		7,750
			-7,750	0
Total		1,250	1,250 (gain)

On Nov. 16, the closing price is lower than the entry price. However, since this is a short position, a decrease in price results in a gain.

The total gain, $1,250 is also equal to the difference between the entry price and the final price times the contract size, $(1.20-1.19)(125,000).

Assume November 23 is the maturity day of the futures contract. The spot rate and the futures price converge on the price of $1.22. You pay the spot rate, $1.22, to buy the euros to deliver to close the position. Recalculate the numbers for Nov. 23 and the total gain/loss.

Trading Day	Settlement Price	Marking to Market	Deposit/withdrawal	Account Balance
Nov. 16	$1.18	$2,500	$2,000	$4,500
Nov. 17	1.21	-3,750	750	1,500
Nov. 18	1.22	-1,250	1,250	1,500
Nov. 19	1.24	-2,500	2,500	1,500
Nov. 20	1.18	7,500		9,000
Nov. 23	1.22	-5,000		4,000
			-4,000	0
Total		-2,500	2,500 (loss)

You pay $1.22 to buy the euros in the spot market and deliver the euros at $1.22 to close the future’s position, so there is no gain/loss on the delivery; however, you lose $2,500 on the futures position. So your total loss is $2,500, which is equal to the difference between the entry price, $1.20, and the final price, $1.22, times the contract size. If you instead enter into a forward contract to sell the same amount of euros at forward price of $1.20 on November 16, assuming that the forward contract matures on the same day as the futures, on November 23 you are obligated to buy the euros at $1.22 in the spot market to deliver at the forward price of $1.20. Your loss is $2,500 (=$(1.22-1.20)(125,000)). Therefore, the futures and the forward contracts yield the same result.

Answer 1

I have prepared the excel sheet that you can use.You can plug in the required values and see the results immediately. Also, furnishing the first futures trade table below

	No. of Contracts	1
Day	Beg. Balance	Funds Deposited	Settlement Price	Futures Price Change	Gain/Loss	Ending Balance			Contract size	12500000
0	0	4590	0.0083			4590
1	4590	0	0.0085	0.0002	2500	7090
2	7090	0	0.0081	-0.0004	-5000	2090
3	2090	1310	0.0078	-0.0003	-3750	-350
4	-350	3750	0.0077	-0.00010	-1250	2150
5	2150	1250	0.008	0.00030	3750	7150
6	7150	0	0.0081	0.00010	1250	8400
	Total				-2500