To find the power ( i.e. ^ n) when you have other variables.

[e4excel]

To find the power ( i.e. ^ n) when you have other variables.

I need some help from a Mathematical Perspective as well as through EXCEL..

Ex: 5^n = 125

We know that 5^3 = 125,

Now if I have been given the value 5 and 125 , then how do i get the answer 3 ( i.e ^ n) value...

I need to solve a slightly complex issue and therefore need help for the above requirement as well as to get the "n" value..

[NBVC]

=LOG(125,5)

will give 3

[e4excel]

Thanks a lot NBVC,

That was very quick eh!

I got the solution but is there a way I can solve it mathematically as I have a slightly complex equation to solve where am not sure I can use the
[ = LOG(Number,base) ] function...

SO any mathematical approach...

I have this mathematical equation which gives me the EMI

E= P x r x (1 + r)^n / ((1+r)^n -1)

Here p=principal amount

r = interesr rate per month (ex: if interest rate per annum is 10% then 10/(12*100))

n= tenure in months ..

Now If I have the values for everything except "n", then how do i back-calculate that?


I tried using Excel's inbuilt NPER function but the answers are not the correct...

So would prefer to do it mathematically and just compute the same...
Ex:
EMI- 6066
P = 500000
R = 8%/12
n = ?

The EMI = 6066 is derived by using "n" as 120, but how do i back-calculate that?

[MickG]

Hi, You can get a result for "n" by using "Solver", also I think your "r = 8%/12", should be r= ((1.08) ^ (1/12))-1). ie 12th root-1.
Where did you get the equation from ??
Regards Mick

[NBVC]

If you know the basic function won't help, then you should post the "real" problem from the start... it wastes people's time and effort to give solutions that work with the examples given, but then don't work on your real sheet, because you haven't told us all we need to know.

This is why your threads seem to never be "short and sweet"....but instead linger for minimum of 10 posts.

Please remember this next time.

I am not a financial type...so I am not sure the appropriate Excel function for your question, if any. But I think shg might be able to help (as he is good with this type of mathematical question)... or any accountant on board, maybe?

[e4excel]

Hi, You can get a result for "n" by using "Solver", also I think your "r = 8%/12", should be r= ((1.08) ^ (1/12))-1). ie 12th root-1.
Where did you get the equation from ??
Regards Mick

A friend of mine from the Bank gave it to me and it works as the values are the same as using the PMT function..I think he had got it from some web-site which he's not aware of now...

Let me try you r solution and come back to you.. MICK
Thanks

[e4excel]

Dear NBVC,

Thanks for the help for the LOG function, I was not even remotely aware of such a function before you gave this solution...

If you know the basic function won't help, then you should post the "real" problem from the start... it wastes people's time and effort to give solutions that work with the examples given, but then don't work on your real sheet, because you haven't told us all we need to know.

I had mentioned that in my very first post

I need some help from a Mathematical Perspective as well as through EXCEL..
Ex: 5^n = 125

We know that 5^3 = 125,

Now if I have been given the value 5 and 125 , then how do i get the answer 3 ( i.e ^ n) value...

I need to solve a slightly complex issue and therefore need help for the above requirement as well as to get the "n" value..

Honestly, NBVC , I wanted to know how to get the value of "n" from a simple equation and build on that in my actual equation and therefore had requested for both a math perspective and the excel way...

I did not intend to waste anyone's time but gather tools which can help me for my problem...however, I am sorry if you feel that, I thought of doing it myself had I got a mathematical answer..

This is why your threads seem to never be "short and sweet"....but instead linger for minimum of 10 posts.

Please remember this next time.

I will remember this time but it depends on solution to solution as well as the nature of the problem..

I am not a financial type...so I am not sure the appropriate Excel function for your question, if any. But I think shg might be able to help (as he is good with this type of mathematical question)... or any accountant on board, maybe?

There's no Excel function I think which can help solve this function rather will have to be done mathematically and therefore I mentioned that in the first post...as someone might have a basic mathematical approach..

Warm regards
e4excel

[e4excel]

Now If I have the values for everything except "n", then how do i back-calculate that?

I tried using Excel's inbuilt NPER function but the answers are not the correct...

So would prefer to do it mathematically and just compute the same...
Ex:
EMI- 6066
P = 500000
R = 8%/12
n = ?

The EMI = 6066 is derived by using "n" as 120, but how do i back-calculate that?

Just to reconfirm,

[ = NPER( RATE%/12 , EMI , PRINCIPAL , FV = ? , TYPE = ? ) ]

