Excel help please...... - DPChallenge Forums

Nikon AF-S Nikkor 70-200mm f/2.8G ED VR II

07/24/2012 04:13:13 PM · #1

Ok I hope I word this right so that it is understandable. I am trying to design a spreadsheet that will monitor profits weekly and then plot it on a graph. The stock pays a dividend every month that gets re-invested at no charge. Plus I have purchased this stock a few times. I can get it to be accurate with one buy but I obviously am going about the formulas wrong to get it to calculate after a dividend / stock reinvest occurs. Does anyone know of a template or something that I can use to get it to give accurate numbers. Thanks in advance for any help

aliqui

Nikon D300s

07/24/2012 04:41:37 PM · #2

What formula do you have in your spreadsheet now?

Nikon AF-S NIKKOR 24-70mm f/2.8G ED

07/24/2012 05:41:46 PM · #3

pretty simple math....add this cell to this cell

$7.44 $6.98 $4.00 $9.47 28.0805 $0.46 29.2918 13.474228

col 1 = stock price
col 2 = orig purchase price
col 3 = commission
col 4 = profit col 8 - col 3
col 5 = numbeer of shares
these are for calculations only
col 6 = total amt of profit per share. col 2 - col 1
col 7 = recalc # shares sum of shares col
col 8 = total amt of profit col 6 x col 7

I hope this helps

Covert_Oddity

Nikon D700

07/24/2012 05:44:46 PM · #4

I would do it something like this:

Column 1 - No of shares
Column 2 - Current Share Price
Column 3 - Dividend

For the first cell in column 1, enter the current number of shares, from then on in column 1, the next cell down will the the previous cell + ((dividend / share price) rounded down to the nearest whole number)
This will give you the new number of shares assuming all the dividend is spent on shares at the current price.

The share price you'll need to enter for each month, along with the dividend unless you know how the dividend is paid out.

After all that you'll have a table with one row for each month, but you'll still have to enter the dividend and share price manually each month.

Is that the sort of thing you're looking to do?

Nikon AF-S NIKKOR 24-70mm f/2.8G ED

07/24/2012 05:52:04 PM · #5

that's on the right track but I would like to break it down to a weekly annotation not a monthly one. I have never really worked in excel so I am trying to teach myself as I go.

Covert_Oddity

Nikon D700

07/24/2012 06:05:54 PM · #6

That could pose you problems, if you only want to apply the dividend at the start (or end) of each month you'll have to factor 4 of 5 week months in unless you want to add a manual marker that shows "pay dividend on this week" kind of thing. If you want to automate it you'll need to use an IF statement that determines if you are within 7 days of the start day of a month, it would look something like this (assuming A1 has the date):

=IF(DAY(A1)<7, dividend calculation, weekly calculation)

Nikon AF-S NIKKOR 24-70mm f/2.8G ED

07/24/2012 06:11:46 PM · #7

You're bringing back memories of my C++ days...It has been so long since I have wrirtten anything like that but you have given me an idea

Covert_Oddity

Nikon D700

07/24/2012 06:13:56 PM · #8

Originally posted by cowboy221977:

You're bringing back memories of my C++ days...It has been so long since I have wrirtten anything like that but you have given me an idea

Looks like I've set you off in the right direction then. I'm off to bed, but good luck :)

Mike

Canon EOS-5D Mark II

Tamron SP 24-70mm f/2.8 Di VC USD Canon Mount

07/24/2012 07:06:43 PM · #9

have you check the excel templates from Microsoft, they may have one that works for you.

07/24/2012 09:18:45 PM · #10

I have looked but haven't found any that suits me yet

SDW