Practice free

INTRATE function

INTRATE returns the simple annual interest rate on a fully invested security, such as a zero-coupon note, when you pay a set amount now and receive a larger amount at maturity.

Example

Read it as a sentence: for a bond bought January 1, 2024 for 980 and redeemed July 1, 2024 for 1000, find the interest rate earned, using basis 0.

Practice this in the app

Syntax

INTRATE(settlement, maturity, investment, redemption, [basis])

Arguments in [square brackets] are optional.

settlement (required)
Date the security is bought (after issue, before maturity).
maturity (required)
Date the security expires and is repaid.
investment (required)
Amount paid for the security.
redemption (required)
Amount received at maturity.
basis (optional)
Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Returns: Annual interest rate on a fully invested security.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420,0) 0.02884 Typical
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420) 0.02884 Basis omitted
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420,0) 0.02884 Basis 0
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420,1) 0.028998462 Basis 1
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420,2) 0.028523077 Basis 2
=INTRATE(DATE(2024,2,15),DATE(2024,8,15),1000000,1014420,3) 0.028919231 Basis 3
=INTRATE(DATE(2024,2,15),DATE(2024,2,15),1000000,1014420,0) #NUM! Settlement = maturity
=INTRATE(DATE(2024,8,15),DATE(2024,2,15),1000000,1014420,0) #NUM! Settlement after maturity

Watch out for

  • The rate is simple, not compounded.

Where it works

Excel
INTRATE is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .