Functions for financial calculations

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

ACCRUE ❖ XLReporting

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. It performs linear interpolation between two values across specified periods.

This function is useful for finance professionals needing to model gradual increases or decreases over a time range, such as budget adjustments, periodic salary changes, accruing revenue or expense amounts, phasing of project costs over time etc.

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

Example:
ACCRUE(1000, 4, 3, 10) = 1000

COMPOUND ❖ XLReporting

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 example projecting the future value of a principal investment under annual compounding. Financial analysts use it to evaluate the growth of reinvested earnings. For calculations with varying rates, you can use FVSCHEDULE.

COMPOUND(data, rate, years)

Example:
COMPOUND(1000, 0.05, 4) = 1215.50625

DB

Calculates depreciation of an asset using the fixed-declining balance method, which applies a constant rate of depreciation over each period, but based on the book value remaining at the start of each period. This method results in higher depreciation expenses in the early years and gradually decreasing amounts in later years. This issuitable for assets that lose value more quickly upfront (like machinery, vehicles, tech equipment).

This function is useful for modeling asset depreciation where the largest expense is in the early years. It supports fiscal-year-aligned calculations with partial year adjustments.

DB(cost, rest, life, period, month)

Example:
DB(1000, 100, 60, 5, 12) returns 1845.21

DDB

Calculates the depreciation of an asset using the double-declining balance method or another specified accelerated rate. This method depreciates the asset faster in the earlier years of its useful life, which is commonly used for assets that lose value quickly (like vehicles, electronics, or machinery).

This function is useful for front-loading depreciation for tax or financial reporting purposes. The factor controls the rate of acceleration.

DDB(cost, rest, life, period, factor)

Example:
DDB(10000, 1000, 5, 2) returns 2400

DEPRECIATE ❖ XLReporting

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.

This function is useful when comparing depreciation methods (straight-line vs. accelerated) for planning or reporting. It provides a flexible interface to support various accounting strategies.

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

EFFECT

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

This function is useful for comparing interest rates with different compounding frequencies, such as APR versus APY.

EFFECT(rate, periods)

Example:
data = 0.0585
EFFECT(data, 12) = 6

FORECAST

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 data is assumed to be evenly distributed over time, and the time pattern will start at 1 with increments of 1.

This function is useful for trend analysis in financial forecasting. It estimates future values based on historical relationships, which is useful in revenue, cost, or sales projections. Linear regression helps you spot trends in your financial data, like how sales or expenses have been changing over time. It draws a straight line through your past numbers to estimate what might happen next. This is useful in forecasting when you want a simple way to predict future results based on past patterns.

If you want to use rolling forecasting, or obtain totals for multiple periods (actual or forecasted, or a combination of both), you can use the ROLLING function.

FORECAST(data, index, time)

Examples:
data = "10, 20, 30, 40"
FORECAST(data, 3) = 30 (intersecting past value)
FORECAST(data, 5) = 50 (forecasted future value)
FORECAST(data, 9) = 90 (forecasted future value)
FORECAST(data, 9, "4,4,5") = 57 (forecasted future value based on the given time distribution)

FV

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 present is the optional present value.

This function is useful for evaluating long-term savings plans, loan investments, or recurring contributions. It supports both lump sum and periodic contributions.

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

type:
0 = end of period (default)
1 = start of period

Example:
data = 0.06
FV(data / 12, 10, -200, -500, 1) = 2581.40

FVSCHEDULE

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.

This function is useful for modeling financial growth under varying interest rates, such as market-linked investment products.

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

IPMT

Calculates the interest portion of a loan payment for a specific period, assuming constant periodic payments and a fixed interest rate. It is typically used in financial models to separate interest from principal in amortizing loans. The future value is optional, and is the balance you want after the last payment.

This function is useful for building amortization schedules or analyzing loan cost structures. It separates interest from each periodic payment.

IPMT(rate, period, periods, present, future, type)

type:
0 = end of period (default)
1 = start of period

Example:
IPMT(0.05 / 12, 1, 12, 10000) returns 41.67

NOMINAL

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

