Calculate Interest Rate - Loan Calculator

[Darren S]

Hi,

I need to calculate Interest Rate based on the following variables:

1. Payment
2. Present value
3. Future value
4. Term
5. Payments in advance or arrears

This is easy to do in Excel but I hav not been able to find an answer on how to do this.  I looked for Custom Function and API options.

Does anyone have a sample file or could point me to a solution?

Thanks in advance.

[Darren S]

Does anyone have a solution or workaround so that I can make this work?

Thanks

[comment]

This normally requires an iterative calculation. Please update your profile to show your version so that we know what you can use.

[Darren S]

31 minutes ago, comment said:

This normally requires an iterative calculation. Please update your profile to show your version so that we know what you can use.

Thank you.

I am using FM18.  Some users are still on FM16 but I can upgrade them if required.

[comment]

In v.16 you need to use a script or a recursive custom function. In v.18, you can use the While() function.

As I said earlier, there is no way to calculate the interest rate directly from the other loan parameters; it must be found by trial and error. A good algorithm for this is the Newton method. Here's a custom function I adapted from the article here:

InterestRate (principal ; payment ; periods ; guess) =

If ( 
not ( IsEmpty ( principal) or IsEmpty ( payment ) or IsEmpty ( periods ) )
;
Let ( [
fPMT = payment - payment * (1 + guess)^-periods - guess * principal ;
fPMT' = periods * payment * (1 + guess)^(-periods-1) - principal ;
nextGuess = guess - fPMT / fPMT'
] ;
If  ( Abs ( guess - nextGuess ) > .00000000001 ;
InterestRate ( principal ; payment ; periods ; nextGuess ) ;
nextGuess
)
)
)

 

You need to supply a guess to the function. In most cases, a guess of =

payment * periods / principal - 1 

will provide a good starting point and allow the function to converge on the wanted solution. However, if you want an excellent  guess, then continue reading the article where it describes the approximation method by David Cantrell. This provides results so close to the actual rate that in some scenarios it might be good to use on its own.

 

[Darren S]

Thanks again.

I used your formula and I can get it to work for payments in arrears and no future value.

Do you know how I would adapt the CF to allow for payments in advance and a future value that is > 0 ?

 

[comment]

There is no such thing as payments in advance. If you're paying at the beginning of a period, you're simply getting a smaller loan. So just subtract the first payment from the principal, reduce the number of payments by 1, and continue as usual.

As for future value, can you give an example? 

Edited December 7, 2019 by comment

[Darren S]

Thanks, the adjustment to my calculation works for payments at the beginning of the period.

With regards to future value, some loan contracts we arrange are structured with a future value or 'balloon' payment at the end of the term.  For example we might finance a car which has a loan amount of $60,000 and at the end of the 36 month term there is a $30,000 balloon payment due.

Excel allows for this in its PMT function.

In FM I use the following formula to calculate repayments:

The futurevalue is the balloon amount and advancearrears is either 1 or 0 (1 for payments at the beginning of the period).

The reason I need to calculate the rate, is that the loan amount will include fees and we need to determine the 'effective' rate, otherwise know as the customer rate.

 

 

 

 

 

[comment]

Sorry, I don't know. This is a completely different scenario from the other one, where only the rate function is missing: Filemaker has no functions at all to deal with this type of loan. I have a rough idea how to approach it, but it would take a lot of time that I am not willing to invest into this.

You might want to try and adapt the VBA code posted here:
https://www.mrexcel.com/board/threads/rate-formula-in-excel.277634/post-1364997
It looks promising, but I haven't tested it (and I am not sure how to test it).

 

[Darren S]

Ok.  I appreciate your help so far.

I'll look through the links and see if I can work it out.  If I find a solution I'll post it here.

Cheers.