ODDLPRICE function

The ODDLPRICE function in Jspreadsheet Formulas Pro is used to compute the price per $100 face value of a financial security that has an unusual last period. This could be a bond or any other sort of security. Essentially, this function helps you determine the value of a security when the final time period is not standard or odd. This can be useful for investors or financial analysts who need to assess the value of securities with irregular time frames.

Documentation

Calculates the price per $100 face value of a security with an odd last period.

Syntax

ODDLPRICE(settlement, maturity, issue, last_interest, 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.
`last_interest`	The date of the last 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 ODDLPRICE function is used to calculate the price per $100 face value of a security that has an odd (short or long) last period. This function takes seven required arguments (settlement date, maturity date, issue date, last interest date, rate, yield, redemption, frequency) and one optional argument [basis].

If any of the required fields are empty, the function will return an error.
Text values are not accepted. Boolean values are automatically converted to numbers (TRUE=1, FALSE=0).
The function expects rate and yield to be expressed as decimals (e.g., 0.08 for 8%). Redemption should be entered as the face value per $100 (e.g., 100).
The ODDLPRICE function handles dates based on the 360 days per year convention (12 x 30 days), which is common in accounting.
If any invalid date is provided, the function returns a #VALUE! error. The function also returns this error if the settlement date is greater than the maturity date.
#NUM! error is returned if the rate, yield, redemption is less than zero, or if the frequency is any number other than 1, 2, or 4.

Common Errors

Error	Description
`#VALUE!`	This error is returned if any of the supplied arguments are non-numeric, or if the dates are not valid.
`#NUM!`	This error is returned if the rate, yield, redemption is less than zero, or if the frequency is any number other than 1, 2, or 4.

Best practices

Always ensure the dates are properly formatted. Spreadsheets can interpret dates in different ways depending on the system settings, so it's good practice to confirm that your dates are being read correctly.

Always express rate and yield as decimal values (e.g., 0.05 for 5%). Redemption should be entered as the redemption value per $100 face value.

Ensure that the settlement date is not later than the maturity date to avoid a #VALUE! error.

Use nested functions like DATE to build dates instead of typing them manually, to avoid potential errors due to different date-time formats.

Usage

A few examples using the ODDLPRICE function.

ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.08,0.09,100,2) returns approximately 98.83  
ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.05,0.06,100,4) returns approximately 97.31  
ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.025,0.03,100,1,4) returns approximately 99.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('MTYyMTA0ZjVlMGU0NzEzODBlMTE3OGI1MWU4NTgyZjcxNWIyZmEzNGQ0ZTQ1YjAzMTg3MDA1Y2MyYTRkOGViZjM5NjA3ZThiNGRkYjg0NWVlZDhlZGUyZDMxOGJlYjUzZjA2N2QyOGE1NTg1YzhjMDRjNzJiNWExZjQ2Nzk5N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Last Interest",
        "Rate",
        "Yield",
        "Redemption",
        "Frequency",
        "Price"
    ],
    [
        "2022-01-01",
        "2022-06-30",
        "2021-12-31",
        "2022-03-31",
        0.08,
        0.09,
        100,
        2,
        "=ODDLPRICE(A2,B2,C2,D2,E2,F2,G2,H2)"
    ],
    [
        "2022-02-15",
        "2022-09-15",
        "2022-01-01",
        "2022-06-01",
        0.05,
        0.06,
        100,
        4,
        "=ODDLPRICE(A3,B3,C3,D3,E3,F3,G3,H3)"
    ],
    [
        "2022-03-01",
        "2022-08-31",
        "2022-01-15",
        "2022-05-15",
        0.025,
        0.03,
        100,
        1,
        "=ODDLPRICE(A4,B4,C4,D4,E4,F4,G4,H4)"
    ]
]
  }]
});
</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('MTYyMTA0ZjVlMGU0NzEzODBlMTE3OGI1MWU4NTgyZjcxNWIyZmEzNGQ0ZTQ1YjAzMTg3MDA1Y2MyYTRkOGViZjM5NjA3ZThiNGRkYjg0NWVlZDhlZGUyZDMxOGJlYjUzZjA2N2QyOGE1NTg1YzhjMDRjNzJiNWExZjQ2Nzk5N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Last Interest",
        "Rate",
        "Yield",
        "Redemption",
        "Frequency",
        "Price"
    ],
    [
        "2022-01-01",
        "2022-06-30",
        "2021-12-31",
        "2022-03-31",
        0.08,
        0.09,
        100,
        2,
        "=ODDLPRICE(A2,B2,C2,D2,E2,F2,G2,H2)"
    ],
    [
        "2022-02-15",
        "2022-09-15",
        "2022-01-01",
        "2022-06-01",
        0.05,
        0.06,
        100,
        4,
        "=ODDLPRICE(A3,B3,C3,D3,E3,F3,G3,H3)"
    ],
    [
        "2022-03-01",
        "2022-08-31",
        "2022-01-15",
        "2022-05-15",
        0.025,
        0.03,
        100,
        1,
        "=ODDLPRICE(A4,B4,C4,D4,E4,F4,G4,H4)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Last Interest",
        "Rate",
        "Yield",
        "Redemption",
        "Frequency",
        "Price"
    ],
    [
        "2022-01-01",
        "2022-06-30",
        "2021-12-31",
        "2022-03-31",
        0.08,
        0.09,
        100,
        2,
        "=ODDLPRICE(A2,B2,C2,D2,E2,F2,G2,H2)"
    ],
    [
        "2022-02-15",
        "2022-09-15",
        "2022-01-01",
        "2022-06-01",
        0.05,
        0.06,
        100,
        4,
        "=ODDLPRICE(A3,B3,C3,D3,E3,F3,G3,H3)"
    ],
    [
        "2022-03-01",
        "2022-08-31",
        "2022-01-15",
        "2022-05-15",
        0.025,
        0.03,
        100,
        1,
        "=ODDLPRICE(A4,B4,C4,D4,E4,F4,G4,H4)"
    ]
]

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

// 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",
        "Issue",
        "Last Interest",
        "Rate",
        "Yield",
        "Redemption",
        "Frequency",
        "Price"
    ],
    [
        "2022-01-01",
        "2022-06-30",
        "2021-12-31",
        "2022-03-31",
        0.08,
        0.09,
        100,
        2,
        "=ODDLPRICE(A2,B2,C2,D2,E2,F2,G2,H2)"
    ],
    [
        "2022-02-15",
        "2022-09-15",
        "2022-01-01",
        "2022-06-01",
        0.05,
        0.06,
        100,
        4,
        "=ODDLPRICE(A3,B3,C3,D3,E3,F3,G3,H3)"
    ],
    [
        "2022-03-01",
        "2022-08-31",
        "2022-01-15",
        "2022-05-15",
        0.025,
        0.03,
        100,
        1,
        "=ODDLPRICE(A4,B4,C4,D4,E4,F4,G4,H4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub