Weighted Average

[amatthews]

I am trying to find out how many shares of stock I can purchase based off the following: A) Total money available to spend on stocks is $100. B) I want to buy two stocks. C) Stock #1 price is $10. D) Stock #2 price is $20. The question is this... How many shares can I buy if I can only purchase 11% of my total available spend of $100 for Stock #1 and how many shares can I buy if I can only purchase 5% of my total available spend of $100 for Stock #2 ? What would my formula look like to come up with the number of shares to buy for these two stocks with their respective % allocations based off and spend allowance of $100?

 

[Legacy 143009]

Asuume you have three columns. A- Total to Spend, B- Stock1 Price, C- Stock 2 Price

Then Column D will be:

=FLOOR(A1*0.11/B1,1)+FLOOR(A1*0.05/C1,1)

I think this is not this easy. Please provide more information.

 

[amatthews]

Hi! First off, thank you for taking the time to help me figure this out. Really appreciate it!

In Excel file I have things setup the following:

Column Headers: A1 = Stock, B1 Number of Shares, C1 = Portfolio Weightings (a percentage), D1 = Price Bought, E1 = Date Bought, F1 = Price on Date, G1 = Profit, H1 = Rating, I1 = Money to Spend

Under column header A1, I list out 5 stocks I want to buy with all the money I have to spend from what I enter in cell I1. Let's say it's $100 to keep it simple.

Each stock under column A1, will have their own respective Portfolio Weightings in column C1, which is a percentage of my overall investment for that stock based off the amount of money I have to spend.

I want to diversify my portfolio by percentages of the amount I have to spend for each of the 5 stocks, as noted in column C1.

In C1, I will have percentages listed for each of the 5 stocks I want to buy. I must only buy the number of shares for that stock as indicated by percentage found in column C1.

So if I have, for example, $100 to spend in cell I1, how can I auto populate the number of shares I would need to buy based off the percentage found in column C1 for that particular stock?

All I want to do is enter in the dollar amount I have to spend in cell "I1" for all the stocks listed and have the spreadsheet auto-populate the number of shares to buy in column B1.

Does my explanation help at all? Sorry if I am being even more confusing. Let me know.

Again, really appreciate your time!

Andy

Asuume you have three columns. A- Total to Spend, B- Stock1 Price, C- Stock 2 Price

Then Column D will be:

=FLOOR(A1*0.11/B1,1)+FLOOR(A1*0.05/C1,1)

I think this is not this easy. Please provide more information.