+ Reply to Thread
Results 1 to 6 of 6

GoalSeek, values to formats? help!

  1. #1
    Desmond
    Guest

    GoalSeek, values to formats? help!

    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!


  2. #2
    JNW
    Guest

    RE: GoalSeek, values to formats? help!

    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!
    >


  3. #3
    Desmond
    Guest

    RE: GoalSeek, values to formats? help!

    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!
    > >


  4. #4
    Desmond
    Guest

    RE: GoalSeek, values to formats? help!

    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!
    > >


  5. #5
    Desmond
    Guest

    RE: GoalSeek, values to formats? help!

    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!
    > >


  6. #6
    Desmond
    Guest

    RE: GoalSeek, values to formats? help!

    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!
    > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1