Difference between revisions of "Documentation/How Tos/Calc: INTRATE function"

Revision as of 14:02, 25 February 2009

INTRATE

Returns the equivalent annual interest rate for an investment bought at one price and sold at another.

Syntax:

INTRATE(settlementdate; maturitydate; purchasevalue; maturityvalue; basis)

settlementdate: the date the item was bought.

maturitydate: the date the item was sold.

purchasevalue: the amount paid for the item.

maturityvalue: the amount received for the item.

basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.

0 - US method (NASD), 12 months of 30 days each

1 - Exact number of days in months, exact number of days in year

2 - Exact number of days in month, year has 360 days

3 - Exact number of days in month, year has 365 days

4 - European method, 12 months of 30 days each

The equivalent interest rate returned is the (un-compounded) interest rate that would have to be paid on an investment of purchasevalue to turn it into maturityvalue at maturity. This function may be helpful with short term zero coupon bonds.

The formula used is:

( (maturityvalue - purchasevalue)/purchasevalue ) * (days_in_year/days_difference)

where days_difference is the number of days between settlementdate and maturitydate, and days_in_year is the number of days in a year, both calculated according to the calendar system basis.

As the formula takes no account of compounding, this function is most reliable for periods of less than a year. See Derivation of Financial Formulas for a formula review.

Example:

INTRATE("2009-02-02"; "2009-12-03"; 1000; 1080; 0)

returns approximately 0.096, or 9.6%.

Template:Documentation/SeeAlso

Issues:

• Calc and Excel do not agree on the number of days in a year in basis 1. It is not clear which is theoretically correct. Calc uses the number of days in the year containing purchasedate. See Issue 93527.