Using Excel to find solution to least squares fit

[Special K]

Is there a way to have Excel find the least squares solution to an overdetermined system of equations? I'm thinking this would be a much better task for MATLAB, but if anyone knows a way to do this in Excel, it would be helpful.

If it matters, the application here is as follows:

I want to approximate Vanguard's Total Stock Market Index fund in my 401k using 3 other funds. I need to find the best way to allocate my money among these 3 funds that will produce the best approximation to the Total Stock Market Index fund, according to Morningstar's 9 style boxes.

Basically, there are 3 unknowns (% of money to allocate among each of the 3 funds) but 9 equations (the 9 Morningstar style boxes that are used to categorize the holdings of a mutual fund - each of the 9 boxes is assigned a number that represents its percentage of the fund's total holdings).

I am doing this mainly as a thought exercise. I have already found the actual solution using pencil, paper, and a calculator. I was interested in possibly making a spreadsheet that others could use by just plugging in the numbers. I like MATLAB, but the general population has never used that before.

 

[PowerEngineer]

I gather that you would ideally like to divide your investments among the three funds in a way that the weighted sum of their scores (in the Morningstar nine styles) comes as close as possible to the score for Vangraurd's TSMI. I also understand that you want to weigh the "goodness" of a possible solution by taking the sum of the squares of the differences on each of the nine style scores. The "best" solution is the one that minimizes the sum of the squares of the differences.

Excel isn't particularly good at solving nonlinear equations, but I have used Excel's "Solver" add-in on problems like this. From Excel Help:

Solver is part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. With Solver, you can find an optimal value for a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in one cell? called the target cell? on a worksheet. Solver works with a group of cells that are related, either directly or indirectly, to the formula in the target cell. Solver adjusts the values in the changing cells you specify? called the adjustable cells? to produce the result you specify from the target cell formula. You can apply constraints (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) to restrict the values Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.

The Microsoft Excel Solver tool uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University. Linear and integer problems use the simplex method with bounds on the variables, and the branch-and-bound method, implemented by John Watson and Dan Fylstra, Frontline Systems, Inc.

The formula in the "target cell" should be the sum of the squares of the differences between the test solution and the TSMI. The Morningstar style numbers for the TSMI and the three funds become cell entries referenced by the "target cell" formula. The investment percentages become the adjustable values that Solver gets to change as it seeks to minimize the "target cell" value. The constraints are that all investment percentages must be non-negative and that their sum must equal 100%

Not particularly pretty, but it should work.

 

[Thyme]

I suspect excel can do enough basic matrix algebra to solve the linear least squares equation. I don't know offhand, though.