## Help! Need to Calculate Money Owed To Me

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Help! Need to Calculate Money Owed To Me

I need to ask a friend to return money he borrowed from me years ago. But I can’t figure out how to do the calculation for the total amount that he owes me. (I asked a representative at my bank to help me, but she said I should find a calculator through Google. I searched, but couldn’t find a calculator that would work with all the variables.)

I loaned my friend money each year from 1998 through 2005. He has not repaid me any of the money he owes. I really need the money now, and I want to ask him to repay the total of what he owes me for all those years, plus interest on the loan, in the value of present day dollars. But I don’t know how to calculate the total, and I don’t know how to use Excel.

Could anyone show me how to make this calculation? The variables are year, principle he borrowed for that year, interest accrued (prime rate) for that year, and carry over to next year. Each of these variables changed each year. I’ve put the numbers for the first three years below.

Year: 1998
Principle: \$397 [Current Value of Principle: \$551]
Interest Rate: 8%
Interest Accrued: \$397 x .08 = \$44 [Current Value of Interest Accrued: \$61]
Total of Principle + Interest Accrued: \$397+ \$44 = \$441 [Current Value: \$612]

Year: 1999
Principle: \$854 [Current Value of Principle: \$1,160]
Interest Rate: 8.25%
Interest Accrued: \$854 x .0825 = \$70 [Current Value of Interest Accrued: \$95]
Total of Principle + Interest Accrued: \$854 + \$70 = \$924 [Current Value: \$1,255]

Year: 2000
Principle: \$922 [Current Value of Principle:\$1,211]
Interest Rate: 9%
Interest Accrued: \$922 x .09 = \$83 [Current Value of Interest Accrued \$109]
Total of Principle + Interest Accrued: \$922+ \$83 = \$1,005 [Current Value: \$1,320]

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

### Re: Help! Need to Calculate Money Owed To Me

You should get interest or you should get present value; trying to get both is greedy.

The reason you get paid 8% on the loan is that you are getting paid back in the future with dollars that presumably are worth less. That's the usual tradeoff. Since you signed up for interest, then that should be all you get, unless you drew up some really funky loan papers with your friend. What do they say?

Usually interest is per year. Why are you giving yourself only one year's worth of interest for your loans, e.g., \$44 for the interest on a loan from 1998. How about at least \$44 times 13 years (straight line, no compounding)?

Was 1999 a new loan or the old loan plus a new loan? I would do each year as a separate loan, e.g., \$397 in 1998, \$457 in 1999 (if that is what the new amount was). Did you really change the interest rate each year on the old money? Your terms are not very clear.

tomd37
Posts: 2879
Joined: Thu Mar 01, 2007 10:39 pm
Location: Middle Tennessee

### Re: Help! Need to Calculate Money Owed To Me

Just out of curiousity, do you have a legal contract for each of the loans?
Tom D.

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

The answer to all your questions is that I am ignorant. If it weren't for this forum, I would probably be invested in a scam. As it is, I am entirely invested in Vanguard index funds. So I owe all Bogleheads a lot of thanks.

Yes, it should be interest or present value - I was confused.

I didn't draw up any loan papers with my friend. (I'm stupid too.)

1999 is a new loan, with nothing carried forward from 1998.

So, should I take the principle from 1998 (\$854) and add to it the 8% interest for that year (\$44) and add an additional \$44 for each year, so that the total that he owes me for the money I loaned him in 1998 is the principle (\$854) plus the interest at 8% (\$44) x 13 = \$572? And I should do that for every year? Why did you say "at least" \$44 times 13 years? Is there another way of calculating it?

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

No, Tomd37, I don't have any legal contract. But I do think he will repay me. He is an honest person, but he forgets a lot of things, not just what he borrows from people, but also what he lends them. And he has more than enough money to repay me now.

But, I should have had a legal contract drawn up. Why treat your friends or family worse than you would treat strangers?

grabiner
Posts: 23085
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

### Re: Help! Need to Calculate Money Owed To Me

If you don't use Excel, you can do the calculation yourself. You need to make a calculation for each day that there is a payment or additional money borrowed, and for each change in the interest rate.

To compute the balance on one day given the balance on an earlier day, you multiply the amount owed by(1+interest rate) raised to the power of (days/365). That is how interest accrues; a bank does the same computation for your balance on a credit card or savings account.

