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.

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 as 0 for FALSE and 1 for TRUE.
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 to 1.

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

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

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

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

// 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)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub