I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!

Perhaps you could post what you have if you want us to "change" your code.

"Desmond" wrote:

I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!

To get cashflow at year end, i have
For t = 1 To 20
CostAllc(t) = Prem * a(t)
Interest(t) = (CFYE(t - 1) + CostAllc(t)) * i
CFYE(t) = CFYE(t - 1) + CostAllc(t) + Interest(t)
Next

The results are in
Cells(18 + t, 1).Value = CFYE(t - 1)
Cells(18 + t, 2).Value = Prem
Cells(18 + t, 3).Value = CostAllc(t)
Cells(18 + t, 4).Value = Interest(t)
Cells(18 + t, 5).Value = CFYE(t)
Range("E11").GoalSeek Goal:=Range("F11"), ChangingCell:=Range("B11")

"JNW" wrote:

Perhaps you could post what you have if you want us to "change" your code.

"Desmond" wrote:

I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!

Accidently posted the unfinished reply.

I got cashflow at year end calculated by VBA, and i know how to use GoalSeek
if it is calculated using formulas. My problem is how to combine these two?

Thanks a lot!

"JNW" wrote:

Perhaps you could post what you have if you want us to "change" your code.

"Desmond" wrote:

I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!

I have this example in VB, but could not figure out how to adapt to VBA.

Private Sub SolveFormula()
Me.CustomerAddress1Cell.Formula = "=(A1^3)"
Me.CustomerAddress1Cell.GoalSeek(27, Me.Range("A1"))
End Sub

"JNW" wrote:

Perhaps you could post what you have if you want us to "change" your code.

"Desmond" wrote:

I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!

I have figured out how:

Cells(20, 3).formula = "=B20 * VLookup(2, AllocRate, 2)"
Cells(20, 1).formula = "=E19"
Cells(20, 4).formula = "=(A20 + C20) * i"
Cells(20, 5).formula = "=A20 + C20+ D20"

Range("E20").GoalSeek Goal:=Range("F20"), ChangingCell:=Range("B20")

Here comes another problem: how to do it in a loop of 10 for example?


"Desmond" wrote:

I have this example in VB, but could not figure out how to adapt to VBA.

Private Sub SolveFormula()
Me.CustomerAddress1Cell.Formula = "=(A1^3)"
Me.CustomerAddress1Cell.GoalSeek(27, Me.Range("A1"))
End Sub

"JNW" wrote:

Perhaps you could post what you have if you want us to "change" your code.

"Desmond" wrote:

I have written VBA to project cashflows at year end for the next 10 years.
The complete formula is:

Cashflow at year end (t) = (cashflow at year start(t-1) + money coming in
at year start * (1- discount rate(t)))*(1+ interest rate)

I have breaken down to the following columns: Cashflow at year start, Money
in at year start, Discounted money in, Interest, and Cashflow at year end.

Now I set my expected Cashflow at year end, how to change my VBA code to
carry out GoalSeek in order to find how much money should come in each year
given other info unchanged?

Many many thanks!