Here, I have tried to implement in JavaScript some of the functions available in Excel. All these functions can be used straightaway in your pages without any modification. You only need to pass the required parameters to these functions and use the values returned by them appropriately in your pages. Each one of the functions listed below has two links - one to the Javascript code and the other to an example of how to use the function. In addition, click on the name of the function to get detailed information about the parameters for that function. Please let me know if you encounter any problems or bugs while using these functions. By the way, some of the equations given in Excel Help screens are incorrect.

I will also include all the other functions (which are in Excel) here in due course of time. Basically, in my first shot, I have covered the functions for which I had the equations with me readily.

PV

This function returns the present value of an investment. The present value is the total worth now of a series of future payments. You can use this function to determine whether the investment is a prudent one by assessing its present value. For example, if your investment of $30000 returns a monthly payment of $250 at 8%, using these values you can find out that the PV for this investment is $29888.57 which is less than the amount you are required to pay upfront. This is not obviously a good investment.

In all annuity functions, the amount that goes out i.e. deposit into a bank, or a loan repayment is represented by a negative number and all receipts like dividend, etc. are denoted by a positive number.

Syntax

PV(rate, per, nper, pmt, fv)

Rate is the annual interest rate.

Per is the per period. (Example : If a loan is to be repaid in monthly instalments, this will be 12. For quarterly repayments, this parameter will be 4, 6 for half-yearly and 1 for yearly repayments)

Nper is the total number of payments / receipts.

Pmt is the periodical amount to be paid / received.

FV is the Future Value or a cash balance that you want to attain after the last payment is made. This is an optional parameter. If omitted, 0 is assumed.

Example

If a car loan is to be repaid in monthly instalments over a period of 5 years at an interest rate of 9.25% and a monthly instalment of $370, you can get the PV by calling the function like this : PV(9.25,12,60,-370,0). PV is returned as a two decimal number. In this case the PV that is returned is 17720.39.

Note : The periodic amount is passed as a negative number, because the amount is going out.

FV

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

Syntax

FV(rate, per, nper, pmt, pv)

All the parameters are the same as explained in PV

Example

Suppose you want to save money for a special project starting in 5 years time and put $100 every month into your savings account with the bank that pays 5% annual interest compounded monthly. Your initial balance in the account is $1000. To find out the FV after 5 years, the function will be called thus : FV(5,12,60,-100,1000) which will return 8083.97.

NPER

This function returns the number of periods for an investment or a loan based on periodic constant payments and a constant interest rate.

Syntax

NPER(rate, per, pmt, pv, fv)

Refer to PV for a description of these parameters.

Example

NPER(12, 12, -100, -1000, 10000) equals 60.

PMT

Returns the periodic payment for an annuity based on constant payments and a constant interest rate.

Syntax

PMT(rate, per, nper, pv, fv)

For a description of these parameters, refer to PV

The payment returned by this function includes principal and interest but no taxes, reserve payments and other similar payments normally associated with annuities. To find the total amount paid over the duration of the loan or annuity, multiply the returned PMT value by NPER.

Example

To find out the monthly payment on a $10000 loan at an annual rate of 8% that is required to be paid off in 10 months, call this function thus : PMT(8, 12, 10, 10000) which will return a PMT of $1037.03

EFFECT

Returns the effective annual interest rate, given the nominal interest rate and the number of compounding periods per year.

Syntax

EFFECT(nominal_rate, npery)

Nominal_rate is the nominal interest rate

Npery is the number of compounding periods per year.

Npery should be an integer. (Even if it is not, the function will convert it into an integer).

Example

In order to find out the effective interest for a nominal annual interest rate of 5.25 which is compounded quarterly, call the function thus : EFFECT(5.25, 4). This will return an effective rate of interest 5.35.

SLN

This function returns the straight-line depreciation of an asset for one period.

Syntax

SLN(cost, salvage, life)

Cost is the initial cost of the asset

Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset)

Life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).

Example

The depreciation allowance for each year for an asset whose cost is $30000, whose life is 10 years and salvage value is $7500 will be calculated thus : SLN(30000, 7500, 10). This returns a value of 2250.

SYD

Returns the sum-of-years' depreciation of an asset for a specified period.

Syntax

SYD(cost, salvage, life, per)

All the parameters are the same as in SLN

The only new parameter is Per which is the period for which you want to get the depreciation and is in the same units as life.

Example

A truck bought for $30000 with useful life of 10 years and salvage value of $7500 has deprecation for the first year is SYD(30000, 7500, 10, 1) which returns 4090.91 and for the 10th year SYD(30000,7500,10,10) which returns $409.09