PV function
PRO
BASIC
The PV
function in Jspreadsheet Formulas Pro is used to determine the current value of a future sum of money or series of payments, taking into account a specified interest rate. This is useful in understanding what an investment or loan is worth in today's terms, given the principal of time value of money. It essentially translates future earnings or payments into current monetary value based on the idea that a dollar today is worth more than a dollar tomorrow. This function is particularly useful for financial analysis and planning.
Documentation
Calculates the present value of an investment or loan based on a constant interest rate and future payments or receipts.
Category
Financial
Syntax
PV(rate, nper, pmt, [fv], [type])
Parameter | Description |
---|---|
rate |
The interest rate per period. |
nper |
The total number of payment periods in the investment or loan. |
pmt |
The payment made each period; it cannot change over the life of the investment or loan. |
[fv] |
Optional. The future value of the investment or loan, payable after the final payment is made. If omitted, defaults to 0. |
[type] |
Optional. The timing of the payment. If omitted, defaults to 0 (payments due at the end of the period). Use 1 for payments due at the beginning of the period. |
Behavior
The PV
function is used to calculate the present value of a series of future payments made at regular intervals. Here's how it handles different input scenarios:
- Number Inputs: The
PV
function requires three primary numerical inputs: rate (interest rate per period), nper (number of periods), and pmt (payment made each period). Optional arguments include fv (future value) and type (whether payment is made at the start or end of the period). - Text Inputs: If a text input is provided in place of a numeric value, the function will return a
#VALUE!
error. - Boolean Inputs: Boolean values are not applicable for the
PV
function. If a boolean input is provided, it will be interpreted as0
forFALSE
and1
forTRUE
. - Empty Cells: If a cell referenced in the function is empty, it will be treated as
0
. - Error Inputs: If any of the referenced cells contain an error, the
PV
function will also return an error.
Common Errors
Error | Description |
---|---|
#VALUE! |
The function returns this error when non-numeric inputs are used in place of numeric parameters. |
#NUM! |
This error occurs if the calculation results in a mathematically invalid operation, such as an impossible number of periods or non-converging value. |
#DIV/0! |
This error occurs when the rate is 0 and the payment is 0 or not provided, as it leads to a division by zero scenario. |
Best practices
- Always ensure to input numeric values as rate, nper and pmt to avoid any
#VALUE!
errors.- Be clear about whether your payment is made at the beginning or end of the period. By default, the
PV
function assumes payment at the end of the period. You can change this by setting type to1
.- Use absolute cell referencing if you plan to drag or copy the
PV
function to other cells in your spreadsheet.- Be cautious about the sign of your payment (pmt) and future value (fv). In financial calculations, outflows (payments) are often represented as negative values, and inflows (future value) as positive.
Usage
A few examples using the PV function.
PV(0.05, 10, -1000)
// Returns 7721.73 → Present value of paying 1000 for 10 years at 5% interest.
PV(0.05/12, 60, -200, 5000, 1)
// Returns 6746.27 → Loan calculation with monthly payments, future value, and payments at beginning of period.
PV(0.08, 20, -500, 0, 0)
// Returns 4909.07 → Investment with 20 yearly payments of 500 at 8% discount rate.
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('MmM4NzA4ZWNiODhkOTc2NDYxOGRkNGU4NDFmYjBiN2VjYmIyMjVkMGFlNTE5NzM2NWJhZjg0OGJkOTJjZDM1MzgwZjVjYmEwMGI2NmRiNzAzN2MwZGEwMmY2YTM5MDVmZGM4MGIzYjcyNTcxN2E4NGVmZjhhMWI5ZjYxMjBkYzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Rate",
"Periods",
"Payment",
"Future Value",
"Present Value"
],
[
0.08,
10,
-1000,
0,
"=PV(A2/12,B2,C2,D2,0)"
],
[
0.06,
15,
-500,
5000,
"=PV(A3/12,B3,C3,D3,0)"
],
[
0.05,
20,
-750,
10000,
"=PV(A4/12,B4,C4,D4,0)"
]
]
}]
});
</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('MmM4NzA4ZWNiODhkOTc2NDYxOGRkNGU4NDFmYjBiN2VjYmIyMjVkMGFlNTE5NzM2NWJhZjg0OGJkOTJjZDM1MzgwZjVjYmEwMGI2NmRiNzAzN2MwZGEwMmY2YTM5MDVmZGM4MGIzYjcyNTcxN2E4NGVmZjhhMWI5ZjYxMjBkYzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Rate",
"Periods",
"Payment",
"Future Value",
"Present Value"
],
[
0.08,
10,
-1000,
0,
"=PV(A2/12,B2,C2,D2,0)"
],
[
0.06,
15,
-500,
5000,
"=PV(A3/12,B3,C3,D3,0)"
],
[
0.05,
20,
-750,
10000,
"=PV(A4/12,B4,C4,D4,0)"
]
];
// 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('MmM4NzA4ZWNiODhkOTc2NDYxOGRkNGU4NDFmYjBiN2VjYmIyMjVkMGFlNTE5NzM2NWJhZjg0OGJkOTJjZDM1MzgwZjVjYmEwMGI2NmRiNzAzN2MwZGEwMmY2YTM5MDVmZGM4MGIzYjcyNTcxN2E4NGVmZjhhMWI5ZjYxMjBkYzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Rate",
"Periods",
"Payment",
"Future Value",
"Present Value"
],
[
0.08,
10,
-1000,
0,
"=PV(A2/12,B2,C2,D2,0)"
],
[
0.06,
15,
-500,
5000,
"=PV(A3/12,B3,C3,D3,0)"
],
[
0.05,
20,
-750,
10000,
"=PV(A4/12,B4,C4,D4,0)"
]
]
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('MmM4NzA4ZWNiODhkOTc2NDYxOGRkNGU4NDFmYjBiN2VjYmIyMjVkMGFlNTE5NzM2NWJhZjg0OGJkOTJjZDM1MzgwZjVjYmEwMGI2NmRiNzAzN2MwZGEwMmY2YTM5MDVmZGM4MGIzYjcyNTcxN2E4NGVmZjhhMWI5ZjYxMjBkYzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Rate",
"Periods",
"Payment",
"Future Value",
"Present Value"
],
[
0.08,
10,
-1000,
0,
"=PV(A2/12,B2,C2,D2,0)"
],
[
0.06,
15,
-500,
5000,
"=PV(A3/12,B3,C3,D3,0)"
],
[
0.05,
20,
-750,
10000,
"=PV(A4/12,B4,C4,D4,0)"
]
]
}]
});
}
}