I don't pretend to understand your assignment, but that's OK. If we can distil it down into an algorithm, then we can help you code the VBA. And I suspect, given the nature of binary trees, that this code might be recursive.
I am still a little confused about what you're trying to code ... A binomial tree of order N has 2^N nodes. instead, you seem to trying to work with N^2 values?
Are we working with a single tree? Are you numbering the nodes? Or the levels? i.e. what do you mean by i and by t, and how do these relate to up, down and previous?
It would also probably really help if we could see all your code.
I'm really sorry for my vage problem description i thought if i say (i,j) its clear that I mean an array value and those are the labels of the nodes.The assignment changed I need to expand with fixed costs of 1500000 and 5000 human units Capacity. Well my code is at the beginning, and I forgot to completely consider the nonrecombining option.
My code is doing nothing yet because it is all just wrong.
My algorithm in words would be:
1) compute up and down factors, based on the volatillity I get from historical population data, formula u=exp(volatility*sqrt(delta_t))d=1/u, where delta_t= Time to maturity/ by number of steps. Compute risk-neutral probabilities p and (1-p) p= exp(riskfreerate*delta_t), these are real options so still donno which probabilities to use since water is not tradeable.
Costs fixed plant= 28 million
Starting Demand=50000
Starting capacity variable 55000 human units
Costs of this project 1 5million
T= 30 years
2)compute demand tree Demand(0,0)=50000 all values until now are seen as input for this function so that they are changeable
3)starting from Demand(0,0) or (1,1) doesn't matter,
SO now comes the great part were I'm stuck i think there should be just to much if statements or if-and statement.and the indexing so the (i,t)'s kill me
4) Compare capacity with demand as you go along the demand tree, so if demand > capacity choose min(expansion costs, penalty) if expansion add capacity and costs of expansion to initial values otherwise add penalty costs don't change capacity. The penalty cost is just a function which represents some opportunity costs for not treating the demand but also the angriness of the people who doesn't get water for drinking or showering etc. I set it in such a way up that the costs exponentially increase because not expanding and thus always paying penalty shouldn't be rational. so so if demand > capacity, Penalty=if((demand-capacity)/demand>0.02;exp((demand-capacity)/demand+1)*x*(demand-capacity);0), x is the penalty factor and is in this example 100. And unsatisfied demand of 2%=0.02 can be tolerated.
If demand < capacity then costs and capacity stay the same but are those from the previous nodes, values.
SO that would be the approach if the first path is a up node so demand increases, but if I go from the path down I'll get a different value for example for node (2,4) or (3,4), so the costs and I think the capacity to will be an non recombining tree,
and at the end nodes my payoff is costs max(fixed plant-costs flexible;0) at every node, and then backwards induction to (0,0) to get the option value.
I hope it is clear now,
here is my code so far but it doesn't make sense because I have to do it differently but here it is: I think it is even easier with fixed expansion costs and fixed additional capacity.
Sub DrawBinomialTree() '''''''''''''''''''''''''''''''''''''''
Dim stock() ' Declaring Variables
Dim cost() '''''''''''''''''''''''''''''''''''''''
Dim ud()
Dim final()
Dim i As Integer
Dim t As Integer
n = 4
u = 1.2
P0 = 100
d = 0.6
costflex = 20000000
CP = (costflex / P0)
ReDim stock(n, n)
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Cells.ClearContents
stock(1, 1) = P0
For t = 2 To n
stock(1, t) = stock(1, t - 1) * u
For i = 2 To t
stock(i, t) = stock(i - 1, t - 1) * d
Next i ' this loop start from for 1-2 to t or from T-2 to n
Next t
For t = 1 To n 'output of the tree in excel
For i = 1 To n
Cells(2 * n - i, n - t + 1) = stock(n - i + 1, n - t + 1) 'Cells(20 - i, 10 - t + 1) = stock(10 - i + 1, 10 - t + 1)
Next
Next
ReDim cost(n, n) 'cost tree
cost(1, 1) = costflex
For t = 2 To n
cost(1, t) = stock(1, t) * CP
For i = 2 To t
cost(i, t) = stock(i, t) * CP
Next i ' this loop start from for 1-2 to t or from T-2 to n
Next t
For t = 1 To n 'output of the cost tree in excel
For i = 1 To n
Cells(4 * n - i, n - t + 1) = cost(n - i + 1, n - t + 1) 'Cells(20 - i, 10 - t + 1) = stock(10 - i + 1, 10 - t + 1)
Next i
Next t
'Calculating unsatisfied demand for all up nodes '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For t = 2 To n 'If I remove this part I get also demand on intermediate nodes'
For i = 1 To t '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If stock(i, t) < stock(1, 1) Then '
stock(i, t) = stock(1, 1) '
Else '
stock(i, t) = stock(i, t) '
End If '
Next i '
Next t
For t = 1 To n 'output of the cost tree in excel
For i = 1 To n
Cells(6 * n - i, n - t + 1) = stock(n - i + 1, n - t + 1) 'Cells(20 - i, 10 - t + 1) = stock(10 - i + 1, 10 - t + 1)
Next i
Next t
ReDim ud(n, n)
ud(1, 1) = 0
For t = 2 To n
ud(1, t) = WorksheetFunction.Max((stock(1, t) - stock(1, t - 1)), 0)
Next t
For t = 2 To (n - 1)
For i = 2 To t
ud(i, t + 1) = WorksheetFunction.Max((stock(i, t + 1) - stock(i, t)), 0)
Next i
Next t
For t = 1 To n 'output of the unsatisfied demand in excel
For i = 1 To n
Cells(6 * n - i, n - t + 1) = ud(n - i + 1, n - t + 1)
Next
Next
'Choosing whether to pay the penalty or to expand
ReDim final(n, n)
final(1, 1) = cost(1, 1)
For t = 2 To n
For i = 1 To n
If cost(i, t) <= cost(i, t - 1) + ud(i, t) * 10 Then
final(i, t) = cost(i, t)
Else
final(i, t) = cost(i, t - 1) + ud(i, t) * 10
End If
Next i
Next t
For t = 1 To n 'output final tree
For i = 1 To n
Cells(8 * n - i, n - t + 1) = final(n - i + 1, n - t + 1)
Next
Next
End Sub