YIELD function
PRO
The YIELD
function in Jspreadsheet Formulas Pro is a tool that calculates the return on an investment that pays periodic interest, based on the price you paid for it and the interest rate it generates. Simply put, it helps you understand how profitable your investment is. By inputting the necessary data, such as settlement date, maturity date, rate, pr, and redemption, it will provide you with the yield of the security. This is particularly useful for understanding the potential earnings from bonds or other interest-earning securities.
Documentation
Returns the yield on a security that pays periodic interest, based on its price and the interest rate.
Category
Financial
Syntax
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
Parameter | Description |
---|---|
settlement |
The security's settlement date. |
maturity |
The security's maturity date. |
rate |
The security's annual coupon rate. |
pr |
The security's price per $100 face value. |
redemption |
The security's redemption (face) value per $100 face value. |
frequency |
The number of coupon payments per year. Must be 1, 2, or 4. |
[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 YIELD
function is used to calculate the yield on a security that pays periodic interest. The function generally requires the following inputs: settlement date, maturity date, rate, pr, redemption, frequency, and basis.
- Empty cells: If any of the required fields are left empty, the
YIELD
function will return an error. - Text: Text in any of the required fields will result in an error.
- Booleans: Boolean values are treated as numbers. TRUE is regarded as 1 and FALSE as 0, which may lead to unexpected results in financial calculations.
- Errors: If any of the input cells contain an error, the
YIELD
function will also return an error. - Dates: The settlement date and maturity date should be entered as a date format. If the dates are not valid or if the settlement date is after the maturity date, the function will return an error.
Common Errors
Error | Description |
---|---|
#VALUE! | This error is displayed when the input parameters are not numeric or the dates are not valid. |
#NUM! | This error occurs when the settlement date is greater than the maturity date, if the rate is less than 0, if pr or redemption is less than or equal to 0, or if frequency is not 1, 2, or 4. |
#DIV/0! | This error will occur if the redemption value is 0. |
Best practices
- Always ensure that the dates are entered in the correct format and that the settlement date is before the maturity date.
- Check to ensure that all input parameters are numeric and that none of them are less than 0.
- Be aware that yield is calculated using the assumption that all payments are received on schedule and are reinvested at the same rate.
- Always double-check your inputs and calculations, as errors in bond yield calculations can significantly impact financial decisions and analyses.
Usage
A few examples using the YIELD function.
// Semiannual coupon bond
YIELD("2022-01-01", "2027-01-01", 0.05, 95, 100, 2)
// Returns the annual yield for a bond with 5% coupon rate, priced at 95, redeemed at 100, paying semiannual coupons.
// Annual coupon bond with actual/actual basis
YIELD("2022-01-01", "2032-01-01", 0.08, 97.5, 100, 1, 1)
// Returns the annual yield for a 10-year bond with 8% coupon, priced at 97.5, annual payments, using actual/actual day count.
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('ODY3OTBiZTBmM2FhODZkOTM1ZjIyYmJlYTYyZTZkNTYwNWE2OTA4NGM0YzFhNDMxY2IyYmE5MjBmODJmNDllYjM1MTkxZDgwMDFjNmMwODdkODgyOTY5MGIxMzcwYzQ2NTJjZmNjMDM3NjMxYTliZjdiMTA4NjcxZDU5YzQ1NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Settlement",
"Maturity",
"Rate",
"Price",
"Redemption",
"Frequency",
"Yield"
],
[
"2024-01-15",
"2029-01-15",
0.06,
98.5,
100,
2,
"=YIELD(A2,B2,C2,D2,E2,F2)"
],
[
"2024-03-01",
"2034-03-01",
0.075,
95.25,
100,
1,
"=YIELD(A3,B3,C3,D3,E3,F3)"
],
[
"2024-06-15",
"2027-06-15",
0.045,
102.75,
100,
2,
"=YIELD(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('ODY3OTBiZTBmM2FhODZkOTM1ZjIyYmJlYTYyZTZkNTYwNWE2OTA4NGM0YzFhNDMxY2IyYmE5MjBmODJmNDllYjM1MTkxZDgwMDFjNmMwODdkODgyOTY5MGIxMzcwYzQ2NTJjZmNjMDM3NjMxYTliZjdiMTA4NjcxZDU5YzQ1NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Settlement",
"Maturity",
"Rate",
"Price",
"Redemption",
"Frequency",
"Yield"
],
[
"2024-01-15",
"2029-01-15",
0.06,
98.5,
100,
2,
"=YIELD(A2,B2,C2,D2,E2,F2)"
],
[
"2024-03-01",
"2034-03-01",
0.075,
95.25,
100,
1,
"=YIELD(A3,B3,C3,D3,E3,F3)"
],
[
"2024-06-15",
"2027-06-15",
0.045,
102.75,
100,
2,
"=YIELD(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('ODY3OTBiZTBmM2FhODZkOTM1ZjIyYmJlYTYyZTZkNTYwNWE2OTA4NGM0YzFhNDMxY2IyYmE5MjBmODJmNDllYjM1MTkxZDgwMDFjNmMwODdkODgyOTY5MGIxMzcwYzQ2NTJjZmNjMDM3NjMxYTliZjdiMTA4NjcxZDU5YzQ1NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Settlement",
"Maturity",
"Rate",
"Price",
"Redemption",
"Frequency",
"Yield"
],
[
"2024-01-15",
"2029-01-15",
0.06,
98.5,
100,
2,
"=YIELD(A2,B2,C2,D2,E2,F2)"
],
[
"2024-03-01",
"2034-03-01",
0.075,
95.25,
100,
1,
"=YIELD(A3,B3,C3,D3,E3,F3)"
],
[
"2024-06-15",
"2027-06-15",
0.045,
102.75,
100,
2,
"=YIELD(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('ODY3OTBiZTBmM2FhODZkOTM1ZjIyYmJlYTYyZTZkNTYwNWE2OTA4NGM0YzFhNDMxY2IyYmE5MjBmODJmNDllYjM1MTkxZDgwMDFjNmMwODdkODgyOTY5MGIxMzcwYzQ2NTJjZmNjMDM3NjMxYTliZjdiMTA4NjcxZDU5YzQ1NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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",
"Rate",
"Price",
"Redemption",
"Frequency",
"Yield"
],
[
"2024-01-15",
"2029-01-15",
0.06,
98.5,
100,
2,
"=YIELD(A2,B2,C2,D2,E2,F2)"
],
[
"2024-03-01",
"2034-03-01",
0.075,
95.25,
100,
1,
"=YIELD(A3,B3,C3,D3,E3,F3)"
],
[
"2024-06-15",
"2027-06-15",
0.045,
102.75,
100,
2,
"=YIELD(A4,B4,C4,D4,E4,F4)"
]
]
}]
});
}
}