How do I write a spread sheet to figure out a loan

[Square]

These arent the exact figures but close a friend has 7k in CC debt at roughly 22%. If he can pay 500 a month to that 7k debt. what would his saving be over the life if I could loan him 2k at 0% but he has to pay me 100 a mo. Wich effectivly would lower him to paying 400 a mo. on 5k @ 22%.

I know you shouldnt loan money to friends, but I'm willing to write off the 2k and know the risk and I want to help him out. Trying to figure out how much this 2k would help over the life of his debt if I did it.

Thanks.

 

[jwkde]

you can play with this calculator
Bankrate.com credit card calculator -- How much will the minimum credit card payment cost me?

looks like 7K @ 500/month takes 17 months to pay it off

5K @ 400/month takes 15 months to pay it off

 

[Square]

darn so it saves about $800 bucks or so I really thought it would help more.

I'd still really like to know what formulas to use and how to set this up in excel though if anyone knows. I know the calc can do it but I like to learn new things or rather relearn things I've forgotten since school haha.

 

[SVHoper]

Here is a tutorial and you can download the workbook:
http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization

I think it saves even less which is shocking to me too.... perhaps negotiating the interest rate with the bank may be a better payoff - assuming you cannot transfer to balance to another card.

 

[Square]

Yes I've been trying very hard with him to be proactive about bal transfers and bugging the hell out of his cc's to lower rates. He keeps getting no's so gets a bit defeated instead of just being more annoying.

 

[SVHoper]

Not sure if this is correct... but I think I read the opening line should be..."I don't think I can pay..." then magically interest rate can be dropped.

 

[Square]

Yeah I've told him that. Problem is he can and has been paying for a while so the CC's are prolly willing to risk that he's bluffing and probaly want to see some defaults before they will help.

He racked up a good amount of CC debt since he went back to school a bit later in life a career change. Now he's beeen in a stable full time job for a bit over a year and while the CC debt is managable it's just a visicious cycle and hard to put a dent in. I've had him try prosper.com but I think he asked for too much of an intial loan and it didnt get funded, call the CC's, try and get balance transfer offers, and so on.

He'll go on a real kick trying to do something and hit's dead ends and feels trapped into his CC terms. I really think that with this little loan from me it could lower his utilazation ratios some and maybe he could start to get some better transfer offers.

Like I said I know it's bad business to loan to friends, but before I even do it Ill consider the money gone just for that sake. Though I really am 99.9% sure he'll pay me back. Regardless he is the type of friend that for the past 12 years dropped everything for me if I asked and I know would step in front of a bullet for me. For the past time of great friendship its worth the 2k to write off.

 

[SVHoper]

I think you and your friend are lucky to have such a great friendship... truly priceless!

 

[Square]

Yeah besides family there are only 2 friends I think this highly of. It is really great to have people like that in your life.

On side note we went out to coffee today and talked about it and he ahd great news. He had gotten a 18 mo 0% from discover and that got him to pay off his highest of 5k at 32% (disgusting! I didnt remember that it was that high) He's got like 10k left at 22% that I hope he'll take me up on my offer for. Also I was off on his monthly ability to pay he can afford about 1k a month toward debt.

So good news he is going to be out of this mess pretty soon and is moving in the right direction.

 

[zinger1457]

darn so it saves about $800 bucks or so I really thought it would help more.

I'd still really like to know what formulas to use and how to set this up in excel though if anyone knows. I know the calc can do it but I like to learn new things or rather relearn things I've forgotten since school haha.

In Excel you can use the NPER formula. You can use it to figure out the number of periods it would take to pay off a loan (can also be used for an investment).

Example: Select the NPER formula and enter the following values. The resulting NPER is the number of payments made to pay off the loan. Just multiple that by $500 to get the total paid (principle and interest) on the loan.

Rate: 22%/12
Pmt: -500
PV: 7000
FV:0

 

[ERD50]

Square - it is really good to hear of this great friendship you have. Those are the things that help us get through rough times.

You know, since the original calc was only an $800 delta, I really think that the advice you are giving (some of which he just took - yeah!) is worth far more. The $800 is a one time thing, your advice can help him his whole life through. I think helping him work through his problem will be more beneficial than the $ amount you would loan him.

Keep up the good work!

-ERD50

 

[Square]

Absolutely ERD50 if the debt was from irresponsible buying and living BEYOND his means I would never think of lending the money. From the get go though he is a responsible person he just essentialy had to incur the debt through CC's to pay his way through school.

His rates didn't even increase from late payments or defaults the CC's just constantly jacked them up on him. I don't know how they did this unless he's lying to me, maybe from utilazation ratios.

Thanks for the info Zinger I'll try it out.

 

[akck]

I have a spreadsheet laid out as follows:

Month
Column A contains a date for each month (9/15/10, 10/15/10, 11/15/10, etc.), say starting in row 4. If you put in a couple of months, highlight them and drag copy them down, the spreadsheet will increment each row by one month.

Current Balance
Column B in row 3 has the starting balance of what is owed. The rows below have the formula "=B3-C4" copied down.

Principle Payment
Column C starting in row 4 has the formula "=MIN($D$2-D4+E4)" and copied down. Cell D2 is the payment to be made each month. The MIN formula will ensure the payment zeros out on the last payment.

Interest Payment
Column D starting in row 4 has the formula "=B3*$C$2/12" and copied down. Cell C2 has the interest rate being charged on an annual basis.

Misc Payments
Column E can contain any one-time additional payments made during the life of the loan. You can also use it to run some what-if scenarios, for instance, when would it be paid off if I paid an additional $50 each month starting 6 months from now?

Your actual interest will vary each month because of the real timing of payments and if your company uses one of the funky methods for interest calculations, but it will be close.

What I like about the spreadsheet is that you can see the progress of how the balances are going down. I've also adjusted the payment schedule for bi-weekly payments, but it requires some changes in the formulas.

 

[Fantasm]

Just find someone who will offer him a hard money loan @ 11%