-1

I want to solve the following system of equations in Excel.

enter image description here

X1, X2, σ1 and σ2, and S are all known. I want to solve this system simultaneously to discover B and σ. This is relatively trivial to do by hand. I can discover B and σ for one point in time without any trouble.

However, in Excel I want to create a time series of B for various different values of S, X etc at different points of time. So I'll need to solve this system over and over again hundreds of times to create this time series.

What tool in Excel or VBA can I use to:

a) solve the above system of equations for given inputs of all variables except B and σ

b) automate the process so that I can solve the same system hundreds of times for different values of X1, X2, S, σ1 and σ2

beeba
  • 422
  • 9
  • 33

2 Answers2

2

Write a vba function that takes in the values of X1, X2, σ1, and σ2 and returns both B and Theta.

Here is how to write a function. http://www.excel-easy.com/vba/examples/user-defined-function.html

Here is how to return multiple values from one function. Return multiple values from a function, sub or type?

You won't be able to call a function directly from the worksheet that edits 2 cells. https://superuser.com/questions/818141/excel-possible-to-fill-two-cells-with-one-if-formula

Instead you will need to write a macro that calls the function over a range of values. How do I call a VBA Function into a Sub Procedure

You can set the range of arguments in the worksheet and just have your sub routine access the worksheet. Here is how to access the values in the worksheet. Excel VBA - read cell value from code

Community
  • 1
  • 1
JPlatts
  • 39
  • 4
2

You can solve the equations analytically to give these worksheet formulas:

B:

=-(1/sigma2-1/sigma1)/(LN(X2/S)/(X2-S)/sigma2-LN(X1/S)/(X1-S)/sigma1)

sigma:

=sigma1/(1+B*LN(X1/S)/(X1-S))

or

=sigma2/(1+B*LN(X2/S)/(X2-S))

You can also derive a formula for sigma that doesn't depend on B:

=(sigma2*(X2-S)/LN(X2/S)-sigma1*(X1-S)/LN(X1/S))/((X2-S)/LN(X2/S)-(X1-S)/LN(X1/S))

I assumed natural logarithms; if you intended base 10 logs then substitute LOG10 for LN.

Hope that helps

xidgel
  • 3,085
  • 2
  • 13
  • 22
  • thank you! can you show me how you derived this expression for B? I get a different solution when I solve the system of equations – beeba Apr 11 '16 at 13:21
  • @beeba Your solution is equivalent to mine. To get from yours to mine: 1. combine terms 1 and 2 in denominator to get n(S-w)ln(x/S). Same for terms 3 and 4 to get v(S-x)ln(w/S). 2. Divide both numerator and denominator through be (S-w)(S-x) leaving (n-v) in numerator and n*ln(x/S)/(S-x)-v*ln(w/S)/(S-w) in denominator. 3. Divide both numerator and denominator through by n*v leaving (1/v-1/n) in numerator and ln(x/S)/v/(S-x)-ln(w/S)/n/(S-w) in numerator. Then with a few small rearrangements you arrive at my solution. – xidgel Apr 11 '16 at 15:20
  • Oh I see! Thank you very much – beeba Apr 11 '16 at 15:22