PRICEDISC function

PRO

The PRICEDISC function in Jspreadsheet Formulas Pro is a handy tool that helps you figure out the price of a discounted security for every $100 of its face value. This function becomes useful when you're dealing with financial analysis involving bonds or other similar securities. It simplifies the process, eliminating the need for complex calculations. So, whether you're a financial analyst or a student learning about securities, the PRICEDISC function makes your work much easier.

Documentation

Returns the price per $100 face value of a discounted security.

Syntax

PRICEDISC(settlement, maturity, discount, redemption, [basis])

Parameter	Description
`settlement`	The settlement date of the security.
`maturity`	The maturity date of the security.
`discount`	The discount rate of the security.
`redemption`	The redemption value per $100 face value of the security.
`[basis]`	Optional. The day count basis to use for calculating bond interest. If omitted, defaults to 0 (US (NASD) 30/360).

Behavior

The PRICEDISC function calculates the price per $100 face value of a discounted security. It requires three key pieces of information: settlement date (the date after the issue date when the security is traded to the buyer), maturity date (the date when the security expires), and the discount rate.

If any of the cells referenced in the function are empty, the function will return a #VALUE! error.
If non-date text or booleans are used as the settlement or maturity dates, PRICEDISC will return a #VALUE! error.
If the discount rate is not a numerical value, the function will return a #VALUE! error.
The function will return a #NUM! error if the settlement date is greater than or equal to the maturity date, or if the discount rate is less than or equal to zero.

Common Errors

Error	Description
`#VALUE!`	Occurs if one or more of the input cells are empty, or if the settlement date, maturity date, or discount rate are not in the correct format.
`#NUM!`	Occurs if the settlement date is greater than or equal to the maturity date, or if the discount rate is less than or equal to zero.

Best Practices

Always ensure that the settlement date is less than the maturity date, and that the discount rate is a positive value.

Use cell references for dates rather than typing them directly into the function to avoid formatting errors.

Remember that the PRICEDISC function does not adjust for weekends or holidays, so adjust your dates accordingly if these are relevant to your calculations.

Double check your discount rate to ensure it is entered correctly, because it can significantly impact the result of the function.

Usage

A few examples using the PRICEDISC function.

// Basic usage with default basis (0 = US 30/360)
PRICEDISC("2023-01-05","2023-01-31",0.038,100,0)
// Result: 99.7256

// Using Actual/Actual basis (basis = 1)
PRICEDISC("2023-01-05","2023-06-30",0.045,100,1)
// Result: 97.8301

// Longer-term security with different redemption value
PRICEDISC("2023-01-05","2024-01-05",0.05,105,0)
// Result: 99.75

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NmQyNmQxZjk4OTQyZDNmZGZkODExNjFlMDQ3ZTQ0N2Q3YjUyYmMyYjM1ZjBmZDc4MTA5YzJiYTNjNGI4OWY0YmE2YTgyMzhlZGQwYTI3NDA5MjQxMTJmYjRlMmUxMjQxNmUyYTYwN2I1MGVlODdjN2JhYjQyYWQ0NDcxYmU5ZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Settlement",
        "Maturity",
        "Discount",
        "Redemption",
        "Basis",
        "Price per $100"
    ],
    [
        "2023-01-05",
        "2023-03-15",
        0.035,
        100,
        0,
        "=PRICEDISC(A2,B2,C2,D2,E2)"
    ],
    [
        "2023-02-10",
        "2023-06-20",
        0.042,
        100,
        1,
        "=PRICEDISC(A3,B3,C3,D3,E3)"
    ],
    [
        "2023-03-01",
        "2023-09-30",
        0.038,
        100,
        0,
        "=PRICEDISC(A4,B4,C4,D4,E4)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('NmQyNmQxZjk4OTQyZDNmZGZkODExNjFlMDQ3ZTQ0N2Q3YjUyYmMyYjM1ZjBmZDc4MTA5YzJiYTNjNGI4OWY0YmE2YTgyMzhlZGQwYTI3NDA5MjQxMTJmYjRlMmUxMjQxNmUyYTYwN2I1MGVlODdjN2JhYjQyYWQ0NDcxYmU5ZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Settlement",
        "Maturity",
        "Discount",
        "Redemption",
        "Basis",
        "Price per $100"
    ],
    [
        "2023-01-05",
        "2023-03-15",
        0.035,
        100,
        0,
        "=PRICEDISC(A2,B2,C2,D2,E2)"
    ],
    [
        "2023-02-10",
        "2023-06-20",
        0.042,
        100,
        1,
        "=PRICEDISC(A3,B3,C3,D3,E3)"
    ],
    [
        "2023-03-01",
        "2023-09-30",
        0.038,
        100,
        0,
        "=PRICEDISC(A4,B4,C4,D4,E4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('NmQyNmQxZjk4OTQyZDNmZGZkODExNjFlMDQ3ZTQ0N2Q3YjUyYmMyYjM1ZjBmZDc4MTA5YzJiYTNjNGI4OWY0YmE2YTgyMzhlZGQwYTI3NDA5MjQxMTJmYjRlMmUxMjQxNmUyYTYwN2I1MGVlODdjN2JhYjQyYWQ0NDcxYmU5ZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Settlement",
        "Maturity",
        "Discount",
        "Redemption",
        "Basis",
        "Price per $100"
    ],
    [
        "2023-01-05",
        "2023-03-15",
        0.035,
        100,
        0,
        "=PRICEDISC(A2,B2,C2,D2,E2)"
    ],
    [
        "2023-02-10",
        "2023-06-20",
        0.042,
        100,
        1,
        "=PRICEDISC(A3,B3,C3,D3,E3)"
    ],
    [
        "2023-03-01",
        "2023-09-30",
        0.038,
        100,
        0,
        "=PRICEDISC(A4,B4,C4,D4,E4)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NmQyNmQxZjk4OTQyZDNmZGZkODExNjFlMDQ3ZTQ0N2Q3YjUyYmMyYjM1ZjBmZDc4MTA5YzJiYTNjNGI4OWY0YmE2YTgyMzhlZGQwYTI3NDA5MjQxMTJmYjRlMmUxMjQxNmUyYTYwN2I1MGVlODdjN2JhYjQyYWQ0NDcxYmU5ZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Settlement",
        "Maturity",
        "Discount",
        "Redemption",
        "Basis",
        "Price per $100"
    ],
    [
        "2023-01-05",
        "2023-03-15",
        0.035,
        100,
        0,
        "=PRICEDISC(A2,B2,C2,D2,E2)"
    ],
    [
        "2023-02-10",
        "2023-06-20",
        0.042,
        100,
        1,
        "=PRICEDISC(A3,B3,C3,D3,E3)"
    ],
    [
        "2023-03-01",
        "2023-09-30",
        0.038,
        100,
        0,
        "=PRICEDISC(A4,B4,C4,D4,E4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub