Calculating interest for loan payments?

[Kyle Spaans]

I'm interested in automatically calculating interest payments for
loans (or even for interest paid by the bank into a savings account).
This is for cases where your bank doesn't give you a breakdown of your
repayments. For example if you have a loan for $10,000 and repay $500
every month, it's going to take you more than 20 months to pay it
back!

Following is not ideal:

---snip---
2015/01/01 bank
assets:bank $10,000
liabilities:loan -$10,000

; make a loan repayment
2015/02/01 bank
liabilities:loan
income:salary -$500

; and so on...
---snip---

If you record these payments until your bank says you are done the
`liabilities:loan` account will be positive and would represent the
amount of interest you've paid. But this doesn't feel like proper
accounting.

Let's say you borrow $10,000 and the bank charges you 1.23% interest
every month. If you repay $500 a month and assume the payment comes
just after the interest is calculated the following manual calculation
would be what I expect:

---snip---
2015/01/01 bank
assets:bank $10,000
liabilities:loan -$10,000

2015/02/01 bank
liabilities:loan $377
expenses:interest $123
income:salary -$500

2015/03/01 bank
liabilities:loan $381.64
expenses:interest $118.36
income:salary -$500

; and so on...
---snip---

But this is a little tedious and if it turns out that I've done the
calculation wrong I need to change many lines! It also doesn't
document my understanding of the interest charges (unless I were to
leave many comments).

What does work better, in my opinion, is to define a variable to store
the interest rate and do the interest calculation programmatically
(and elide an amount, why not!). This does what I want and is fairly
easy to understand, but it still has a lot of redundancy. Again, if I
discover later that my calculations are wrong I will have to change
many lines. At least now it's simply a copy and paste job.

---snip---
define interest_rate=(0.0123)

2015/01/01 bank
assets:bank $10,000
liabilities:loan -$10,000

2015/02/01 bank
liabilities:loan
expenses:interest (-(account("liabilities:loan").amount * interest_rate))
income:salary -$500

2015/03/01 bank
liabilities:loan
expenses:interest (-(account("liabilities:loan").amount * interest_rate))
income:salary -$500

2015/04/01 bank
liabilities:loan
expenses:interest (-(account("liabilities:loan").amount * interest_rate))
income:salary -$500

; and so on...
---snip---

Ideally this could be captured in a single automated transaction!
Looking through the mailing list archives suggests this isn't
possible[0][1]. With some help from "afh" in IRC we came up with the
following:

---snip---
define interest_rate=(0.0123)

= Repayment
(Expenses:Interest) (-(account("Liabilities:Loan").total * interest_rate))
(Liabilities:Loan) (amount + (account("Liabilities:Loan").total *
interest_rate))


2015/01/01 Bank Of Ledger
Assets:Checking $10,000.00
[Liabilities:Loan] -$10,000.00

2015/02/01 ACME Inc.
Assets:Checking
Repayment $500.00

2015/03/01 ACME Inc.
Assets:Checking
Repayment $500.00
---snip---

This gets rid of the redundant lines. However the virtual accounts
mean that the correct repayment isn't made to the real "Loan" account
and interest in subsequent months is wrong. By making line 5 a real
account rather than a virtual one the transactions stop balancing.
Perhaps this virtual account automatic transaction could be used as an
aid to calculate the interest which can then be added to the real
transaction manually? The virtual account could then exist to
double-check against the real account.

I'm satisfied with the second example that calculates the interest,
but I'm wondering if any more experienced users can see a more concise
way of performing these calculations in ledger.

Thanks!

0 - https://groups.google.com/forum/#!searchin/ledger-cli/loan/ledger-cli/tERMoTgxtcU/UrN9TrH-vw8J
1 - https://groups.google.com/forum/#!searchin/ledger-cli/loan/ledger-cli/VUqvZ-MOpP8/oJ8ohMe4HXgJ

[Martin Blais]

I think you're wasting your time by attempting to automatically generate those amounts, they will never be precisely the ones your bank used, and if anything, rounding errors and unpredictable fees will cause small variations that will leave you frustrated if you're trying to be thorough and install balance assertions, or if you have a variable interest rate or make occasional lump sum payments or have unusual mortgage contract small-print involved (e.g. with front-loaded interest payments) or decide at some point to change your monthly repayment amount.

