Simple Math Answer...

[bran8989]

If I have an annualized number of 1.5000%, i calculate the monthly equivalent by doing the following:

=(1-(1-1.5000%)^(1/12))
=0.1259%

When I try to re-annualize that number, by doing the following, I can't get back to my 1.5000% figure. Can anyone help and provide an answer as to why?

=(1+0.1259%)^12-1
=1.5209%

Thanks!!

 

[admiral100]

Hi,

Try this: D15=0.1259%

=1-POWER(D15-1,12)

 

[shg]

	A​	B​	C​
1​	Annual	1.5000%​	B1: Input
2​	Monthly	0.1241%​	B2: =(1+B1)^(1/12) - 1
3​	Annual	1.5000%​	B3: =(1+B2) ^ 12 - 1

 

[joeu2004]

The correct formulas are:

A1: =(1+1.5%)^(1/12) - 1
B1: =(1+A1)^12 - 1

But you might notice that B1 is 1.49999999999992%, not 1.50000000000000%. Such "errors" are to be expected with computer arithmetic.

And the "error" will be much greater if you use the (rounded) value displayed in A1 instead of referencing A1 directly in the formula in B1.

 

[bran8989]

Hi,

Try this: D15=0.1259%

=1-POWER(D15-1,12)

This worked thanks

 

[joeu2004]

D15=0.1259%
=1-POWER(D15-1,12)

This worked thanks

Only by coincidence. And so does:

D1: =1.5%/PI()
E1: =D1*PI()

They "work" insofar as E1 is 1.5% again.

But hopefully, you recognize that D1 is not the compounded monthly rate corresponding to the annual rate of 1.5%.

Likewise, your original formula =1-(1-1.5000%)^(1/12) is not the correct compounded monthly rate, in the first place.

That was the point that shg and I tried to impress upon you.

-----

Aside....

BTW, 1-POWER(D15-1,12) = 1-(D15-1)^12 is not the correct algebraic inverse operation of your original formula.
It should be 1-POWER(1-D15,12) = 1-(1-D15)^12.

To demonstrate, change 12 to 11 in all formulas. That is:

D15: =1-(1-1.5%)^1/11
E15: =1-POWER(D15-1,11)
F15: =1-POWER(1-D15,11)

F15 is about 1.50%. E15 is about 198.50%.

 

[joeu2004]

typo, too late to edit....

D15: =1-(1-1.5%)^1/11

Of course, that should be =1-(1-1.5%)^(1/11)