3 Replies Latest reply on May 16, 2013 4:52 PM by StanMillar

# Excel PMT() function

Hi all

The PMT() function in Excel has two extra parameters compared to the equivalent in FileMaker.

These are fv (future value) and type (0 for payments in arrears as in a standard loan and 1 for payments in advance as in a lease)

After many hours spent attempting to get this Excel function to do what I want, I have finally succeeded. Now I need to convert this to FileMaker Pro (12).

Does anyone have a custom function that will do similar?

I have tried Michael Myett's "Monthly Payment ( Loan Amount ; APR ; Term ; Days To First Payment )" function but it looks like it only works for initial periods of greater than 30 days.

I need one that will work for weekly, fortnightly or monthly rentals with an initial rental payable on the day the goods are financed. This rental may be larger than the standard monthly rental (In the trade we call this a large initial rental). This will be entered by the user if required. I can code around this.

What I want is a function to calculate the payments based on:

Lease amount \$55000

Residual \$5000

Initial rental \$10000

Term 48 months (ie 47 payments plus initial rental)

Rate 30.21%

In Excel function PMT ( rate ; nper ; pv ; fv ; type ), I enter PMT(30.21%/12 ; 47 ; (55000 - 10000 - 5000) ; 5000 ; 1 ) which gives me a result of \$1480.63 as the monthly rentals.

I can send the spraedsheet if required.

Any takers?

TIA

Stan

• ###### 1. Re: Excel PMT() function

Hi Stan,

Perhaps this will help:

I also came up with the following calc which should do the trick, although I take no credit for the formula itself.

I googled around and found the formula in various formats, javascript etc and simply applied it to filemaker.

let ([

_rate =  30.21 / 1200 ;

_nper = 47 ;

_pv = 55000 - 10000- 5000 ;

_fv = 5000 ;

_type = 1

];

round (

( _rate * ( _pv * ( (_rate+1) ^ _nper ) + _fv ) ) / ( ( _rate * _type + 1 ) * ( ( (_rate+1) ^ _nper) -1 ) )

;2 )

)

Result = 1480.63

Message was edited by: Steve Wright - Added _ prefix to prevent conflict with FM functions

• ###### 2. Re: Excel PMT() function

Thanks Steve

I have been Googling for about 2 weeks without success. Shows how much info is out there!

I'll give your sugggestions a try.

• ###### 3. Re: Excel PMT() function

Steve

This is exactly what I was looking for.

Searched the FM help files but was obviously looking in the wrong place.

Thanks again for your help.

Now for the fun bit - working and scripting in FM. Love it!!