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

# Financial function or script to emulate Excel Goal Seek feature

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

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

• ###### 1. Re: Financial function or script to emulate Excel Goal Seek feature

There are a couple of web sites that offer free custom functions, briandunning.com being the oldest, but not the only one. If these isn't one that does the trick you might be able to hire some who has written one similar to what you are looking for to create one for you.

• ###### 2. Re: Financial function or script to emulate Excel Goal Seek feature

Bumper...Thanks for the site recommendations

• ###### 3. Re: Financial function or script to emulate Excel Goal Seek feature

Hey, just wondering if you ever solved this as I am also looking for a Goal Seek function in Filemaker Pro. Thanks