It would be more useful to just enter the precise amounts of principal and interest paid on the loan for each payment in the _past_. Unfortunately, not all banks will provide this detail on a frequent basis (you can always contact their mortgage dept. to get an annual summary with the detail on each payment). In any case, the banks will update the remaining amount of the loan principal on their web interface and even without them providing the explicit breakdown of interest vs. principal in the transaction info if you login dutifully a few days after each payment you should be able to observed the change in your loan and back out the interest. This is what I had to do when I had a mortgage.

As for the future transactions, if you really want to generate all those future payment entries (again, they'll be approximate so I don't really see the point except for forecasting the end of your loan's term, which your bank should provide anyway) I would write a routine to generate an amortization from the remaining amount of principal that you have. Instead of doing that, I think it would be more useful to write a script that calculates the remaining duration for repayment of your current loan value - just the information you really want - based on a few different and realistic scenarios (a range of interest rates you could get somewhere else when refinancing, lump sum repayment scenarios, monthly payment amounts, etc.), a kind of analysis of the sensitivity of duration from various changes you could make to your contract. You'd have to write a script to do that and I wouldn't even bother generating transactions, just a table of final results. If your use case is budgeting, you don't need the breakdown to do that you know the monthly payment and its impact on the other accounts you have.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Daniël Bos (远洋)]

I use a simple spreadsheet to calculate the interest/principle payments and optional extra payments. I have a simple script that takes the spreadsheet and converts it into ledger format, which I keep in a separate file. Whenever something changes (interest change, extra payment) I simply regenerate the ledger file.

[Kyle Spaans]

On 25 December 2014 at 22:32, Martin Blais <bl...@furius.ca> wrote:
> It would be more useful to just enter the precise amounts of principal and
> interest paid on the loan for each payment in the _past_. Unfortunately, not
> all banks will provide this detail on a frequent basis (you can always
> contact their mortgage dept. to get an annual summary with the detail on
> each payment). In any case, the banks will update the remaining amount of
> the loan principal on their web interface and even without them providing
> the explicit breakdown of interest vs. principal in the transaction info if
> you login dutifully a few days after each payment you should be able to
> observed the change in your loan and back out the interest. This is what I
> had to do when I had a mortgage.

This sounds reasonable enough, I can see how trying to guess what the
banks are doing under the hood would end in tears. ;)

> As for the future transactions, if you really want to generate all those

> future payment entries ...

Nope! Just wanted to try and track payments as precisely as possible.

> If your use case is budgeting, you don't need the breakdown
> to do that you know the monthly payment and its impact on the other accounts
> you have.

This isn't for budgeting either. I don't (yet) have any loans or
mortgages so I was trying to figure out what ledger is capable of
helping me with.

Thanks Martin!

[Kyle Spaans]

On 26 December 2014 at 01:53, Daniël Bos (远洋) <cor...@gmail.com> wrote:
> I use a simple spreadsheet to calculate the interest/principle payments and
> optional extra payments. I have a simple script that takes the spreadsheet
> and converts it into ledger format, which I keep in a separate file.
> Whenever something changes (interest change, extra payment) I simply
> regenerate the ledger file.

Considering it took me 30 seconds to write a spreadsheet to
double-check my interest calculations, I like this idea!

[Stefano Zacchiroli]

On Thu, Dec 25, 2014 at 05:32:36PM -0500, Martin Blais wrote:
> If your use case is budgeting, you don't need the breakdown to do that
> you know the monthly payment and its impact on the other accounts you
> have.

It's tangential to the main question of this thread, but I'd like to
mention an annoyance in Ledger's budgeting which I've encountered
precisely when dealing with mortgage interests. Maybe someone will have
a brilliant solution to propose!

Let's assume you've a fixed interest rate mortgage. Usually the amount
of money you pay monthly is fixed (say, 1000 USD), but the breakdown of
that amount into principal vs interests changes over time (with the
relative amount of interests you pay monthly diminishing over time).

