Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
How do I write a spread sheet to figure out a loan
Old 11-05-2010, 07:06 PM   #1
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
How do I write a spread sheet to figure out a loan

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.
__________________

__________________
Square is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 11-05-2010, 07:15 PM   #2
Recycles dryer sheets
 
Join Date: Jan 2010
Posts: 174
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
__________________

__________________
jwkde is offline   Reply With Quote
Old 11-05-2010, 07:27 PM   #3
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
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.
__________________
Square is offline   Reply With Quote
Old 11-05-2010, 10:02 PM   #4
Recycles dryer sheets
 
Join Date: Apr 2010
Location: Silicon Valley
Posts: 198
Here is a tutorial and you can download the workbook:
http://www.tvmcalcs.com/calculators/...n_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.
__________________
SVHoper is offline   Reply With Quote
Old 11-05-2010, 10:57 PM   #5
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
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.
__________________
Square is offline   Reply With Quote
Old 11-05-2010, 11:28 PM   #6
Recycles dryer sheets
 
Join Date: Apr 2010
Location: Silicon Valley
Posts: 198
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.
__________________
SVHoper is offline   Reply With Quote
Old 11-06-2010, 08:38 AM   #7
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
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.
__________________
Square is offline   Reply With Quote
Old 11-06-2010, 10:45 AM   #8
Recycles dryer sheets
 
Join Date: Apr 2010
Location: Silicon Valley
Posts: 198
I think you and your friend are lucky to have such a great friendship... truly priceless!
__________________
SVHoper is offline   Reply With Quote
Old 11-06-2010, 02:12 PM   #9
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
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.
__________________
Square is offline   Reply With Quote
Old 11-06-2010, 06:19 PM   #10
Thinks s/he gets paid by the post
zinger1457's Avatar
 
Join Date: Jul 2007
Posts: 1,455
Quote:
Originally Posted by Square View Post
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
__________________
zinger1457 is offline   Reply With Quote
Old 11-06-2010, 06:43 PM   #11
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,298
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
__________________
ERD50 is online now   Reply With Quote
Old 11-06-2010, 08:18 PM   #12
Confused about dryer sheets
 
Join Date: Dec 2009
Posts: 7
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.
__________________
Square is offline   Reply With Quote
Old 11-09-2010, 02:44 PM   #13
Full time employment: Posting here.
 
Join Date: Mar 2008
Posts: 704
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.
__________________
akck is offline   Reply With Quote
Old 11-09-2010, 07:55 PM   #14
Recycles dryer sheets
 
Join Date: Oct 2009
Location: Los Angeles
Posts: 50
Just find someone who will offer him a hard money loan @ 11%
__________________

__________________
Fantasm is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Found 15 acres, loan to buy land, loan to build... how is it done? jIMOh FIRE and Money 17 11-05-2010 01:18 PM
Bid/Ask Spread John Galt FIRE and Money 4 02-21-2010 07:48 PM
Trying to play the spread on my CC. JohnDoe FIRE and Money 14 04-25-2009 07:35 PM
Do you find yourself staring at your Excel spread sheet? dex Young Dreamers 14 06-24-2006 11:31 AM
FIRECalc - Retire Early Spread Sheet Comparison Merron FIRE and Money 9 11-06-2003 03:09 AM

 

 
All times are GMT -6. The time now is 09:45 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.