This should get you started. The last payment is off a little if the numbers are not round. You will have to add the future or present value into the mix.:
/*** Loan Calculation Variables ***/
DECLARE
@InterestRate FLOAT,
@Loan FLOAT,
@AmoritizationMonths FLOAT,
@Payment FLOAT,
@Period FLOAT,
/*** CALCULATED PROCEDURAL VARIABLES ***/
@Payment2 FLOAT,
@TotalPayment FLOAT,
@FinanceCharges FLOAT,
@CompoundingPeriod FLOAT,
@CompoundingInterest FLOAT,
/*** CALCULATED LOAN VARIABLES ***/
@CurrentBalance FLOAT,
@Principal FLOAT,
@Interest FLOAT,
/*** MORTGAGE TIME VARIABLES ***/
@MortgageStartDate SMALLDATETIME,
@MortgageEndDate SMALLDATETIME,
@MortgagePayDate SMALLDATETIME,
@MortgageDueDate SMALLDATETIME
/*** USER VARIABLES ***/
SET @InterestRate = 0.0719
SET @CompoundingPeriod = 12
SET @Loan = 1000000.57
SET @AmoritizationMonths = 360
SET @MortgageStartDate = '2005-2-01'
/*** END USER VARIABLES ***/
SET @CompoundingInterest = @InterestRate/@CompoundingPeriod
SET @Payment = ROUND(
(
((@InterestRate/12) * @Loan)/(1- ( POWER( (1 + (@InterestRate/12)),
(-1 * @AmoritizationMonths) )))),2)
SET @TotalPayment = @Payment * @AmoritizationMonths
SET @FinanceCharges = @TotalPayment - @Loan
IF EXISTS(SELECT object_id FROM tempdb.sys.objects WHERE name LIKE '#Schedule%')
BEGIN
DROP TABLE #SCHEDULE
END
CREATE TABLE #SCHEDULE (
PERIOD INT
,PAYDATE SMALLDATETIME
,PAYMENT MONEY
,CURRENT_BALANCE MONEY
,INTEREST MONEY
,PRINCIPAL MONEY
)
SET @Period = 1
SET @MortgageEndDate = DATEADD(year,@AmoritizationMonths/12,@MortgageStartDate)
SET @MortgagePayDate = @MortgageStartDate
BEGIN
WHILE (@Period < = @AmoritizationMonths)
BEGIN
SET @CurrentBalance = ROUND (
@Loan * POWER( (1 + @CompoundingInterest) , @Period ) -
( (@Payment/@CompoundingInterest) *
(POWER((1 + @CompoundingInterest),@Period ) - 1)),2)
SET @Principal =
CASE
WHEN @Period = 1
THEN
ROUND((@Loan - @CurrentBalance),2)
ELSE
ROUND
((SELECT CURRENT_BALANCE - @CurrentBalance
FROM #SCHEDULE
WHERE PERIOD = @Period -1),2)
END
SET @Interest = ROUND(ABS(@Payment - @Principal),2)
SET @MortgageDueDate = @MortgagePayDate
INSERT
#SCHEDULE(PERIOD,
PAYDATE,
PAYMENT,
CURRENT_BALANCE,
INTEREST,
PRINCIPAL)
SELECT
@Period,
@MortgageDueDate,
@Payment,
@CurrentBalance,
@Interest,
@Principal
SET @Period = @Period + 1
SET @MortgagePayDate = DATEADD(MM,1,@MortgagePayDate)
END
END
SELECT * FROM #SCHEDULE
GO |