To book those transactions properly in ledger, you should use distinct
accounts like Liabilities:Mortgage, Expenses:Interests, and of course
Assets:Checking. The only leg of those periodic transactions that will
stay constant over time is Assets:Checking; the *sum* of
Liabilities:Mortgage and Expenses:Interests will stay constant as well,
but the two amounts will not.

How do you do budgeting/forecasting about this in Ledger?

Ideally, I want Ledger to only report drifts when the sum of principal
and interests is not as expected (which should never happen). But that
does not seem to be possible with Ledger budgeting support. AFAICT I can
only enter fully detailed periodic transactions, including
Liabilities:Mortgage and Expenses:Interests. But if I do so, Ledger will
dutiful complain that over time the actual postings on those categories
drift over time from the expected ones, cluttering Ledger's output.

Is there any way around that annoyance?

TIA,
Cheers.
--
Stefano Zacchiroli . . . . . . . za...@upsilon.cc . . . . o . . . o . o
Maître de conférences . . . . . http://upsilon.cc/zack . . . o . . . o o
Former Debian Project Leader . . @zack on identi.ca . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

[Daniël Bos (远洋)]

There are standard formulas to calculate this, as mentioned previously, I just plugged those in to a spreadsheet and calculated all the interest/principle values and exported those to a separate ledger file. I have a variable rate and make occasional extra principle payments. When that happens, I just update the spreadsheet and regenerate the ledger file entirely.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+unsubscribe@googlegroups.com.

[Stefano Zacchiroli]

On Fri, Dec 26, 2014 at 11:42:41AM +0000, Daniël Bos (远洋) wrote:
> There are standard formulas to calculate this, as mentioned previously, I
> just plugged those in to a spreadsheet and calculated all the
> interest/principle values and exported those to a separate ledger file. I
> have a variable rate and make occasional extra principle payments. When
> that happens, I just update the spreadsheet and regenerate the ledger file
> entirely.

Sure, but that is slightly different from the annoyance I mention, isn't
it?

Or are you saying that you also update the single periodic transaction
you use for budgeting purposes?

That would work, but only for the *current* month. If you, say, compare
the periodic transactions used for budgeting purposes with *past*
months, you'll get back (bogus) differences and "ledger budget" will
complain about them.

[Daniël Bos (远洋)]

Hmm, at least with my bank, I'm not seeing any differences between the calculated and actual amounts. I only need to update the speadsheet when interest rates change.

Maybe your bank is skimming pennies? ;-)

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

--

Best regards,
Daniël Bos


Your government is reading your email. Slow them down with encryption.

My public key: http://goo.gl/gms497 (4096 bit RSA, id EF2D5D91)
Fingerprint  : D8D0 9FBE F075 F709 7B52  2F73 326C 2123 EF2D 5D91

[Stefano Zacchiroli]

On Fri, Dec 26, 2014 at 08:25:06PM +0800, Daniël Bos (远洋) wrote:
> Hmm, at least with my bank, I'm not seeing any differences between the
> calculated and actual amounts. I only need to update the speadsheet when
> interest rates change.
>
> Maybe your bank is skimming pennies? ;-)

It seems to me you're still talking about a different problem. I do have
a fully detailed payment schedule from my bank, which is precise to the
penny.

What I'm talking about is a limitation of ledger budgeting feature, as
in <http://www.ledger-cli.org/3.0/doc/ledger3.html#Budgeting> ; are you
familiar with that feature of Ledger-CLI?

To use that, you need to enter periodic transactions. The problem is
that, month after month, the amount of principal and interest that I pay
change; what is constant is their sum. One month a transation could be
something like:

Liabilities:Mortgage 10 EUR
Expenses:Interest 5 EUR
Assets:Checking -15 EUR

While a few months later it will be more like

Liabilities:Mortgage 10,5 EUR
Expenses:Interest 4,5 EUR
Assets:Checking -15 EUR

i.e., the relative amount of interests you pay decreases over time.
AFAICT this is fairly common, at last across various banks in Europe.

So if I use a periodic transaction like this

~ Monthly
Liabilities:Mortgage 10 EUR
Expenses:Interest 5 EUR
Assets:Checking -15 EUR

