iv answered the first page of this excel sheet i dont know how to answer the second one which is calculating the return on investment for the short sales. need an excel expert please.
Margin_Short (1) (1).xlsx - Trio Office Calc Eile Edit View Insert Format Styles Sheet Data Tools Window Help Abc Calibri 11 a a a a - % 00 A B C D E F G H - j 1 Ending Retum on Retum with St Price Investment No Margin Action or Formula for Column B $500,000.00 $500,000.00 (B4/B10)-B4 $100.00 Enter data $10,000.00 (B4/B10)/B6 $100.00 Enter data $5.40 50.00% 30.00% | $20.00 35.00 50.00 65.00 80.00 95.00 110.00 125.00 140.00 155.00 170.00 185.00 200.00 -153.2% -123.2% -93.2% -63.2% -33.2% -3.2% 26.8% 56.8% 86.8% 116.8% 146.8% 176.8% 206.8% -74.6% -59.6% -44.6% -29.6% -14.6% 0.4% 15.4% 30.4% 45.4% 60.4% 75.4% 90.4% 105.4% 8.00% 61 2 Buying on Margin 3 4 Initial Equity Investment 5 Amount Borrowed 6 Initial Stock Price 7 Shares Purchased 8 Ending Stock Price 9 Cash Dividends During Hold Per. 10 Initial Equity Percentage 11 Maintenance Margin Percentage 12 13 Rate on Margin Loan 14 Holding Period in Months 15 16 Retum on Investment 17 Capital Gain on Stock 18 Dividends 19 Interest on Margin Loan 20 Net Income 21 Initial Investment 22 Return on Investment 23 24 Margin Call: 25 Margin Based on Ending Price 26 Price When Margin Call Occurs 27 28 Return on Stock without Margin 29 30 31 $0.001 B7*(B8-B6) $54,000.00 B7*B9 $20,000.00 B5*(B14/12)*813 $34,000.00 B17 B18-B19 $500,000.00 B4 6.8% B20/21 ROI with/without Margin 250.00% 200.00% 150.00% 100.00% 50.00% 0.00% 50.00% (B76B8)-B5)/(B7*88) $71.43 85/(B7-(B11*B7)) -50.00% -100.00% Krozs $110,00 $95.00 $100.00 $125.00 $35 5.4% ((B8-B6)+B9)/B6 $50 555 - 150.00% $155.00 $170.00 $185,00 $200.00 -200.00% Investment No Margin B D E F G H J K L M 1 Ending Retum on St Price Investment Action or Formula for Column B $500,000.00 $100.00 10,000 (B4/B9/B5 $70.00 Enter data $0.00 50.00% 30.00% Return on 1200.00% 1000.00% 800.00% 600.00% 400.00% 2 Short Sales 3 4 Initial Investment 5 Initial Stock Price 6 Number of Shares Sold Short 7 Ending Stock Price 8 Cash Dividends Per Share 9 Initial Equity Percentage 10 Maintenance Margin Percentage 11 12 Retum on Short Sale 13 Capital Gain on Stock 14 Dividends Paid 15 Net Income 16 Initial Investment 17 Return on Investment 18 19 Margin Positions 20 Margin Based on Ending Price 21 22 Price for Margin Call 23 24 25 26 27 200.00% $10,000.00 $0.00 $10,000.00 $500,000.00 2.00% $170.00 160.00 150.00 140.00 130.00 120.00 110.00 100.00 90.00 80.00 70.00 60.00 50.00 40.00 30.00 20.00 10.00 B6*(B5-B7) B8B6 B13-B14 B4 B15/B16 00065 00 OITS 00 POOLS DOOZTS 00 DETS 00015 000 00 POSTS 00 OLIS 0.00% 580.00 $80.00 $70.00 00 OSS $ 30.00 $20.00 $ 40.00 00 OTS Return on investment 114.29% (B4+(B56B6)-B14-(B6*B7))/(16*B7) $115.38 (B4+(B5-B6)-814)/(16*(1+B10)) LEGEND: Enter data Value calculated See comment Margin_Short (1) (1).xlsx - Trio Office Calc Eile Edit View Insert Format Styles Sheet Data Tools Window Help Abc Calibri 11 a a a a - % 00 A B C D E F G H - j 1 Ending Retum on Retum with St Price Investment No Margin Action or Formula for Column B $500,000.00 $500,000.00 (B4/B10)-B4 $100.00 Enter data $10,000.00 (B4/B10)/B6 $100.00 Enter data $5.40 50.00% 30.00% | $20.00 35.00 50.00 65.00 80.00 95.00 110.00 125.00 140.00 155.00 170.00 185.00 200.00 -153.2% -123.2% -93.2% -63.2% -33.2% -3.2% 26.8% 56.8% 86.8% 116.8% 146.8% 176.8% 206.8% -74.6% -59.6% -44.6% -29.6% -14.6% 0.4% 15.4% 30.4% 45.4% 60.4% 75.4% 90.4% 105.4% 8.00% 61 2 Buying on Margin 3 4 Initial Equity Investment 5 Amount Borrowed 6 Initial Stock Price 7 Shares Purchased 8 Ending Stock Price 9 Cash Dividends During Hold Per. 10 Initial Equity Percentage 11 Maintenance Margin Percentage 12 13 Rate on Margin Loan 14 Holding Period in Months 15 16 Retum on Investment 17 Capital Gain on Stock 18 Dividends 19 Interest on Margin Loan 20 Net Income 21 Initial Investment 22 Return on Investment 23 24 Margin Call: 25 Margin Based on Ending Price 26 Price When Margin Call Occurs 27 28 Return on Stock without Margin 29 30 31 $0.001 B7*(B8-B6) $54,000.00 B7*B9 $20,000.00 B5*(B14/12)*813 $34,000.00 B17 B18-B19 $500,000.00 B4 6.8% B20/21 ROI with/without Margin 250.00% 200.00% 150.00% 100.00% 50.00% 0.00% 50.00% (B76B8)-B5)/(B7*88) $71.43 85/(B7-(B11*B7)) -50.00% -100.00% Krozs $110,00 $95.00 $100.00 $125.00 $35 5.4% ((B8-B6)+B9)/B6 $50 555 - 150.00% $155.00 $170.00 $185,00 $200.00 -200.00% Investment No Margin B D E F G H J K L M 1 Ending Retum on St Price Investment Action or Formula for Column B $500,000.00 $100.00 10,000 (B4/B9/B5 $70.00 Enter data $0.00 50.00% 30.00% Return on 1200.00% 1000.00% 800.00% 600.00% 400.00% 2 Short Sales 3 4 Initial Investment 5 Initial Stock Price 6 Number of Shares Sold Short 7 Ending Stock Price 8 Cash Dividends Per Share 9 Initial Equity Percentage 10 Maintenance Margin Percentage 11 12 Retum on Short Sale 13 Capital Gain on Stock 14 Dividends Paid 15 Net Income 16 Initial Investment 17 Return on Investment 18 19 Margin Positions 20 Margin Based on Ending Price 21 22 Price for Margin Call 23 24 25 26 27 200.00% $10,000.00 $0.00 $10,000.00 $500,000.00 2.00% $170.00 160.00 150.00 140.00 130.00 120.00 110.00 100.00 90.00 80.00 70.00 60.00 50.00 40.00 30.00 20.00 10.00 B6*(B5-B7) B8B6 B13-B14 B4 B15/B16 00065 00 OITS 00 POOLS DOOZTS 00 DETS 00015 000 00 POSTS 00 OLIS 0.00% 580.00 $80.00 $70.00 00 OSS $ 30.00 $20.00 $ 40.00 00 OTS Return on investment 114.29% (B4+(B56B6)-B14-(B6*B7))/(16*B7) $115.38 (B4+(B5-B6)-814)/(16*(1+B10)) LEGEND: Enter data Value calculated See comment
