Spreadsheet to calculate ESOP? (2000)

Topic

New Reply

I’m wrestling with my taxes and I’ve been trying to find a simple spreadsheet to help me calculate my Employee Stock Ownership Plan. All I want is something that will let me enter the date, number of shares bought and the cost. On the reverse, it would also let me say I sold X number of shares from date Y at cost Z. Then it would give me a profit/loss sheet. Has anyone seen or made a sheet like this?

Reply | Quote

Replies

Briana

This is not too difficult.
1) have a Column for Dates.
2) Have a column for Number of Shares.
3) Have a column for Cost per share
4) Have a column with the formula that would multiple the Number of shares bought by the Cost per share. This will give you the cost of buying these shares.
5) Have a column for Shares sold.
6) Have a column with the formula to subtract the shares sold from the shares bought.
7) Have a column for the cost of selling the shares.
8) Have a column with a formula to calculate your P/L.

Now if activity happens on a certain date, well enter all these numbers on the row for that date.

How about that?

Wassim

Reply | Quote

Wassim:

I’ll give it a try! Thanks!

Brian A.

Reply | Quote

Briana, I went to get mine and discovered that I have forgotten the password. OUCH!

But I also have a version on my Palm, so here it is, censored so as not to show any of my actual personal data (except I left in my company’s standard three year staggered vesting). My Palm spreadsheet, Quicksheet, does not carry all the capabilities of the Excel version, in particular I have conditional formatting to indicate long and short term capital gains.

The other thing that’s REALLY sloppy in my design is that I do not have a bulletproof way to calculate a series of partial exercises of one particular grant at a number of different times; as you’ll see from the formulas in certain of cells N8:R9, I basically hand edit the formulas to get the correct calcs. As you exercise a grant in more and more ‘bits’ this will get messier.

Use at your own risk; this works for me, it may not work for you.

Now what was that darn password? Grumble, grumble.

Reply | Quote