PRICE function
PRO
The PRICE
function in Jspreadsheet Formulas Pro is a tool that calculates the cost of a financial security for every $100 of its face value, considering its annual interest rate or coupon rate. This function is particularly useful when you need to determine the actual cost of investment bonds or similar securities. It essentially helps you understand how much you would need to invest today to gain a particular amount in the future. With this, you can make informed decisions on your investment strategies.
Documentation
Returns the price per $100 face value of a security with an annual coupon rate.
Category
Financial
Syntax
PRICE(settlement, maturity, rate, yld, redemption, [frequency], [basis])
Parameter | Description |
---|---|
settlement |
The settlement date of the security. |
maturity |
The maturity date of the security. |
rate |
The annual coupon rate of the security. |
yld |
The annual yield of the security. |
redemption |
The redemption value per $100 face value of the security. |
[frequency] |
Optional. The number of coupon payments per year. If omitted, defaults to 2 (semi-annual). |
[basis] |
Optional. The day count basis to use for calculating bond interest. If omitted, defaults to 0 (US (NASD) 30/360). |
Behavior
The PRICE
function is used to calculate the price per $100 face value of a security that pays periodic interest. It requires various parameters such as settlement date, maturity date, rate, yield, redemption, and frequency of payments.
- If the provided input cells are empty, the
PRICE
function will return a#VALUE!
error. - Text values are not valid parameters for this function and will result in a
#VALUE!
error. - Boolean values are treated as numbers (TRUE = 1, FALSE = 0)
- If the settlement date is greater than the maturity date, the function will return a
#NUM!
error. - The
PRICE
function handles dates according to the 360-day year (12 x 30-day month), which is common in accounting calculations.
Common Errors
Error | Description |
---|---|
#VALUE! |
This error occurs when the provided input cells are empty, or the parameters provided are non-numeric. |
#NUM! |
This error is returned when the settlement date is greater than the maturity date, or if any numeric parameter is less than or equal to zero. |
Best practices
- Always ensure that the settlement date is less than the maturity date to avoid a
#NUM!
error.- The parameters provided to the
PRICE
function should be in the correct order. Incorrect ordering of parameters could lead to wrong results or errors.- Avoid using text or boolean values as parameters in the
PRICE
function.- Validate that the rate, yield, redemption, and frequency are all greater than zero to avoid a
#NUM!
error.
Usage
A few examples using the PRICE function.
//Basic usage with semi-annual payments
PRICE("2023-01-01","2043-01-01",0.06,0.08,100)
//Quarterly payments (frequency = 4)
PRICE("2023-01-01","2030-01-01",0.05,0.045,100,4)
//Using a different day count basis (basis = 1 for Actual/Actual)
PRICE("2023-01-01","2028-01-01",0.07,0.065,100,2,1)
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('N2QzOGFmOTQ2MTVlYTJjYzY5YWU2MjkxOWQ2MDgyYTBlNjkwNDEzOGRjZDA4ZWQxZWQzMGRlM2M1YTkzZjJlNjllMDUwYjNjOWMwNWQzOTJhM2I2YTZiYTE1ZDBlMGZmODQzNjdmMWZhZjBmOWRkMTk4ODU0M2M3YWMxNGQ1YTAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Settlement",
"Maturity",
"Rate",
"Yield",
"Redemption",
"Price"
],
[
"1/1/2024",
"1/1/2034",
0.05,
0.06,
100,
"=PRICE(A2,B2,C2,D2,E2)"
],
[
"6/15/2024",
"6/15/2029",
0.04,
0.045,
100,
"=PRICE(A3,B3,C3,D3,E3)"
],
[
"3/1/2024",
"3/1/2044",
0.075,
0.08,
100,
"=PRICE(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('N2QzOGFmOTQ2MTVlYTJjYzY5YWU2MjkxOWQ2MDgyYTBlNjkwNDEzOGRjZDA4ZWQxZWQzMGRlM2M1YTkzZjJlNjllMDUwYjNjOWMwNWQzOTJhM2I2YTZiYTE1ZDBlMGZmODQzNjdmMWZhZjBmOWRkMTk4ODU0M2M3YWMxNGQ1YTAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Settlement",
"Maturity",
"Rate",
"Yield",
"Redemption",
"Price"
],
[
"1/1/2024",
"1/1/2034",
0.05,
0.06,
100,
"=PRICE(A2,B2,C2,D2,E2)"
],
[
"6/15/2024",
"6/15/2029",
0.04,
0.045,
100,
"=PRICE(A3,B3,C3,D3,E3)"
],
[
"3/1/2024",
"3/1/2044",
0.075,
0.08,
100,
"=PRICE(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('N2QzOGFmOTQ2MTVlYTJjYzY5YWU2MjkxOWQ2MDgyYTBlNjkwNDEzOGRjZDA4ZWQxZWQzMGRlM2M1YTkzZjJlNjllMDUwYjNjOWMwNWQzOTJhM2I2YTZiYTE1ZDBlMGZmODQzNjdmMWZhZjBmOWRkMTk4ODU0M2M3YWMxNGQ1YTAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Settlement",
"Maturity",
"Rate",
"Yield",
"Redemption",
"Price"
],
[
"1/1/2024",
"1/1/2034",
0.05,
0.06,
100,
"=PRICE(A2,B2,C2,D2,E2)"
],
[
"6/15/2024",
"6/15/2029",
0.04,
0.045,
100,
"=PRICE(A3,B3,C3,D3,E3)"
],
[
"3/1/2024",
"3/1/2044",
0.075,
0.08,
100,
"=PRICE(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('N2QzOGFmOTQ2MTVlYTJjYzY5YWU2MjkxOWQ2MDgyYTBlNjkwNDEzOGRjZDA4ZWQxZWQzMGRlM2M1YTkzZjJlNjllMDUwYjNjOWMwNWQzOTJhM2I2YTZiYTE1ZDBlMGZmODQzNjdmMWZhZjBmOWRkMTk4ODU0M2M3YWMxNGQ1YTAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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",
"Yield",
"Redemption",
"Price"
],
[
"1/1/2024",
"1/1/2034",
0.05,
0.06,
100,
"=PRICE(A2,B2,C2,D2,E2)"
],
[
"6/15/2024",
"6/15/2029",
0.04,
0.045,
100,
"=PRICE(A3,B3,C3,D3,E3)"
],
[
"3/1/2024",
"3/1/2044",
0.075,
0.08,
100,
"=PRICE(A4,B4,C4,D4,E4)"
]
]
}]
});
}
}