For example, if the interest rate was 8% for all of 1998, and your friend borrowed \$1000 on 12/1/1998, then the balance on 1/1/1999 would be \$1000 * (1.08)^(31/365) = \$1006.56. Now, say you adjust the interest rate to 8.25%; the balance on 3/1/1999 would be \$1006.56 * (1.0825)^(59/365) = \$1019.54. And now, if he borrows another \$500, you adjust the balance to \$1519.54.
So, should I take the principle from 1998 (\$854) and add to it the 8% interest for that year (\$44) and add an additional \$44 for each year, so that the total that he owes me for the money I loaned him in 1998 is the principle (\$854) plus the interest at 8% (\$44) x 13 = \$572? And I should do that for every year? Why did you say "at least" \$44 times 13 years? Is there another way of calculating it?
Most loans charge compound interest; if you accrue interest and do not pay it, then you pay interest on the total balance including both interest and principal. That is why, even if the rate remained constant at 8% for 13 years, the interest would increase. The first year's interest would be \$66.32, which is 8% of \$854. The second year's interest would be \$73.97, which is 8% of \$854+66.32. And so on.
David Grabiner

kontango
Posts: 114
Joined: Sun Dec 18, 2011 11:27 am

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote:
Year: 1998
Principle: \$397 [Current Value of Principle: \$551]
Interest Rate: 8%
Interest Accrued: \$397 x .08 = \$44 [Current Value of Interest Accrued: \$61]
Total of Principle + Interest Accrued: \$397+ \$44 = \$441 [Current Value: \$612]

Year: 1999
Principle: \$854 [Current Value of Principle: \$1,160]
Interest Rate: 8.25%
Interest Accrued: \$854 x .0825 = \$70 [Current Value of Interest Accrued: \$95]
Total of Principle + Interest Accrued: \$854 + \$70 = \$924 [Current Value: \$1,255]

Year: 2000
Principle: \$922 [Current Value of Principle:\$1,211]
Interest Rate: 9%
Interest Accrued: \$922 x .09 = \$83 [Current Value of Interest Accrued \$109]
Total of Principle + Interest Accrued: \$922+ \$83 = \$1,005 [Current Value: \$1,320]
Using the numbers you gave above, here is what your friend owes you (at least for the first three loans). I'll assume the loans occurred on January 1 of each year for simplicity. I'll also assume that the rate on the loan is fixed for the life of the loan.

First Loan: 397*(1.08)^14 = \$1166.07

Second Loan: 854*(1.0825)^13 = \$2393.43

Third Loan: 922*(1.09)^12 = \$2593.28

The basic formula is: Loan*(1 + r)^T, where r is the interest rate and T is the term of the loan (in years). Your friend owes you a lot of money! Best of luck.

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

Thanks so much, David.

This is a lot of information for a mathematically challenged person like me to grasp. But I think I now see how the calculation should be done. Let me see if I understand.

I was paying for my friend’s health insurance, starting on July 1, 1998, and going through September 2005. I made a payment for him every month. The health insurance payment went up once or twice a year. And the prime rate changed many many times during that period. So, to calculate the total he owes me, I need to make a calculation for every additional health insurance payment that I made for him on the first of every month, and I need to make a calculation for each time the prime rate changed. I already have the dates that I paid his health insurance for him. So I need the additional information of the date and change in the prime rate. Once I have this additional information, I can make the calculation.

Is there perhaps a calculator online that could do this for me? I would think that since credit card compound interest is calculated in the same way that there would be one. If not, my bank will surely have an excel program that they can run for me. Of course, the bank may not want to do the calculation for me, but I can always try.

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

Thank you, Kontango!

I was confused about alternative ways of calculating the interest on the loans year by year.

But if I start with the total loan for each year, and apply the prime rate (averaged out for that year) for the life of the loan, it is easy to make the calculations. This does amount to quite a lot of money!

Do you think that doing the calculation this way is fair to him? The current value of the money I loaned him in 1998 (\$397) is \$551. Of course, that is a lot less than \$1166.07. What if he offers to pay me the present value of the money that I loaned him in each year? Would that be a reasonable suggestion on his part? Or would I be dumb to accept that suggestion? (This probably does not make any difference as to what is fair, but at the time that I made those loans to him, he really really needed the help. Now he makes lots of money, and I really really need money to pay medical bills.)

Posts: 1480
Joined: Fri Jul 09, 2010 10:29 pm

### Re: Help! Need to Calculate Money Owed To Me

I think you could use a simple savings calculator to calculate the debt and compounding interest - there's really no difference (it's just interest rate and number of years) - see
http://www.dinkytown.net/java/CompoundInterest.html

