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.
On Fri Dec 26 2014 at 6:32:40 AM Martin Blais <[email protected]> wrote: > 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. > -- --- 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.
