Finance Functions
A finance function applies financial algorithms or mathematical calculations.
FinanceCAGR
FinanceCAGR(BeginningValue, EndingValue, NumYears): Calculates the Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.
FinanceEffectiveRate
FinanceEffectiveRate(NominalRate, PaymentsPerYear): Calculates the Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with an annual compound interest payable in arrears.
FinanceFV
FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Calculates Future Value of an Investment: The value of an asset at a specified time in the future, assuming a certain interest rate or rate of return.
FinanceFVSchedule
FinanceFVSchedule(Principal, Year1Rate, Year2Rate): Calculates Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.
FinanceIRR
FinanceIRR(Value1, Value2): Calculates the Internal Rate of Return: The interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.
FinanceMIRR
FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2): Calculates Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.
FinanceMXIRR
FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2): Calculates the Modified Internal Rate of Return of an investment with dates.
FinanceNominalRate
FinanceNominalRate(EffectiveRate, PaymentsPerYear): Calculates Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).
FinanceNPER
FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the Number of periods for an investment or loan.
FinanceNPV
FinanceNPV(Rate, Value1, Value2): Calculates Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.
FinancePMT
FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan.
Alteryx PMT Versus Excel PMT
Some important distinctions must be made when comparing the Alteryx FinancePMT function to Excel's PMT function.
Both the Alteryx and Excel PMT functions use the same PMT formula. However, there are differences to be aware of:
Excel treats positive values as inflows (money coming in) and negative values as outflows (money going out). So, to get a positive payment number, you must give PresentValue and FutureValue opposite signs (positive/negative). If PresentValue and FutureValue have the same sign, Excel returns a negative PMT to balance the cash flows (since a payment is considered money going out).
The Alteryx function assumes the same “positive deposit” framing and, by default, returns PMT as a negative number (the payment is money going out). As such, to make the returned PMT positive, it's common to enter FutureValue with the opposite sign to PresentValue (often negative). For example:
FinancePMT(0.005, 360, -250000, 0, 0) returns ~1499 (note that PresentValue is a negative number). If PresentValue were a positive number, the return would be -1499 to signify money out.
FinancePV
FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Calculates Present Value of an investment: The value on a given date of a future payment, or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.
FinanceRate
FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the interest rate (per period).
FinanceXIRR
FinanceXIRR(Value1, Date1, Value2, Date2): Calculates the Internal Rate of Return of an investment with dates.
FinanceXNPV
FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Calculates the Net Present Value of an investment with dates.