Help rearranging this algebra for Excel

[OneTimeUS]

I'm not quite sure how I'd rearrange this equation for use in Excel.

A=B(1+x)^-(C/365)

I need to solve for the value of x, and values A B and C are values from other cells on the excel workbook (E.G. H1=D7[1+x]^-[D3/365]) that a user would input and change.

I've tried re-arranging the equation itself to make x the subject but I'm not massively mathematically competent and I'd like to know if there's some way Excel could make this easier for me.

Thanks in advance.

[Jakobshavn]

Sure..... if B is B1
and x is X1
and C is C1
then in A1 enter:
=B1*(1+X1)^(-C1/360)

[OneTimeUS]

Sure..... if B is B1
and x is X1
and C is C1
then in A1 enter:
=B1*(1+X1)^(-C1/360)

Sorry, maybe this was unclear.

I already have the value for A (one that the user would input themselves), what I don't have is a value for X. Hence, I need to solve this equation for the value of X somehow using excel. I don't already have a value for x, it's something that needs to be calculated.

Thanks for your input, though.

[Jakobshavn]

I understand............if you have a desired value for A (the formula in cell A1), then use Solver to adjust X1 to drive A1 to that desired value.


See:

http://www.youtube.com/watch?v=YAugMpW-aJw

[OneTimeUS]

I'm not sure if solver is the solution I'm looking for because A is used to calculate B. (I should have made this clearer earlier, only A and C are user inputted.)

I made some adjustments anyway so the cells weren't self-referential and set solver on it just to see if it'd work but excel is now non-responsive and from the sounds of it is pushing my CPU full-throttle. I know the equation is machine solvable because I put it through WolframAlpha and came out exactly as I expected.

[OneTimeUS]

Never mind, I found a reference error which was causing excel to crash and solver came back with the figure I was expecting, which is a step closer to a good solution.

Now, is there any way I can automate this so the user doesn't have to mess around with solver every time they change values A or C?

Thanks again.

[Jakobshavn]

There may be with a macro. However, your formula looks like a finance formula. There may be a invert formula already available.

What are you trying to calculate??

[OneTimeUS]

I'm trying to calculate the APR on a fixed interest loan with one repayment using the EU approved formula for calculating APR (first result from https://www.google.co.uk/search?q=co...directive+2008 ).

A is the value of the loan given to the customer, B is the total amount repayable on that loan, C is the number of days the loan is live for.

[ChemistB]

Algebraically (although Jacob is right, there might be a built in function, I don't use the finance functions)

((POWER(A,1/(C/360)))/B)-1 = x

EDIT: Not working... back to the pen and paper.

[Jakobshavn]

Thank you ChemistB!

[OneTimeUS]

Algebraically (although Jacob is right, there might be a built in function, I don't use the finance functions)

((POWER(A,1/(C/360)))/B)-1 = x

EDIT: Not working... back to the pen and paper.

Tried this out and got something wildly different from what I was expecting, sorry.

Trying to rearrange the equation myself got me to (x+1)^(C/365)=B/A but quite frankly I have no idea what to do when you start dealing with fractions of powers.

[ChemistB]

x=POWER(A/B, -360/C)-1

[Jakobshavn]

How about:

=EXP((360*(LN(B1)-LN(A1))/C1))-1

This inverts the formula correctly (numerically) in at least one case.

[ChemistB]

Based on my examples where I ran both forwards and backwards, both Jakob's and my formula give the correct answers.

[OneTimeUS]

How about:

=EXP((360*(LN(B1)-LN(A1))/C1))-1

This inverts the formula correctly (numerically) in at least one case.

I think this is what I'm looking for, I've tried a few figures and compared it to wolfram and some other references and I'm getting the right results.

Thanks very much to both of you!

[Jakobshavn]

I finally was driven back to a moldy algebra textbook. Glad it works for you!