Set up the test objects and data:

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 DECIMAL(8, 5)

)

RETURNS MONEY

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;

GO

----------------------------------------------------------------------------------------------------------------

--==== First check for and drop the procedure if it exists

IF (OBJECT_ID('tempdb..#spGetAmortization','p') IS NOT NULL)

BEGIN

DROP PROCEDURE #spGetAmortization;

END;

GO

--==== Start of procedure

CREATE PROCEDURE #spGetAmortization (@loan_id INT)

AS

SET NOCOUNT ON;

--==== Declare the local variables.

DECLARE @payment MONEY,

@Balance MONEY,

@cur_interest MONEY,

@cur_principle MONEY,

@cum_interest MONEY,

@cum_principle MONEY,

@periods INT,

@calc_rate DECIMAL(18, 16)--FLOAT does not work as well here;

--==== 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 (LOAN_ID, PERIOD)

)

--==== Set the local variables from the loans table.

SELECT @Balance = LOAN_AMT,

@periods = LOAN_PERIODS,

@payment = LOAN_PMT,

@calc_rate = (LOAN_RATE / LOAN_PER_ANUM),

@cum_interest = 0,

@cum_principle = 0

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 VALUES (@loan_id, 0, NULL, @Balance, 0, 0, 0, 0, 0);

--==== Now populate a row for each period. The values will be updated later. A loop

-- is used here for those folks who do not have a tally table.

DECLARE @i INT;

SET @i = 1;

WHILE (@i <= @periods)

BEGIN

INSERT INTO #amort

SELECT

LOAN_ID = @loan_id,

PERIOD = @i,

PMT_DT = DATEADD(MONTH, @i - 1, LOAN_FIRST_PMT_DATE),

BALANCE = 0,

PAYMENT = @payment,

CUR_INTEREST = 0,

CUM_INTEREST = 0,

CUR_PRINCIPLE = 0,

CUM_PRINCIPLE = 0

FROM #loans

WHERE LOAN_ID = @loan_id;

SELECT @i = (@i + 1);

END;

--==== Now update the values in the table.

UPDATE a

SET @cur_interest = cur_interest = ROUND(@balance * @calc_rate, 2),

@payment = payment = CASE period WHEN @periods THEN (@cur_interest + @Balance) ELSE payment END,

@cur_principle = cur_principle = CASE period WHEN @periods THEN @Balance ELSE (payment - @cur_interest) END,

@Balance = balance = (@balance - @cur_principle),

@cum_interest = cum_interest = (@cum_interest + @cur_interest),

@cum_principle = cum_principle = (@cum_principle + @cur_principle)

FROM #amort a

WHERE a.PERIOD > 0;

--==== store the calculated Total Interest back in the Loans table

UPDATE ln

SET ln.LOAN_INTEREST = @cum_interest

FROM #LOANS ln

WHERE ln.LOAN_ID = @loan_id;

SELECT * 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

-- Matches an actual, bank-provided Amortization Schedule

SELECT * FROM #LOANS;

I believe it is possible to make similar updates to Dwain's rCTE method, but I don't have time (especially not after doing this one) to work through the "type mismatch" issue I ran into.

Take care,

Solomon...