My Math Forum Excel Formula for Breakeven Interest Rate

 Math Software Math Software - Mathematica, Matlab, Calculators, Graphing Software

 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:  Originally Posted by RickOShay I need 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) In the above I would need to rewrite the formula to solve for X.... but like I said, I may be way off on this.
What do you call an Irishman that bounces off the walls? Answer: ricochet (rick o'shay) :P

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 ?

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

,

,

,

,

,

,

,

,

,

,

,

,

,

,

# break even excel carry interest

Click on a term to search for related topics.
 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post redliner74 Economics 3 July 17th, 2013 11:04 PM grace25 Elementary Math 12 January 24th, 2013 08:46 PM douglasrac Economics 0 February 17th, 2011 09:14 AM nastenka Economics 0 December 24th, 2009 01:12 PM RickOShay Algebra 0 December 31st, 1969 04:00 PM

 Contact - Home - Forums - Cryptocurrency Forum - Top