MySQL Loan Origination and Accurate Payment Stream Calculations

loans.sql

-- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-- 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 = 0) THEN
		RETURN(1);
	END IF;

	SET if1 = interest + 1.0;

	IF(if1 = 0) THEN
		RETURN(0);
	END IF;

	IF(periods = 1) THEN
		RETURN(if1);
	END IF;

	IF(periods < 0) THEN
		SET periods = -1.0 * periods;
		SET if1 = 1.0 / 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 = 0) 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.0 * ((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.0) / 12.0);

	-- periods = 36 months
	SET periods = 36;

	-- present value = -1 * [principal] = -50.00
	SET present_value = -50.0;

	-- future value = 0
	SET future_value = 0.0;

	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(0);
	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(0);
	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(0);
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 >= 0) 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 > 0 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 < 0 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
--

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, 0, 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, 0, 2, the_year);

			IF(the_date = washington_day) THEN
				RETURN(1);
			END IF;

		WHEN 3 THEN
			-- no holiday in March
			RETURN(0);

		WHEN 4 THEN
			-- no holiday in April
			RETURN(0);

		WHEN 5 THEN
			-- Memorial Day, last Monday in May
			SET memorial_day = get_nth_weekday_of_month(-1, 0, 5, the_year);

			IF(the_date = memorial_day) THEN
				RETURN(1);
			END IF;

		WHEN 6 THEN
			-- no holiday in June
			RETURN(0);

		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(0);

		WHEN 9 THEN
			-- Labor Day, 1st Monday in September
			SET labor_day = get_nth_weekday_of_month(1, 0, 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, 0, 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(0);
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(0);
	END IF;

	-- check if date is a bank holiday
	IF(is_holiday(the_date) = 1) THEN
		RETURN(0);
	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 = 0) THEN
		RETURN(the_new_date);
	END IF;

	IF(days > 0) 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 > 0 DO
			SET the_new_date = DATE_ADD(the_new_date, INTERVAL 1 DAY);

			IF(is_weekend(the_new_date) = 1) THEN
				-- jump over weekend
				SET the_new_date = DATE_ADD(the_new_date, INTERVAL 1 DAY);
			ELSE
				-- jump over holidays
				IF(is_holiday(the_new_date) = 0) 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 < 0 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) = 0) 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, 0);
	INSERT INTO transactions VALUES (NULL, transfer_date, 1, 2, the_amount, 0);

	-- #####

	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, 0);
		INSERT INTO transactions VALUES (NULL, transfer_date, 1, 1, the_amount, 0);
	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(0);
		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)
	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 = 0;
		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(0);
		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 = 0.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 = 0.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(0);
			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$$

-- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=