Catégorie : Excel function

Calculates the number of days from the settlement date to the next coupon payment date for a fixed-income security.

Syntax

COUPDAYSNC(Settlement; Maturity; Frequency; Basis)

Arguments

Argument	Required	Description	Valid Values
Settlement	Yes	Date ownership transfers	Valid date (time truncated)
Maturity	Yes	Bond repayment date	Must be after Settlement
Frequency	Yes	Coupon payments per year	1, 2, or 4
Basis	No	Day-count method	0-4 (default=0)

Day-Count Basis Methods

Basis	Method	Key Characteristic
0	30/360 (NASD)	Standard financial convention
1	Actual/Actual	Exact day count
2	Actual/360	Money market convention
3	Actual/365	British convention
4	30/360 (European)	Eurobond convention

Key Features

1. Primary Use:
   • Determines days until next coupon payment
   • Essential for calculating:
     • Accrued interest allocations
     • Present value of future cash flows
2. Error Handling:
   • #VALUE!: Invalid date format
   • #NUM!:
     • Invalid Frequency/Basis
     • Settlement ≥ Maturity
3. Calculation Method:
   • Automatically identifies next coupon date
   • Applies specified day-count convention

EXAMPLE

Common Implementation Errors

1. Using maturity date as settlement
2. Incorrect Basis for security type
3. Forgetting to truncate time values

Calculates the total number of days in the coupon period that contains the settlement date for a fixed-income security.

Syntax

COUPDAYS(Settlement; Maturity; Frequency; Basis)

Arguments

Argument	Required	Description	Valid Values
Settlement	Yes	Date of ownership transfer	Valid Excel date
Maturity	Yes	Bond repayment date	Must be after Settlement
Frequency	Yes	Interest payments per year	1 (annual), 2 (semi-annual), 4 (quarterly)
Basis	No	Day-count convention	0-4 (default=0)

Day-Count Basis Methods (Table 1)

Basis	Method	Description
0	30/360 (NASD)	30-day months, 360-day year
1	Actual/Actual	Exact calendar days
2	Actual/360	Actual days/360-day year
3	Actual/365	Actual days/365-day year
4	30/360 (European)	European 30-day convention

Requirements & Error Handling

• Dates must be valid (time components are ignored)
• Frequency and Basis are converted to integers
• Returns #VALUE! for:
  • Invalid date formats
  • Text instead of dates
• Returns #NUM! for:
  • Invalid Frequency (not 1, 2, or 4)
  • Invalid Basis (not 0-4)
  • Settlement date later than Maturity date

Background

• Determines the length of the current coupon period
• Essential for calculating:
  • Accrued interest
  • Day-count fractions for yield calculations
• Different day-count methods produce slightly different results
• Used in conjunction with other coupon functions (COUPDAYBS, COUPDAYSNC)

Example Applications

Key Notes

• For US corporate bonds: Typically Basis=0 (30/360)
• For government bonds: Typically Basis=1 (Actual/Actual)
• Always verify Settlement date precedes Maturity date
• Combine with COUPNUM() and COUPPCD() for complete coupon analysis
• Critical for accurate dirty price calculations in bond trading

Calculates the number of days between the last coupon payment date and the settlement date for a fixed-income security with regular interest payments.

Syntax

COUPDAYBS(Settlement; Maturity; Frequency; Basis)

Arguments

Argument	Required	Description	Valid Values
Settlement	Yes	Date of ownership transfer	Valid date format
Maturity	Yes	Bond repayment date	Must be after Settlement
Frequency	Yes	Interest payments per year	1 (annual), 2 (semi-annual), 4 (quarterly)
Basis	No	Day-count convention (see Table 1)	0-4 (default=0)

Day-Count Basis Methods (Table 1)

Basis	Method	Description
0	30/360 (NASD)	30-day months, 360-day year
1	Actual/Actual	Exact calendar days
2	Actual/360	Actual days/360-day year
3	Actual/365	Actual days/365-day year
4	30/360 (European)	European 30-day convention

Requirements & Error Handling

