I've been given a task to convert a simple spreadsheet to a HTML form to be used on a website, that is going to do some calculations using JavaScript. This would be no problem at all except I know nothing about financial functions, and I've ran into FV (Future Value).

This is the spreadsheet:

B3 = 100
B4 = 10
B5 = B4*12
B6 = 12
B7 = 3
B8 = (1+B6)^(1/12)-1
B9 = ABS(FV(B8,B5,B3/(1+B7),,1)) // and here lies the problem

This is what OO's help has to say about FV:

Returns the future value of an investment based on periodic, constant payments and a constant interest rate (Future Value).

Syntax: FV(Rate; Num_periods; Payment; PV (optional); Type)

The question is: How does one calculate FV using the parameters above?

Sample values:

B3 = 100          1        1        2
B4 = 10           1        2        2
B9 = 21,751.06    12.39    26.28    52.55

I've been given a task to convert a simple spreadsheet to a HTML form to be used on a website, that is going to do some calculations using JavaScript. This would be no problem at all except I know nothing about financial functions, and I've ran into FV (Future Value).

This is the spreadsheet:

B3 = 100
B4 = 10
B5 = B4*12
B6 = 12
B7 = 3
B8 = (1+B6)^(1/12)-1
B9 = ABS(FV(B8,B5,B3/(1+B7),,1)) // and here lies the problem

This is what OO's help has to say about FV:

Returns the future value of an investment based on periodic, constant payments and a constant interest rate (Future Value).

Syntax: FV(Rate; Num_periods; Payment; PV (optional); Type)

The question is: How does one calculate FV using the parameters above?

Sample values:

B3 = 100          1        1        2
B4 = 10           1        2        2
B9 = 21,751.06    12.39    26.28    52.55

• javascript
• finance

4 Answers 4

This is the exact FV formula from Excel in Javascript. It works with 0% interest as well

function FV(rate, nper, pmt, pv, type) {
  var pow = Math.pow(1 + rate, nper),
     fv;
  if (rate) {
   fv = (pmt*(1+rate*type)*(1-pow)/rate)-pv*pow;
  } else {
   fv = -1 * (pv + pmt * nper);
  }
  return fv.toFixed(2);
}

Future value is just the principal amount plus all the accrued interest over the period outstanding.

In your example, the principal is 100 (B3), the time is 10 years (120 months -- B5), and the interest rate is B8. Are you sure that B6 does not equal .12, or 12%? That would be an annual interest rate of 12%, or about 1% per month (the actual formula for the monthly interest rate that would yield a 12% annual rate is 1.12 ^ (1/12) - 1).

The future value is simply principal times (1 + interest rate) ^ time, or in this case, roughly 100*(1.01)^120.

Here's a calculator that you can refer to.

Go to the Javascript source, you can find that the formula to calculate the future value is this:

function checkNumber(input, min, max, msg)

{

    msg = msg + " field has invalid data: " + input.value;



    var str = input.value;

    for (var i = 0; i < str.length; i++) {

        var ch = str.substring(i, i + 1)

        if ((ch < "0" || "9" < ch) && ch != '.') {

            alert(msg);

            return false;

        }

    }

    var num = 0 + str

    if (num < min || max < num) {

        alert(msg + " not in range [" + min + ".." + max + "]");

        return false;

    }

    input.value = str;

    return true;

}



function puteField(input)

{

    if (input.value != null && input.value.length != 0)

        input.value = "" + eval(input.value);

    puteForm(input.form);

}



function puteForm(form)

{

    if ((form.payments.value == null || form.payments.value.length == 0) ||

        (form.interest.value == null || form.interest.value.length == 0) ||

        (form.principal.value == null || form.principal.value.length == 0)) {

        return;

    }



    if (!checkNumber(form.payments, 1, 480, "# of payments") ||

        !checkNumber(form.interest, .001, 99, "Interest") ||

        !checkNumber(form.principal, 1, 10000000, "Principal")) {

        form.fv.value = "Invalid";

        return;

    }



    var i = form.interest.value;

    if (i > 1.0) {

        i = i / 100.0;

        form.interest.value = i;

    }

    i /= 12;


   var pow = form.principal.value;

    for (var j = 0; j < form.payments.value *12; j++)

        pow = (pow * i) + (pow *1);

    form.fv.value = pow

        form.totalint.value = (form.fv.value - form.principal.value)

}

You will have hard time moving Excel to web.
Instead you better consider using Google Docs.

But if you need only this one particular spreedsheet then this page explains the formula.
Additionally you can find the information on the Compound Interest Wiki page.