COUPPCD Excel

What Is COUPPCD Excel Function?

The COUPPCD Excel function is a financial formula that calculates the calendar date of the most recent coupon payment. This function provides the result in the form of a date, making it a valuable resource for financial analysis and planning. It stands for “Coupon payment on previous coupon date” and is particularly useful for investors looking to determine the clean price of a bond. The COUPPCD Excel function takes into account the issue date, settlement date, first interest date, frequency of interest payments, day count basis, and redemption value to accurately calculate the price without including any accrued interest up to the settlement date.

In the example, using the values given in the table, we enter the formula shown below using the COUPPCD Excel function.

The entered formula is =COUPPCD(B1,B2,1)

The result obtained is 20 March 2023.

Syntax

1. Settlement – (Mandatory) This is the settlement date when a security is transferred from the seller to the buyer after a trade has been made.
2. Maturity – (Mandatory) This is the expiration date of the security.
3. Frequency – (Mandatory) This is the frequency of coupon payments per year.

1	Annual Payment
2	Semi-annual Payment
4	Quarterly

4. Basis – (Optional) This document specifies the day count basis that will be utilized.

Basis	Day Count Basis
0	US(NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

How To Use COUPPCD Function in Excel?

To effectively utilize the COUPPCD function in Excel, follow these steps.

#1 – Access from the Excel ribbon

#2 – Enter the worksheet manually

Step 1: Enter the formula in a vacant cell for the output, =COUPPCD() or type =C and double-click on COUPPCD from Excel’s suggestions.

Step 2: To get the result, hit Enter Key.

Examples

Let us look at some interesting examples to understand the functioning of COOPPCD.

Example #1

The COUPPCD Excel formula helps calculate coupon payments on a previous coupon date. In this example, Mr. Bonce purchased $100 in bonds from a textile company with annual payment frequency, settlement date, maturity date, and basis value as actual/actual, all shown in a table.

Step 1: Insert the COUPPCD formula in cell B5 as shown below.

=COUPPCD(B1,B2,1,1)

Step 2: The coupon date calculated using the COUPPCD function is 31 Jan 2020.

The COUPPCD Excel function determines the next coupon date after a specified settlement date which ensures that investors receive their periodic interest payments on time and allows them to accurately assess the cash flows associated with holding the bond. 

Example #2

This example demonstrates how to determine the previous coupon date for bonds purchased by Mr. Charles from a shoe company, with a settlement date of February 28, 2000. The company pays coupons semi-annually at a rate of 15% with a basis value as actual/365. The number of coupons is determined using the COUPPCD Excel function.

Step 1: Enter the COUPPCD Excel formula in cell B5 using the values from the data table.

=COUPPCD(B1,B2,2,3)

Step 2: The outcome is showcased in cell B5.

When a bond is purchased at a 15% rate with settlement and maturity date using the COUPPCD function, several key factors come into play. The COUPPCD function calculates the amount of interest due on a security that pays periodic interest based on the assumption of a 360-day year. 

Example #3

Mrs. Luna purchased bonds for $500 on April 8, 2021, with a 10% interest rate. The bonds will mature on December 31, 2022. The calculation will be done quarterly using a European 30/360 basis value method.

Let’s start the calculation by entering the values as prescribed below;

Step 1: Insert the formula to calculate the number of coupons payable in cell B5. The complete formula is = COUPPCD(B1,B2,4,4)

Step 2: The previous coupon date is attained in cell B5 when you press Enter.

In the realm of finance and investments, the accuracy and efficiency of calculating coupon dates hold paramount importance in ensuring timely interest payments to shareholders. The COUPPCD Excel function emerges to streamline this process, particularly in instances where shares are purchased at a rate of interest. 

Important Things To Note

1. The #NUM! error occurs when the settlement date is after the maturity date, when the frequency argument is not 1, 2, or 4, or when the basis argument is not 0, 1, 2, 3, or 4.
2. Settlement and maturity dates should be entered using date parsing functions like DATE or TO_DATE instead of typing out the date in text format.
3. The COUPPCD function makes all arguments whole numbers.
4. The #VALUE! error occurs when the settlement date or maturity date is not a valid date or when the dates are given in text format instead of date format in excel. The error occurs if any of the arguments provided are non-numeric.

Frequently Asked Questions (FAQs)

Download Template

This article must help us understand the COUPPCD Excel Function’s formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to COUPPCD Excel Function. Here we explain how to use COUPPCD function with examples & downloadable excel template. You can learn more from the following articles. –

• COUPNUM Excel
• COUPDAYS Excel
• COUPDAYSNC Excel