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

    Excel PMT() function

    StanMillar

      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