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.

  • 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

    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.

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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

    Jason A. Long - Wednesday, September 20, 2017 9:43 PM

    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.

    Yeah... no interest on interest... there's that nasty bong charge called a late fee which is worse.

    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

    Jeff Moden - Thursday, September 21, 2017 7:10 AM

    Jason A. Long - Wednesday, September 20, 2017 9:43 PM

    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.

    Yeah... no interest on interest... there's that nasty bong charge called a late fee which is worse.

    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).

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher - Thursday, September 21, 2017 10:19 AM

    Jason A. Long - Thursday, September 21, 2017 8:30 AM

    Jeff Moden - Thursday, September 21, 2017 7:10 AM

    Jason A. Long - Wednesday, September 20, 2017 9:43 PM

    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.

    Yeah... no interest on interest... there's that nasty bong charge called a late fee which is worse.

    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).

    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).

    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.

    • This reply was modified 5 months, 3 weeks ago by  Sergiy.
    • This reply was modified 5 months, 3 weeks ago by  Sergiy.

Viewing 8 posts - 91 through 98 (of 98 total)

You must be logged in to reply to this topic. Login to reply