Javascript Equivalent of Common Financial Formulas Found In Google Sheets

Continuing my work on RTU assets from yesterday, I found I needed to program some of the common formulas you see in Google Sheets into Javascript to be used in my Suitescript code.

These common functions include:

• PV(rate, number_periods, payment_amount, future_value, end_or_beginning)
• FV(rate, number_periods, payment_amount, present_value, end_or_beginning)
• PMT(rate, number_periods, present_value, future_value, end_or_beginning)

Each of the above functions comes in handy when trying to calculate the present value of an annuity, investment or mortgage.

I’ve found I need to have these functions to be able to automate Netsuite to process the required journals to perform the Right-of-Use accounting transactions for leased assets automatically.

Here’s the snippet for each function:

PV() Present Value Formula As Javascript Function

There are two distinct Present Value formulae depending on when payment is made.

I have labelled the present value formulas as either being in advance (payment is made upfront for the period) or being in arrears (payment is made at the end of the period).

The above functions were generated using this Katex code:

$$ PV_{advance} = P \times \Big[1 - (1 + i)^{-n}\Big] \times \left[\frac{1 + i}{i}\right] \ \ \ PV_{arrears} = P \times \frac{\Big[1 - (1 + i)^{-n}\Big]}{i} $$

The parameters in both formulas correspond to the following values:

• P is the payment per period.
• i is the interest per period. If you have an annual interest rate, yet payment is made monthly, you will need to convert the interest rate to a monthly value. A simple calculation with this type would be to divide the annual interest rate by 12.
• n is the total number of periods.

Here’s the resulting formula in Javascript that you could copy and paste into your Suitescript project:

Testing this formula would look as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// per annum interest rate
const pa = 7.07;
// convert to monthly rate
const rate = pa / 100 / 12;
// 20 years
const years = 20;
// convert periods to months
const numberPeriods = years * 12;
// payment per period
const paymentPerPeriod = 4583.33;
// future value to be 0
const futureValue = 0;
// payment made at the end
const paymentType = 0;
// entering these all into the pv() function
const presentValue = pv(rate, numberPeriods, paymentPerPeriod, futureValue, paymentType);
console.log(`Present Value: ${presentValue.toFixed(2)}`);
// Present Value: 587978.60

This output matches the same result from Google Sheets: 587,978.60.

If you were to change the paymentType parameter to 1 where payment is made in advance of the period, the result would be from the Javascript formula:

1
2
3
4
5
6
7

...
// payment made at the end
const paymentType = 1;
// entering these all into the pv() function
const presentValue = pv(rate, numberPeriods, paymentPerPeriod, futureValue, paymentType);
console.log(`Present Value: ${presentValue.toFixed(2)}`);
// Present Value: 591442.77

This output also matches the same result from Google Sheets, being: 591,442.77

FV() Future Value Formula As Javascript Function

Similar to the Present Value formulae above, the Future Value has two distinct formulas based on how payment is made: is it upfront or at the end of the period?

Here are the two formulas:

Here is the Katex code used to generate the image above:

$$ FV_{advance} = P \times \frac{\Big[(1 + i)^{n} - 1\Big] \times (1 + i)}{i} \ \ \ FV_{arrears} = P \times \frac{\Big[(1 + i)^{n} - 1\Big]}{i} $$

The parameters in both formulas correspond to the same parameters used in the Present Value formulas above, which are:

• P is the payment per period.
• i is the interest per period. If you have an annual interest rate, yet payment is made monthly, you will need to convert the interest rate to a monthly value. A simple calculation with this type would be to divide the annual interest rate by 12.
• n is the total number of periods.

Notice that this function does not need the Present Value to calculate its result, yet the Google Sheets and Excel formulas do have the option to enter the Present Value within this formula’s parameters.

You can enter either the Payment per Period OR the Present Value to obtain the Future Value of an investment or mortgage. If you enter BOTH then they compound the payment value in the formulas above.

Here’s the resulting Javascript function to show you how it looks and how it would work in your Suitescript code:

There are several different tests you could do for this function due to the optional parameters of pymt and pval. I’ll start with entering both the pymt and it’s pval values:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

