In this article:

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

These functions perform financial calculation on numeric values, and return the result. You can use these to make calculations in imports, reports, or models.

Returns the given value if the given period falls within the given from-to periods. Optionally, return the newvalue if the given period is beyond the newper period.

This function is useful for (example) applying salaries to a range of periods, or accruing revenue or expense amounts over a range of periods.

ACCRUE(value, period, from, to, newper, newvalue)

Example:

ACCRUE(1000, 4, 3, 10) returns 1000

Calculates the sum of the given amount and compounded interest over that amount at the given annual interest rate over the given number of years. Interest is compounded at the end of every year.

This function is useful for simple long-term calculations using a constant interest rate. For calculations with varying rates, you can use FVSCHEDULE.

COMPOUND(data, rate, years)

Example:

COMPOUND(1000, 0.05, 4) returns 1215.50625

Calculates the total depreciation amount for the given from-to period, based on the
asset cost value, residual value, the total number of periods, and the depreciation
type. If you omit the *to* period, the depreciation will be calculated for the
*from* period.

DEPRECIATE(cost, rest, periods, from, to, type)

Type:

0 = straight line (default)

1 = declining balances

2
= double declining balances

Examples:

DEPRECIATE(3700, 100, 36, 1)
returns 100

DEPRECIATE(3700, 100, 36, 1, 12) returns 1200

DEPRECIATE(3700,
100, 36, 9, 12) returns 300

DEPRECIATE(3700, 100, 36, 1, 12, 1) returns 1134.60

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

EFFECT(rate, periods)

Example:

data = 0.0585

EFFECT(data, 12) returns 6

Calculates the forecasted value for the given index by analyzing the linear regression trend in
the given cell range or data array. The *data* parameter should contain a range of values. The
*index* parameter indicates which index (e.g. period) you want to forecast. If *index* is higher
than the number of periods in the data, the result will be the forecasted future value for that
given period. If *index* is smaller than the number of periods in the data, the result will be
the intersecting past value in that given period.

The *time* parameter is optional and can contain a numeric time series (e.g. 1, 2, 3 etc) in a
cell range or data array. If not given, the time pattern is assumed to be sequential, starting
at 1 with increments of 1.

FORECAST(data, index, time)

Example:

data = [10, 20, 30, 40, 50, 60]

FORECAST(data, 3)
returns 30 (intersecting past value)

data = [10, 20, 30, 40, 50, 60]

FORECAST(data,
9) returns 90 (forecasted future value)

data = [10, 20, 30, 40, 50, 60]

time =
[2010, 2011, 2012, 2013, 2014, 2015]

FORECAST(data, 2020, time) returns 110 (forecasted
future value)

Calculates the future value of an investment, based on a constant interest rate. You
can use FV with either periodic or constant payments, or one single payment. The
*payment* is the periodic payment (negative) or income (positive), and *value* is
the optional present value.

FV(rate, periods, payment, value, type)

Type:

0 = end of period

1 = start of period

Example:

data
= 0.06

FV(data / 12, 10, -200, -500, 1) returns 2581.40

Calculates the sum of the given amount and compounded interest using a schedule of
annual interest rates, one for each year. Interest is compounded at the end of every
year. The *rates* parameter should be a cell range or data array with interest
rates.

For simpler long-term calculations using a constant interest rate, you can use COMPOUND.

FVSCHEDULE(data, rates)

Example:

A1:A5 = 0.04, 0.04, 0.05, 0.05, 0.06

FVSCHEDULE(1000,
A1:A5) returns 1264.01

Calculates the nominal annual interest rate, given the effective rate and the number of compounding periods per year. NOMINAL is the reverse of EFFECT.

NOMINAL(rate, periods)

Example:

data = 0.06

NOMINAL(data, 12) returns 0.0585

Calculates the net present value of an investment, based on a constant interest rate and a series of future periodic payments (negative values) and/or periodic income (positive values). NPV is similar to the PV function (present value). The primary difference between PV and NPV is that NPV allows for varying periodic payments.

NPV(rate, payments)

Example:

data = 0.06

NPV(0.06 / 12, -200, -250, -300, 100,
90) returns -556.26

Calculates the projected value for the given index by multiplying a pattern against a range of
values in the given cell range or data array. The *data* parameter should contain a range of
values. The *index* parameter (starting at 1) indicates which index (e.g. period) you want to
project. The *pattern* parameter must contain a numeric data range that can be multiplied
against the range of values.

If you provide no *index* (e.g. 0), the function will calculate all indices against the same
pattern and return the sum of the projected value of all indices.

A practical example is a refund pattern on monthly revenues. Say, if refunds are estimated at 10% of revenue in the 1st month (of revenue), 5% in the 2nd month, and 2% in the 3rd month, then this function can be used to calculate the total refund amount in any given month based on preceeding monthly revenues. In this example, the 3-month pattern will be applied to the revenue amount of every month, calculating the refund amount in a later month.

PROJECT(data, index, pattern)

Example:

data = [1000, 2000, 1500, 3000, 5000]

pattern = [.10, .05,
.02]

PROJECT(data, 5, pattern) returns 680 (the total refund amount in period 5 based on
multiplying the refund pattern to the revenues of preceeding months)

Calculates the present value of an investment, based on a constant interest rate. You
can use PV with either periodic or constant payments, or a future value. The
*payment* is the periodic payment (negative) or income (positive), and *value* is
the optional future value.

PV(rate, periods, payment, value, type)

Type:

0 = end of period (default)

1 = start of
period

Example:

data = 0.06

PV(data / 12, 10, -200, -500, 1)
returns 2431.49

Calculates the equivalent interest rate for the growth of an investment from present to future over the given number of periods.

RRI(periods, present, future)

Example:

RRI(12, 1000, 1100) returns 0.00797