This function is useful for back-calculating nominal rates from known effective rates, often used in regulatory disclosures or internal modeling.

NOMINAL(rate, periods)

Example:
data = 0.06
NOMINAL(data, 12) = 0.0585

NPER

Determines the number of periods needed to repay a loan or investment, based on constant periodic payments and a fixed interest rate. The future value is optional, and is the balance you want after the last payment.

This function is useful for determining how long it will take to repay a loan or reach a financial goal based on consistent payments and a fixed interest rate. It helps financial planners answer questions such as "how many months (or years) will it take" when evaluating payment plans or savings strategies.

NPER(rate, payment, present, future, type)

type:
0 = end of period (default)
1 = start of period

Example:
NPER(0.06 / 12, -150, 5000) returns -36.55

NPV

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.

This function is useful for evaluating investment viability, comparing project alternatives, or calculating return-on-investment. It assumes end-of-period cash flows.

NPV(rate, payments, payments, ..)

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

PMT

Calculates the fixed payment required to fully repay a loan over time, using a constant interest rate and equal payments. It includes both principal and interest, and is commonly used in loan calculators, mortgage estimators, and investment projections. The future value is optional, and is the balance you want after the last payment.

This function is useful for calculating monthly or annual repayments on loans and mortgages. It gives a consistent cash outflow that covers interest and principal.

PMT(rate, periods, present, future, type)

type:
0 = end of period (default)
1 = start of period

Example:
PMT(0.05 / 12, 12, 10000) returns -856.07

PPMT

Returns the principal portion of a loan payment for a specific period, based on constant periodic payments and a fixed interest rate. It complements the IPMT function, which calculates the interest portion. Together, they show how a payment is split over time between paying down the loan balance (principal) and paying interest. The future value is optional, and is the balance you want after the last payment.

This function is useful for dissecting loan payments into their principal components. Use alongside IPMT to generate detailed amortization schedules.

PPMT(rate, periods, present, future, type)

type:
0 = end of period (default)
1 = start of period

Example:
PPMT(0.05 / 12, 2, 12, 10000) returns -894.17

PREDICT ❖ XLReporting

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).

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.

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

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.

PROJECT ❖ XLReporting

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".

This function is useful for modeling time-lagged (in other words, delayed) financial effects such as customer refunds, cohort-based metrics, postponed payments, cost amortization, churn, or forecasting retention-driven revenue. In other words, amounts that compound over time, rather than occurring at once. This is used in financial planning, when revenue or costs don't happen immediately but unfold over several periods. It enables accurate forward projections by applying real-world percentage patterns to historical data, making it ideal for rolling forecasts.

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)

PV

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 future is the optional future value.

This function is useful for estimating the current worth of future cash flows or liabilities, discounted at a constant interest rate. It's commonly used in budgeting, investment analysis, and comparing alternative financial options.