• Dates must be valid (no time component)
• Frequency and Basis are truncated to integers
• Returns #VALUE! for invalid dates
• Returns #NUM! for:
  • Invalid Frequency (≠1,2,4)
  • Invalid Basis (≠0-4)
  • Settlement date > Maturity date

Background

• Used to calculate accrued interest owed when bonds trade between coupon dates
• Different day-count methods yield slightly different results
• Essential for accurate bond pricing and yield calculations

Example Applications

1. Accrued Interest Calculation:

Accrued Interest = (Annual Coupon Rate × Face Value) × (COUPDAYBS()/Days in Coupon Period)

1. Yield Analysis:
   • Used with YIELD() and PRICE() functions
   • Helps determine exact holding period returns

Key Notes

• For US corporate bonds: Typically Basis=0 (30/360)
• For government bonds: Typically Basis=1 (Actual/Actual)
• Always verify Settlement < Maturity
• Combine with COUPNCD() and COUPPCD() for complete coupon date analysis

This function calculates the depreciation amount for an asset in a given period using the French accounting system (degressive depreciation with a switch to linear). The result is rounded to an integer.

Syntax

AMORDEGRC(Cost; Date; First_Period; Residual_Value; Period; Rate; Basis)

Arguments

• Cost (required) – Purchase cost of the asset (including incidental expenses, minus discounts).
  • Must be a positive number; otherwise, #VALUE! or #NUMBER! errors occur.
• Date (required) – The purchase date (start of depreciation).
• First_Period (required) – The end date of the first depreciation period (assigned period number 0).
• Residual_Value (required) – Expected remaining value after depreciation.
  • Must be less than Cost and non-negative; otherwise, #NUMBER! is returned.
• Period (required) – The time period for which depreciation is calculated (integer ≥ 0).
• Rate (required) – The depreciation rate (initially linear, then degressive).
• Basis (optional) – Day-count method (see Table 1 below).

Table 1: Day-Count Methods

Basis	Method	Description
0	30/360 (NASD)	Months = 30 days; years = 360 days. Adjusts 31st to 30th.
1	Exact/Exact	Actual days per month/year.
2	Exact/360	Actual days/month; year = 360 days.
3	Exact/365	Actual days/month; year = 365 days.
4	30/360 (European)	Months = 30 days; years = 360 days. Converts 31st to 30th.

Background

• Depreciation reflects the asset’s value loss (not physical wear).
• The Rate is first treated as linear (e.g., 10% = 10-year lifespan).
• Degressive weighting is applied based on the rate:
  • Factor 1.5 if Rate > 25% (3–4 years).
  • Factor 2 if 16.66% ≤ Rate ≤ 25% (5–6 years).
  • Factor 2.5 if Rate < 16.67% (>6 years).
• Residual value handling:
  • If residual = 0, the last two periods split the remaining value.
  • If residual > 0, depreciation stops when book value ≤ residual.

Example

An asset is purchased on June 6, 2010, for $1,000, with:

• Depreciation rate: 10%
• Residual value: $142
• First period ends: December 31, 2010

Formula

=AMORDEGRC(1000; « 6/6/2010 »; « 12/31/2010 »; 142; 0; 10%; 4)

Manual Calculation

1. Days in first period:
   =DAYS360(« 6/6/2010 »; « 12/31/2010 »; TRUE) → 204 days

1. Depreciation:
   =ROUND(1000 × 10% × 2.5 × (204/360); 0) → $142 (matches AMORDEGRC).

Subsequent Periods

• Multiply the previous book value by 10% × 2.5.

The ACCRINTM() function calculates the accrued interest of a debt instrument (such as a bond or loan) that has a single interest payment at maturity. This is a simplified version of ACCRINT(), designed for securities with one annual interest period.

Syntax

ACCRINTM(Issue; Settlement; Nominal_Interest; Par_Value; Basis)

Arguments

• Issue (required) – The issuance date of the security.
• Settlement (required) – The date when ownership of the security changes.
• Nominal_Interest (required) – The annual interest rate of the security.
• Par_Value (optional) – The nominal value of the security. If omitted, Excel defaults to 1000 (contrary to older Excel Help documentation).
• Basis (optional) – Day-count convention.

