How to Calculate an Interest Rate for a Security with INTRATE in Excel

Find the annual interest rate earned on a negotiable CD from the amount invested and the amount paid back at maturity.

You will learn to

• Calculate the rate earned on a fully invested security
• Change the day-count basis
• Convert the simple rate to an effective annual yield

How it works

INTRATE(settlement, maturity, investment, redemption, [basis]) returns the simple annual interest rate on a security you fully invest in and redeem for a larger amount, such as a negotiable CD: (redemption − investment) ÷ investment, scaled to a year.

Unlike DISC, which divides the gain by the redemption value, INTRATE divides by what you invested, so it is the rate actually earned on your money. Set basis to 3 (actual/365) to compare it with bank savings rates, and compound it if you want an effective annual yield.

=INTRATE(B2,B3,B4,B5,B6)