RECEIVED function

The RECEIVED function in Excel is used to calculate the amount of interest received for a fully invested bond between its settlement date and maturity date. This function is often applied in bond valuation to find out how much interest a bondholder will receive during the period between the settlement date and maturity.

Syntax

RECEIVED(settlement, maturity, investment, rate, basis)

Parameters

1. settlement: The bond’s settlement date, which is the date after the issuance when the bond is traded to the buyer. The settlement date cannot be later than the maturity date.
2. maturity: The bond’s maturity date, which is the date when the bond’s principal is due to be repaid.
3. investment: The amount of money invested in the bond.
4. rate: The bond’s annual coupon rate, expressed as a decimal.
5. basis (optional): The day count basis to use in the calculation of the interest. It can take the following values:
   • 0 (or omitted): US (NASD) 30/360 method (most common)
   • 1: Actual/Actual
   • 2: Actual/360
   • 3: Actual/365
   • 4: European 30/360

How It Works

The RECEIVED function calculates the interest payment on a bond based on the investment, the coupon rate, and the period between the settlement and maturity dates. It accounts for the bond’s price, settlement date, and day count basis to calculate the correct interest earned by the bondholder during the holding period.

Example

Let’s say you have invested in a bond with the following details:

• The settlement date is January 1, 2025,
• The maturity date is January 1, 2030,
• You invested $1,000 in the bond,
• The bond has an annual coupon rate of 5%,
• You want to use the 30/360 day count basis.

To calculate the interest received, you would use the following formula:

=RECEIVED("2025-01-01", "2030-01-01", 1000, 0.05, 0)

Important Notes

• The investment parameter refers to the amount you have invested in the bond, and it should be a positive number.
• The rate should be expressed as a decimal. For example, for a 5% bond rate, use 0.05.
• The basis parameter is optional, and if omitted, it defaults to the 30/360 day count method.
• This function only applies to bonds and does not work for other types of investments.

Summary

The RECEIVED function in Excel is used to calculate the amount of interest a bondholder has received between the settlement and maturity dates. It factors in the investment amount, coupon rate, and day count basis to determine the interest accrued for that specific period. It’s most commonly used in the valuation of bonds and other fixed-income securities.