# Financial function or script to emulate Excel Goal Seek feature

**alanfink@comcast.net**Apr 26, 2011 9:16 AM

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

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