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. On Thu, Dec 25, 2014 at 4:47 PM, Kyle Spaans <[email protected] > wrote: > 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 > > -- > > --- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- --- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
