LoanPaymentScheduleCalculator.sql

-- ====================================================================================================================
-- Author:		Martin Childs
-- Create date: 	2017-11-09
-- Description:		Calculates a mortgage style annuity loan payment schedule
-- Version:		v001
-- ====================================================================================================================


Print'Create numbered list'
	--http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
	begin try drop table #Numbers end try begin catch end catch
	SELECT TOP (360) Mth = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
		INTO #Numbers
		FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
		OPTION (MAXDOP 1);
	CREATE UNIQUE CLUSTERED INDEX Mth ON #Numbers(Mth)
	-- WITH (DATA_COMPRESSION = PAGE)
	;--select * from #numbers


Print'Accounts'
begin try drop table #Accounts end try begin catch end catch
Select 
		'marty' as account_number
		,250000.00 as Original_Loan
		,0.030000 as Interest_Rate
		,power(1.030000,1.000000/12)-1 as Monthly_Rate
		,25*12 as Term
	into #Accounts


Print'Full Schedule'
begin try drop table #Schedule end try begin catch end catch
SELECT 
		Mth
		,account_number
		,power(1+isnull(Interest_Rate,0.000000),(1.000000/12))-1 as Monthly_Rate
		,round(Original_Loan*(Monthly_Rate*power(1+Monthly_Rate,Term))/(power(1+Monthly_Rate,term)-1),2) as Monthly_Payment
		,Original_Loan
		,9999999.99 as BalanceBF
		,9999999.99 as Interest
		,9999999.99 as IPMT
		,9999999.99 as PPMT
		,9999999.99 as PMT
		,9999999.99 as BalanceCF
into #Schedule
	FROM #accounts
	cross join #Numbers
	--select* from #Schedule


Print'The Calculation'
	declare @MthNow as int = 1 
	declare @MthTot as int = 360

while @MthNow <= @MthTot
	begin		
	Print @MthNow
		update #schedule set BalanceBF	=isnull((select BalanceCF from #Schedule as item where #Schedule.account_number=item.account_number and item.Mth=(#Schedule.Mth-1) ),Original_Loan)	where Mth = @MthNow
		update #schedule set Interest	=BalanceBF*Monthly_Rate													where Mth = @MthNow
		update #schedule set IPMT	=-Interest																	where Mth = @MthNow
		update #schedule set PPMT	=-iif(monthly_payment> BalanceBF+IPMT, BalanceBF, monthly_payment+IPMT)		where Mth = @MthNow
		update #schedule set PMT	=IPMT+PPMT																	where Mth = @MthNow
		update #schedule set BalanceCF	=BalanceBF+Interest+PMT													where Mth = @MthNow
		Set @MthNow = @MthNow + 1
	end

select * from #Schedule