I have the following fields set up.

Amount_Financed, Apr, Term, Payment, Payments made

What would be the calculation to determine the payoff (doesn't have to
be to the penny)

Thanks
JC

In article <1132711911.846630.3150 (AT) f14g2000cwb (DOT) googlegroups.com>, "JC"
<jc (AT) jclewis (DOT) biz> wrote:

That depends on what you mean by "payoff" - I'd guess the total amount
paid when you reach the end of the payments, but I've never done any
accounting so I don't know what the formula is for that. There are a
few Financial functions built-in to FileMaker, otherwise it should be
easy enough to implement the appropriate function (although it may
require a script in older versions of FileMaker if it require iterative
calculations).

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Dear JC:

The payoff usually means the remaining balance of the original loan
amount.In that case you would use the formula PV(periodic payment,
interest rate per period, number of periods).

For this formula to work, certain conditions are necessary:

1. each periodic payment is the same;
2. the interest rate is unchanged for the entire period of time;
3. each period is of the same length (usually a month)

If all three above conditions are not met, than you have to prepare an
amortization schedule for all payments, reflecting in each payment made
the amount of payment, the length of the period (the number of days),
and the interest charged for the period. It becomes messy, but it would
be the only method to compute how much of principal was repaid for the
period.

George


JC wrote:

This is an article I wrote many moons ago that might be handy to keep
around...

Building Your Own Annuity (Financial) Functions

You may need to calculate a very specific kind of annuity (financial)
function, but the appropriate calculation is not a built-in function in the
program you are using. Or, the function used by the program does not include
enough arguments to specify all the options you want in the calculation.
(For example, all FileMaker Pro financial functions assume that
payments/debits are made at the end of a period, not at the beginning.)

All financial functions are based on one large, complex formula that can be
re-arranged (using standard algebraic techniques) to obtain whatever value
you want. Let's look at a formula, and an example of how to derive a
function from it.

The Master Formula:

when rate *<> 0:
pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) /
rate) + fv = 0

when rate = 0:
pv + pmt * nper + fv = 0

in this formula,
pv = the present value of the investment
rate = the interest rate each period
nper = the number of periods
pmt = the payment amount each period
type = when payments are made.
(0 = end of the period, 1 = beginning of the period.)
fv = the future value of the investment

Note: Expenses (negative cash flow) are represented by negative numbers.
Income (positive cash flow) is represented by positive numbers. For example,
pmt is usually a negative number.

So, suppose that you wanted to find out the payment required for an
investment when the payments are made at the beginning of a period (the
FileMaker Pro PMT function works only with payments made at the end of a
period.) Here is how you would derive the needed formula:

1. Realize that your end goal is to isolate the PMT variable on one side of
the equals sign. You must be able to supply all other variables. Because the
interest rate in this case is not zero, we'll use the first formula.

2. PMT is buried inside the formula in a way that does not let us simply
subtract it from both sides or divide both sides by it. So we have to move
step-by step to simplify the component of the equation that contains PMT.

a. Subtract FV from both sides.
pv * (1 + rate)^nper + pmt * (1 + rate * type) * (((1 + rate)^nper - 1) /
rate) = -fv

b. Subtract pv * (1 + rate)^nper from both sides.
pmt * (1 + rate * type) * (((1 + rate)^nper - 1) / rate) = -fv - pv * (1 +
rate)^nper

c. Divide both sides by (1 + rate * type) * (((1 + rate)^nper - 1) / rate)
pmt = (-fv - pv * (1 + rate)^nper) / ((1 + rate * type) * (((1 +
rate)^nper - 1) / rate))

You can now fill in the values for the variables you know (fv, pv, rate,
nper, and type) and FileMaker Pro will calculate the correct payment.

Note: Due to the complexity of the equation, the Rate variable cannot be
solved by itself with basic algebraic methods. Other programs that do solve
for Rate do it through an iterative process (essentially guessing at a
probable number and continuing to guess until the number fits).

Bill

"George" <g.sova (AT) comcast (DOT) net> wrote