SQL, SSRS | Jak vytvořit proceduru pro splátkový kalendář v SQL Server – Včetně reportu s parametry

Poslední aktualizace:

V poslední době jsem věnoval spoustu času finanční matematice v Excelu. Zkusím toho trošku využít a přetočím téma z Excelu do SQL Serveru. Třeba někdo uvažuje o tom, že si udělá aplikaci pro výpočet splátkového kalendáře.

Výsledkem mého počínání bude SQL procedura [Get_Amortization_schedule], která přijímá parametry @Loan_Value, @Loan_Term_Years, @Interest_rate,  @Payment_frequency. Tato procedura vrací tabulku => splátkový kalendář. Tento kalendář pak budu reportovat pomocí SQL Server Reporting services.

Jak vypadá splátkový kalendář a jaké jsou jeho části (Příprava před programováním)

Cílem je vytvoření SQL skriptu, který bude přijímat určité parametry a po spuštění vygeneruje splátkové schéma, tak jak jste zvyklí u různých kalkulaček.

Pro začátek by neškodilo prostudovat si nějaké materiály abychom věděli, co vlastně k sestavení kalendáře potřebujeme vědět. Můžete pooglit nebo se podívat na článek, ve kterém jsem sestavení kalendáře ukazoval v Excelu – PLATBA (PMT) | Excel – Výpočet splátky a splátkového kalendáře

Opáčko: Splátkový kalendář obsahuje seznam pravidelných plateb, které posíláme po určité období bance. Typicky se splátka platí měsíčně (ale není to vždy podmínkou). Každá splátka (anuita) obsauje 2 části – úrok a úmor. Úrok je výnosem banky a zaplacením úroku nám neklesá zůstatek úvěru. NAopak úmor je ta část splátky o kterou nám klesá dlužná částka.

Jak vypadá splátkový kalendář – splátkový kalendář typicky obsahuje

  • Datum nebo pořadové číslo platby (v případě 30-letého úvěru bude kalendář obsahovat 30*12 = 360 záznamů)
  • Stav úvěru na začátku období/periody
  • Anuitní splátka – ta se vypočítává pomocí vzorce
  • Úrok – úrok se počítá jako [Stav úvěru na začátku období] * [Úroková sazba úvěru]
  • Úmor – vypočtá se jako [Anuitní splátka] – [Úrok]
  • Stav úvěru na konci období

Info: Stav úvěru na konci období připadající na poslední platbu je vždy roven 0 => to znamená, že úvěr je již zcela splacen

Postup pro sestavení kalendáře popořadě:

  1. Připravíme si tabulku obsahující tolik záznamů kolik je počet plateb (závisí na délce uvěru a frekvenci placení)
  2. Vypočítáme anuitní splátku
  3. Stav úvěru na začátku období = stav úvěru na konci období předešlé platby
  4. Spočítáme úrok
  5. Spočítáme úmor
  6. Stav úvěru na konci období = [Stav úvěru na začátku období] – [Úmor]

SQL Procedura pro vygenerování splátkového kalendáře a anuitní splátky

Procedura přijímá 4 parametry:

  • @Loan_Value – hodnota úvěru
  • @Loan_Term_Years – splatnost úvěru v letech
  • @Interest_rate – úroková sazba p.a.
  • @Payment_frequency – frekvence placení úvěru (procedura umí 2 možnosti – měsíčně a ročně)

Co procedura dělá:

  • Na začátku si zaloří proměnné @Loan_term_formula a @Interest_rate_formula a přepočítá počet splátek/úrokovou sazbu podle toho, jaká hodnota je v parametru @Payment_frequency..
  • Výpočet anuitní splátky do proměnné @Annuity
  • Založí tempové tabulky a vložení 1 platby
  • Loop a dopočítání zbývajících řádků do tempové tabulky

CREATE PROCEDURE Get_Amortization_schedule (
  @Loan_Value DECIMAL (15,2)
  ,@Loan_Term_Years INT
  ,@Interest_rate FLOAT
  ,@Payment_frequency VARCHAR(255)
)
AS
DECLARE @Loan_term_formula INT = CASE WHEN @Payment_frequency = 'Monthly' THEN @Loan_Term_Years*12 ELSE @Loan_Term_Years END
DECLARE @Interest_rate_formula FLOAT = (CASE WHEN @Payment_frequency = 'Monthly' THEN @Interest_rate/12 ELSE @Interest_rate END)/100
DECLARE @Annuity FLOAT

