All of those who are using financial functions in Google Sheets is not necessarily be a financial analyst. But it’s a must to understand some of the financial terms to correctly use such functions. Using the DISC function in Google Sheets is no different.

So I will try to include bits and pieces of such terms here and there to make you comfortable in using the DISC function in Google Sheets.

The purpose of the DISC function in Google Sheets is to calculate the discount rate (interest rate) of securities (a negotiable financial instrument such as bonds).

The discount rate will be higher for long term bonds considering many factors such as risks involved.

DISC Function in Google Sheets – Syntax and Arguments

Syntax

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

Arguments

As you can see there are four required arguments and a fifth optional argument in Google Sheets DISC function.

In function syntaxes, the optional arguments are normally put inside open/close square brackets. Here are those arguments and their descriptions.

• settlement – the settlement date of the security. It’s the date after issuance when the security is delivered (traded) to the buyer.
• maturity – the maturity or expiry date of the security.
• price – the price at which the security is bought.
• redemption – the redemption value of the security.
• day_count_convention – 0 to 4 and 0 is by default (the type of day count basis to use).

Day Count Convention:

Google Sheets Formula to Calculate the Discount Rate of a Bond

In Google Sheets, insert the following values for the discount rate calculation of security (bond).

Want to specify the arguments as values within the formula? Then use the DISC formula as below.

Recommended:

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

Not Recommended:

=disc("20/01/2019","15/06/2020",964.87,1000,1)

Using the dates in text format as above may cause errors as the function may or may not recognize it depending on the regional/locale settings of your sheet. So I am not recommending this usage.

How to Format the DISC Function Output to Percentage in Google Sheets?

The above formula in cell C7 would return the output as 0.02506605226 (number formatted). You can opt either of the below methods to convert this number (discount rate) to percentage.

1. Select the cell (here C7) containing the formula. Then go to the menu Format > Number and click on Percent or;
2. By wrapping the DISC formula with To_Percent function as below.

=to_percent(disc(C2,C3,C4,C5,C6))

Note

Assume you have all the above input values excluding the redemption amount but including the discount rate.

Using the RECEIVED function you can calculate the redemption amount as below in Google Sheets.

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

This formula will return the redemption (RECEIVED) amount which is 1000.00.

Errors in the DISC Function Output in Google Sheets

The DISC function in Google Sheets may return two possible error types which are 3 and 6. The corresponding error values are #VALUE! and #NUM!

=ERROR.TYPE(C7)

Reasons:

• The formula returns #VALUE! error when invalid dates (dates in the wrong format) are used.
• Cause of #NUM! errors:
  • Check whether the settlement date is less than the maturity date. If not correct it.
  • Valid ‘basis’ values (the optional day_count_convention argument) are between 0 and 4 inclusive. Adhere to it.
  • If price/redemption is blank or <=0, do correct it.

If you still find any error, hover your mouse over the error and read the tooltip carefully. That may help you sort out the error.

That’s all. Enjoy!