cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

fit mutual fund data

ptc-4822034
1-Newbie

fit mutual fund data

I have been trying for hours to setup a solve block for what I thought would be a simple task with no results. I would like to enter data representing mutual funds and find weighting coefficients to find the best fit to a given set of data. For example Morningstar summarizes stock mutual funds with a 3x3 matrix that gives the percentage allocation between value, blend, growth and small, medium, large companies. I want to calculate weighting coefficients for each matrix to force a prospective portfolio of funds to be a best fit to a given matrix representing ideal value, blend, growth and small, medium, large.

14 REPLIES 14

Just post the worksheet here to show what you are trying to do.

I think I figured it out finally. The instructions said to use cntl = within the solve block but it started working when I changed to :=

Gary Rubert wrote:

I think I figured it out finally. The instructions said to use cntl = within the solve block but it started working when I changed to :=

That depends. You would have to use := for assigning guess values to the solved for variables, but you have to use the fat boolean = (ctrl 😃 for the constraints in your solve block.

But as it started working for you, you obviously had done it the right way.

RichardJ
19-Tanzanite
(To:ptc-4822034)

Your equatons had errors.

Your coefficients do not add to 1, because the error in both equations is being minimized. Neither one acts as a hard constraint. Your coefficents can also be negative, because they are not constrained in any way. Both of these things can be fixed, but there's not much point unless you first fix a much bigger problem. You have six unknowns, and only two equations, so there is an infinite number of solutions.

I had previously tried adding additional equations to limit the values to positive but 'w' still kept going negative. That is why it was left out on the example. Any suggestions to setup the problem correctly? The fourth column in the matrix represents portfolio allocation between domestic, international, and bonds+cash.

Constraints in a solve block with minerr are not hard constraints. You can weight them if you multiply with a high number. If in Richards sheet you insert 10^7*w>0 you get a positive solution for w (close to zero) but a slighty bader overall fit (check the value of variable ERR after the solve).

The solution will depend also much on the guess values and the algorithm chosen. LMA will give you the best fit most of the times, though, but you can change the algorithm to something else to see what happens.

Its not clear to me why you think that there exists a satisfactory solution to your problem, that is, why you think you can get fideal using a linear combination of the other six f's with coefficients summing up to 1.

As I see it you basically have 13 equations plus six constraints (all coefficients >0) but just six variables to play with.

Attached a slight modification of Richards sheet adding weights for your conditions and constraints.

RichardJ
19-Tanzanite
(To:Werner_E)

As I see it you basically have 13 equations plus six constraints (all coefficients >0) but just six variables to play with.

Yes, I guess it is 13 equations, not 2. Don't know what I was thinking when I said 2

Its not clear to me why you think that there exists a satisfactory solution to your problem, that is, why you think you can get fideal using a linear combination of the other six f's with coefficients summing up to 1.

There is a best fit (least squares) solution. I can see why Gary wants to do this, but what I don't see is how fideal could be defined in the first place. What is the optimum balance of funds with different size and value/growth ratings?

Yes, I guess it is 13 equations, not 2. Don't know what I was thinking when I said 2

And I had already typed (and sent) in my answer something about that "the problem still remains that there are only 2 equations and ..". Somehow it dawned on me then that this may not be true 🙂

There is a best fit (least squares) solution. I can see why Gary wants to do this, but what I don't see is how fideal could be defined in the first place. What is the optimum balance of funds with different size and value/growth ratings?

Maybe I should have read Gary's first post more thoroughly. As I understand it now it is mandatory that the coefficients (being percentages) add up to 1 and I guess the in every matrix f the left 3x3 should add to 100 as should the last column. Thats true for fideal but not not for the others. I changed the values slightly to ensure that and solved for just the first 5 coefficients, the last being the complement to one.

I encounter some strange effects: If the "weight" of the last two constraints are not the same or if I change the order by moving the first one a bit downwards, the solution changes significantly and the left 3x3 values don't add up to a perfect 100 anymore. Especially the last effect is inexplicably to me, as the 6 coefficients still would add up to a perfect 1.

05.10.png

05.10.png

RichardJ
19-Tanzanite
(To:Werner_E)

I tihnk it's some sort of weird round-off problem. If you make the weights 10^15 rather than 10^8 the order no longer matters.

Richard Jackson wrote:

I tihnk it's some sort of weird round-off problem. If you make the weights 10^15 rather than 10^8 the order no longer matters.

Yes, I noticed that when I sum 1000*X instead of X I see a slight difference from 1. Changing the last two weight higher cures is until I change the first weight from 10^0 to 10^1. I can't see any reason for that, but then roundoffs can be very tricky anyway.

Aslo the order of the f's matters - try to change f2 with f5.

The aprupt change of result values (especially the first one) is still surprising.

RichardJ
19-Tanzanite
(To:Werner_E)

Aslo the order of the f's matters - try to change f2 with f5.

The aprupt change of result values (especially the first one) is still surprising.

I can't reproduce that.

I can't reproduce that.

Which effect? The one I sent the screenshots of?

When I switch f2 for f5, the values don't change and the order of the constraints does not matter at all. However the sum of the elements the "left" 3x3 matrix is always 100.213, no matter how big I set the weights.

RichardJ
19-Tanzanite
(To:Werner_E)

OK. I see what you mean.

Yes I am looking for a best fit not a perfect fit. With your help I have a useful tool for what I want to try. When I use the Morningstar Xray tool to test the Mathcad model it matches very nicely. My goal is to periodically rebalance a portfolio of mutual funds. The ideal 3x3 matrix represents the total US market index. The fourth column is the allocation. I want to be able to move the allocation from 70/30 to 60/40 over time while controlling the international portion and keeping the equity portion as a best fit to the total market index.

Top Tags