YIELDMAT function

PRO

The YIELDMAT function in Jspreadsheet Formulas Pro is used to calculate the yield of a security that provides interest only at the maturity date. It does this by considering the security's current price and face value. This yield value can help you understand the return you would get if you hold the security until it matures. It's a useful tool for evaluating the potential profitability of different financial investments.

Documentation

Returns the yield of a security that pays interest at maturity, based on its price and face value.

Syntax

YIELDMAT(settlement, maturity, issue, rate, price, [basis])

Parameter	Description
`settlement`	The security's settlement date.
`maturity`	The security's maturity date.
`issue`	The security's issue date.
`rate`	The security's annual coupon rate.
`price`	The security's price per $100 face value.
`[basis]`	Optional. The day count basis to use in the calculation. Can be 0, 1, 2, 3, or 4. If omitted, it defaults to 0 (US (NASD) 30/360).

Behavior

The YIELDMAT function is used to calculate the annual yield of a security or bond that pays interest at maturity. It requires the following information: settlement date, maturity date, issue date, rate, and price.

If any of the required information is missing or in an incorrect format, the function will return an error.
If the settlement date is later than the maturity date, the function will return an error.
Text values are not valid inputs for this function, and using them will result in an error.
Booleans: Boolean values are treated as numbers. TRUE is regarded as 1 and FALSE as 0, which may cause unexpected results if used as input values.
If any of the date fields (settlement, maturity, or issue date) contain a date that is not a valid date, the function returns an error.
Empty cells are treated as zeros, which will likely cause the function to return an error, as zero is not a valid rate or price for a bond.

Common Errors

Error	Description
#NUM!	Occurs if the settlement date is greater than the maturity date. Also, if the rate ≤ 0, or if the price ≤ 0.
#VALUE!	Occurs if the supplied arguments are non-numeric text or if the settlement, maturity, or issue dates are not valid. Boolean values are interpreted as numbers and may produce unexpected results rather than direct errors.
#DIV/0!	Occurs if the function calculates a division by zero error.

Best practices

Always ensure that the dates are correct and in the right format. Incorrect dates can lead to errors or inaccurate results.

Make sure that the rate and price are positive values. Zero or negative values will cause the function to return an error.

Be aware that the function defaults to the US (NASD) 30/360 basis if no basis is specified, but you can select other day-count conventions (0–4) depending on the financial instrument.

Avoid leaving cells empty that are used in the YIELDMAT function to prevent unexpected errors or results.

Usage

A few examples using the YIELDMAT function.

// Short-term security with interest at maturity, using Actual/Actual basis
YIELDMAT("2021-09-01", "2022-03-01", "2021-09-01", 0.08, 98, 1)  
// Returns the annual yield for a 6-month security issued on Sept 1, 2021, maturing Mar 1, 2022, 
// with an 8% coupon, purchased at 98, using Actual/Actual day count.

// Medium-term bond with European 30/360 basis
YIELDMAT("2021-09-01", "2025-01-01", "2021-09-01", 0.06, 95, 4)  
// Returns the annual yield for a bond issued on Sept 1, 2021, maturing Jan 1, 2025, 
// with 6% coupon, purchased at 95, using European 30/360 basis.

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Rate",
        "Price",
        "Basis",
        "Yield"
    ],
    [
        "2024-01-15",
        "2024-07-15",
        "2024-01-15",
        0.05,
        97.5,
        1,
        "=YIELDMAT(A2,B2,C2,D2,E2,F2)"
    ],
    [
        "2024-03-01",
        "2025-03-01",
        "2024-03-01",
        0.06,
        95.0,
        1,
        "=YIELDMAT(A3,B3,C3,D3,E3,F3)"
    ],
    [
        "2024-06-01",
        "2024-12-01",
        "2024-06-01",
        0.04,
        99.2,
        1,
        "=YIELDMAT(A4,B4,C4,D4,E4,F4)"
    ]
]
  }]
});
</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('ZmNmMjExMDVlNzg3YjcyMjBiMTEzMzNjMDE2ZTQwN2FmNjhlY2YxNDRhNjEzNDI1NzJiNDVmMmZkNGJjZDU5NDBiZjg1OTc0M2ViNGM3N2IxYmYyZjI4NThiNjhkMDBkN2FlMmE0YjUwOGZkMjEyN2U2MjA2ZGIyMjMwNDVkOWQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Rate",
        "Price",
        "Basis",
        "Yield"
    ],
    [
        "2024-01-15",
        "2024-07-15",
        "2024-01-15",
        0.05,
        97.5,
        1,
        "=YIELDMAT(A2,B2,C2,D2,E2,F2)"
    ],
    [
        "2024-03-01",
        "2025-03-01",
        "2024-03-01",
        0.06,
        95.0,
        1,
        "=YIELDMAT(A3,B3,C3,D3,E3,F3)"
    ],
    [
        "2024-06-01",
        "2024-12-01",
        "2024-06-01",
        0.04,
        99.2,
        1,
        "=YIELDMAT(A4,B4,C4,D4,E4,F4)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Settlement",
        "Maturity",
        "Issue",
        "Rate",
        "Price",
        "Basis",
        "Yield"
    ],
    [
        "2024-01-15",
        "2024-07-15",
        "2024-01-15",
        0.05,
        97.5,
        1,
        "=YIELDMAT(A2,B2,C2,D2,E2,F2)"
    ],
    [
        "2024-03-01",
        "2025-03-01",
        "2024-03-01",
        0.06,
        95.0,
        1,
        "=YIELDMAT(A3,B3,C3,D3,E3,F3)"
    ],
    [
        "2024-06-01",
        "2024-12-01",
        "2024-06-01",
        0.04,
        99.2,
        1,
        "=YIELDMAT(A4,B4,C4,D4,E4,F4)"
    ]
]

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

// 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",
        "Rate",
        "Price",
        "Basis",
        "Yield"
    ],
    [
        "2024-01-15",
        "2024-07-15",
        "2024-01-15",
        0.05,
        97.5,
        1,
        "=YIELDMAT(A2,B2,C2,D2,E2,F2)"
    ],
    [
        "2024-03-01",
        "2025-03-01",
        "2024-03-01",
        0.06,
        95.0,
        1,
        "=YIELDMAT(A3,B3,C3,D3,E3,F3)"
    ],
    [
        "2024-06-01",
        "2024-12-01",
        "2024-06-01",
        0.04,
        99.2,
        1,
        "=YIELDMAT(A4,B4,C4,D4,E4,F4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub