3 Replies Latest reply on Feb 24, 2012 1:28 PM by harrisonsa

    Financial function or script to emulate Excel Goal Seek feature

    alanfink@comcast.net

      Title

      Financial function or script to emulate Excel Goal Seek feature

      Post

      Excel has a nice feature to solve for a number tied to multiple dependent calculations based on a number.  Ideally I was looking for a plug-in for FM Pro Advanced 11.3 to provide this feature but can not find one.  

      Based on the following data, I would like to have a function that solves for Cap Cost Reduction

      Desired Function: Calculate and return cap cost reduction portion of total due on lease given the following Parameters ...
      a. Tax Rate in percentage form
      b. Term in months
      c. Money Factor
      d. Residual
      e. Gross Cap Cost
      f. Fixed Fees
      s. Total Due

      Given BASELINE EXAMPLE A below, yields a starting cash amount (total due) of 1601.01  

      In EXAMPLE B below, I need to solve for a total cash amount (total due) of 7849.99 (an additional cash amount of 6248.98 over BASELINE EXAPLE A).  Adding additional cash above the amount of 1601.01 results in change to all calculations in RED, and a portion of the additional 6248.98 is given to Cap Cost Reduction.  The correct amount of additional cash that gets attributed for Cap Cost Reduction is 5900.01.

      Excels Goal Seek Feature is great for this.  In the BASELINE EXAMPLE A below, using Excel Goal Seek you could give 3 input items and get the result.  IE: set Total Due (s) to 7849.99 by changing Cap Cost Reduction (g)

      I am looking for help in a function or plug in that may exist, or a good detailed example script to walk through test solving iterations.  Its got me stumped !

      BASELINE EXAMPLE A

      a Tax Rate: .0925

      b Term: 36

      c Factor: .00275

      d Residual: 15912

      e Gross Cap Cost: 24980

      f: Fixed Fees: 1202.96

      g Cap Cost Reduction: (?) =0

      h Adjusted Cap Cost: (e-g) =24980

      i Depreciation Over Term: (h-d) =9068

      j Monthly Depreciation: (i/b) =251.89

      k Monthly Rent Charge: ((h+d)*a) =112.45

      l Base Monthly Payment: (j+k) =364.34

      m Monthly Sales Tax: (l*a) =33.7

      n Total Monthly Payment: (l+m) =398.04

      Total Due Calculation

      o Fixed Fees: =1202.96

      p Tax on Cap Cost Reduction: (g*a) =0

      q 1st Payment: (n) =398.04

      r Cap Reduction: (?) =0

      s   Total Due: (o+p+q+r) = 1601.01

      SOLVED EXAMPLE B

      a Tax Rate: .0925

      b Term: 36

      c Factor: .00275

      d Residual: 15912

      e Gross Cap Cost: 24980

      f Fixed Fees: 1202.96

      g Cap Cost Reduction: (?) =5900.01

      h Adjusted Cap Cost: (e-g=19079.99

      i Depreciation Over Term: (h-d) =3167.99

      j Monthly Depreciation: (i/b) =88.00

      k Monthly Rent Charge: ((h+d)*a) =96.23

      l Base Monthly Payment: (j+k) =184.23

      m Monthly Sales Tax: (l*a) =17.047

      n Total Monthly Payment: (l+m=201.27

      Total Due Calculation

      o Fixed Fees: =1202.96

      p Tax on Cap Cost Reduction: (g*a) =545.75

      q 1st Payment: (n) =201.27

      r Cap Reduction: (?) =5900.01

      s    Total Due: (o+p+q+r) = 7849.99