
Math Software Math Software  Mathematica, Matlab, Calculators, Graphing Software 
 LinkBack  Thread Tools  Display Modes 
August 29th, 2013, 02:29 PM  #1 
Newbie Joined: Aug 2013 Posts: 4 Thanks: 0  Excel Formula for Breakeven Interest Rate
I need to come up with an excel formula that will tell me what interest rate the loan can increase to in order to pay the loan 1:1. A1 = \$500,000 (Loan Amount) A2 = 5.50% (Current Interest Rate) A3 = 30 (Loan Years) A4 = \$2,838.95 (Monthly Payment) A5 = \$50,000 (Income generated to repay the loan) A7 is where I need a formula that will tell me how high the rate can go and still have the loan repaid on a 1:1 basis. I have a formula in mind that I need to work backwards from but I may be way off base: 1=(A1*(X/12))/(1(1+(X/12))^(A3*12) I the above I would need to rewrite the formula to solve for X.... but like I said, I may be way off on this. Any help please? Thanks, Last edited by greg1313; September 13th, 2014 at 04:46 PM. 
August 29th, 2013, 08:43 PM  #2  
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 14,597 Thanks: 1038  Re: Excel Formula for Breakeven Interest Rate Quote:
Rick, A1 to A4 are correct...the rate would be better stated this way: 5.50% annual compounded monthly... WHAT d'heck does A5 mean ? And 1:1 basis ? Your formula: 1=(A1*(X/12))/(1(1+(X/12))^(A3*12) is missing a ")" on right side. Explain what you're trying to do....perhaps give an example...  
August 30th, 2013, 07:49 AM  #3 
Newbie Joined: Aug 2013 Posts: 4 Thanks: 0  Re: Excel Formula for Breakeven Interest Rate
Ok, let's see if I can explain this... A4 is the monthly loan payment. If we multiply it by 12 you get \$34,067.40. A5 is the monthly income the person generates to repay the loan annually. If we divided \$50,000 by \$34,067.40 we get 1.47, meaning income generated is on a 1.47:1 ratio. As interest rates increase the monthly payment will also increase, pushing the monthly payment closer to the \$50,000 mark. I need to know a formlua in excel that will calculate what interest rate will make the loan payments equal to the income generated to repay the loan (1:1). I listed the cell references with appropriate info so that a formula could be created. Thanks for the help. Last edited by greg1313; September 13th, 2014 at 04:45 PM. 
August 30th, 2013, 09:41 AM  #4 
Newbie Joined: Aug 2013 Posts: 4 Thanks: 0  Re: Excel Formula for Breakeven Interest Rate
Sorry A5 is annual income that the person has.

August 30th, 2013, 12:56 PM  #5 
Newbie Joined: Aug 2013 Posts: 4 Thanks: 0  Re: Excel Formula for Breakeven Interest Rate
I got it: =RATE(30*12, 50000/12, 500000)*12 
August 31st, 2013, 10:01 AM  #6 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 14,597 Thanks: 1038  Re: Excel Formula for Breakeven Interest Rate
That doesn't make much sense to me... you're simply applying simple interest, but you need compounding results. You're doing this: since 2838.95 means 5.5% then 4166.67 means 5.5 / 2838.95 * 4166.67 = ~8.07% : 4166.67 is 50,000/12 Result is way too low. Since the formula is P = Ai / [1  1/(1+i)^n], then i cannot be directly calculated: numeric methods (iteration) are required. (P = monthly Payment, A = Amount borrowed) For the above example, a rate of ~9.397 will result. In other words, use .09397/12 as i in above formula, and result will be P = 4166.67 

Tags 
breakeven, excel, formula, interest, rate 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Compound Interest and Target projections in Excel  redliner74  Economics  3  July 17th, 2013 11:04 PM 
Simple Interest: How to find the actual interest rate?  grace25  Elementary Math  12  January 24th, 2013 08:46 PM 
Calculate interest rate based on interest in parcels  douglasrac  Economics  0  February 17th, 2011 09:14 AM 
need help to find interest rate of return by excel  nastenka  Economics  0  December 24th, 2009 01:12 PM 
Excel Formula for Breakeven Interest Rate  RickOShay  Algebra  0  December 31st, 1969 04:00 PM 