Template Thin Cap

AlexanderDB · December 17, 2018, 11:32am

I want to create a template that calculates the Thin cap maximum.
The calculation sould be as followed:

Capital and reserves situation year beginning situation year end (= period)
Issued capital
Legal reserve
…
total

max loan: total situation year beginning *5

I can’t find the tag to give the value Situation Y-1 (= situation year beginning)

exemple:
bookyear: 01/01/2017-31/12/2018
Issued capital: 18.600 on 01/01/2017 which tag do I have to use to give the result 18.600 on “beginsituatie 01/01/2017”?

sven · December 17, 2018, 1:48pm

Hi @AlexanderDB,

If you wish to call on the value of accounts beginning in the year, then you can do this by actually calling on the end-value of last year (which should be the same).

Like this:

{% assign previous_period = period.minus_1y.year_end %}

So this newly created variable will look into the current period, go one year back, and then go to the end of the year in which that previous period is.

With this variable you can do this then:

{% assign thin_cap = previous_period.accounts | range:"10" %}

{{ thin_cap | currency }}

Which will give you the end value of last year of that range ( = beginning value of current year)

AlexanderDB · December 19, 2018, 1:41pm

Thanks,
I want to create a table where I want to display a range of accounts

{% input custom.range.accounts as:account_collection range:“10,11,12,130,131,133,14” default:“10” accounts_var:accounts %}

the following columns are also shown: |Begintoestand | eindtoestand

I’m having touble with displaying the values in column “begintoestand” using
{% assign previous_period = period.minus_1y.year_end %}

sven · December 19, 2018, 3:56pm

Hi @AlexanderDB,

Can’t really say anything from your code what goes wrong; missing your code for that

But I do know what you are going for, and you could use this case:

{% comment %}let user select range{% endcomment %}
{% input custom.range.thin_cap as:account_collection range:"10__15" %}

{% comment %}create account collection with the selected ranges (including zero accounts!) which are stored in the database variable "custom.range.thin_cap" {% endcomment %}
{% assign thin_cap_accounts = period.accounts.include_zeros | range:custom.range.thin_cap %}

{% comment %}create period drop of last year end{% endcomment %}
{% assign last_year = period.minus_1y.year_end %}