You should use compound interest, not simple interest. If he didn't repay you what he owed, then the interest is added to the debt. After all, if he had repaid you, you could have invested the money.

If you want to give him a break, then maybe drop the interest rate from 8 or 9% to, say, 5 or 6%, to arrive at a total number that sounds fair to you. Remember that if you had saved the money you lent him, you would have been able to get much higher percentages back then than you can now (so don't use the measly 1% returns on savings account now as a guide). And with inflation running at around 3%, be sure you get compensated for your generosity.

As for presentation, I would write everything out, show how the math works, and be firm when requesting that your loan be returned on the terms agreed.

555
Posts: 4955
Joined: Thu Dec 24, 2009 7:21 am

### Re: Help! Need to Calculate Money Owed To Me

Just get the principle back and be done with it. How can you even say what the interest rate is?

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

Thanks PaddyMac, your message is very useful. (And thanks for the URL for the compound interest calculator.) You’ve given me a sense of what it is reasonable to ask. I’ll be seeing him soon, and now I’m prepared to discuss it with him. I will be firm about his repaying me, and I trust that the two of us old friends will be able to work something out that is satisfactory to both of us. And I will write everything out. He is good at math and can correct any mistakes I make.

I see your point, 555. I won’t ever loan money without a written agreement again. I just wanted to have enough information so that I can negotiate with him reasonably concerning what interest rate would be appropriate. And thanks to the kind replies of people on this forum, I think I do know enough now. Probably not enough if he were trying to cheat me, but I can't believe that he would cheat anyone.

neurosphere
Posts: 2985
Joined: Sun Jan 17, 2010 1:55 pm

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote: I just wanted to have enough information so that I can negotiate with him reasonably concerning what interest rate would be appropriate. And thanks to the kind replies of people on this forum, I think I do know enough now. Probably not enough if he were trying to cheat me, but I can't believe that he would cheat anyone.
Please come back with the result of your discussions. I have a sneaking suspicion that what you feel is fair and what he feels is fair will differ, especially when he sees the 'magic' of compounding work against him.

Be prepared to lay out in detail what he owes and why, based on a reasonable interest rate. Was interest on the loan EVER discussed? In my opinion, using the prime rate is very fair. No one can reasonably borrow from a commercial source at the prime rate on an unsecured loan. They would be charged a much higher rate. However, once you lay out your terms, I would then, for the sake of the friendship, be prepared to immediately 'waive' the interest, and just hope to get back your principal.

Another thing to consider is that he may not want to or be able to pay you all at once. In that case, it's a good time to prepare a NEW loan. I.e. get him to agree that 1) he does indeed owe you at minimum the principal and 2) if he doesn't pay you in full today, lay out a payment plan with interest, for how he will return this money to you. And get it in writing!

NS
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

JeffX
Posts: 208
Joined: Thu Mar 01, 2007 4:28 pm
Location: MI

### Re: Help! Need to Calculate Money Owed To Me

How can you expect to collect on a "loan" that is 5+ years old? This case screams Judge Judy!!!!

A. you don't have a written contract
B. If you intended it to be a loan, then you should have starting collecting sooner.
C. You can't just start collecting on money when your in need of money thats 5+ years old.

I wish you the best and I hope you and him see eye to eye. I would suggest just asking for what you loaned him. The payments made to the health insurance company from 1999-2005. If you get it , pray, and run! If you don't, good luck in small claims court.

kontango
Posts: 114
Joined: Sun Dec 18, 2011 11:27 am

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote:Thank you, Kontango!

Do you think that doing the calculation this way is fair to him? The current value of the money I loaned him in 1998 (\$397) is \$551. Of course, that is a lot less than \$1166.07. What if he offers to pay me the present value of the money that I loaned him in each year? Would that be a reasonable suggestion on his part? Or would I be dumb to accept that suggestion? (This probably does not make any difference as to what is fair, but at the time that I made those loans to him, he really really needed the help. Now he makes lots of money, and I really really need money to pay medical bills.)
The way I did the calculations is correct. Certainly we could quibble on details (which would ordinarily be spelled out in a contract ), but the calculations are correct. I'm not quite sure how you arrived at your numbers but they seem way too small in terms of what he properly owes you.

I do agree with others here that without a contract it could be difficult to get him to repay. I hope it works out for you.

pkcrafter
Posts: 13126
Joined: Sun Mar 04, 2007 12:19 pm
Location: CA
Contact:

### Re: Help! Need to Calculate Money Owed To Me

You might consider what kind of friend hasn't bothered to repay you on his own after six years. Ask for the amount you loaned him, and if you get that consider yourself lucky. The interest lost you can chalk up to cost of education.

Paul
When times are good, investors tend to forget about risk and focus on opportunity. When times are bad, investors tend to forget about opportunity and focus on risk.

john94549
Posts: 4638
Joined: Tue Jul 26, 2011 8:50 pm

### Re: Help! Need to Calculate Money Owed To Me

You might want to check on the applicable statute of limitations in your state for collecting on a rolling debt.

cheese_breath
Posts: 8074
Joined: Wed Sep 14, 2011 7:08 pm

### Re: Help! Need to Calculate Money Owed To Me

When you loaned him the money was there any discussion of him paying interest? Be happy if you get your principle back.
The surest way to know the future is when it becomes the past.

tyrion
Posts: 1152
Joined: Fri Feb 08, 2008 3:33 pm

### Re: Help! Need to Calculate Money Owed To Me

I get the sense this won't end well.

You might consider broaching the subject of repayment when you see this friend, then offer to do the legwork to figure out how much he owes you. That will give him some time to emotionally prepare for it. Handing him a paper with a large number at the bottom may lead to an unpleasant conversation. It sounds like he owes you tens of thousands of dollars.

And I'll echo what others have said, that simply getting the principal back at this point would be a good start.

norookie
Posts: 3016
Joined: Tue Jul 07, 2009 1:55 pm

### Re: Help! Need to Calculate Money Owed To Me

:peace
Last edited by norookie on Wed Dec 28, 2011 4:31 pm, edited 1 time in total.
" Wealth usually leads to excess " Cicero 55 b.c

Sam I Am
Posts: 2062
Joined: Tue Feb 20, 2007 7:58 pm

### Re: Help! Need to Calculate Money Owed To Me

norookie wrote: tag for outcome, or date to watch judge judy. Good Luck!
Ha! I would gladly pay up before I would get in front of Judge Judy! I love her, but only fools stand up before her and try to BS.

Back on task: Is it remotely possible your friend has any idea what a "fair" settlement might be?

Sam I Am

HomerJ
Posts: 11919
Joined: Fri Jun 06, 2008 12:50 pm

### Re: Help! Need to Calculate Money Owed To Me

555 wrote:Just get the principle back and be done with it. How can you even say what the interest rate is?
This. You'll be very lucky to get paid at all, I'm guessing.

Last thing you want is for him to quibble over what the interest rates were 13 years ago. That will delay him paying you back.

Just ask for the principal back. And be very happy if you get it.

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

Hi Kontango,

I didn't mean to question your calculations- I was just confused as usual.

Thanks everyone for your help! I saw him earlier today, and he had in fact forgotten all about the loan. He said how sorry and ashamed he was many many times. And we have come to a very satisfactory agreement. He and I have been good friends for almost 20 years, and I have never known him to do anything dishonorable in all that time.

So, I'm happy to announce that there are some honest people in the world. (But, I've learned my lesson. No more loans to friends without a clear understanding and a contract.)

