CODEWITHSUNDEEP
oracle-databaseoracle12cfinance
Tomas Greif
Tomas Greif

Reputation: 22643

Calculate interest rate from monthly payment in SQL

Is there a way how to calculate interest rate knowing loan amount, monthly payment, and term in SQL (Oracle)? I can easily calculate the payment knowing the interest rate, however the opposite way seems to be much more difficult.

Monthly payment calculation (Interest rate = 0.1 (10%), Loan size = 1000, Term = 24):

   select (0.1/12 * 1000) / (1 - power(1 + 0.1/12, -24)) as mpayment 
   from dual;
   46.1449263375165

The question is how to go to from $46.14 monthly payment, $1,000 loan size, 24 months term and calculate 10% as a interest rate.

E.g. in MS Excel the function to use would be RATE()

Upvotes: 0

Views: 9891

Answers (1)

user5683823
user5683823

Reputation:

As I said in a Comment below your post: what you are looking for is called the "internal rate of return". Actually you are looking for a very special case - an amortized loan, with equal payments at regular intervals. Oracle offers an IRR function in add-on packages; if you want to use basic SQL and PL/SQL only, you will have to use a UDF (user-defined function).

Here is one way to code it, using Newton's method. I demonstrate a few things at the same time. Notice the numeric data types (which are specific to PL/SQL and can't be used in plain SQL; however, the runtime will convert the inputs from NUMBER to the PL/SQL data types, and the return value back to NUMBER, transparently). Using these data types in the code makes the function much faster - especially if you use native compilation (which is done as I show in the first line of code below).

So far everything should work in older versions of PL/SQL. Since version 12.1 only, and only if you are going to call the function primarily from SQL, you can use the pragma udf declaration - which will speed up plain SQL code that calls the function.

The function returns an "annualized" mortgage rate (it computes the monthly rate and then it simply multiplies by 12 - no compounding - since that's how mortgage interest rates work, at least in the U.S.). The rate is returned as a decimal number, not multiplied by 100; that is, not as a percentage. If the rate returned by the function is 0.038, that means 3.8% (ANNUAL mortgage interest rate). In the brief demo at the end, I should how you can wrap the function call within other SQL code to beautify the answer.

For the example at the end, I took a 200,000 principal value and calculated the monthly payment over 30 years (360 months) at 6.5% interest rate; I got a monthly payment of 1,264.14. Then I compute the interest rate from the other values.

The function requires the principal amount and the monthly payment, both NOT NULL and assumed positive. The term (IN MONTHS) is also needed, but I coded a default of 360. (Perhaps it would be better to code no default for this and make it required as well.) Optionally you can enter a desired precision; I coded a very high precision as default, since the computations are super-fast anyway.

I didn't code any kind of error handling; obviously that will have to be done, if you choose to use this function (or anything similar to it) for any purpose other than training/learning.

alter session set plsql_code_type = native;

create or replace function mortgage_rate(
  p_principal       simple_double
, p_monthly_payment simple_double
, p_term            simple_integer default 360
, p_precision       simple_double  default 0.00000001
)
return number
as 
  pragma udf;     --  Comment out this line if Oracle version is < 12.1
  z     simple_double  := p_monthly_payment/p_principal; 
  u     simple_double  := 1 / (p_term * z);
  v     simple_double  := 0;
  delta simple_double  := 0;
begin
  for i in 1 .. 100 loop
    v     := power(u, p_term);
    delta := ( z * u * ( v - 1) - u + 1 ) / ( z * (p_term + 1) * v - z - 1 );
    u     := u - delta;
    exit when abs(delta) < p_precision;
  end loop;
  return 12 * (1/u - 1);
end;
/


select to_char( 100 * mortgage_rate(200000, 1264.14, 360), 'fm990.000')
       || '%' as interest_rate
from   dual; 


INTEREST_RATE
----------------
6.500%

Upvotes: 3

Related Questions