# USASpreadsheet to Calculate Margin Interest Deduction

#### P and L

I want to create a spreadsheet to track and calculate the margin interest deduction, which I think I can do based on monthly brokerage statements. However, there are a couple of things I'm not clear on, and I'm hoping someone here can help.

Subject to some limitations, margin interest is deductible for taxable investments. So if I borrow to buy stock and later for a cash advance, I can deduct only the margin interest corresponding to the percentage of the loan used for the stock. But if I carry a margin balance from month to month, how do I incorporate that in the monthly ratio?

For example, in January I borrow \$6000 to buy stock and \$4000 for a cash advance. I receive \$200 in dividends from the stock, which, according to the IRS, can be allocated to the cash advance first. So I owe \$9800, of which \$6000, or 61%, was for stock, and I can deduct that percentage of the margin interest.

The next month, I spend \$7000 on stock, \$3000 on a cash advance, and I receive \$250 in dividends. The stock represents \$7000/9750 = 72% of the total margin interest. But I still owe \$9800 from the previous month, which I'm also paying interest on. How do I account for that in the current month's interest payment? The total for the two months would be (\$6000 + \$7000)/(\$9800 + \$9750) = 66%. But I can't keep doing this every month (I hope), because the calculations get longer and longer.