neurosphere
Posts: 2985
Joined: Sun Jan 17, 2010 1:55 pm

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote: he had in fact forgotten all about the loan. He said how sorry and ashamed he was many many times. And we have come to a very satisfactory agreement. He and I have been good friends for almost 20 years, and I have never known him to do anything dishonorable in all that time.
You two must be very good friends indeed. It sounds like you expect to get repaid an amount which you feel is fair. Please come back and let us know how it all turns out in the end. And thanks for sharing your story, as there are likely many lurkers who may learn from your situation.

NS
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

Ktemene
Posts: 83
Joined: Thu Mar 20, 2008 12:58 pm

### Re: Help! Need to Calculate Money Owed To Me

Thanks Neurosphere,

I will come back and report how it all ends. My friend is a professor and is very absent-minded about things that most people could never forget. So it really is plausible that he really did forget about it. (I'm a professor too and even more absent-minded than he is. That's why I mistakenly put wrong information in my original posting. I thought I had copied the correct information, but I had copied gibberish.

Both of my parents were very honorable people, and I grew up thinking that most people were like them. Of course, now I know that honesty is one of the rarest things in the world. But there are some people who are honest, and won’t steal or exploit even when they know they could get away with it.

I’ve certainly learned a lot about how interest rates work.

I was so lucky to happen on this forum several years ago. Even though I know almost nothing about finance, I now have a well chosen portfolio just because I read all the advise on this forum about index funds and the wisdom of simplicity.

Thanks so much.

Minot
Posts: 423
Joined: Sat Jan 29, 2011 2:35 pm

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote:Thanks Neurosphere,

I will come back and report how it all ends. My friend is a professor and is very absent-minded about things that most people could never forget. So it really is plausible that he really did forget about it. (I'm a professor too and even more absent-minded than he is. That's why I mistakenly put wrong information in my original posting. I thought I had copied the correct information, but I had copied gibberish.

Both of my parents were very honorable people, and I grew up thinking that most people were like them. Of course, now I know that honesty is one of the rarest things in the world. But there are some people who are honest, and won’t steal or exploit even when they know they could get away with it.

I’ve certainly learned a lot about how interest rates work.

I was so lucky to happen on this forum several years ago. Even though I know almost nothing about finance, I now have a well chosen portfolio just because I read all the advise on this forum about index funds and the wisdom of simplicity.

Thanks so much.
So how did it end?

555
Posts: 4955
Joined: Thu Dec 24, 2009 7:21 am

### Re: Help! Need to Calculate Money Owed To Me

Minot wrote:So how did it end?
Interest keeps piling up at 8% or 9% per year.

lululu
Posts: 1378
Joined: Thu Apr 10, 2014 4:23 pm

### Re: Help! Need to Calculate Money Owed To Me

Minot wrote: So how did it end?
Not well, I'm thinking, given the length of time money was owed with no attempt to pay it back.

This is possibly the most disorganized financial situation I've ever seen in my life.

dickenjb
Posts: 2941
Joined: Tue Jan 05, 2010 1:11 pm

### Re: Help! Need to Calculate Money Owed To Me

OP posted last night so maybe he will provide an update. I am curious whether the prime rate was agreed or is an afterthought. Unusual that no promissory note was executed. I will send OP a PM in hopes that he will update this thread. "Inquiring minds want to know".

derosa
Posts: 464
Joined: Wed Jul 24, 2013 5:18 pm

### Re: Help! Need to Calculate Money Owed To Me

I would forget all your calculations about interest. The reason being is that you can't figure it out here. And when he says how did you get that crazy number and you start stumbling all over your shoe laces... well he will say you don't know what you are talking about.

I appreaciate that you refer to this person as a friend. But you know and I know that he is not. A friend would have paid this back before 15 years went by.

I would give him a little sheet with all the money you have lent him and ask for that back. And when he says that he can't. Time to walk away.

Well now you know what kind of "friend" he was.

IPer
Posts: 1639
Joined: Sun Jul 28, 2013 8:51 pm

### Re: Help! Need to Calculate Money Owed To Me

They are NOT friends! Unfortunately, they are acquaintances. They gave nothing in return. And they defaulted when convenient. If I never crossed paths with them do you think I would have been better off, most definitely!

ps before you waste all the time going through these gyrations about the calculations (which I think you should list each loan separately with its principal and interest and then add all that up) shouldn't you ask this person if they are in the capacity to be able to repay it at this time?

oldzey
Posts: 1100
Joined: Sun Apr 13, 2014 8:38 pm
Location: Land of Lincoln

### Re: Help! Need to Calculate Money Owed To Me

pkcrafter wrote:You might consider what kind of friend hasn't bothered to repay you on his own after six years. Ask for the amount you loaned him, and if you get that consider yourself lucky. The interest lost you can chalk up to cost of education.

Paul
+1 to this.

"Lend money to an enemy and you will gain him, to a friend and you will lose him." - Benjamin Franklin
"The broker said the stock was 'poised to move.' Silly me, I thought he meant up." ― Randy Thurman

MikeRes
Posts: 31
Joined: Wed May 18, 2011 10:07 pm

### Re: Help! Need to Calculate Money Owed To Me

Ktemene wrote:I saw him earlier today, and ....... we have come to a very satisfactory agreement.

So, I'm happy to announce that there are some honest people in the world. (But, I've learned my lesson. No more loans to friends without a clear understanding and a contract.)
I'm glad you're satisfied with the outcome.
I'm wondering though if you care to share what the total amount of the various loans were and what the amount repaid was.

One more thing.
I'm not sure that you have learned your lesson.
Let me fix your last sentence (it was too long): No more loans to friends! without a clear understanding and a contract.

If you feel compelled to lend money to friends (or family) consider the money a gift and not a loan.
If you get repaid say a quiet prayer and be thankful.