ODDFPRICE function

The ODDFPRICE function in Jspreadsheet Formulas Pro is used to compute the price per $100 face value of a financial security that has an irregular first period. This is useful in financial analysis when dealing with bonds or securities where the first payment period doesn't align with the subsequent regular payment periods. By using ODDFPRICE, users can accurately determine the cost of such securities, optimizing their financial models and predictions.

Documentation

Calculates the price per $100 face value of a security that has an irregular (short or long) first coupon period.

Syntax

ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

Parameter	Description
`settlement`	The settlement date of the security.
`maturity`	The maturity date of the security.
`issue`	The issue date of the security.
`first_coupon`	The date of the first interest payment, which is an odd period.
`rate`	The annual coupon rate of the security.
`yld`	The annual yield of the security.
`redemption`	The redemption value of the security per $100 face value.
`frequency`	The number of coupon payments per year.
`[basis]`	Optional. Argument that specifies the day count basis to use. If omitted, Jspreadsheet uses the US (NASD) 30/360 basis.

Behavior

The ODDFPRICE function is a financial function that is used to calculate the price per $100 face value of a security that has an odd (short or long) first period. This function takes multiple arguments including settlement, maturity, issue, first_coupon, rate, yield, redemption, frequency, and [basis].

If any of the required fields are left empty, the function will return an error.
Boolean values in Jspreadsheet are implicitly converted to numbers (TRUE = 1, FALSE = 0). The function processes them accordingly instead of returning an error.
It does not handle text values. If inserted, the function will return an error.
The settlement, maturity, issue, and first coupon arguments must be valid dates recognized by Jspreadsheet If the dates are not in the correct format, it will return a #VALUE! error.
The function treats any value less than 0 (negative numbers) in the rate, yield, redemption, frequency, and basis as invalid and will return a #NUM! error.
If the settlement date is greater than or equal to the maturity date, the function will return a #NUM! error.
It also expects the 'frequency' to be any of the following integers 1, 2, or 4 representing annual, semi-annual, and quarterly respectively. Any other number will result in a #NUM! error.

Common Errors

Error	Description
#VALUE!
#NUM!	Occurs if the rate, yield, redemption, frequency, and basis are less than 0, or if the settlement date is greater than or equal to the maturity date, or if frequency is any number other than 1, 2, or 4.
#REF!	Occurs if the cell reference within the formula is invalid.

Best practices

Always ensure that the dates are in the correct date format that your spreadsheet software can understand.

When entering the rate, yield, redemption, frequency, and basis, make sure they are greater than or equal to 0. For frequency, the values should be either 1, 2, or 4.

Avoid using text values as this function does not handle them and it will result in an error.

Always double-check the formula for any spelling errors or incorrect cell references to avoid #NAME? and #REF! errors respectively.

Usage

A few examples using the ODDFPRICE function.

ODDFPRICE('2022-01-01', '2022-06-30', '2021-12-31', '2022-03-31', 0.08, 0.09, 100, 2) returns 99.62  
ODDFPRICE('2022-02-15', '2023-08-31', '2022-01-01', '2022-05-31', 0.075, 0.07, 100, 2, 1) returns 101.12  
ODDFPRICE('2022-01-01', '2023-12-31', '2021-12-01', '2022-04-01', 0.06, 0.065, 100, 4) returns 98.45

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('NTQ2YTY1YmU2MDM1MDJkMjkzNWY5MTg1ZjlmZmNkNjc3ZjI4MDYyNDQwODA1NTM3MzE2ZjQ1M2E1MGJmMzdiNmJiNGEyMGFhMTU2ZmQwZTQzYWZhYWE2MzhlNmU4MGQyZTNiZTdmZDFkMmNkNmRlY2FiYTZkMTU1MTA0OTFhZDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNVFk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Settlement",
        "2022-01-15",
        "=ODDFPRICE(B1,B2,B3,B4,B5,B6,B7,B8)"
    ],
    [
        "Maturity",
        "2025-12-31"
    ],
    [
        "Issue",
        "2021-11-01"
    ],
    [
        "First Coupon",
        "2022-06-30"
    ],
    [
        "Rate",
        0.075
    ],
    [
        "Yield",
        0.082
    ],
    [
        "Redemption",
        100
    ],
    [
        "Frequency",
        2
    ]
]
  }]
});
</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('NTQ2YTY1YmU2MDM1MDJkMjkzNWY5MTg1ZjlmZmNkNjc3ZjI4MDYyNDQwODA1NTM3MzE2ZjQ1M2E1MGJmMzdiNmJiNGEyMGFhMTU2ZmQwZTQzYWZhYWE2MzhlNmU4MGQyZTNiZTdmZDFkMmNkNmRlY2FiYTZkMTU1MTA0OTFhZDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNVFk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Settlement",
        "2022-01-15",
        "=ODDFPRICE(B1,B2,B3,B4,B5,B6,B7,B8)"
    ],
    [
        "Maturity",
        "2025-12-31"
    ],
    [
        "Issue",
        "2021-11-01"
    ],
    [
        "First Coupon",
        "2022-06-30"
    ],
    [
        "Rate",
        0.075
    ],
    [
        "Yield",
        0.082
    ],
    [
        "Redemption",
        100
    ],
    [
        "Frequency",
        2
    ]
];

    // 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('NTQ2YTY1YmU2MDM1MDJkMjkzNWY5MTg1ZjlmZmNkNjc3ZjI4MDYyNDQwODA1NTM3MzE2ZjQ1M2E1MGJmMzdiNmJiNGEyMGFhMTU2ZmQwZTQzYWZhYWE2MzhlNmU4MGQyZTNiZTdmZDFkMmNkNmRlY2FiYTZkMTU1MTA0OTFhZDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNVFk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Settlement",
        "2022-01-15",
        "=ODDFPRICE(B1,B2,B3,B4,B5,B6,B7,B8)"
    ],
    [
        "Maturity",
        "2025-12-31"
    ],
    [
        "Issue",
        "2021-11-01"
    ],
    [
        "First Coupon",
        "2022-06-30"
    ],
    [
        "Rate",
        0.075
    ],
    [
        "Yield",
        0.082
    ],
    [
        "Redemption",
        100
    ],
    [
        "Frequency",
        2
    ]
]

        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('NTQ2YTY1YmU2MDM1MDJkMjkzNWY5MTg1ZjlmZmNkNjc3ZjI4MDYyNDQwODA1NTM3MzE2ZjQ1M2E1MGJmMzdiNmJiNGEyMGFhMTU2ZmQwZTQzYWZhYWE2MzhlNmU4MGQyZTNiZTdmZDFkMmNkNmRlY2FiYTZkMTU1MTA0OTFhZDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNVFk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "2022-01-15",
        "=ODDFPRICE(B1,B2,B3,B4,B5,B6,B7,B8)"
    ],
    [
        "Maturity",
        "2025-12-31"
    ],
    [
        "Issue",
        "2021-11-01"
    ],
    [
        "First Coupon",
        "2022-06-30"
    ],
    [
        "Rate",
        0.075
    ],
    [
        "Yield",
        0.082
    ],
    [
        "Redemption",
        100
    ],
    [
        "Frequency",
        2
    ]
]
            }]
        });
    }
}

Edit this page on GitHub