Excel function to compute Nominal Int Rate where effective Rate is negative

[howard]

I would like to know if there is a function in excel to compute the nominal interest rate if the effective rate is negative

If the effective rate is positive the formula =Nominal works perfectly

I can however compute this using a mathematical/accounting formula

Book1
	A	B
1	Effective Int Rate	6.00%
2	Nominal Rate	5.84%
3
4
5	Effective Int Rate	-6.00%
6	Nominal Rate	#NUM!
7	Using Accounting Formula
8	Nominal Rate Manual	0.51%
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
B2	=NOMINAL(B1,12)
B6	=NOMINAL(B5,12)
B8	=1-((1+B5)^(1/12))

It would be appreciated if someone could provide me with a solution using Excel's built in functions

 

[joeu2004]

We might think that the correct calculation is:

=SIGN(B5)*NOMINAL(ABS(B5),12)

But actually, that returns the wrong nominal rate, namely: -5.84106067841166%.

You have the wrong formula in B8. It should be:

=12*((1+B5)^(1/12) - 1)

Interestingly, when B5 is a negative rate, the nominal rate is more negative, namely: -6.17161539818754%.

To confirm, given that nominal rate in B8, we calculate the effective (compounded) rate as follows:

=(1+B8/12)^12 - 1

The result is -6.00000000000003%. The infinitesimal difference (-0.00000000000003%) is an anomaly of binary floating-point arithmetic, which has a finite precision.

PS.... There is no good reason for Excel NOMINAL to return a #NUM error. It should do the same arithmetic as shown above. I suspect that it returns #NUM arbitrarily, simply refusing to accept a negative effective rate.

 

[joeu2004]

Clarification....

Interestingly, when B5 is a negative rate, the nominal rate is more negative, namely: -6.17161539818754%.

I meant to say: you might find it interesting or odd, because the magnitude of the negative nominal rate is greater than the magnitude of the negative effective rate. For positive rates, we are used to seeing that the magnitude of the nominal rate is less.

But it makes good sense mathematically. The actual nominal rate is always less than the effective rate (except for zero). For negative rates, that means more negative (greater magnitude).

 

[howard]

Thanks for your help and input. it makes perfect sense