Financial functions

Reference for all 24 Financial functions in Kazi Sheets, with syntax and examples.

Loans, investments, depreciation, growth and returns. Kazi Sheets has 24 Financial functions. Type = in a cell, start typing a name, and Kazi suggests it.

#BREAKEVEN

Calculates breakeven unit volume needed to cover costs.

Syntax

BREAKEVEN(fixed_costs, price_per_unit, variable_cost_per_unit)

Example

=BREAKEVEN(100000, 50, 20)

#CAGR

Computes Compound Annual Growth Rate over multiple periods.

Syntax

CAGR(start_val, end_val, periods)

Example

=CAGR(B2, D2, 3)

#CAPM

Calculates expected return on asset using Capital Asset Pricing Model.

Syntax

CAPM(risk_free_rate, beta, market_return)

Example

=CAPM(0.04, 1.2, 0.10)

#DB

Calculates depreciation using the fixed-declining balance method.

Syntax

DB(cost, salvage, life, period)

Example

=DB(100000, 10000, 5, 1)

#DDB

Calculates depreciation using the double-declining balance method.

Syntax

DDB(cost, salvage, life, period, [factor])

Example

=DDB(100000, 10000, 5, 1)

#EFFECT

Returns the effective annual interest rate given the nominal rate and compounding periods.

Syntax

EFFECT(nominal_rate, npery)

Example

=EFFECT(0.08, 12)

#FV

Calculates Future Value of an investment.

Syntax

FV(rate, periods, pmt, [pv])

Example

=FV(0.07, 5, -10000, -50000)

#GROWTH

Computes percentage growth rate ((End - Start) / Start).

Syntax

GROWTH(start_val, end_val)

Example

=GROWTH(B2, D2)

#IPMT

Calculates the interest payment portion for a given period.

Syntax

IPMT(rate, per, nper, pv)

Example

=IPMT(0.05/12, 1, 36, 100000)

#IRR

High-precision Newton-Raphson solver for Internal Rate of Return.

Syntax

IRR(values_range, [guess])

Example

=IRR(B2:B6)

#MARGIN

Computes gross or operating profit margin ratio ((Rev - Cost) / Rev).

Syntax

MARGIN(revenue, cost)

Example

=MARGIN(E2, E3)

#MARKUP

Computes markup percentage ((Price - Cost) / Cost).

Syntax

MARKUP(price, cost)

Example

=MARKUP(150, 100)

#NOMINAL

Returns the nominal annual interest rate given the effective rate and compounding periods.

Syntax

NOMINAL(effect_rate, npery)

Example

=NOMINAL(0.083, 12)

#NPER

Calculates the number of periods for an investment based on periodic constant payments.

Syntax

NPER(rate, pmt, pv, [fv])

Example

=NPER(0.06/12, -200, 10000)

#NPV

Calculates Net Present Value of periodic cash flows.

Syntax

NPV(discount_rate, cashflow_range)

Example

=NPV(0.08, B4:D4)

#PAYBACK

Calculates payback period in years or periods to recover capital.

Syntax

PAYBACK(initial_investment, cashflows_range)

Example

=PAYBACK(100000, B2:B10)

#PMT

Calculates the payment per period for a loan or annuity. Like Excel, a loan you receive (positive present value) gives a negative payment; use -PMT(...) or a negative present value for a positive number.

Syntax

PMT(rate, periods, present_value, [future_value], [type])

Example

=PMT(0.005, 36, 50000)

#PPMT

Calculates the principal payment portion for a given period.

Syntax

PPMT(rate, per, nper, pv)

Example

=PPMT(0.05/12, 1, 36, 100000)

#PV

Calculates the Present Value of an investment or loan.

Syntax

PV(rate, periods, pmt)

Example

=PV(0.08/12, 60, -500)

#RATE

Calculates the interest rate per period of an annuity.

Syntax

RATE(nper, pmt, pv)

Example

=RATE(48, -250, 10000)

#SLN

Returns the straight-line depreciation of an asset for one period.

Syntax

SLN(cost, salvage, life)

Example

=SLN(100000, 10000, 5)

#WACC

Calculates Weighted Average Cost of Capital.

Syntax

WACC(equity, debt, cost_equity, cost_debt, [tax_rate])

Example

=WACC(500000, 300000, 0.12, 0.06, 0.25)

#XIRR

Calculates internal rate of return for non-periodic cash flows.

Syntax

XIRR(values, dates, [guess])

Example

=XIRR(B2:B10, A2:A10)

#XNPV

Calculates Net Present Value for a schedule of cash flows that may be non-periodic.

Syntax

XNPV(rate, values, dates)

Example

=XNPV(0.1, B2:B10, A2:A10)