Notes

• Dates must be entered without time values (decimals are truncated).
• Basis must be an integer (decimals are truncated).
• Errors:
  • #VALUE! – Invalid dates or non-numeric entries.
  • #NUMBER! – Invalid numeric inputs.

Background

• This function is specifically for single-payment securities (e.g., zero-coupon bonds or loans due in full at maturity).
• Unlike ACCRINT(), which handles periodic interest payments, ACCRINTM() assumes one annual interest period.
• The calculation method aligns with ACCRINT(), but with a simplified structure.

Examples

1. Debt Due in Full

A $1,000 debt issued on June 1, 2010, with:

• 4% annual interest
• Ownership change on August 9, 2010

Formula:
=ACCRINTM(C2, C3, 4%, 1000, 4)

• C2 = Issue date (June 1, 2010)
• C3 = Settlement date (August 9, 2010)

Result:

Calculates the accrued interest from issuance to settlement.

2. German Federal Government Bond (WKN 113517)

• Issued: October 25, 2000
• Nominal Interest: 5.5% (annual)
• Maturity: January 4, 2031
• Purchase Date: August 30, 2010
• Market Price: €143.27

Formula:
=ACCRINTM(C24, C25, 5.5%, 100, 4)

• C24 = Last interest payment date (January 4, 2010)
• C25 = Settlement date (August 30, 2010)

Result:

• Accrued Interest: €3.61 per bond
• Total Payment: €143.27 (price) + €3.61 (interest) = €146.88

The calculates accrued interest for:

• Debt instruments due in full intra-annually
• Fixed-interest securities with periodic payments

Syntax

ACCRINT(Issue; First_Interest_Date; Settlement; Nominal_Interest; Par_Value; Frequency; Basis; Calculation_Method)

Arguments

Argument	Required?	Description	Notes
Issue	Yes	Date of issuance	Must be valid date
First_Interest_Date	Yes	First coupon date	For intra-annual debt = due date
Settlement	Yes	Ownership transfer date	Must be ≥ Issue date
Nominal_Interest	Yes	Annual coupon rate	Decimal format (4% = 0.04)
Par_Value	No	Face value	Default = 1000
Frequency	Yes	Payments per year	1, 2, or 4
Basis	No	Day-count method	0-4 (Default=0)
Calculation_Method	No	Interest calculation	TRUE (full duration) or FALSE (since last payment)

Day-Count Methods (Basis)

Basis	Method	Description
0	30/360 (NASD)	Standard 30-day months
1	Actual/Actual	Exact days
2	Actual/360	Bankers’ year
3	Actual/365	Exact days/365
4	30/360 (European)	Eurobond standard

Key Features

1. Accrued Interest Calculation:
   • For multiple annual payments: (Nominal_Interest/Frequency) × (Days held/Days in period)
   • Uses specified day-count method (Basis)
2. Error Handling:
   • #VALUE! – Invalid dates
   • #NUMBER! – Negative values or invalid numbers
3. Special Cases:
   • First period (Period 0) is prorated
   • When Calculation_Method=FALSE, only calculates since last payment

Practical Example

Scenario: $1,000 debt issued June 1, 2010, due December 1, 2010 (4% annual rate), sold August 9, 2010.

Solution:

1. Accrued Interest (68 days held):

=ACCRINT(« 6/1/2010″, »12/1/2010″, »8/9/2010 »,4%,1000,1,4)

Returns: $7.56

Manual verification:

1000 × 4% × (68/360) = $7.56

1. Present Value Calculation (112 days remaining):
   • Future value: $1,020 (principal + 6mo interest)
   • Discounted at 4.5%:

$1,020 / (1 + 0.045 × (112/360)) = $1,005.92

Pro Tips

1. For German bonds, use Basis=4 (30/360 European)
2. Combine with PRICEMAT() for full valuation:

=(PRICEMAT(…)×10) + ACCRINT(…)

1. Verify calculations using:
   • DAYS360() for day counts
   • COUPDAYBS() for days since last coupon

Common Use Cases

• Bond transactions between coupon dates
• Loan transfers with accrued interest
• Financial reporting for interest receivables