const pa = 7.07
const rate = pa / 100 / 12;
const years = 20;
const numberPeriods = years * 12;
const paymentPerPeriod = 4583.33;
const futureValue = 0;
const paymentType = 0;
const presentValue = pv(rate, numberPeriods, paymentPerPeriod, futureValue, paymentType);
console.log(`Present Value: ${presentValue.toFixed(2)}`);
const futureValueFn = fv(rate, numberPeriods, -paymentPerPeriod, presentValue, paymentType);
console.log(`Future Value: ${futureValueFn.toFixed(2)}`);
// outputs
// Present Value: 587978.60
// Future Value: -0.00

Entering the same values into Google Sheets’ Future Value formula outputs 0.00.

If you were to modify the futureValueFn variable as follows you would also get the same output from Google Sheets, here were some of my tests to confirm:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

...
var futureValueFn = fv(rate, numberPeriods, -paymentPerPeriod, 0, paymentType);
console.log(`Future Value: ${futureValueFn.toFixed(2)}`);

// outputs
// Future Value: 2407975.00
// Google Sheets FV() = 2,407,975.00

...
futureValueFn = fv(rate, numberPeriods, 0, 100, paymentType);
console.log(`Future Value: ${futureValueFn.toFixed(2)}`);

// outputs
// Future Value: -409.53
// Google Sheets FV() = -409.53

...
futureValueFn = fv(rate, numberPeriods, paymentPerPeriod, presentValue, paymentType);
console.log(`Future Value: ${futureValueFn.toFixed(2)}`);

// outputs
// Future Value: -4815950.00
// Google Sheets FV() = -4,815,950.00

As you can see, the same results from the Future Value function written in Javascript achieves the same output from Google Sheets FV() formula.

I tested the same results using the payments in advance (payments made at the beginning, and it also achieved the same results).

PMT() Payment Formulas As Javascript Function

Another popular financial formula found in Excel and Google Sheets is the payment formula PMT(). This formula calculates the value paid per period according to the present value (and future value) of an investment, the interest rate per period, and the number of periods.

Here are the formulas in their raw format:

Here’s the Latex code:

$$ PMT_{advance} = \frac{-i \times \Big[PV \times (1 + i)^{n} + FV\Big]}{(1 + i)^{n} - 1} \times \frac{1}{1 + i} \ \ \ PMT_{arrears} = \frac{-i \times \Big[PV \times (1 + i)^{n} + FV\Big]}{(1 + i)^{n} - 1} $$

The parameters used in these formulas are somewhat the same, with only a few new additions:

• i is the interest rate per period
• n is the total number of periods
• PV is the present value of the asset
• FV is the future value of the asset at the end of the total periods

The FV parameter is optional as if this value is not set, the number 0 is used, which will not impact the result of the formula.

Here’s the corresponding Javascript function, which you can incorporate into your Suitescript project if needed:

Here are a few tests I performed using this function and comparing it against Google Sheets PMT() results too:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

const pa = 7.07
const rate = pa / 100 / 12;
const years = 20;
const numberPeriods = years * 12;
const paymentPerPeriod = 4583.33;
const futureValue = 0;
const paymentType = 0;
const presentValue = pv(rate, numberPeriods, paymentPerPeriod, futureValue, paymentType);
console.log(`Present Value: ${presentValue.toFixed(2)}`);
const futureValueFn = fv(rate, numberPeriods, paymentPerPeriod, presentValue, paymentType);
console.log(`Future Value: ${futureValueFn.toFixed(2)}`);
let paymentAmount = pmt(rate, numberPeriods, presentValue, futureValue, paymentType);
console.log(`Payment Amount: ${paymentAmount.toFixed(2)}`);

// outputs:
// Present Value: 587978.60
// Future Value: 0.00
// Payment Amount: -4583.33

This result matches the Google Sheets output.

Some other tests also confirm:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

...
paymentAmount = pmt(rate, numberPeriods, presentValue, 1000, paymentType);
console.log(`Payment Amount: ${paymentAmount.toFixed(2)}`);

// outputs:
// Payment Amount: -4773.67
// Google Sheets PMT() = -4,773.67

paymentAmount = pmt(rate, numberPeriods, 100000, 1000, 1);
console.log(`Payment Amount: ${paymentAmount.toFixed(2)}`);

// outputs:
// Payment Amount: -775.13
// Google Sheets PMT() = -775.13

As you can see, the results from the pmt Javascript function and Google Sheets produce the same results.

Summary

If you’re performing financial calculations in Javascript you’ll eventually come across the need to use the common present value, future value and payment functions.

With these three core functions you can extend your needs for additional financial functions by incorporating the other popular Google Sheets and Excel formulas like: PPMT, IPMT, CUMIPMT, CUMPRINC.