% error in sheet

[dellor]

Im trying to make a spreadsheet for rebalancing a portfolio. See pic:

The idea is that you enter values in € for each asset you are holding, (the B4 to B7 cells) and you get the total below as well as how much % each asset represents (C4 to C7)

You also enter the % that each asset should represent in a portfolio (D4 to D7).

With this then you get the devitation in % and € (E and F columns) and how should you distribute your next investment in order to compensate for said deviations (in this example, im investing 5000€ next, in the %'s seen in column G, and expressed in € next in column H)

The result is in J and K, but notice how in K (Rebalanced %) it doesn’t go back to 70%, 10%, 10%, 10%, but 70,51%, 9,49%, 10,51% and 9,49%

I don’t know why, it has to be something obvious but i’ve stared at this for too long now to realize.

[altasilvapuer]

Which of these cells are manual-entry and which are calculated?

The percentages in K are correct for the corresponding values in J. The values in J are not correct for the sum of B & H, assuming I’m reading this correctly and that’s your intent.

EDIT: Actually… I find it odd that the decimal portions of G and J are the same. I think something’s targeting a wrong column in one of your formulae. If you can share the formulae for each column, this would be much faster.

[dellor]

I have attached what I have so far.
example.ods (14.2 KB)

Light gray cells are the cells you enter the data.

[EarnestAl]

As I understand it, your goal is to add a new investment of x Euros and as a part of that you will attempt to re-balance the portfolio. I presume that exact re-balancing might not be possible because you might have to buy a whole number of units, hence the need for the spreadsheet.
Your spreadsheet should focus on the goal and work back from there.
Percentage75937.ods (12.3 KB)
Cheers, Al

[dellor]

You can buy fractions of a share. Why it wouldn’t be possible to get the Rebalanced € and % the same as the goal ones? I still don’t get it to be honest. I have attached the file above.

[EarnestAl]

My comment was merely on the starting situation.

If you enter your numbers into the spreadsheet I posted, you will see that the numbers and the percentages come out exactly. I didn’t give a final percentage column because it would duplicate the Goal column.

If you play with the New Investment cell, you will see that 1500 Euro investment is sufficient to balance the funds to the goal.

[dellor]

Right, it seems like it gets it accurately. I like to have these information columns to double check everything is working at all times. So there was an error in the way I got the deviation%. I will check tomorrow.

[dellor]

How can I add an error if the deviation goes above 15% or below 15% of the goal? I was trying to achieve that by getting the difference between the goal and current %, I used that and that is where I wasn’t getting the correct values in “Rebalanced %”.

Can I add a new column that says “Dev. % difference” (which would be “Dev. %”) in the example file I posted) and just use this? This is what I have now:

The idea is to know if it goes beyond this 15 or -15% treshold, then I would need a rebalance is needed. Is it working fine?

I still don’t quite understand what the Info Only Dev% means tbh but it’s working fine compared to how I was doing it.

[EarnestAl]

Sorry for slowness, the end of March/beginning of April is a busy time of year.

The column you added that appears to be Current%of total-Goal%of total which gives a percentage difference against total funds. Therefore if you have set a small goal for a fund, e.g. Emerging at 10% it won’t trigger a warning until it reaches 25% of the total fund or -5% of the total fund.

The Info Only Dev% column I put in gives the percentage difference against your goal for that fund. You could add Conditional Formatting of Cell value is not between 0.85 and 1.15

[dellor]

But the way I set it on the screenshot with the 2 columns with the “Info Only Dev % diff.” which is the current%-goal% is correct? I understand it better this way, so I can see how much it has diviated from the goal, and then it turns red if not between 15 or -15% (which would mean I need to rebalance)

[EarnestAl]

Because a change in one fund affects the total there your column is not entirely separated from the Goal% but it might be worth looking at the fractions. The top fraction is your calculation Dev % diff., the lower one is Dev %

Neither calculation might be what you are looking for.
Also as new investment becomes a smaller proportion of the total fund you will have to consider how to balance the funds by more than just new investment if one or more funds change value rapidly.