Inverting non-square matrix for bootstrapping

[RaulSerg]

Hi, attached is an example of a topic I am currently looking into, some basic application of linear algebra in something called yield curve construction.
Any mathematicians may excuse my inexact language here.
I acknowledge this may chiefly be more of a math question than a (pure) Excel one.

My example is simplified for the purpose of illustrating how this works and what my problem is.

I am given 4 bonds with a certain cash flow profile, i.e. mathematically speaking basically 4 polynoms of degrees one to four which are not dependant upon each other in any way.
In rows 27 to 43, instead of iteratively as shown in the previous rows in the spreadsheet, I solved the equations with inverting the coefficient matrix and multiplying it with the constants matrix.

So far, so good.
The numbers and the technique employed makes sense to me (first block coloured in gray and which reconciles to the previous manual solutions).

To my problem:
Now I was trying to construct the same result vector but with a slightly reduced set of equations to work with.
I was taking equations 1, 2 and 4 of the previous example, so left out Bonds #3 which gives me a non-square matrix.
Whether or not there's still a solution to this matrix, I guess depends among others on its determinant.
In order to be able to invert the matrix, I multiplied both sides with the transposed matrix of A and solved in the same way as before.

I am now a bit puzzled at the results of this, the second gray vector.
Especially, given that the first two equations are the same as before these two equations alone should be sufficient to solve x1 and x2 of the variable matrix X,
how can the solutions be different than before?


Maybe someone can shed some light on whether my matrix transformations are incorrect technically or mathematically or what I am missing about the interpretation of the results.

Thank and Regards

[MrShorty]

I'm not sure if it answers the question, but what I see is that, in the case of the square 4x4 matrix, it is like you are solving a system of 4 equations with 4 unkowns. When you remove one of the "equations", you now have a system of 3 equations in 4 unknowns which is not solvable. Adding the transpose of A (B=AT*A*X --> X=B*(AT*A)-1) seems a different problem -- maybe something more like a "least squares regression" type of problem, but I usually visualize that as more of a "more than 4 equations with 4 unknowns".

I don't know how bootstrapping is supposed to be expressed in terms of linear algebra and matrix operations, but that's what I see so far.

[RaulSerg]

Yea, not sure how to explain in words that make sense to maths people.

You are correct, the first part is 4 equations with 4 variables. So, solvable unless determ(A) = 0 which is not the case here.
The matrix technique used is I guess more or less a standard text book example how to use Excel/linear algebra to solve linear equations.

Reducing the entire system of equations by one may indeed mean there's no unambiguous solution anymore.

My rationale was more driven by the goal of creating a square matrix again which is why I mulitplied the coefficients and constants matrix with the transpose of A.
Whether or not this is a sensible thing to do, I guess is part of my questions/problem.
A little different than what you wrote, I multiplied the transpose of A to both sides, i.e.:
A * X = B
A_T * A * X = A_T * B
(A_T * A)^-1 * (A_T * B) = X (not sure this is valid still?)
So unless some of this is wrong mathematically, applying the same calculations to both sides, shouldn't create a new equation system, right?
Just as if I would multiply e.g. E (unity matrix) on both sides.

A_T * A here does have a determ unequal to zero, so I thought there should be a solution possible.
But at the same time determ((A_T * A)^-1) <> determ(A_T * A)^-1, so something seems wrong.
But I am getting a bit lost in the relevant math here I guess.

Cheers

[MrShorty]

I can see what you are doing and, as near as I can tell, the functions are calculating "correctly" -- whatever that means for this problem. But the math and statistics are above my pay grade, so I really don't understand what is going on here.

As mentioned, what you are doing looks an awful lot like "least squares linear regression" (Y=B*X+error where Y is the vector of known y values and X is the matrix of known x values and B is the vector of coefficients), I tried approaching it that way. Select K3:O3 and array enter =LINEST(B3:B6,C3:F6,FALSE) and I get the same results as you get in H39:H42.

I then select put =LINEST(H47:H49,C47:G49,FALSE) into K47:O47, and I get something different. Two of the parameters are the same, one has become 0, and the fourth is changed a little.

Of course, the ultimate test for a "solve a system of equations" problem is to put the resulting x's back into the original equations, and calculate the y's and see if they match. To check the original 4x4 problem, I put =MMULT(C3:D6,H39:H42) into P39:P42 and got all 100s. Then I put =MMULT(C47:F49,H58:H61) into M58:M61 and got three different, non-100 but kind of close to 100 values, and MMULT(C47:F49,TRANSPOSE(K47:O47)) into N58:N61 and got three 100s.

It looks like the LINEST() function does something above and beyond the basic matrix inversion algorithm that is not the same as the MINVERSE() function. Since the math/stats/etc. of the problem are above my pay grade, I don't know if this is a suitable way to treat the problem. One would need to understand the math/statistics behind the problem to really understand if adding the AT multiplication to the problem and treating it like a least squares linear regression is appropriate. If it is, then maybe you can use the LINEST() function to do the "regression" more reliably than the MINVERSE() function.