My Math Forum  

Go Back   My Math Forum > Math Forums > Math Software

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


Reply
 
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.
RickOShay is offline  
 
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 ?

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...
Denis is offline  
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.
RickOShay is offline  
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.
RickOShay is offline  
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
RickOShay is offline  
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
Denis is offline  
Reply

  My Math Forum > Math Forums > Math Software

Tags
breakeven, excel, formula, interest, rate



Search tags for this page
Click on a term to search for related topics.
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





Copyright © 2019 My Math Forum. All rights reserved.