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átek – taková kalkulačka na splátkový kalendář kde si můžete libovolně zadávat parametry a bude vám to generovat výsledek do tabulky.

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.

Mimochodem, procedura se vám bude hodit i v případě, kdy nechcete splátkový kalendář reportovat v Reporting services, ale chcete si přes sql proceduru data nahrát do excelu.

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.

Co obsahuje 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 – z internetových zdrojů se dá pravidelná splátka spočítat viz odkaz
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.

SSRS report jako kalkulačka na splátkový kalendář 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.