Extending a loan (lending money)

Couple of questions:

2) it this an amortized loan (where the payments cover the interest for the month, and gradually reduce the principal until it's zero), or did you calculate the payments on some other basis?

The optimal way to handle the situation would have been to, upon first making the loan, to create an Asset account in Quicken and when writing the loan check to use that account as the category for the Quicken entry. Then, whenever a payment is received, to split the deposit and record a portion as interest income and a portion to the Loan asset account. This will reduce the ourstanding loan amount. When you made the 2nd loan, just record it into the same asset account and keep recording payments received until the Loan asset balance goes to zero.

thanks, Dan...

If I understand your questions correctly, when I first made the loan I set it up as a loan with an attendant asset account and made the payment via the loan screen with the principal portion reducing the asset account at each payment point.

It was originally set up as amortized over 3 years...I think at the time she could only afford $120 twice a month so I used that as a payment...but I really can't recall my thinking at that time or if I checked the payment schedule to see if it would be at zero after the three years. (Can't believe now that I was so obtuse).

When I lent her the additional funds, I just wrote her a cheque and posted the entry against the asset account.

I guess from reading your comments it was at the first step of setting up the amortization that I made my first mistake and then just compounded it by not dealing with the increased loan issue at that time. hmmm...sloppiness catches up...

In any event, now the loan is at its last payment and she still owes me...I don't know what to change to make the payment schedule to continue down to zero. Additionally, at this time she wants to increase her payment and to make it every two weeks. Originally it was set up semi monthly...which I guess I also still didn't entirely understand, because she was going to pay me on the 15th and the end of the month and the schedule just never worked out that way. Now she gets paid every two weeks.

Its hard to sit here and face all the dumb choices I made in this instance, but its good...sort of like confession...lol...although it wasn't my fault on the multiple posts...Google got back to me and said they had a glitch...aha...I won one...

Thanks again for your help, any additional assistance you can offer would be wildly appreciated.

Ok, if I understand your situation .... not only did the principal amount increase (when you made the subsequent loan) but also the payments were irregular (sometimes twice a month, sometimes every 2 weeks).

The Quicken loan wizard is REALLY set up to handle regular, recurring amounts (your mortgage or an auto loan, for example) where the payment is made on a recurring, predictable UNCHANGING basis, and the principal only changes due to regular payments.

In short, you're going to have to calculate this manually, probably using a spreadsheet, to calculate for each payment how much of it was owed to you as interest and how much was applied to reduce the principal. The basic formula for calculating interest for each period is: Interest-Due = Principal-outstanding TIMES Interest-Rate-for-the-period TIMES time.

Principal-outstanding is, initially, the amount of the loan. Interest-rate-for-the-period is the agreed upon rate divided by (12 times the number of agreed upon payments -- i.e, 24 if paying twice a month, 26 if paying every 2 weeks). Time is the number of days since the previous payment, divided by 365 or 366.

The total payment received minus the interest-due is the amount that reduces the principal, thus producing the principal-outstanding amount for the next period.

So, the columns that you'll need in the spreadsheet are: Date, Action, Principal-outstanding, Interest-due, Principal, New Principal-Outstanding.

After you create the line for the first payment, you just copy it down (editting the date field as you go) until you get to the point of the subsequent loan. At that point, you'll need to slip in an additional line to reflect the addition to principal of the subsequent loan. Be careful with the immediate next payment calculation, it can get a bit weird.

BTW (curiosity got the best of me), are you Canadian? I noticed the use of the word "Cheque".

My first reaction...OMG.. but I'll soldier on in true Canadian fashion and give it a go. Thanks sooo much for all your time is assisting me with this mess I've made. Guess I've got my work cut for me this evening.

I think I've done what you suggested but right off the bat the figures are a long way from quickens A B C D E F

My formula for D6 Æ*(10/(12*24)*(A6-A5)/365) My formula for E6 =$B$2-D6 My formula for F6 Æ-E6

Quicken's breakdown for payment of 03/15 is 30.32 Interest and 89.68 Principal resulting in NPO fo 7810.32

I think I've got it right but not according to Quicken..could you please look over and let me know what I've done incorrectly. At this rate, I'll be owing 'her' money...sheesh

I looked back over the Quicken loan info and the only piece I don't understanding where it fits in is the monthly compounding variable...

I had this all nicely lined up until I got to preview which threw it all out of whack...then spent an equal amount of time trying to line it up not so nicely...no tab functions in edit that I could see...sorry...hope its clear enough

Thanks again...

If this loan was amortized at 36 monthly payments, each of the 36 payments would be $254.91. At 72 payments, each would be $127.23 (slightly less than half because principal is being paid off slightly faster). The interest due portion for the first payment would be $65.83 (at 36 payments) or $30.30 (at 72 payments). So it appears that you input 72 payments (twice a month) into the Q Loan Wizard to arrive at $30.32 ( I'm not sure where the 2 penny difference came from).

BUT, your first payment wasn't received a half-month later, it was received almost a full month later ... and the formula (corrected below) was for computing interest with a flexible payment schedule.

The correct formula (using your notation) for D6 is: C6*B$1*(A6-A5)/365. Sorry if I wasn't sufficiently clear in my prior explanation. C6 is principal, B$1 is interest, and the remainder is number of elapsed days divided by # days in a year.

To make things a bit easier, cell C7 can just refer back to cell F6 (i.e., contain the value +F6). Just watch out for when you insert the additional loan amount and be sure to point to the proper cell in the immediately succeeding payment row.

Upon seeing your response and reviewing 'my' formula, I realized (with a resounding 'duh) that I must have been more tired than I realized. I had figured out the right formula on subsequent rows but had forgotten to go back and change original formula.

Nevertheless, I have carried on and, thanks to your immense help, have finalized the spreadsheet and found that at this point my linked asset account is 'out' by about $300 (lesson learned).

So, of course, now all sorts of other questions arise...like how do I now fix the loan account? A lot of those previous entries were wrong. Can I just (I hate to say it) plug the asset account to reflect the correct number and carry on with the payments from my spreadsheet. Do I dump the 'loan' through Quicken and just post to the asset and income accounts as you suggested in your 'optimal' recommendation? Can I do this without deleting the asset account? ...hmmm...I have a sense of dejavu ...after all this work, I feel like I'm right back to my original question and I'm quite sure you will never answer another post from 'willi'. When we are finished you can just tell me where to send the 'cheque'...lol

Once again, having trouble with Google Groups posting...hope this works...

As I mentioned before, the Quicken loan wizard is really intended for payments occurring at a regular schedule on loan balances that decline due to full amortization and doesn't increase with addition loans.. The loan that we've been discussing doesn't come close to meeting those requirements.

SO, you've got a couple of choices: A) go back and edit each of the previous payments to reflect the info in your spreadsheet (continuing to use the existing asset account, but NOT using the loan wizard), or B) just "plugging" the value in the asset account and proceeding forward.

My inclination (particularly now that the correct values are in hand) would be to go back and edit the previous transactions (70 or so transactions really isn't that bad, probably 90 minutes or so), and then you wouldn't need to keep the spreadsheet around in case the person who received the loan questions your accounting. On the edits, the total payment amount wouldn't change, just how you allocate the funds within the split.

So, Dan, I'm done...I don't know how to thank you for all the help you've been, but, please just know that you have made this lady much less stressed...and I'm so grateful