I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh

L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. I would suggest using Range Names to refer to
your ranges.

Where T is # of years
...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:
I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh

T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01*pm, Dana DeLouis wrote:
* L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
* what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. *I would suggest using Range Names to refer to
your ranges.

* Where T is # of years
* ...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:



I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix..

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.

minyeh- Hide quoted text -

- Show quoted text -

Oh. Ok. It looks like an interesting problem. :)

When you wrote...
( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. The
efficiency gets better as the power gets larger.

= = = = = =
HTH :)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:
T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01 pm, Dana wrote:
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. I would suggest using Range Names to refer to
your ranges.

Where T is # of years
...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:



I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.

minyeh- Hide quoted text -

- Show quoted text -

On Apr 23, 12:46*pm, Dana DeLouis wrote:
Oh. *Ok. *It looks like an interesting problem. *:)

When you wrote...

( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. *I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. *The
efficiency gets better as the power gets larger.

= = = = = =
HTH *:)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:



T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01 pm, Dana *wrote:
* *L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
* *what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. *I would suggest using Range Names to refer to
your ranges.

* *Where T is # of years
* *...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:

I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.

minyeh- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Ya, i know that limitation of using only the formula, am still
convincing IT dept to change their policy on that, but the approval
would take a while. if you can help with VBA, it'll be welcome as
well : )

Hi. You sent me a message with a 9 * 9 Input matrix, and the 9 * 9
solution.
This vba solution inputs your data from A1:I9, and outputs the data
below that beginning in A12.

The matrix converges quickly for a 1-Year. I only looped 20 times (not
infinity) and got the same solution.

I normally would call functions in my library. Therefore, this is a
quick and dirty way to do it. Because the solution was in percent, I
multiplied the solution by 100.

This is just one of a few ways to show how a vba solution is, what I
think, is much easier to handle.

Sub Convergence()
Dim Y As Long
Dim J As Long
Dim Fx
Set Fx = WorksheetFunction

Y = 1 '1 Year

With ActiveWorkbook.Names
.Add "Orig", [A1].Resize(9, 9).Value
.Add "M", [A1].Resize(9, 9).Value
.Add "Ans", IdentityMatrix(9)
.Add "Ans", [Ans+M]

For J = 2 To 20
.Add "k", Y / Fx.Fact(J) ' T/j!
.Add "M", [MMult(M, Orig)] 'Matrix to the next power
.Add "Ans", [Ans + k*M] 'Sum
Next J
.Add "Ans", [Ans*100]
[A12].Resize(9, 9) = [Ans]
End With

End Sub

Function IdentityMatrix(n)
Dim s
s = "0+(Row(#)=Column(#))"
s = Replace(s, "#", [A1].Resize(n, n).Address)
IdentityMatrix = Evaluate(s)
End Function

= = = = = = = = = =
HTH
Dana DeLouis



On 4/23/2010 3:09 AM, minyeh wrote:
On Apr 23, 12:46 pm, Dana wrote:
Oh. Ok. It looks like an interesting problem. :)

When you wrote...

( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. The
efficiency gets better as the power gets larger.

= = = = = =
HTH :)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:



T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01 pm, Dana wrote:
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. I would suggest using Range Names to refer to
your ranges.

Where T is # of years
...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:

I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.

minyeh- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Ya, i know that limitation of using only the formula, am still
convincing IT dept to change their policy on that, but the approval
would take a while. if you can help with VBA, it'll be welcome as
well : )


--
= = = = = = =
HTH :)
Dana DeLouis