MySQL Loan Origination and Accurate Payment Stream Calculations · GitHub

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= stored procedures for loan origination and payment streams -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DROP FUNCTION IF EXISTS fvif$$ CREATE FUNCTION fvif ( interest NUMERIC(20,8), periods INT(11) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN DECLARE if1 NUMERIC(20,8); IF(periods = ) THEN RETURN(1); END IF; SET if1 = interest + 1.; IF(if1 = ) THEN RETURN(); END IF; IF(periods = 1) THEN RETURN(if1); END IF; IF(periods < ) THEN SET periods = 1. * periods; SET if1 = 1. / if1; END IF; RETURN(POW(if1, periods)); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DROP FUNCTION IF EXISTS pmt$$ CREATE FUNCTION pmt ( interest NUMERIC(20,8), periods INT(11), present_value NUMERIC(20,8), future_value NUMERIC(20,8) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN monthly payment DECLARE pmt NUMERIC(20,8); numerator DECLARE num NUMERIC(20,8); denominator DECLARE denom NUMERIC(20,8); IF(interest = ) THEN pmt = -(fv + pv) / n SET pmt = 1 * (future_value + present_value) / periods; ELSE pmt = [[(pv + fv) / ((1 + i)^n – 1)] + pv] * -i SET num = (present_value + future_value); SET denom = fvif(interest, periods) 1; SET pmt = 1. * ((num / denom) + present_value) * interest; END IF; RETURN(pmt); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DROP FUNCTION IF EXISTS loan_payment$$ CREATE FUNCTION loan_payment ( interest NUMERIC(20,8) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN annual interest DECLARE annual_interest NUMERIC(20,8); periods DECLARE periods NUMERIC(20,8); present value DECLARE present_value NUMERIC(20,8); future value DECLARE future_value NUMERIC(20,8); ##### interest = annual interest, e.g. (10.00% / 100) / 12 SET annual_interest = ((interest / 100.) / 12.); periods = 36 months SET periods = 36; present value = -1 * [principal] = -50.00 SET present_value = 50.; future value = 0 SET future_value = .; RETURN(pmt(annual_interest, periods, present_value, future_value)); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= is_saturday() returns whether date is a Saturday DROP FUNCTION IF EXISTS is_saturday$$ CREATE FUNCTION is_saturday ( the_date DATE ) RETURNS TINYINT(1) DETERMINISTIC BEGIN weekday index (0 = Monday, 1 = Tuesday, …, 6 = Sunday) IF(WEEKDAY(the_date) = 5) THEN RETURN(1); ELSE RETURN(); END IF; END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= is_sunday() returns whether date is a Sunday DROP FUNCTION IF EXISTS is_sunday$$ CREATE FUNCTION is_sunday ( the_date DATE ) RETURNS TINYINT(1) DETERMINISTIC BEGIN weekday index (0 = Monday, 1 = Tuesday, …, 6 = Sunday) IF(WEEKDAY(the_date) = 6) THEN RETURN(1); ELSE RETURN(); END IF; END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= is_weekend() returns whether date is a Saturday or Sunday DROP FUNCTION IF EXISTS is_weekend$$ CREATE FUNCTION is_weekend ( the_date DATE ) RETURNS TINYINT(1) DETERMINISTIC BEGIN check if date date is a Saturday IF(is_saturday(the_date) = 1) THEN RETURN(1); END IF; check if date date is a Sunday IF(is_sunday(the_date) = 1) THEN RETURN(1); END IF; RETURN(); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= get_nth_weekday_of_month() returns nth weekeday of the month DROP FUNCTION IF EXISTS get_nth_weekday_of_month$$ CREATE FUNCTION get_nth_weekday_of_month ( nth INT(11), weekday_index INT(11), month INT(11), year INT(11) ) RETURNS DATE DETERMINISTIC BEGIN DECLARE the_date DATE; construct first day of month SET the_date = CONCAT(year, , month, -1); IF(nth >= ) THEN identify first specified weekday of month WHILE (WEEKDAY(the_date) <> weekday_index) DO add one day SET the_date = DATE_ADD(the_date, INTERVAL 1 DAY); END WHILE; SET nth = nth 1; WHILE nth > DO add one week SET the_date = DATE_ADD(the_date, INTERVAL 1 WEEK); decrement nth SET nth = nth 1; END WHILE; ELSE get last day of month SET the_date = LAST_DAY(the_date); identify first specified weekday of month WHILE (WEEKDAY(the_date) <> weekday_index) DO subtract one day SET the_date = DATE_SUB(the_date, INTERVAL 1 DAY); END WHILE; SET nth = nth + 1; WHILE nth < DO subtract one week SET the_date = DATE_SUB(the_date, INTERVAL 1 WEEK); increment nth SET nth = nth + 1; END WHILE; END IF; RETURN(the_date); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= is_holiday() returns whether date is a bank holiday
Read about:   Premier Mortgage Group (PMG) | Mortgage Lender Colorado, Nationwide
DROP FUNCTION IF EXISTS is_holiday$$ CREATE FUNCTION is_holiday ( the_date DATE ) RETURNS TINYINT(1) DETERMINISTIC BEGIN New Year’s Day, January 1 (or following Monday, if Sunday) DECLARE new_years_day DATE; Birthday of Martin Luther King, Jr., 3rd Monday in January DECLARE mlkjr_day DATE; Washington’s Birthday, 3rd Monday in February DECLARE washington_day DATE; Memorial Day, last Monday in May DECLARE memorial_day DATE; Independence Day, July 4 (or following Monday, if Sunday) DECLARE independence_day DATE; Labor Day, 1st Monday in September DECLARE labor_day DATE; Columbus Day, 2nd Monday in October DECLARE columbus_day DATE; Veterans Day, November 11 (or following Monday, if Sunday) DECLARE veterans_day DATE; Thanksgiving Day, 4th Thursday in November DECLARE thanksgiving_day DATE; Christmas Day, December 25 (or following Monday, if Sunday) DECLARE christmas_day DATE; ##### year of the date DECLARE the_year INT(11); get year of the date SET the_year = YEAR(the_date); get month of the date CASE MONTH(the_date) WHEN 1 THEN New Year’s Day, January 1 (or following Monday, if Sunday) SET new_years_day = CONCAT(the_year, -1-1); IF(WEEKDAY(new_years_day) = 6) THEN SET new_years_day = DATE_ADD(new_years_day, INTERVAL 1 DAY); END IF; IF(the_date = new_years_day) THEN RETURN(1); END IF; Birthday of Martin Luther King, Jr., 3rd Monday in January SET mlkjr_day = get_nth_weekday_of_month(3, , 1, the_year); IF(the_date = mlkjr_day) THEN RETURN(1); END IF; WHEN 2 THEN Washington’s Birthday, 3rd Monday in February SET washington_day = get_nth_weekday_of_month(3, , 2, the_year); IF(the_date = washington_day) THEN RETURN(1); END IF; WHEN 3 THEN no holiday in March RETURN(); WHEN 4 THEN no holiday in April RETURN(); WHEN 5 THEN Memorial Day, last Monday in May SET memorial_day = get_nth_weekday_of_month(1, , 5, the_year); IF(the_date = memorial_day) THEN RETURN(1); END IF; WHEN 6 THEN no holiday in June RETURN(); WHEN 7 THEN Independence Day, July 4 (or following Monday, if Sunday) SET independence_day = CONCAT(the_year, -7-4); IF(WEEKDAY(independence_day) = 6) THEN SET independence_day = DATE_ADD(independence_day, INTERVAL 1 DAY); END IF; IF(the_date = independence_day) THEN RETURN(1); END IF; WHEN 8 THEN no holiday in August RETURN(); WHEN 9 THEN Labor Day, 1st Monday in September SET labor_day = get_nth_weekday_of_month(1, , 9, the_year); IF(the_date = labor_day) THEN RETURN(1); END IF; WHEN 10 THEN Columbus Day, 2nd Monday in October SET columbus_day = get_nth_weekday_of_month(2, , 10, the_year); IF(the_date = columbus_day) THEN RETURN(1); END IF; WHEN 11 THEN Veterans Day, November 11 (or following Monday, if Sunday) SET veterans_day = CONCAT(the_year, -11-11); IF(WEEKDAY(veterans_day) = 6) THEN SET veterans_day = DATE_ADD(veterans_day, INTERVAL 1 DAY); END IF; IF(the_date = veterans_day) THEN RETURN(1); END IF; Thanksgiving Day, 4th Thursday in November SET thanksgiving_day = get_nth_weekday_of_month(4, 3, 11, the_year); IF(the_date = thanksgiving_day) THEN RETURN(1); END IF; WHEN 12 THEN Christmas Day, December 25 (or following Monday, if Sunday) SET christmas_day = CONCAT(the_year, -12-25); IF(WEEKDAY(christmas_day) = 6) THEN SET christmas_day = DATE_ADD(christmas_day, INTERVAL 1 DAY); END IF; IF(the_date = christmas_day) THEN RETURN(1); END IF; END CASE; RETURN(); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= is_business_day() returns whether date is a weekend or bank holiday DROP FUNCTION IF EXISTS is_business_day$$ CREATE FUNCTION is_business_day ( the_date DATE ) RETURNS TINYINT(1) DETERMINISTIC BEGIN check if date date is a Saturday or Sunday IF(is_weekend(the_date) = 1) THEN RETURN(); END IF; check if date is a bank holiday IF(is_holiday(the_date) = 1) THEN RETURN(); END IF; RETURN(1); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= get_business_day() returns the next or previous business day DROP FUNCTION IF EXISTS get_business_day$$ CREATE FUNCTION get_business_day ( the_date DATE, days INT(11) ) RETURNS DATE DETERMINISTIC BEGIN DECLARE the_new_date DATE; ##### SET the_new_date = the_date; IF(days = ) THEN RETURN(the_new_date); END IF; IF(days > ) THEN if starting on Saturday, begin on Sunday to jump over the weekend IF(is_saturday(the_new_date) = 1) THEN SET the_new_date = DATE_ADD(the_new_date, INTERVAL 1 DAY); END IF; WHILE days > DO SET the_new_date = DATE_ADD(the_new_date, INTERVAL 1 DAY); IF(is_weekend(the_new_date) = 1) THEN jump over weekend
Read about:   FHA vs Conventional Loan Chart
SET the_new_date = DATE_ADD(the_new_date, INTERVAL 1 DAY); ELSE jump over holidays IF(is_holiday(the_new_date) = ) THEN SET days = days 1; END IF; END IF; END WHILE; ELSE if starting on Sunday, begin on Saturday to jump over the weekend IF(is_sunday(the_new_date) = 1) THEN SET the_new_date = DATE_SUB(the_new_date, INTERVAL 1 DAY); END IF; WHILE days < DO SET the_new_date = DATE_SUB(the_new_date, INTERVAL 1 DAY); IF(is_weekend(the_new_date) = 1) THEN jump over weekend SET the_new_date = DATE_SUB(the_new_date, INTERVAL 1 DAY); ELSE jump over holidays IF(is_holiday(the_new_date) = ) THEN SET days = days + 1; END IF; END IF; END WHILE; END IF; RETURN(the_new_date); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= principal_balance(date, account_id) get current principal balance DROP FUNCTION IF EXISTS principal_balance$$ CREATE FUNCTION principal_balance ( the_date DATE, the_account_id INT(11) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN ##### DECLARE the_balance NUMERIC(20,8); SELECT SUM(blue) INTO the_balance FROM transactions WHERE (account_id = the_account_id) AND (date <= the_date); ##### RETURN(the_balance); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= interest_balance(date, account_id) get current interest balance DROP FUNCTION IF EXISTS interest_balance$$ CREATE FUNCTION interest_balance ( the_date DATE, the_account_id INT(11) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN ##### DECLARE the_balance NUMERIC(20,8); SELECT SUM(red) INTO the_balance FROM transactions WHERE (account_id = the_account_id) AND (date <= the_date); ##### RETURN(the_balance); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= balance(date, account_id) get current balance (principal and interest) DROP FUNCTION IF EXISTS balance$$ CREATE FUNCTION balance ( the_date DATE, the_account_id INT(11) ) RETURNS NUMERIC(20,8) DETERMINISTIC BEGIN ##### DECLARE the_balance NUMERIC(20,8); SELECT SUM(blue + red) INTO the_balance FROM transactions WHERE (account_id = the_account_id) AND (date <= the_date); ##### RETURN(the_balance); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= deposit(date, amount) create deposit transaction DROP FUNCTION IF EXISTS deposit$$ CREATE FUNCTION deposit ( the_date DATE, the_amount NUMERIC(20,8) ) RETURNS TINYINT(1) DETERMINISTIC BEGIN business days after which transfers are available DECLARE transit_days INT(11); actual transfer date DECLARE transfer_date DATE; ##### business days after which transfers are available SET transit_days = 3; get actual transfer date, transit_days after transfer date SET transfer_date = get_business_day(the_date, transit_days); INSERT INTO transactions VALUES (NULL, transfer_date, 2, 1, 1 * the_amount, ); INSERT INTO transactions VALUES (NULL, transfer_date, 1, 2, the_amount, ); ##### RETURN(1); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= withdraw(date, amount) create withdrawl transaction DROP FUNCTION IF EXISTS withdraw$$ CREATE FUNCTION withdraw ( the_date DATE, the_amount NUMERIC(20,8) ) RETURNS TINYINT(1) DETERMINISTIC BEGIN business days after which transfers are available DECLARE transit_days INT(11); actual transfer date DECLARE transfer_date DATE; DECLARE the_principal_balance NUMERIC(20,8); DECLARE the_interest_balance NUMERIC(20,8); DECLARE the_remainder NUMERIC(20,8); ##### business days after which transfers are available SET transit_days = 3; get actual transfer date, transit_days after transfer date SET transfer_date = get_business_day(the_date, transit_days); SET the_principal_balance = principal_balance(the_date, 2); IF(the_principal_balance >= the_amount) THEN INSERT INTO transactions VALUES (NULL, transfer_date, 2, 2, 1 * the_amount, ); INSERT INTO transactions VALUES (NULL, transfer_date, 1, 1, the_amount, ); ELSE SET the_remainder = the_amount the_principal_balance; SET the_interest_balance = interest_balance(the_date, 2); IF(the_interest_balance >= the_remainder) THEN INSERT INTO transactions VALUES (NULL, transfer_date, 2, 2, 1 * the_principal_balance, 1 * the_remainder); INSERT INTO transactions VALUES (NULL, transfer_date, 1, 1, the_principal_balance, the_remainder); ELSE RETURN(); END IF; END IF; ##### RETURN(1); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= originate(date, interest, credit_grade) get current principal balance get current interest balance create new account create loan origination transaction create all loan payment transactions DROP FUNCTION IF EXISTS originate$$ CREATE FUNCTION originate ( origination DATE, the_interest NUMERIC(20,8), the_credit_grade VARCHAR(2) ) RETURNS TINYINT(1)
Read about:   Important Points to Consider Before Applying For Mortgage Loan Modifications
DETERMINISTIC BEGIN ##### 12 months per year DECLARE periods_per_year INT(11); all Prosper loans have 3 year terms or 36 months DECLARE periods INT(11); business days after which payments are available DECLARE transit_days INT(11); average days per month – 365 days per year / 12 months per year DECLARE average_days_per_month NUMERIC(20,8); 0.5% service fee – annual DECLARE annual_service_fee_rate NUMERIC(20,8); 0.5% service fee – daily rate DECLARE daily_service_fee_rate NUMERIC(20,8); convert annual interest to daily modified interest rate DECLARE daily_modified_interest_rate NUMERIC(20,8); ##### get monthly amount payment amount DECLARE payment_amount NUMERIC(20,8); get first due date, one month after origination DECLARE due_date DATE; actual payment date DECLARE payment_date DATE; running principal balance DECLARE running_principal_balance NUMERIC(20,8); monthly principal payment DECLARE principal_amount NUMERIC(20,8); monthly interest payment DECLARE interest_amount NUMERIC(20,8); monthly service fee amount DECLARE service_fee NUMERIC(20,8); monthly net received DECLARE net_received_amount NUMERIC(20,8); loop index DECLARE i INT(11); ##### DECLARE the_principal_balance NUMERIC(20,8); DECLARE the_interest_balance NUMERIC(20,8); DECLARE the_remainder NUMERIC(20,8); the principal and interest amounts DECLARE the_principal_amount NUMERIC(20,8); DECLARE the_interest_amount NUMERIC(20,8); DECLARE the_account_id INT(11); ##### SET the_interest_balance = interest_balance(origination, 2); IF(the_interest_balance >= 50.00) THEN SET the_principal_amount = ; SET the_interest_amount = 50.00; ELSE SET the_remainder = 50.00 the_interest_balance; SET the_principal_balance = principal_balance(origination, 2); IF(the_principal_balance >= the_remainder) THEN SET the_principal_amount = the_remainder; SET the_interest_amount = the_interest_balance; ELSE RETURN(); END IF; END IF; ##### INSERT INTO accounts VALUES (NULL, New Loan); SET the_account_id = LAST_INSERT_ID(); INSERT INTO loans VALUES (NULL, the_account_id, the_interest, the_credit_grade, origination); INSERT INTO transactions VALUES (NULL, origination, 2, 2, 1 * the_principal_amount, 1 * the_interest_amount); INSERT INTO transactions VALUES (NULL, origination, 1, the_account_id, the_principal_amount, the_interest_amount); ##### 12 months per year SET periods_per_year = 12; all Prosper loans have 3 year terms or 36 months SET periods = 3 * periods_per_year; business days after which payments are available SET transit_days = 4; average days per month – 365 days per year / 12 months per year SET average_days_per_month = (365 / 12); 0.5% service fee – annual SET annual_service_fee_rate = 0.005; SET annual_service_fee_rate = .000; 0.5% service fee – daily rate SET daily_service_fee_rate = annual_service_fee_rate / 365 * average_days_per_month; ##### convert annual interest to daily modified interest rate SET daily_modified_interest_rate = ((the_interest / 100) annual_service_fee_rate) / periods_per_year; ##### get monthly amount payment amount SET payment_amount = loan_payment(the_interest); running principal balance SET running_principal_balance = 50.00; get first due date, one month after origination SET due_date = DATE_ADD(origination, INTERVAL 1 MONTH); ##### all loans have 3 year terms, 36 months set index SET i = 1; WHILE i <= periods DO get actual payment date, transit_days after due date SET payment_date = get_business_day(due_date, transit_days); calculate monthly interest payment SET interest_amount = running_principal_balance * daily_modified_interest_rate; calculate monthly service fee SET service_fee = running_principal_balance * daily_service_fee_rate; handle final catch-up payment differently IF(i = periods) THEN SET payment_amount = running_principal_balance + interest_amount + service_fee; END IF; calculate monthly net received SET net_received_amount = payment_amount service_fee; calculate monthly principal payment SET principal_amount = net_received_amount interest_amount; ##### SET principal_amount = principal_amount; SET interest_amount = interest_amount; ##### SET the_principal_balance = principal_balance(payment_date, the_account_id); IF(the_principal_balance >= principal_amount) THEN SET the_principal_amount = principal_amount; SET the_interest_amount = .00; ELSE SET the_remainder = principal_amount the_principal_balance; SET the_interest_balance = interest_balance(payment_date, the_account_id); IF(the_interest_balance >= the_remainder) THEN SET the_principal_amount = the_principal_balance; SET the_interest_amount = the_remainder; ELSE RETURN(); END IF; END IF; INSERT INTO transactions VALUES (NULL, payment_date, 2, the_account_id, 1 * the_principal_amount, 1 * the_interest_amount); INSERT INTO transactions VALUES (NULL, payment_date, 1, 2, principal_amount, interest_amount); ##### update running principal balance SET running_principal_balance = running_principal_balance principal_amount; advance due date one month SET due_date = DATE_ADD(due_date, INTERVAL 1 MONTH); increment index SET i = i + 1; END WHILE; RETURN(1); END$$ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

13 thoughts on “MySQL Loan Origination and Accurate Payment Stream Calculations · GitHub

Leave a Reply

Your email address will not be published.