SET @Annuity = @Loan_Value * (POWER(1+@Interest_rate_formula,@Loan_term_formula) * ((1 + @Interest_rate_formula)-1))
/
(POWER(1+@Interest_rate_formula,@Loan_term_formula)-1)

DROP TABLE IF EXISTS #Schedule

SELECT
  CAST(1 AS INT) AS [Payment_Number]
  ,CAST(@Loan_Value AS MONEY) AS [Balance_Start]
  ,CAST(@Annuity AS MONEY) AS [Annuity payment]
  ,CAST(@Loan_Value * @Interest_rate_formula AS MONEY) AS [Thereof: Interest]
  ,CAST(@Annuity - (@Loan_Value * @Interest_rate_formula) AS MONEY) AS [Ammortization]
  ,CAST(@Loan_Value - (@Annuity - (@Loan_Value * @Interest_rate_formula)) AS MONEY) AS [Balance_End]
INTO #Schedule

DECLARE @Loop_Payment_Nr as INT = 1
WHILE @Loop_Payment_Nr <= @Loan_term_formula
BEGIN 
INSERT INTO #Schedule (
  [Payment_Number]
  ,[Balance_Start]
  ,[Annuity payment]
  ,[Thereof: Interest]
  ,[Ammortization]
  ,[Balance_End]
)
SELECT 
  [Payment_Number] = @Loop_Payment_Nr
  ,[Balance_Start] = [Prev_row].[Balance_End]
  ,[Annuity payment] = @Annuity
  ,[Thereof: Interest] = @Interest_rate_formula * [Prev_row].[Balance_End]
  ,[Ammortization] = [Prev_row].[Annuity payment] - (@Interest_rate_formula * [Prev_row].[Balance_End])
  ,[Balance_End] = [Prev_row].[Balance_End] - ([Prev_row].[Annuity payment] - (@Interest_rate_formula * [Prev_row]. [Balance_End]))
FROM
(SELECT
  [Payment_Number]
  ,[Balance_Start]
  ,[Annuity payment]
  ,[Thereof: Interest]
  ,[Ammortization]
  ,[Balance_End]
FROM #Schedule
WHERE Payment_Number = @Loop_Payment_Nr - 1
) AS [Prev_row]
SET @Loop_Payment_Nr = @Loop_Payment_Nr +1
END 

SELECT
  [Payment_Number] AS [Payment_Number]
  ,CAST([Balance_Start] AS DECIMAL(15,2)) AS [Balance_Start]
  ,CAST([Annuity payment] AS DECIMAL(15,2)) AS [Annuity payment]
  ,CAST([Thereof: Interest] AS DECIMAL(15,2)) AS [Thereof: Interest]
  ,CAST([Ammortization] AS DECIMAL(15,2)) AS [Ammortization]
  ,CAST([Balance_End] AS DECIMAL(15,2)) AS [Balance_End]
FROM #Schedule

Proceduru založím a vyzkouším. Výsledek můžu porovnat s kalkulačkou splátek na internetu – sedí to.

sql vygenerovani splatkoveho kalendare

SSRS report s výpočtem splátkového schématu pro Daenerys Targaryen

Pokud mám SQL proceduru (nebo jiný objekt), tak si ji můžu zavolat z nějaké aplikace. Já výsledek pošlu do reporting services a nastavím nějaké parametry typu Klient a poté parametry typu Úvěr. Parametry typu úvěr pošlu do procedury, která vrátí výsledek a klientské parametry nechám zobrazit v tisknutelném reportu.

Při úvěru 2 400 000 USD, splatnosti 30 let, úrokové sazbě 2,7% a měsíční frekvenci splácení se vygeneruje 360 splátek s anuitní splátkou 9 734 USD.

Pokud změníme splatnost na 10 let a frekvenci placení na roční, tak by se logicky mělo vygenerovat pouze 10 splátek a na konci 10. roku by úvěr měl být zcela splacen – je to tak, anuitní splátka je v tomto případě 277 062 USD.

Splátkový kalendář - roční frekvence splátek

 

SQL, SSRS | Jak vytvořit proceduru pro splátkový kalendář v SQL Server – Včetně reportu s parametry
5 (100%) 2 vote[s]

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Rubrika: SSRS - Reporting services Užitečné SQL skripty

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..