EXCEL FORMULA INCLUDING EXPONENTIAL

[curious99]

Hi all

I want to translate that excel formula

=($B$4/1200)/(1-(1+$B$4/1200)^-$B$3)

in filemaker but I am lost when I get to exponential...how would you guys program it the fields being:

$B$4 = m_achat_taux int

[-Queue-]

Exp is the inverse of the Ln function. Can you not use ^ in the French version?

[curious99]

indeed, that does it...I've got another problem :

=VPM(B4/1200;B3;-B8)

what is the equivalent of VPM in filemaker?

thanks

:-)

Luc

[-Queue-]

I'm not sure what the equivalent function for VPM would be in English. What does it do?

[curious99]

here is the french description in Microsoft's help menu of Excel:

VPM(taux;npm;va;vc;type)

taux=le taux d'int

[Ugo DI LUCA]

PMT or REMBOUR is what you are after.

VPM (InterestRate/12; number of months;-Amount)

is equal to

Rembour(Amount; InterestRate / 12; number of months)

[Ugo DI LUCA]

Actually, the FM/Excel ways are just a simplification of

(K x (i/12))/(1-(1+(i/12))^-n)

where

K = Amount

i = Interest Rate

n = number of months

But you can still have it worked with the later formula, in case you need to put some additional variables

(Amount x (InterestRate/12))/(1-(1+(InterestRate/12))^(-1*NumberPayments))

[curious99]

thanks Ugo...it works!

I've got one last formula to work out for this project...it involves pmt(Rembour in french) once more but in excel vpm has more variables that what appears to be supported by filemaker's pmt

here it is:

=VPM(B15/1200;B14;-B19;B20;1)

once again here is the excel definition of vpm in the help menu

VPM(taux;npm;va;vc;type)

Pour une description plus compl

[Ugo DI LUCA]

Luc,

It's been some time since I last played with Excel, but the function you're trying to convert are used for different scenarios.

These functions would apply for Loans, Capitalization and Savings, which partly explains the use of different arguments to determine which of the 3 cases you're using.

FM's PMT function currently support Loans only.

Moreover, all of the functions you mentionned above could be either intra-dependant or used as a single numeric (calc) value. It makes the function dynamic and you can calculate any combination of principal, interest and terms.

As an example, FV ( ) (your VC future Value) is based on a construction of FV(RATE , nper, pmt, pv, type) while RATE ( ) uses a construction of type RATE (nper, pmt, pv, FV , type, guess)...

Obviously, Excel wouldn't help more than any other processor if in the later example, either FV ( ) or Rate ( ) wasn't defined as a fixed parameter.

This means that you may need to set the parameters first in FM too.

You could use a number field to determine if you're dealing with a Loan, a Capitalization or Savings, so as to determine (by a calc) what the values comprised in the FV, RATE, PV (your VA) or NPER (your NPM) colums should be converted to, and what formula to use.

BTW, PMT (rate, nper, pv, fv, type) is the equivalent of your VPM(taux;npm;va;vc;type)) and determines the periodic payment for an annuity.

Looking a bit more to these variables...

FV (FV (rate, nper, pmt, pv, type)) is your VC and represents the residual balance at the end of a payment stream. It is an optional argument, as it is necesserily equal to 0 if it is a loan.

PV (PV (rate, nper, pmt, fv, type)) is your VA and returns the present value of an investment. Its value would be equal to 0 for Savings, Positive for Capitalization and Negative for a Loan (unless you want the payments to be returned as negative values)

NPER(NPER (rate, pmt, pv, fv, type) ) is your NPM and returns the number of periods for an investment.

TYPE indicates when the payment is due. A type of Zero (or omitted) calculates the payment as due at the end of the period. A type of 1 calculates the payment as due at the beginning of the period.

As said, if FM's PMT function doesn't answer your need, you can revert it to some mathematical formulaes, all of these being interdependant as in Excel's in-built calculator :

PeriodPayment =

(Amount x (Rate/12))/(1-(1+(Rate/12))^(-1*NbPeriods))

or

(Amount*Rate/12*(1+Rate/12)^NbPeriods) / (((1+Rate/12)^NbPeriods)-1)

NbPeriods=(LN((12*PeriodPayment)/(12*PeriodPayment-Rate*Amount)))/(LN(1+Rate/12))

Amount = 12*PeriodPayment*((1+Rate/12)^NbPeriods - 1) / ( (1+Rate/12)^NbPeriods * Rate)

Several other formulae could be used to fit your needs, hopefully. When FM doesn't do it, a Math formula would do it.

HTH

[curious99]

wow, that is a fantastic answer...I'll play around with the different scenarios...with the great precision of your answer, I'll definitely am on the right path to work it out...thanks once more for all the time you take to answer my queries.

cheers

Luc

[Ugo DI LUCA]

Glad I could help...

Have a look here too as there may be some other answers to your needs.

Calculating Investment Returns

[George TOUBALIS]

Hi Ugo did you get my mail?

George Toubalis

Version: v6.x

Platform: Mac OS 9