Mortgage amortization table
-
Miss a single payment and you gonna have an interest applied to the whole outstanding amount, including the previously accrued interest.
You don't pay interest on interest within a single statement period.
_____________
Code for TallyGenerator -
You don't pay interest on interest ever. Unpaid interest and principal balance are kept in two separate buckets and interest is only calculated on the principal.
The only way you'd ever end up paying interest on interest (on a fixed home loan loan) would be if you and the lender both agreed to rewrite the loan. In which case any unpaid interest from the original loan would be combined with remaining loan balance to form the basis of the new loan.
That said, I have seen cases where the borrower fell a single month behind, and remained a month behind, and ended up making payments for more than a year without a single penny going toward principal. - Jason A. Long - Wednesday, September 20, 2017 9:43 PM
Yeah... no interest on interest... there's that nasty bong charge called a late fee which is worse.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, September 21, 2017 7:10 AM
Late fees certainly don't help a person get back up when they've been knocked down but I don't know that they are worse. I worked for Sally Mae for a brief period of time in their call center, servicing student loans. I got to see first hand, loans that had nearly double in size, because they would offer (and borrowers were dumb enough to accept) forbearance on any past due loans... Not just a single "one time get out of jail free card" but over and over again. Some loans had as many as nine forbearances in their history (IIRC 9 is the max that a loan can legally have).
- Jason A. Long - Thursday, September 21, 2017 8:30 AM
Agreed. The late fees are, sadly, necessary because of many peoples' economic ignorance and avoidance. Indeed, the late fees probably prevent a lot of foreclosures, by getting people to pay each month rather than getting behind. Once most people got behind on a big expense like a mortgage, they would never get caught up (unless, by chance, they got their big income tax refund in time -- yet another example of economic ignorance, most people preferring to get a large tax refund when they could instead have invested the extra money themselves for all that time).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Thursday, September 21, 2017 10:19 AM
I am great fan of you sir ,Thanks for finding issues with Script ,I will change it soon .
-
I loved the way you represented information about mortgage Amortization table and your logic behind it.
-
I've got a shorter version of that procedure. No update required.
USE [tempdb];
GO
--==== Remove existing objects
IF (OBJECT_ID('tempdb..#LOANS','u') IS NOT NULL)
BEGIN
DROP TABLE #LOANS;
END;
IF (OBJECT_ID('tempdb..#LOAN_CUSTOMERS','u') IS NOT NULL)
BEGIN
DROP TABLE #LOAN_CUSTOMERS;
END;
IF (OBJECT_ID('fnCalculateMonthlyPayment','fn') IS NOT NULL)
BEGIN
DROP FUNCTION dbo.fnCalculateMonthlyPayment;
END;
--==== Create function dbo.fnCalculateMonthlyPayment
GO
CREATE FUNCTION [dbo].[fnCalculateMonthlyPayment] (
@loan_amt MONEY,
@periods INT,
@per_anum INT,
@rate FLOAT -- DECIMAL(8, 5)
)
RETURNS DECIMAL(10, 2)
WITH SCHEMABINDING -- can''t be deterministic without this; needed for computed column to be PERSISTED
AS
BEGIN
DECLARE @calc_rate FLOAT; --DECIMAL(18, 16) does not work so well here
SELECT @calc_rate = (@rate / @per_anum);
RETURN ROUND(
@loan_amt *
(
(@calc_rate * POWER(1 + @calc_rate, @periods))
/
(POWER(1 + @calc_rate, @periods) - 1)
)
, 2);
END;
GO
--==== Create and populate a customers table
CREATE TABLE #LOAN_CUSTOMERS
(
CUST_ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
CREATE_DATE DATETIME DEFAULT (GETDATE())
);
INSERT INTO #LOAN_CUSTOMERS (FIRST_NAME, LAST_NAME)
SELECT 'John', 'Doe'
UNION ALL
SELECT 'Jane', 'Buck';
--==== Create and populate a loans table
CREATE TABLE #LOANS
(
LOAN_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CUST_ID INT NOT NULL, -- If a real table, FK to LOAN_CUSTOMERS (CUST_ID)
LOAN_START_DATE DATE NOT NULL,
LOAN_FIRST_PMT_DATE DATE NOT NULL,
LOAN_PERIODS INT NOT NULL,
LOAN_RATE FLOAT NOT NULL,
LOAN_PER_ANUM INT NOT NULL,
LOAN_AMT MONEY NOT NULL,
LOAN_PMT AS (dbo.fnCalculateMonthlyPayment([LOAN_AMT], [LOAN_PERIODS], [LOAN_PER_ANUM], [LOAN_RATE])) PERSISTED,
LOAN_INTEREST MONEY
);
INSERT INTO #LOANS (CUST_ID, LOAN_START_DATE, LOAN_FIRST_PMT_DATE, LOAN_PERIODS, LOAN_RATE, LOAN_PER_ANUM, LOAN_AMT)
SELECT 1, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.05, 12, 200000
UNION ALL
SELECT 2, GETDATE(), DATEADD(MONTH ,DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.06, 12, 188100
UNION ALL
SELECT 2, GETDATE(), DATEADD(MONTH ,DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.03875, 12, 142000
UNION ALL
SELECT 3, GETDATE(), DATEADD(MONTH ,DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.06, 12, 100000;
GO
----------------------------------------------------------------------------------------------------------------
--==== First check for and drop the procedure if it exists
IF (OBJECT_ID('tempdb..#spGetAmortization','p') IS NOT NULL)
DROP PROCEDURE #spGetAmortization;
GO
--==== Start of procedure
CREATE PROCEDURE #spGetAmortization
(@loan_id INT)
AS
SET NOCOUNT ON;
DECLARE @periods INT;
--==== Create a temp table for the output of the procedure.
CREATE TABLE #amort
(
--LOAN_ID INT NOT NULL,
PERIOD INT NOT NULL,
PMT_DT SMALLDATETIME,
BALANCE MONEY NULL,
PAYMENT MONEY NULL,
CUR_INTEREST MONEY NULL,
--CUM_INTEREST MONEY NULL,
CUR_PRINCIPLE MONEY NULL,
--CUM_PRINCIPLE MONEY NULL,
CONSTRAINT [PK_#amort] PRIMARY KEY CLUSTERED (PERIOD)
)
--==== Set the local variables from the loans table.
SELECT @periods = LOAN_PERIODS
FROM #LOANS
WHERE loan_id = @loan_id;
--==== First populate a header row for the output. This will be period 0,
-- and will not show any activity
INSERT INTO #amort
(PERIOD,PMT_DT,BALANCE,PAYMENT,CUR_INTEREST,/*CUM_INTEREST,*/CUR_PRINCIPLE/*,CUM_PRINCIPLE*/)
SELECT 0, NULL, LOAN_AMT, 0, 0, 0
FROM #LOANS
WHERE loan_id = @loan_id;
--==== Now populate a row for each period.
-- Calculations for each period are based on the balances calculated for previous period
DECLARE @i INT;
SET @i = 1;
WHILE (@i <= @periods)
BEGIN
INSERT INTO #amort
(PERIOD,PMT_DT,BALANCE,PAYMENT,CUR_INTEREST,/*CUM_INTEREST,*/CUR_PRINCIPLE/*,CUM_PRINCIPLE*/)
SELECT @i, DATEADD(MONTH, @i - 1, LOAN_FIRST_PMT_DATE),
Balance - CASE @i WHEN LOAN_PERIODS THEN Balance ELSE (LOAN_PMT - ROUND(BALANCE * calc_rate, 2)) END,
CASE @i WHEN LOAN_PERIODS THEN (ROUND(BALANCE * calc_rate, 2) + BALANCE) ELSE LOAN_PMT END,
ROUND(BALANCE * calc_rate, 2),
/*CUM_INTEREST + ROUND(BALANCE * calc_rate, 2),*/
CASE @i WHEN LOAN_PERIODS THEN Balance ELSE (LOAN_PMT - ROUND(BALANCE * calc_rate, 2)) END/*,
cum_principle + CASE @i WHEN @periods THEN @Balance ELSE (@payment - ROUND(BALANCE * @calc_rate, 2)) END*/
FROM #amort a
inner join (
select LOAN_ID, LOAN_FIRST_PMT_DATE, LOAN_PMT,
LOAN_RATE / LOAN_PER_ANUM calc_rate, LOAN_PERIODS
from #loans ) l on l.LOAN_ID = @loan_id
WHERE a.Period = @i-1;
SELECT @i = (@i + 1);
END;
--==== store the calculated Total Interest back in the Loans table
UPDATE ln
SET ln.LOAN_INTEREST = cum_interest
FROM #LOANS ln
CROSS JOIN (select SUM(CUR_INTEREST) cum_interest from #amort) A
WHERE ln.LOAN_ID = @loan_id;
SELECT
@loan_id LOAN_ID ,
PERIOD ,
PMT_DT ,
BALANCE ,
PAYMENT ,
CUR_INTEREST ,
--CUM_INTEREST ,
CUR_PRINCIPLE
--CUM_PRINCIPLE
FROM #amort;
GO
--Run the examples:
EXEC #spGetAmortization 1;
-- Payment: 1073.64
-- Final Payment: 1076.48
-- Total Interest: 186513.24
EXEC #spGetAmortization 2;
-- Payment: 1127.75
-- Final Payment: 1132.25 (1132.24 when using Bankers' (i.e. ToEven) Rounding)
-- Total Interest: 217894.50 (217894.49 when using Bankers' (i.e. ToEven) Rounding)
EXEC #spGetAmortization 3;
-- Payment: 667.74
-- Final Payment: 665.75
-- Total Interest: 98384.41
EXEC #spGetAmortization 4;
-- Matches an actual, bank-provided Amortization Schedule
SELECT *
FROM #LOANS;As you can see, I used FLOAT only for all rate numbers, and no banker's rounding anywhere.
Not sure what was not working with FLOAT's for the guys back then.
All results seem correct:
Actually, more correct than in some online study examples, like this:
https://docs.google.com/spreadsheets/d/1A67RAZ3yWmpHG6euY38n3_58eWGVtq2q7ykxNRqRwEQ/edit#gid=0
When checking the discrepancies I found that they did not bother to round the Payment value to cents.
The spreadsheet just displays the rounded value, but the original value $599.55052515276 is used in all the calculations.
Not sure which online mortgage calculators may be trusted as a base for comparison.
And banks don't seem to bother with cents when it comes to repayments. All rounded to dollars:
At least on down-under side of the world.
Good the cumulative interest is lower when repayment amount is higher.
_____________
Code for TallyGenerator
Viewing 8 posts - 91 through 97 (of 97 total)
You must be logged in to reply to this topic. Login to reply