IS this correct , I feel the Type is OPtional , I need to get the answer as 120

Can someone help me on the same?

[NBVC]

I did some quick research on EMI and came up with this site: suggesting to use PMT() to find the EMI... with your numbers, I get the above final value for EMI.


Then to get the number of periods, I would use NPER()

and formula: =NPER(A3/12,-A1,A2)

Where A1 contains EMI (6066)

A2 contains Principal Value (500,000)

A3 contains interest rate (8%)

This gives result 120.

[MickG]

Hi, If you use your original formula the ans = "117.8368" , which is close.
I have use the formula before and have proved its Correctness.
The "NPER" Function does not seem to return a valid Result.
If you plot the Basic Equation on you speed sheet you will find "117" approx is the value For "n", and if you put 117 in the formula you get "6095" as a fig for the Repayments.
I'd stick to the formula !!
Regards Mick

[e4excel]

Thanks NBVC,

I dont know why I was not getting it though I was very close to that..
But thnks anyways

Maybe I was adding the FV and messing it up..

The thread would be Marked solved...but I am excited to hear from MICk about a mathematical approach...!

Hey MICK,

Very honestly , I am not quite familiar using "SOLVER", however how did you the solve his equation..
Can you please elaborate...

Hi, If you use your original formula the ans = "117.8368" , which is close.
I have use the formula before and have proved its Correctness.

I had reached this stage

E = ( 1 + R%/12)^n
----- ----------------
P X R ( 1 + R%/12)^n -1

I dont know how to proceed after this!.

[MickG]

Hi In a way you don't realy need "Solver" , because if you follow the following, you just need to substitute various values for "n" until you get the required periodic repayment value.
Place the following data in range "A1 :D2".

Please Login or Register  to view this content.

Place this formula in Cell "A2", (The cell above showing 6065.998587)

Please Login or Register  to view this content.

This is your formula ! (I am familar with it, and have used it many times before)
If you've done it correctly, it should now be reading 6065.99587.
Now by changing the value in "n" in "D2" you will alter the repayment value.
The value "r" is ((1+0.08)^(1/12))-1), but if you use (0.08/12) it makes little difference in this case, the difference is one is a Compounded value the other a Flat rate.
Have a play.
Regards Mick

[e4excel]

Place the following data in range "A1 :D2".

Please Login or Register  to view this content.

Place this formula in Cell "A2", (The cell above showing 6065.998587)

Please Login or Register  to view this content.

This is your formula ! (I am familar with it, and have used it many times before)
If you've done it correctly, it should now be reading 6065.99587.
Now by changing the value in "n" in "D2" you will alter the repayment value.
The value "r" is ((1+0.08)^(1/12))-1), but if you use (0.08/12) it makes little difference in this case, the difference is one is a Compounded value the other a Flat rate.
Have a play.
Regards Mick

Hi MIck,

I tried that, however I need to back-calculate the answer for "n"?
using the equation [ EMI = P x r x (1 + r)^n / ((1+r)^n -1) ]

I have all the other variables :
P = 500000
r = 8%/12
EMI= 6066.38
The "n" value used to get the EMI = 6066.38 was 120 months ( i.e. 10 years)
, Now How I would back-calculate the same Mathematically?

I need the answer to be ~120 to confirm the equations authenticity..

What will be the Mathematical Equation for calculating "n" ?

n = ???

Warm Regards
e4excel...

PS I know I am able to get it with NPER the way NBVC showed but I am also keen to get a Mathematical solution..

[MickG]

Hi, To get an equation for "n" , will take someone a lot brighter than me.
Most people would use "Solver".
The Reason For the two value "117" and "120" is because the Nper Function uses "0.08/12" =(0.006666667)
, which I believe is not strictly correct. (I 'm open to other views)
And the formula uses the result of ((1+0.08)^(1/12)-1) =(0.00643403).
Explained previously !!
If you change the rate value for both the Function and the Equation, you will see you get these two results.
Mick

[e4excel]

Hi!,

I am at my WIts end trying to solve the mystery but to no avail...
CAn you please do it for me both with/without "1"...in a sheet..pLease

I am not familiar using SOLVER at all..

[MickG]

Hi, This is about the best I can Do. !!
Read and Digest !
Regards Mick

[e4excel]

Thanks a million for all the efforts of putting that together, thats really very useful..

I am studying this right now..
The reason of all this rigmarole was to come up with something to beat the Floating rate of Interest occuring in a long tenure of Amortization..

God bless you and thanks once again...
Its seems there's not a single math formula to get the "n" so alternatively this method can be employed..