Why do I get error message 502 when using INTRATE() function?

I entered the following information

Settlement    2013-8-9
Maturity      2014-6-24
Investment    10000
Redemption    12000
Basis         3

What is “intrate function”? What is your formula?

There is a INTRATE function with the above mentioned input values.

Oh, I see :slight_smile: Thanks for editing the question!

Error 502 means normally a wrong format (number, date…) in the input fields of a formula. Check if your date format is the input format defined in Tools>Options>Languages Settings>Languages “Date acceptance Patterns”.

EDIT: I inserted your values in the formula and discovered that you have to enter dates as strings “2013-8-9” with the quotation marks. If you enter them without you get the Error 502.

So forget what I wrote before.
EDIT End.

@horst, thanks for your comment against the question. It all makes more sense now.

Its seems INTRATE gives an equivalent annual average interest rate on an investment (i.e. zero coupon bond with short durations) that does not earn or pay any interest

It is the uncompounded or undiscounted interest rate where the security does not pay or earn any interest. The security is bought at a discount and at maturity pays the par value or face value of the security

Since the rate is not discounted or compounded thus the regular discounted formula is not used

Instead the INTRATE at first finds what they call the HPR - holding period return as follows

HPR = (maturity value - investment value) / investment value
or
HPR = (maturity value / investment value) - 1

Then it accounts the HPR for duration of the investment using various day count conventions

INTRATE = HPR * (number of days in a year / duration of investments in days)

The example calculation for your data would be

HPR = (12000 - 10000) / 10000 = 0.2
or
HPR = (12000 / 10000) - 1 = 0.2

number of days in a year (basis 3) = 365
duration of investment in days = (6/24/2014)-(08/09/2013) = 319

INTRATE = 0.2 * 365 / 319
INTRATE = 0.2288401254

INTRATE = 22.88%

More on financial topics may be viewed here http://www.dailymotion.com/abraham-joseph-5011

Set your output to -12000 (redemption as +12000 is meaningless) and use this formula:

=XIRR(B1:B2;A1:A2)
with the result 26%