The stored proc I was attempting to create is a fairly simple "Amortization Schedule". So here is how you go about building an Amortization Schedule from scratch.
Because I'm an old school programmer here's my very simple method of deciding how to do just about every programming task:
Gunny's Rules
- 1. Identify the output
- 2. Identify the inputs
- 3. Ask ====> Are there enough inputs to create that output?
This is really bone simple but it works. Or as I used to say to my Major... "It's Gump Proof" (Yeah I know I'm a Marine but it's still a cool saying. Calling something Gomer Proof doesn't have the same meaning.)
Identify The Output:
A database table that contains payment information for each monthly payment in an Amortization Schedule. To be more specific:
- Payment Number
- Payment Date
- Balance Before Payment
- Payment Amount
- Principal Portion of Payment
- Interest Portion or Payment
- Balance After Payment
- Payment Start Date
- Loan Amount
- Annual Percentage Rate (APR)
- Length of Loan in Months
Answer: No. I need the payment amount.
Decision Time: Okay, there are two choices at this point.
- Ask for this value to be supplied just like the other values
- Calculate the payment based on the existing input values
Identify The Inputs:
- Payment Start Date
- Loan Amount
- Annual Percentage Rate (APR)
- Length of Loan in Months
- Loan Payment Formula
Answer: No. I need two more bits of information.
- Another formula for calculating the Interest Portion of the payment
- A method for advancing the Payment Date by one month
Identify The Inputs:
- Payment Start Date
- Loan Amount
- Annual Percentage Rate (APR)
- Length of Loan in Months
- Loan Payment Formula
- Interest Payment Formula
- Date Math Routine
Answer: Yes
That is Gunny's simple Gump Proof way of gathering requirements. Here is a Microsoft SQL Server version of the stored procedure. Next time I will show you my working but lame attempt at creating the same amortization stored procedure using ElevateDB. Enjoy!
----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
----------------------------------------------------------------
CREATE PROCEDURE [dbo].[spAmortizationSchedule]
----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------
@StartDate datetime = '2011-09-15' ,
@Principal decimal(19,2) = 195000 ,
@APR decimal(19,4) = 3.25 ,
@Months integer = 180
AS
----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------
DECLARE @Payment decimal(19,2)
DECLARE @PaymentLast decimal(19,2)
DECLARE @PmtNumber int
DECLARE @PmtDate date
DECLARE @BalanceStart decimal(19,2)
DECLARE @PmtInterest decimal(19,2)
DECLARE @PmtPrincipal decimal(19,2)
DECLARE @BalanceEnd decimal(19,2)
----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------
CREATE TABLE #TempAmortization
(
PmtNumber int ,
PmtDate date ,
PmtAmount decimal(19,2) ,
BalanceStart decimal(19,2) ,
PmtPrincipal decimal(19,2) ,
PmtInterest decimal(19,2) ,
BalanceEnd decimal(19,2)
)
----------------------------------------------------------------
-- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS
-- This line may wrap and be hard to read
----------------------------------------------------------------
SET @Payment = ROUND(@Principal * ((@APR/1200)/(1- EXP((@Months*-1) *
LOG(1 + (@APR/1200))))),2)
----------------------------------------------------------------
-- INITALIZE VARIABLES BEFORE THE LOOP STARTS
----------------------------------------------------------------
SET @PmtNumber = 0
SET @BalanceEnd = @Principal
----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
WHILE @PmtNumber < @Months -1
BEGIN
SET @PmtNumber = @PmtNumber +1
SET @BalanceStart = @BalanceEnd
SET @PmtDate = DATEADD(m, @PmtNumber-1, @StartDate)
SET @PmtInterest = ROUND(@BalanceStart *(@APR/1200) ,2)
SET @PmtPrincipal = @Payment - @PmtInterest
SET @BalanceEnd = @BalanceStart - @PmtPrincipal
INSERT INTO #TempAmortization
(
PmtNumber ,
PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
)
VALUES
(
@PmtNumber ,
@PmtDate ,
@Payment ,
@BalanceStart ,
@PmtPrincipal ,
@PmtInterest ,
@BalanceEnd
)
END
----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
SET @PmtNumber = @PmtNumber +1
SET @BalanceStart = @BalanceEnd
SET @PmtDate = DATEADD(m, @PmtNumber-1, @StartDate )
SET @PmtInterest = ROUND(@BalanceStart *(@APR/1200) ,2)
SET @PaymentLast = @BalanceStart + @PmtInterest
SET @PmtPrincipal = @BalanceStart
SET @BalanceEnd = @BalanceStart + @PmtInterest - @PaymentLast
INSERT INTO #TempAmortization
(
PmtNumber ,
PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
)
VALUES
(
@PmtNumber ,
@PmtDate ,
@PaymentLast ,
@BalanceStart ,
@PmtPrincipal ,
@PmtInterest ,
@BalanceEnd
)
----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------
SELECT
PmtNumber ,
PmtDate ,
PmtAmount ,
BalanceStart ,
PmtPrincipal ,
PmtInterest ,
BalanceEnd
FROM #TempAmortization
----------------------------------------------------------------
-- HOUSEKEEPING
----------------------------------------------------------------
DROP TABLE #TempAmortization
< Prev | Next > |
Thanks
ReplyDelete