If i know the loan term, the interest payable and the amount financed, is there a way to backward engineer the interest rate? (so items not present are repayment and interest rate)
This will be my final contribution to this discussion, since you keep changing the problem. Ironically, this is the problem that I thought you might be interested in solving.
Yes, it can be done -- to some degree. But with two inter-dependent variables, there are many solution. Moreover, the solution depends on the amortization model.
As I explained before, using your second example, if the principal loan ("amount financed") is $20,000, there are 71 payments ("loan term"), and the total interest is $6552 ("interest payable"), the regular (or average) payment is (20000+6552)/71 = $373.971830985916.
You can choose how to round that. But for whatever precision you choose, I suggest that you round
up in order to ensure that the last payment is no more than the regular payment.
You might round up to $373.98, $374, $375 (multiple of $5), etc. I will assume $374.
In a simple amortization model, which assumes equal "monthy" payments (30/360 day-count basis), the interest rate is =12*RATE(71, 374, -20000), which is 9.96699242996182%.
However, based on your previous data, I believe your (onlilne?) loan calculator uses an amortization model based on the daily interest rate and the actual days between payments. I had also determined that the loan calculator uses a different daily interest rate for normal and leap years. (Otherwise, the rounded amounts for the variables were not consistent with your original requirements.)
There is no Excel function that can estimate an interest rate for that model.
Instead, you can use Solver with the amortization model that I posted in response
#6 .
Enter $20,000 into H1, 71 into H2, $374 into H3, and a loan date (6/16/2019) into H8. Delete any value in H2, the derived interest rate, since Solver is influenced by the initial value.
(Note: An amortization model like this is sensitive to the loan date. If you choose a loan date in a different month, you might get a very different result.)
Then, the Solver set-up would be:
Set: J7
To Value: 6552
By Changing: H2
Click Solve
In my Excel (Excel 2010), Solver derives an interest rate of 9.96081722659562%. (Very close to the 30/360 model.)
Caveat: the Solver algorithms might change from one version of Excel to another; your version might derive a different result. And even within one Excel version, sometimes Solver derives different results with the same starting values(!).
Also note that Solver does not tolerate explicit rounding well. It would be prudent to remove all rounding in the amortization model. (I got lucky with the little rounding that I have.)
Finally, in theory, Solver can derive both a regular payment (unrounded!) as well as an interest rate. But in my model, the solution is too trivial without additional constraints. And remember: there are an "infinite" number of solutions (many pairs of regular payments and interest rates).
)