{% comment %}loop over those selected accounts, and display the previous value as well{% endcomment %}
{% stripnewlines %}
| Account
| Current value
| End value last year
{% newline %}
|----60%----
|----20%----:
|----20%----:
{% ic %}#{% endic %}
{% nic %}+{% endnic %}
{% comment %}loop over accounts{% endcomment %}
{% for acc in thin_cap_accounts %}
  {% newline %}
  {% comment %}display account and number as linkto{% endcomment %}
  | {{ acc.link }}
  {% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
  | {{ acc.value | currency }}
  {% comment %}create variable which goes to the period.accounts-drop but in the last period of last book year, filtered on the account number{% endcomment %}
  {% assign value_end_year = last_year.accounts | range:acc.number %}
  {% comment %}display new created var "value_end_year"{% endcomment %}
  | {{ value_end_year | currency }}
{% endfor %}
{% endstripnewlines %}

I added explanation in comment-tags for you in the code.

AlexanderDB · January 3, 2019, 4:41pm

thanks,

Now i’m struggling with an “if” statement.

If the range “10” is selected within the account collection, then values of range “10” current period must be displayed. if this is not the case then the values of last year must be displayed.

I can’t find a proper condition.

so for example, if first row = “100000 geplaatst kapitaal” then value in third row must be value current period, if not, value last year.

robindeclercq · January 4, 2019, 9:08am

Hi @AlexanderDB,

Can you further explain what you mean by value last year? Do you want to see the value of last year of the account ‘100000 Geplaatst Kapitaal’ even when this specific account is not selected in the account collection?

Would it be possible to paste (part) of your code so we can be more specific with our answer to help you with your template?

Thanks!

Kind regards,
Robin

AlexanderDB · January 4, 2019, 9:50am

Hi Robin,

if the range “10” is selected in an account collection, the value of the current year must be displayed in the column “base” in the row with te range “10”. here for example “100000 geplaatst kapitaal” "Basis " sould be -1.147.590,00 in stead of 647.590,00 . If other ranges are selected then the values from last year sould be displayed displayed.

{% stripnewlines %}
| Gegevens uit jaarrekening
| Beginstoestand
| Eindtoestand
| Basis
| Max. R/C
| Max. Thin Cap  
{% newline %}
| ----25%----
| ----15%----:
| ----15%----:
| ----15%----:
| ------:+
| ------:+
{% ic %}#{% endic %}
{% nic %}+{% endnic %}
{% comment %}loop over accounts{% endcomment %}
{% for account in thin_cap_accounts %}
  {% newline %}
  {% comment %}display account and number as linkto{% endcomment %}
  | {{ account.link }}
  {% comment %}create variable which goes to the period.accounts-drop but in the last period of last book year, filtered on the account number{% endcomment %}
  {% assign value_end_year = last_year.accounts | range:account.number %}
  {% comment %}display new created var "value_end_year"{% endcomment %}
  | {{ value_end_year | currency }}
  {% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
  | {{ account.value | currency }}
  {% comment %}if range = "10" then display account.value else display Value_end_year{% endcomment %}
  | {% if custom.range.thin_cap= range "10" %} {{account.value | currency }} {% else %} {{ value_end_year | currency }} {% endif %}
  | 
  |
{% endfor %}
{% comment %}Give totals of the selected ranges in correct columns{% endcomment %}
{% newline %}
|
|**{{ $0 | currency }}**
|**{{ $1 | currency }}**
|**{{ Base.value | currency }}**
|**{{ Base.value | currency }}**
|**{{ Base.value*5 | currency }}**
{% endstripnewlines %}

Also, The I cant figure out how to display the total of each collumn.
for the total of “Basis” I probably have to assign a variable? “Base.value”

AlexanderDB · January 4, 2019, 11:41am

Robin,

I think you misunderstood.
If “100000 geplaatst kapitaal” is selected then account.value should be shown in “Basis”.
if, for example "130000 Wettelijke reserves is selected, then Value_end_year should be shown in “Basis”

In the future it would be possible to start up company’s without placed capital. So the “100000 geplaatst kapitaal” doesn’t necessarily have to be selected.

in this case the column “Basis” should be
GEGEVENS UIT JAARREKENING BASIS
100000 geplaatst kapitaal -1.147.590,00
130000 Wettelijke reserve -10.387,72
130300 Wettelijke liquidatiereserve -2.397,11
133000 Beschikbare reserve -197.366,59
133300 Liquidatierserve -45.545,01
Totaal -1.303.286,43

robindeclercq · January 4, 2019, 1:26pm

Hello @AlexanderDB,

Indeed, I totally misunderstood. My apologies for that!

Nevertheless, the other points I mentioned in my post still apply. If we add the comments I made above, the code looks like this

{% comment %}let user select range{% endcomment %}
{% input custom.range.thin_cap as:account_collection range:“10__15” %}

{% comment %}create account collection with the selected ranges (including zero accounts!) which are stored in the database variable “custom.range.thin_cap” {% endcomment %}
{% assign thin_cap_accounts = period.accounts.include_zeros | range:custom.range.thin_cap %}

{% comment %}create period drop of last year end{% endcomment %}
{% assign last_year = period.minus_1y.year_end %}

{% stripnewlines %}
| Gegevens uit jaarrekening
| Beginstoestand
| Eindtoestand
| Basis
| Max. R/C
| Max. Thin Cap
{% newline %}
| ----25%----
| ----15%----:
| ----15%----:
| ----15%----:
| ------:+
| ------:+
{% ic %}#{% endic %}
{% nic %}+{% endnic %}
{% comment %}loop over accounts{% endcomment %}
{% for account in thin_cap_accounts %}
{% newline %}
{% comment %}display account and number as linkto{% endcomment %}
| {{ account.link }}
{% comment %}create variable which goes to the period.accounts-drop but in the last period of last book year, filtered on the account number{% endcomment %}
{% assign value_end_year = last_year.accounts | range:account.number %}
{% comment %}display new created var “value_end_year”{% endcomment %}
| {% =$0+ value_end_year | currency %}
{% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
| {% =$1+ account.value | currency %}
{% comment %}if range = “10” then display account.value else display Value_end_year{% endcomment %}
| {% if account.number contains “100000” %} {% assign base = account.value %} {% =$2+ base | currency %} {% else %} {% assign base = value_end_year %}{% =$2+ base | currency %} {% endif %}
|
|
{% endfor %}
{% comment %}Give totals of the selected ranges in correct columns{% endcomment %}
{% newline %}
|
|{{ $0 | currency }}
|{{ $1 | currency }}
|{{ $2 | currency }}
|{{ Base.value | currency }}
|{{ Base.value*5 | currency }}
{% endstripnewlines %}

I think this should generate the result you were looking for. If not, please inform us hereof.

Kind regards,
Robin

AlexanderDB · January 9, 2019, 3:42pm

Robin,

the question above was resolved using following code:

|{% comment %}if range = "10" then display account.value else display Value_end_year{% endcomment %}
  {% assign check_nbr = account.number | slice:0,2 %} 
  {% if check_nbr == "10" %}{{ account.value }}{% else %}{{ value_end_year }}{% endif %}

thanks to @sven

AlexanderDB · January 9, 2019, 3:56pm

I want to make a totals of “Bedrag lening”, Max.R/C and Herkwalificatie" but cant figure out how.

for now I have following code

{% assign rc_accounts = period.accounts.include_zeros | range:custom.range.rc %}

{% stripnewlines %}
| Soort lening
| Rentetarief
| Marktconforme rente?
| Bedrag lening
| Max. R/C
| Herkwalificatie

{% newline %}
| ----25%----
| ----15%----
| ----15%----:
| ----15%----:
| ----15%----:
| ----15%----:
{% ic %}#{% endic %}
{% nic %}+{% endnic %}
{% comment %}loop over accounts{% endcomment %}

{% for rc in rc_accounts %}
  {% newline %}
  {% comment %}display account and number as linkto{% endcomment %}
  | {{ rc.link }}
  |{% input  rc.custom.rente.tarief as:percentage %}
  | {% input rc.custom.boolean.marktrente as:boolean %}
    {% if rc.custom.boolean.marktrente == 'true' %}Ja {% else %} Nee{% endif %}
  {% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
  | {{ -rc.value | currency }}
  | {% if -rc.value > Base %} Maximum R/C met {{ -rc.value-Base | currency }} overschreden{% else %}oke{% endif %}
  | {% if -rc.value > Base %} {% assign herkwalificatie = -rc.value-Base %}{% assign rentetarief_loan = rc.custom.rente.tarief  %}{{ herkwalificatie*rentetarief_loan | currency }} {%else%} NVT{% endif %}
 
{% endfor %}
{% newline %}
    | **Totaal**
    |
    |
    | {{ -rc.value | currency }}
    |
  {% assign herkwalificatiebedrag = herkwalificatie*rentetarief_loan %}
    | **{{ herkwalificatiebedrag | currency }}**
{% endstripnewlines %}

for now the output is as follows:

The output should actually be:
Total Bedrag lening: 1.942.116,32 in stead of 0,00
Totaal MAX. R/C : 172.869,54
Totaal Herkwalificatie: 1.3765,29 in stead of 192,83

Kimberly_Vlietinck · January 10, 2019, 9:16am

Hi @AlexanderDB,

You can calculate the totals that you want by using registers.

Please find your code hereafter with some adjustments:

{% assign rc_accounts = period.accounts.include_zeros | range:custom.range.rc %}

{% stripnewlines %}
| Soort lening
| Rentetarief
| Marktconforme rente?
| Bedrag lening
| Max. R/C
| Herkwalificatie

{% newline %}
| ----25%----
| ----15%----
| ----15%----:
| ----15%----:
| ----15%----:
| ----15%----:
{% ic %}#{% endic %}
{% nic %}+{% endnic %}
{% comment %}loop over accounts{% endcomment %}

{% for rc in rc_accounts %}
  {% newline %}
  {% comment %}display account and number as linkto{% endcomment %}
  | {{ rc.link }}
  |{% input  rc.custom.rente.tarief as:percentage %}
  | {% input rc.custom.boolean.marktrente as:boolean %}
    {% if rc.custom.boolean.marktrente == 'true' %}Ja {% else %} Nee{% endif %}
  {% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
  | {%=$0+ -rc.value | currency %}
  | {% if -rc.value > Base %} Maximum R/C met {%=$1+ -rc.value-Base | currency %} overschreden{% else %}oke{% endif %}
  | {% if -rc.value > Base %} {% assign herkwalificatie = -rc.value-Base %}{% assign rentetarief_loan = rc.custom.rente.tarief  %}{%=$2+ herkwalificatie*rentetarief_loan | currency %} {%else%} NVT{% endif %}
 
{% endfor %}
{% newline %}
    | **Totaal**
    |
    |
    | {{ $0 | currency }}
    | {{ $1 | currency }}
  {% assign herkwalificatiebedrag = herkwalificatie*rentetarief_loan %}
    | **{{ $2 | currency }}**
{% endstripnewlines %}

As you can see we added the registers $0, $1 and $2 to the values you want to add up within your loop. This means that for every loop that value will be added to either $0, $1 and $2. At the bottom of your table on the line with the Totals we then print the value of those registers to show the total.

This code should generate the totals you were looking for. If not, please let us know.

Kind regards,
Kimberly

AlexanderDB · January 10, 2019, 3:53pm

Kimberly,

i’ve tried this in the past but I Always get a Liquid syntax error

liquid%20error

{% for rc in rc_accounts %}
  {% newline %}
  {% comment %}display account and number as linkto{% endcomment %}
  | {{ rc.link }}
  |{% input  rc.custom.rente.tarief as:percentage %}
  | {% input rc.custom.boolean.marktrente as:boolean %}
    {% if rc.custom.boolean.marktrente == 'true' %}Ja {% else %} Nee{% endif %}
  {% comment %}display the value in current period, which is the db variable value of the account drop{% endcomment %}
  | {%=$90+ -rc.value | currency %}
  | {% if -rc.value > Base %} Maximum R/C met {%=$91+ -rc.value-Base | currency %} overschreden{% else %}oke{% endif %}
  | {% if -rc.value > Base %} {% assign herkwalificatie = -rc.value-Base %}{% assign rentetarief_loan = rc.custom.rente.tarief  %}{%=$92+ herkwalificatie*rentetarief_loan | currency %} {%else%} NVT{% endif %}
 
{% endfor %}
{% newline %}
    | **Totaal**
    |
    |
    | {{ $90 | currency }}
    | {{ $91 | currency }}
  {% assign herkwalificatiebedrag = herkwalificatie*rentetarief_loan %}
    | **{{ $92 | currency }}**
{% endstripnewlines %}

I’ve used $90 , $91 and $92 for registers ($0, $1 and $2 already existed.

SamVanEenoo · January 10, 2019, 4:36pm

Hi @AlexanderDB

Registers (=variables starting with a dollar sign $) can only go from 0 till 39.
That’s why you are getting the error.

Kind regards
Sam

Topic		Replies	Views
Period minus - variables Templates variables , capture	2	425	April 20, 2017
Calculating with amount of days Templates math , drops , drop_period	5	474	January 28, 2019
Template Verklarende bijlage bij de belaste reserves Templates	10	417	June 6, 2017
Period.minus_1y - placeholder - calculation Templates	1	442	April 2, 2017
CASE: changing mapping lists between periods and account locking impact on templates Cases mapping	0	274	September 18, 2023

Template Thin Cap

Related Topics