a few months later ledger will complain that I'm "drifting" from that
period transaction. According to ledger I'll be spending 0,50 EUR more
of liabilities reimbursement, and 0,50 EUR less of interests. While I do
not want "ledger budget" to complain about that, because that is not a
real "drift"; it is fully expected (and matches my payment schedule).

I hope it's clear(er) now,

[Daniël Bos (远洋)]

Ah, clear now :-) I automatically generate dated entries for the next 30 years (life of my mortgage) Those will be included in the forecast, I'm not using the budget feature for this.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+unsubscribe@googlegroups.com.

[Walker Pendleton]

Stefano Zacchiroli <za...@upsilon.cc> writes:

[...]

> It's tangential to the main question of this thread, but I'd like to
> mention an annoyance in Ledger's budgeting which I've encountered
> precisely when dealing with mortgage interests. Maybe someone will have
> a brilliant solution to propose!
>
> Let's assume you've a fixed interest rate mortgage. Usually the amount
> of money you pay monthly is fixed (say, 1000 USD), but the breakdown of
> that amount into principal vs interests changes over time (with the
> relative amount of interests you pay monthly diminishing over time).
>
> To book those transactions properly in ledger, you should use distinct
> accounts like Liabilities:Mortgage, Expenses:Interests, and of course
> Assets:Checking. The only leg of those periodic transactions that will
> stay constant over time is Assets:Checking; the *sum* of
> Liabilities:Mortgage and Expenses:Interests will stay constant as well,
> but the two amounts will not.
>
> How do you do budgeting/forecasting about this in Ledger?
>
> Ideally, I want Ledger to only report drifts when the sum of principal
> and interests is not as expected (which should never happen).

[...]

We use virtual accounts for budgeting loans. We track the real amounts
in the ledger file, while virtual accounts track the budget
requirements.

An automated transaction matches the liability and expense accounts, and
transforms them into a single virtual 'loan budget' account. The budget
shows money coming out of our asset account and going into the combined
loan budget account.

= Liability:Loan A
[Liability:Loan A] -1
[Virtual:Loan A] 1

= Expense:Interest:Loan A
[Expense:Interest:Loan A] -1
[Virtual:Loan A] 1

When I want to know how much money I still owe, I can pass "--real" to
ledger. The "--real" flag ignores both virtual and automatic
transactions (like budget transactions), so that means I'm not asking
budget questions. But most of the time I want to track myself against
the budget, and no flags are needed.

Here's more complete sample:

--8<---------------cut here---------------start------------->8---
; I have the following *real* accounts:

account Liability:Loan A
account Expense:Interest:Loan A
account Asset:Checking

; A virtual Loan A account for budgeting:

account Virtual:Loan A

; My budget entry is:

~ Monthly
[Virtual:Loan A] $100.00
[Asset:Checking]

; And then the magic is in the automated transaction. Sorry about the
; regular expression here, but that's what we really do

= /^(Liability|Expense:Interest):Loan A
[$account] -1
[Virtual:Loan A] 1

; Now my transactions

2014/10/26 Bank Of Example
Liability:Loan A $50.00
Expense:Interest:Loan A $50.00
Asset:Checking $-100.00

2014/11/26 Bank Of Example
Liability:Loan A $60.00
Expense:Interest:Loan A $40.00
Asset:Checking $-100.00

; For the posts I'm making today, I don't actually know what the bank
; is going to do, so I put in a guess and tag it for later revision

2015/12/26 Bank Of Example
Liability:Loan A $70.00 ; :Estimate:
Expense:Interest:Loan A $30.00 ; :Estimate:
Asset:Checking $-100.00
--8<---------------cut here---------------end--------------->8---

[Stefano Zacchiroli]

On Fri, Dec 26, 2014 at 09:02:37AM -0800, Walker Pendleton wrote:
> We use virtual accounts for budgeting loans. We track the real amounts
> in the ledger file, while virtual accounts track the budget
> requirements.

Thanks, this is a really interesting solution. I haven't yet adopted it,
mainly because I'm trying to stay clear of virtual accounts. But this
might very well be the first convincing example of the usefulness of
virtual accounts I've seen. (Arguably, one could avoid that if budgeting
had more expressive constraints, that allow statements on the sum of
separate accounts, but oh well.)

Thanks!