Your spreadsheet should accept the Input Data and display the columns listed below:

(i)

Input Data

?

Number of contracts

?

Investment amount (equal to number of contracts

price of the futures contract

on trade date)

?

Initial margin (= 5% of the investment amount)

?

Maintenance margin (= 5% of the investment amount)

(ii)

Column 1:

Futures price time-series

(iii)

Column 2:

Underlying reference rate

(iv)

Column A:

Daily gain/loss = number of contracts

change in futures price

(v)

Column B:

Opening margin account balance for that day. This should be equal to

net margin account balance (Column G) as of the previous day.

(vi)

Column C:

Indicate if there is a margin call on a particular day. You can display 1/0

or true/false to indicate a margin call.

(vii)

Column D:

Closing margin account balance after margin calls, if any.

(viii)

Column E:

Interest income on the margin account, calculated on a daily basis, for

any amount that is greater than maintenance margin. For example, if the maintenance

margin is $1000 and the account balance is $1200, you will earn interest on $200 over

the next day

)

Column F:

Interest paid for margin calls, calculated on a daily basis, for the total

amount deposited into the margin account due to margin calls up to that day. For

example, if the maintenance margin is $1000 and the account balance falls to $700.

Then the exchange issues a margin call and you will have to borrow $300 to fund the

margin call. Suppose, you've had margin calls for a total of $100 up to that day, then

the total interest paid will have to be calculated on $400 over the next day.

(x)

Column G:

Net margin account balance = D + E + F

A B C D E F G H column 1 column 2 column A column B column G column C column D DAY TRADE PRICE PRICE($) Underlying reference rate GAIN($) CUMULATIVE GAIN($) Opening margin account balance MARGIN ACCOUNT BALANCE MARGIN CALL Closing margin account 02-Mai-2018 $97,65 2,43 48822,5 03-Mai-2018 $97,65 2,43 0 0 48822,5 48822,5 FALSE 04-Mai-2018 $97,63 2,43 -150,00 -150,00 48822,5 48.672,50 TRUE 07-Mai-2018 $97,63 2,43 -4.882,25 -5.032,25 48672,5 43.790,25 TRUE 08-Mai-2018 $97,65 2,43 976,45 -4.055,80 43790,25 44.766,70 TRUE 09-Mai-2018 $97,65 2,43 122,06 -3.933,74 44766,7 44.888,76 TRUE 10-Mai-2018 $97,65 2,295 0,00 -3.933,74 44888,75625 44.888,76 TRUE 0 11-Mai-2018 $97,66 2,25 0,00 -3.933,74 44888,75625 44.888,76 TRUE 1 14-Mai-2018 $97,67 2,265 0,00 -3.933,74 44888,75625 44.888,76 TRUE 2 15-Mai-2018 $97,71 2,4 0,00 -3.933,74 44888,75625 44.888,76 TRUE 3 16-Mai-2018 $97,68 2,4 0,00 -3.933,74 44888,75625 44.888,76 TRUE 4 17-Mai-2018 $97,68 2,4 0,00 -3.933,74 44888,75625 44.888,76 TRUE 15 18-Mai-2018 $97,69 2,39 0,00 -3.933,74 44888,75625 44.888,76 TRUE 6 21-Mai-2018 $97,68 2,39 0,00 -3.933,74 44888,75625 44.888,76 TRUE 7 22-Mai-2018 $97,68 2,39 0,00 -3.933,74 44888,75625 44.888,76 TRUE 8 23-Mai-2018 $97,69 2,39 0,00 -3.933,74 44888,75625 44.888,76 TRUE 9 24-Mai-2018 $97,70 2,39 0,00 -3.933,74 44888,75625 44.888,76 TRUE 10 25-Mai-2018 $97,70 2,38 0,00 -3.933,74 44888,75625 44.888,76 TRUE 21 29-Mai-2018 $97,71 2,38 0,00 -3.933,74 44888,75625 44.888,76 TRUEcolumn D Closing margin account balance after margin calls 48.67250 43.790,25 44.766,70 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 44.888,76 COLUMN E COLUMN F COLUMN G M Interest income on the margin account Interest paid for margin calls Net margin account balance IOOOOOOOOODDDDDDDD 48.67250 43.79025 44.766,70 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.88876 44.888,76 44.888,76 44.888,76 97.34500 87.58050 89.533,40 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 89.77751 P INPUT DATA NUMBER OF CONTRACTS INVESTMENTAMOUNT 9 INITIAL MARGIN 45 MAINTENANCE MARGIN 4i N O P Q R S INPUT DATA NUMBER OF CONTRACTS 10000 INVESTMENT AMOUNT 976450 INITIAL MARGIN 48822,5 MAINTENANCE MARGIN 48822,5