Not everyone who uses financial functions in Google Sheets is a financial analyst, but it’s essential to understand some key financial terms to use these functions correctly. The DISC function in Google Sheets is no different.

In this guide, I will introduce you to important financial concepts and terminology that will help you become comfortable using the DISC function.

Purpose of the DISC Function

The DISC function in Google Sheets calculates the discount rate (interest rate) of securities, which are negotiable financial instruments such as bonds. The discount rate can vary based on several factors, including the term of the bond and associated risks. Typically, long-term bonds will have higher discount rates due to the increased risk over a longer time horizon.

DISC Function in Google Sheets – Syntax and Arguments

Syntax:

DISC(settlement, maturity, price, redemption, [day_count_convention])

Arguments:

The DISC function requires four arguments and has one optional argument. In function syntax, optional arguments are enclosed in square brackets. Here’s a breakdown of the arguments:

• settlement: The settlement date of the security, which is the date after issuance when the security is delivered (traded) to the buyer.
• maturity: The maturity or expiration date of the security.
• price: The price at which the security is purchased.
• redemption: The redemption value of the security.
• day_count_convention (optional): A number from 0 to 4 indicating the type of day count basis to use, with 0 as the default value.

Day Count Convention

Formula to Calculate the Discount Rate of a Bond

To calculate the discount rate of a security (bond) in Google Sheets, enter the following values:

If you want to specify the arguments as values within the formula, use the DISC function as follows:

Recommended:

=DISC(date(2019,1,20), date(2020,6,15), 964.87, 1000, 1)

Not Recommended:

=DISC("2019-1-20", "2020-6-15", 964.87, 1000, 1)

Using dates in text format, as shown above, may cause errors because the function may or may not recognize them, depending on the regional or locale settings of your sheet. Therefore, I do not recommend this usage.

Formatting the DISC Function Output as a Percentage in Google Sheets

The formula in cell C7 will return an output like 0.02506605226 (formatted as a number). To convert this number (discount rate) to a percentage, you can use either of the following methods:

Select the cell (e.g., C7) containing the formula, then go to the menu Format > Number and click on Percent.

Wrap the DISC formula with the TO_PERCENT function, as shown below:

=TO_PERCENT(DISC(C2, C3, C4, C5, C6))

Calculating the Redemption Amount with the RECEIVED Function

Assuming you have all the input values except the redemption amount, you can calculate the redemption amount using the RECEIVED function in Google Sheets, as follows:

=RECEIVED(date(2019,1,20), date(2020,6,15), 964.87, 2.51%, 1)

This formula will return the redemption amount, which is 1000.00.

Common Errors with the DISC Function in Google Sheets

The DISC function may return two possible error types: #VALUE! and #NUM!. You can identify these errors using:

=ERROR.TYPE(C7)

Reasons for Errors:

• #VALUE! Error: This error occurs when invalid dates (dates in the wrong format) are used.
• #NUM! Error: This error arises due to the following issues:
  • The settlement date must be earlier than the maturity date. If it is not, correct it.
  • Valid “basis” values (for the optional day_count_convention argument) must be between 0 and 4, inclusive.
  • If the price or redemption values are blank or less than or equal to 0, correct them.
  • If you encounter any errors, hover your mouse over the error message to read the tooltip; it may provide helpful information to resolve the issue.

Conclusion

That’s all about the DISC function in Google Sheets! Try it out, and enjoy the simplicity of calculating discount rates for your financial analyses.