[GNC] Automating the Interest payment transactions on a Loan.

[Adrien Monteleone]

You can use formulas with variables in Scheduled Transactions (SX).

When the SX fires, it will ask you for the value of the variables.

Thus you can then input the principle and rate separately as needed, and the SX will then be filled in with the resulting calculations. You can then opt to review the created transaction.

See the Help & Guide for more info on SX.

This isn’t an optimum solution, but it might meet your needs.

An alternative would be to set up a spreadsheet that does the calculations and then puts the results on a separate tab with each transaction on its own row. You can then export that sheet as CSV and import the resulting transaction(s) into GnuCash.

Regards,
Adrien

> On Apr 4, 2020 w14d95, at 3:28 PM, Roger Nathanial Ashby <roger.ashby at openwise.co> wrote:
> 
> Hello,
> 
>    Hopefully someone has tried to do this same thing over the years.
> 
>   1. Our company has deferred compensation liability accounts.
>   2. Each month a certain amount of salary is placed in this account as a
>   principal with a set interest rate with an amortization of 10 years with
>   disbursement at the end of the 10 year period.
>   3. To keep things simple lets say $1000 salary payment to this
>   deferred compensation liability account for each month of the year, with
>   10% intrest amortized yearly for ten years.
>   The schedule would look like below for each month:
> 
>      start principal start balance interest end balance end principal
>   1 $1,000.00 $1,000.00 $100.00 $1,100.00 $1,000.00
>   2 $1,000.00 $1,100.00 $110.00 $1,210.00 $1,000.00
>   3 $1,000.00 $1,210.00 $121.00 $1,331.00 $1,000.00
>   4 $1,000.00 $1,331.00 $133.10 $1,464.10 $1,000.00
>   5 $1,000.00 $1,464.10 $146.41 $1,610.51 $1,000.00
>   6 $1,000.00 $1,610.51 $161.05 $1,771.56 $1,000.00
>   7 $1,000.00 $1,771.56 $177.16 $1,948.72 $1,000.00
>   8 $1,000.00 $1,948.72 $194.87 $2,143.59 $1,000.00
>   9 $1,000.00 $2,143.59 $214.36 $2,357.95 $1,000.00
>   10 $1,000.00 $2,357.95 $235.79 $2,593.74 $1,000.00
> 
> 
> So, the first year each month we would record entries like this:
> 
> 
>    Debit            Credit
> Expenses:
>      Deferred compensation (Y1-P)                        $1000
> Liabilities
>        Deferred compensation (Y1-P)
>     $1000
> 
> So, the first Quarter of a year would look like this:
> 
> 
>         January                                     February
>         March
> 
> 
>    Debit            Credit               Debit            Credit
>  Debit            Credit
> Expenses:
>      Deferred compensation (Y1-P)                        $1000
>                       $1000                                $1000
> Liabilities
>        Deferred compensation (Y1-P)
>     $1000
>         $1000
> 
> 
> In year two, each month we'd have to enter these entries:
> 
> Expenses:
>      Deferred compensation (Y2-P)                           $1000
>      Deferred compensation (Y1-Y1 Interest)            $100
> Liabilities
>        Deferred compensation (Y2-P)
>    $1000
>        Deferred compensation (Y1-Y1 Interest)
> $100
> 
> 
> In year three, each month we'd have to enter these entries:.
> 
> Expenses:
>      Deferred compensation (Y3-P)                           $1000
>      Deferred compensation (Y2-Y1 Interest)            $100
>      Deferred compensation (Y1-Y2 Interest)            $110
> Liabilities
>        Deferred compensation (Y2-P)
>    $1000
>        Deferred compensation (Y2-Y1 Interest)
> $100
>        Deferred compensation (Y1-Y2 Interest)
> $110
> 
> 
> And so on for 10 years...
> 
> Even with the easy math above this will get tedious *very* quickly.
> 
> In our actual real world case the amount of monthly principal will
> fluctuate depending on
> salary changes each year, and the interest rate will change quarterly
> depending on the 10 year T note.
> 
> Is there a way to automate these transactions?  I can schedule the
> principal transactions because the monthly
> principal contributions will be the same for given years salary, but I
> don't know how to automate the interest
> payment transactions since they are based on the principal and would
> increase each year for each principal payment.