So, one relative of mine has borrowed money from another relative of mine over the years. The borrowing is in small amounts at various points, but the total is quite a bit apparently. The borrower is now doing well and wishes to repay. They’ve asked me if there’s software out there that can calculate how much is owed after each payment.
They want to structure the loan this way: each amount borrowed will be subject to a 5% simple interest rate annually from the date of borrowing until the present. Each repayment will be split between principal and interest pro-rata, and there are no late fees or interest on interest.
They’ve asked me if there is software out there to do the calculations and tell them at any time how much principal and interest is remaining.
Please note that I did not structure these repayment terms, and I am not giving them advice on the wisdom of these repayment terms. They just want to know about software. My google fu isn’t working, and I’ve tried looking at Quicken and Quickbooks, but it looks like they will only do interest calculations on amortized loans (if I’m in error, please correct me). Thanks for the help.
You can have Microsoft Excel (and I presume Open Office and Google Docs) do amortization schedules. I have my mortgage balances projected out using that (with prepayment amounts etc.). It’s not as flexible with dealing with sliding payment dates etc., however.
To my knowledge, Quicken does not have that capability - at least my Quicken 2009 does not seem to. QuickBooks might , however.
Correction: it’s possible that newer versions of Quicken do offer this: Quicken Articles - dummies - I last checked Quicken prior to acquiring the 2009 version (was trying to figure out how to track notional accounts for my kids’ allowances).
Thanks for the response, but this is not an amortized loan, since the borrower doesn’t want to commit to a minimum fixed payment every month, and the payments will be split between interest and principal, rather than being interest heavy up front. This is more akin to a revolving line of credit.
It looks to me like although Quicken and Quickbooks allow you to set up line of credit accounts, they won’t do the interest calculation for you (presumably because the lender always does the interest calculation and then sends you a statement). Although, if someone has additional information about these, please add.
This doesn’t make sense to me. The reason payments are interest heavy up front is because most of the balance is outstanding. If they don’t to commit to a fixed payment, why not tell them they can pay whatever they want and add the outstanding interest for the period to the remaining balance. It’s still going to “interest heavy” because most of the principal is still on loan.
As I already stated, these are the terms they have agreed to, and they are looking for software to calculate along these terms. What they want is software that will do all the calculations for them and tell them at the end of the year how much is interest and how much is principal so they can generate the tax forms properly. Telling them to add outstanding interest to the principal doesn’t answer the question. Yes, I can do this calculation manually in Excel, but they want something that will take care of all of this for them.
When your credit card calculates how much interest you owe, they must use some software to do it, right? Is there any publicly available software that will do that type of calculation?
Right, I got that. And I’m saying that it doesn’t make mathematical sense. It’s a fundamental principle of the Time Value of Money that interest is proportional to the principal. If you owe twice as much money, you owe twice as much interest.
Your credit card company takes the principal, multiplies it by the interest rate for that period, adds a tiny percentage of the principal (~1%) and calls that the minimum payment. Any spreadsheet software program can easily handle this.
Yes, Excel or the free Open Office Calc app as noted. If you consider that a manual calculation, you aren’t building the spreadsheet the right way. You can build almost anything like that in Excel. Other software either wouldn’t have the options you listed available or would be massive overkill for this. A spreadsheet with the formulas filled in and ready to go for each entry is the way to go. That is one of the main uses for spreadsheets. You can build whole applications in Excel if you are good at programming or it can just be cells with constant display of interest and principle for each loan and totaled anyway you want. I am an advanced Excel developer and this one simply isn’t hard to do.
Yeah. I’m not an advanced Excel developer and it took me roughly 10 minutes to do it earlier today (had a little time to kill while my car was being worked on).
Calculate the daily interest rate (.05 divided by 365). For a payment date, multiply that times the previous balance times the number of days since the previous payment. That’s your interest accrued. Add that to the total interest accrued (keep separate from the principal, per what was said upstream).
Then figure out how much they’re paying. Manually decide how much to go toward interest and how much to go toward principal. Subtract principal portion from the total principal loaned, and subtract interest from the total interest accrued.
Ideally, you’d put enough of it toward interest to cover the accrued interest, so there’d be no increase in total interest. As described above, it sounds like after a number of months, the principal would be paid off, but the interest due would keep increasing. Even if they don’t charge interest on that, there’s still the assumption that any given payment, more is paid toward principal than should have been done. Odd, but as the OP noted, it’s what the two parties have agreed upon.
I’d be VERY surprised if any canned solution would follow those rules.
Ok, I’ll make a spreadsheet for them. Ugh. Apparently there’s something like four or five hundred individual amounts spread out over a period of five years. I’ll let them fill in the individual values. Thanks for the advice.
Sounds like a pain, data-entry-wise, but they’d have the same pain if they had a more formal software tool too.
It’s fairly simply to select row, copy, go down one row, paste… then they just need to enter the dates and loan/payment amounts on each row. I assume this person is at least basically computer-savvy?
No, they’re not really savvy. For all I know, they’ve written all the payments down in a ledger or on napkins. But, yeah, the data entry would have been an issue either way. I’ll get them to input the data into a txt file and then I’ll import it into Excel and build it around that. It’s probably too complicated to get them to enter it directly into Excel.
Umm… no. For a 5% annual interest rate, to get a daily rate of m you want n^365=1.05, and m=(n-1). Multiply by 100 to express as a percentage.
But given the apparent number of loans, this is going to end up hugely complex. Might it not be simpler to suggest they keep each loan separate and that each repayment be one or more of the loans in their entirety? Then all you have to do is say that the repayment = original loan * (1.05 ^ years). Both parties can keep copies of the spreadsheet and knock off each loan as the loan is repaid.
Beyond that, I’ll advise that unless kept very clear and simple this whole thing could end in tears with you being blamed by both sides.