 My Math Forum Excel Formula for Breakeven Interest Rate
 User Name Remember Me? Password

 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 Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded 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      