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* and
*to* periods. Optionally, return the *newvalue* if the given period is beyond the
*newper* period.

This function is useful for calculations such as (examples): 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) = 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) = 1215.50625

Calculates the total depreciation amount for the given *from* and *to* period,
based on the asset *cost* value, *rest* (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)
= 100

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

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

DEPRECIATE(3700, 100, 36, 1, 12, 1) = 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) = 6

Calculates the forecasted value for the given *index* by analyzing the linear regression
trend in the given cell range, data array, or comma-delimited list of values. 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, data array, or comma-delimited list of values. If not given, the time pattern
is assumed to be sequential, starting at 1 with increments of 1.

FORECAST(data, index, time)

Examples:

data = "10, 20, 30, 40, 50, 60"

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

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

FORECAST(data,
2020, "2010, 2011, 2012, 2013, 2014, 2015") = 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 (default)

1 = start of period

Example:

data = 0.06

FV(data / 12, 10, -200, -500, 1) = 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) = 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) = 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, payments, ..)

Example:

data = 0.06

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

Calculates the predicted amount for the rest of the year, as per the given period, based on the
number of periods that "from" and "to" fall within the given year. The *data* parameter
should contain the monthly amount of the item. The *period* parameter indicates the
reporting period, from which the prediction will be made up to the end of the year. The
*from* and *to* parameters are the start and end period of a given item (e.g. a
contract, subscription, recurring donation, operating expense etc).

This function is useful to forecast items that typically occur at a monthly amount, but may not actually in every month in the year.

The *to* parameter is optional and defaults to the end of the given year when omitted.

The *sample* parameter is also optional and defaults to 3 (periods) when omitted.

By default, this function assumes there are maximum 12 periods in a year (effectively calendar
months within a year). You can specify the optional *max* parameter if you want the
function to calculate with a different number of periods.

PREDICT(data, period, from, to, sample, max)

Example:

PREDICT(1000, "2019-02", "2019-01", "2019-06") returns
2666.67

Given that the item has only occurred in 2 out of the 3 past months, and 4
future months need to be predicted, the calculation is 1000 * (2 / 3) * 4 = 2666.67.

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 must contain a range
of numeric values. The *index* parameter (starting at 1) indicates which index (e.g.
period) you want to project. The *pattern* parameter must contain a range of numeric
values that can be multiplied against the *data* range of values.

If you provide no *index* parameter, the function will calculate all indices against the
same pattern and return the sum of the projected value of all indices.

An example use-case 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 preceding 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.

You can also include retention or attrition in the calculation, based on the first value in the
*data* range of values, by passing a cell range or data array with cumulative percentages
as the optional *trend* parameter. These percentages have to be 100-based, for example
"0.95, 1.10, 1.05".

PROJECT(data, index, pattern, trend)

Example:

data = "1000, 2000, 1500, 3000, 5000"

PROJECT(data, 5, ".10,
.05, .02") = 680 (the total refund amount in period 5 based on multiplying the refund
pattern to the revenues of preceding 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) = 0.00797

Calculate a tiered amount or discount, based on a series of tresholds and related percentages.

TIERED(data, level, perc, level, perc, ..)

Example:

TIERED(1000, 200, .05, 500, .1, 800, .15) = 85

Up to
200 is calculated at 5%,

between 200 and 500 is calculated at 10%,

between 500 and 800
is calculated at 15%.

Calculates the forecasted value of a given amount by multiplying it against a pattern of amounts
or percentages. The *data* parameter should contain the base amount. The *pattern*
parameter must contain numbers (either absolute amounts or percentages) in the given cell range
or data array that can be applied against the base amount.

If you provide the optional *index* parameter, the function will return the future amount
as per that index. Else, it will return the last amount.

A practical example is to calculate the number of customers in the future, based on a pattern of new customers and lost customers per period.

TREND(data, pattern, index, type)

type:

0 = percentages (default)

1 = absolute amounts

Example:

TREND(1000, "0.10, -0.20, 0.30") =
1144 (the last period)

TREND(1000, "0.10, -0.20, 0.30", 1) = 1100 (the 1st period)

TREND(1000,
"100, -200, 300", 3, 1) = 1200 (absolute changes)

Calculates the total for the given period based on a standard pattern of numbers per day of the week, and excluding holidays.

This function is useful to forecast data that has typical and repeating weekday patterns, for example direct staff hours or sales in certain industries, such as restaurants.

The *data* parameter is mandatory and needs to be a cell range, data array, or
comma-delimited list of values containing the pattern for each day of the week, starting at
Monday. For example "10, 10, 0, 20, 20, 30, 30" indicates 10 on Mondays and Tuesday, 0 on
Wednesdays, 20 on Thursday and Fridays, and 30 on Saturdays and Sundays.

The *period* parameter is also mandatory and is the calendar month for which you want to
calculate the total.

The *holidays* parameter is optional and can be a cell range, data array, or
comma-delimited list of values containing the calendar dates that represent holidays. Holidays
must be entered as DD/MM/YYYY, DD-MM-YYYY or YYYY-MM-DD.

The *type* parameter is optional and can be used to either calculate the total amount in
the period excluding holidays, or the total amount in the period that falls on holidays.

WEEKPLAN(data, period, holidays, type)

type:

0 = total excluding holidays (default)

1 = total on
holidays

Examples:

data = "10, 10, 0, 20, 20, 30, 30"

WEEKPLAN(data, "2019-01",
"1/1/2019, 10/1/2019") = 500

WEEKPLAN(data, "2019-01", "1/1/2019, 10/1/2019", 1) = 10