SOLVED

balancing stock portfolio

Copper Contributor

Hello,

 

I need help with creating this table. I want to balance the stock portfolio in this way.

 

Khalid0090_1-1656176355427.png

 

From AA12 to AA15, we need to have the number of stocks (for each stock) that when we multiply them with there prices we get the Total value (From Z12 to Z15) that when we divied every total value on the total (Z16) we get the Target % (From Y12 to Y15) which should be the same as the Target % on column AG (From AG12 to AG15).

 

 

2 Replies
best response confirmed by Khalid0090 (Copper Contributor)
Solution

@Khalid0090 

 

I found it difficult (impossible!) to read your teeny tiny image; enlarging it just made it more unreadable.  So I did my best to duplicate your data. 

 

Refer to the following image and the attached Excel file.

 

JoeUser_0-1656211955046.png

 

Formulas (copy into similar cells):

B2:  =C2/$C$7

C7:  =SUM(C2:C5)

E2:  =C2/D2

F7:  =SUM(F2:F5)

G2:  =ROUND(F2*$C$7, 2)

G5:  =C7 - SUM(G2:G4)

G7:  =SUM(G2:G5)

H2:  =G2/E2

I2:  =G2-C2

I7:  =SUM(I2:I5)

 

We are given the current market values in column C.

 

We might be given the number of shares in column D and/or the current price per share in column E.  But it is prudent to double-check by calculating one of them.

 

I calculated the current price per share in column E.

 

Then, given the target allocation percentages in column F (the check-sum in F7 should be 100%), we can calculate the target market values in column G, rounding at least to the cent because we must have real currency values.

 

Finally, the amount to buy or sell is calculated in column I.

 

That presumes that we can buy and sell based on dollar amounts, not number of shares.  And in that case, the check-sum in I7 should be zero.

 

-----

 

That should work for mutual funds.

 

However, for individual stocks and ETFs, usually we must buy and sell whole shares.

 

The unrounded target number of shares is calculated in column H.

 

But if we must buy and sell whole shares, there are many choices that need to made.  It is difficult to automate them in an Excel file.

 

For example, is it acceptable to sell all shares in a position (see H4); or do you want to continue to hold at least one share, even if that throws the new allocation off?

 

Either way, the new allocation percentages will be different than the target.  So you might need to adjust your expectations.

 

Sorry for the teeny tiny image haha but thank you so much this is what I wanted, I really appricate your effort. Thanks a lot.
1 best response

Accepted Solutions
best response confirmed by Khalid0090 (Copper Contributor)
Solution

@Khalid0090 

 

I found it difficult (impossible!) to read your teeny tiny image; enlarging it just made it more unreadable.  So I did my best to duplicate your data. 

 

Refer to the following image and the attached Excel file.

 

JoeUser_0-1656211955046.png

 

Formulas (copy into similar cells):

B2:  =C2/$C$7

C7:  =SUM(C2:C5)

E2:  =C2/D2

F7:  =SUM(F2:F5)

G2:  =ROUND(F2*$C$7, 2)

G5:  =C7 - SUM(G2:G4)

G7:  =SUM(G2:G5)

H2:  =G2/E2

I2:  =G2-C2

I7:  =SUM(I2:I5)

 

We are given the current market values in column C.

 

We might be given the number of shares in column D and/or the current price per share in column E.  But it is prudent to double-check by calculating one of them.

 

I calculated the current price per share in column E.

 

Then, given the target allocation percentages in column F (the check-sum in F7 should be 100%), we can calculate the target market values in column G, rounding at least to the cent because we must have real currency values.

 

Finally, the amount to buy or sell is calculated in column I.

 

That presumes that we can buy and sell based on dollar amounts, not number of shares.  And in that case, the check-sum in I7 should be zero.

 

-----

 

That should work for mutual funds.

 

However, for individual stocks and ETFs, usually we must buy and sell whole shares.

 

The unrounded target number of shares is calculated in column H.

 

But if we must buy and sell whole shares, there are many choices that need to made.  It is difficult to automate them in an Excel file.

 

For example, is it acceptable to sell all shares in a position (see H4); or do you want to continue to hold at least one share, even if that throws the new allocation off?

 

Either way, the new allocation percentages will be different than the target.  So you might need to adjust your expectations.

 

View solution in original post