Yes indeed, and I do use the PMT Function for payments in arrears , but it only works correctly if the first payment is made 30 days after the loan is started, it doesn't cater for those cases where the first payment is 0 days after the load started.
Perhaps you need a down payment field which then gets accounted for in your PMT calc.
Did consider that but application calls for x equal repayments, and to have a downpayment equal to the regular payment I would need to have aready used the PMT Function.
Hmmm, tricky. Can't think of another approach right now, but I'm sure there is a way. Keep thinking! Do you have FMAdvanced, by the way? The Data Editor makes it much easier to build complicated calculations.
Thank you , yes I do have FMAdvanced
Why not craft your own PMT function?
The PMT formula, though exponential, is not difficult.
You could prototype what you need in Excel then decide how you'd like to implement it in FMP.
For actual formulas: Compound interest - Wikipedia
I would use a microservice so I can do heavy lifting like that using Java, then call that service from FMP. The service runs so any FMP (or any other HTTP-enabled application) can use it. You can also use tools like POSTMAN to test the service.
I'll be glad to help you write a custom function, though mine would use a service-based approach so any user (from 1 to 10,000+ could use it for free).
HOPE THIS HELPS.
1 of 1 people found this helpful
If one makes an immediate payment, doesn't that reduce the principle by the amount of that first payment?
In any case, I'd search a custom function site such as the Brian Dunning site. You likely find one ready made that you can use.
That's a good question.
I don't know the answer in this specific case, but when you, say, buy a car, you already have an amortized payment booklet. So, even if you pay the first payment right away, it's just the first coupon in the booklet. All the payments are pre-computed. Now, maybe if you send in "extra" $ with a given payment coupon, over and above the expected payment amount, that might shave off some from the principle balance.
These rules can be tricky. And, must be understood completely before trying to write a custom function or using anyone else's for that matter.
Yes but when buying a car, one can make an initial down payment and the more you pay up front, the less you borrow. You don't pay interest on the cost of the car, rather the cost less the down payment. This initial zero month Payment feels like the same thing but that's probably just my ignorance here.
If I'm right though, it affects how you calculate these payments.
We just don't have the OP's full picture so it could be anything.
Thanks everyone for your great help and direction , on delving into this further I realised there are two extra parameters within the Excel PMT Function which are not available in the FMP Option and this hopefully illustrates the issue.
In the Excel PMT, there are additional options, "FV" and "Type"in the PMT argument , FV is straightforward Future Value and would normally be "0" but the last option "Type" is the whole crux of my question...If set to "0" it presumes payments are due at the end of a period, if set to "1" then payments are due at the beginning of the period.
If I can work out how to do this in FMP I will be very happy, but I cannot seem to locate the actual mathematical formula for the use of this "Type" option in the argument.
Here is a page where someone worked out the actual math behind Excel's implementation of PMT including the Type option.
It looks like the Excel formula is
In my quick test, this matches up to what Excel gives for the same input parameters. It also matches FileMaker's PMT() for FV=0 and Type=0.