PV(rate, periods, payment, future, 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

RATE

Calculates the interest rate per period for a loan or investment, based on fixed periodic payments, the number of periods, and present and future values. The guess parameter is optional, and is the initial guess for the interest rate (default = 10%).

This function is useful for solving the internal rate of return for loans or investment streams. It back-solves unknown interest rates.

RATE(periods, payment, present, future, type, guess)

type:
0 = end of period (default)
1 = start of period

Example:
RATE(36, -200, 6000) returns 1.02%

ROLLING

Calculates the actual or forecasted rolling value for the given index (or sum of multiple periods) based on the average trend in the given cell range or data array. This is often used for rolling reporting and forecasting. The data parameter should contain a range of values. The index parameter indicates which index (e.g. period) you want to report or forecast. The optional periods parameter indicates how many periods you want to totalize (default = 1).

If index is lower than the number of periods in the data, the result will be the actual value in the data.

If index is higher than the number of periods in the data, the result will be the forecasted value for that given period.

This function is useful for rolling forecasting, because it can report rolling totals of actual and projected amounts (or both), based on trends in the given data. It has some similarity to the FORECAST function, but uses cumulative variances between periods (versus linear regression used by FORECAST) and is focussed on rolling forecasting as it can return totals of multiple periods in one go.

If you want to calculate forecasting by linear regression, you can use the FORECAST function.

ROLLING(data, index, periods)

Examples:
data = "10, 20, 30, 40"
ROLLING(data, 3) = 30 (actual value)
ROLLING(data, 3, 2) = 70 (sum of actual values)
ROLLING(data, 5) = 50 (forecasted value)
ROLLING(data, 5, 2) = 110 (sum of forecasted values)

Below are 2 detailed examples of the FORECAST and ROLLING functions.

RRI

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

This function is useful for calculating the implied annual (or periodic) growth rate when you know the beginning value, ending value, and number of periods. It’s ideal for evaluating the performance of investments or returns over time.

RRI(periods, present, future)

Example:
RRI(12, 1000, 1100) = 0.00797

SCENARIO

Looks up driver values for scenario planning, by matching the given key value to the given cell range or data array (data), and returning the value on the same row in the second cell range or data array (range).

The function will try to find an exact match on the key value, and return zero when no exact match is found. Essentially, this function is similar to the LOOKUP function, but specifically intended for scenario planning.

Driver values can be percentages, absolute values, references to other cells, or formulas. You can embed the SCENARIO function in any formula or calculation of your liking, so you can define any scenario logic you want. If you combine this with editors in your model, you can create an intuitive model for end users to work through different scenarios or "what-if" analysis.

This function is useful for creating dynamic driver-based forecasting models (also known as "scenario planning"), with intuitive scenario names (e.g. "best", "worst", "rapid growth" etc) and centralized parameters. See the example below.

SCENARIO(data, range, key)

Example:

SLN

Calculates the straight-line depreciation for an asset over its useful life. This method assumes the asset loses the same amount of value each period (typically per year), making it the simplest and most commonly used depreciation method in accounting.

This function is useful for evenly spreading asset costs across time, often used for property, plant, and equipment.

SLN(cost, rest, life)

Example:
SLN(10000, 1000, 5) returns 1800

SYD

Calculates depreciation of an asset using the sum-of-years' digits method, which accelerates depreciation more in the early years. This method is a type of accelerated depreciation, where the asset loses value more quickly in the early years of its life and more slowly in later years.

This function is useful for allocating more depreciation in the earlier years of an asset's life, which reflects assets that lose value quickly or are more productive when new. It helps in matching costs with revenues more accurately and is often used in tax planning and financial modeling.

SYD(cost, rest, life, period)

Example:
SYD(10000, 1000, 5, 1) returns 3000

TIERED ❖ XLReporting

Calculates a tiered amount or discount, based on a series of thresholds and their associated percentages. The function applies each percentage rate only to the portion of the value (data) that falls within each defined level range. You can define multiple tier levels and rates.

This function is useful for calculating pricing tiers, (volume-based) discounts, progressive tax rates, or commissions where the rate changes as the amount increases. It enables you to reflect real-world financial logic directly in your model without complex nested formulas.

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%.

TREND ❖ XLReporting

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 final amount after applying all patterns.

This function is useful for seasonal calculations (for example, revenue or expense forecasting), investment growth scenarios, or retention-based calculations (for example, number of customers in the future), based on known or expected patterns.

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)

VDB

Calculates depreciation using the double-declining balance or a specified declining rate, with an option to switch to straight-line depreciation. It combines declining balance (typically double declining) in early years with straight-line depreciation in later years, switching automatically when straight-line becomes more advantageous (unless disabled). The declining balance factor is optional and defaults to 2 for double declining.

This function is useful for calculating variable declining balance depreciation over a range of periods. It is typically used when assets depreciate quickly in early years but benefit from a smoother depreciation path later on. The function also allows control over when to switch to straight-line depreciation, which is ideal for financial planning, forecasting, and tax calculations.

VDB(cost, rest, life, from, to, factor, type)

type:
0 = switch to straight-line depreciation
1 = do not switch to straight-line depreciation

Example:
VDB(10000, 1000, 5, 1, 1) returns 4000

WEEKPLAN ❖ XLReporting

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

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.

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.

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