Introduction
Financial functions in power BI are very similar to Excel functions. These functions are used to calculate financial data. In a DAX formula, these functions perform financial calculation.
In this article you will learn about financial functions. Below is the list of some popular financial functions.
Financial Functions and their Description
ACCRINT
Returns the accrued interest for a security that pays interest on periodic basis.
Syntax- ACCRINT(IssueDate, FirstInterestDate, SettlementDate, Rate, ParValue, Frequncy[, Basis [, CalcMethod]])
IssueDate – Issue date of Security.
FirstInterestDate – Date of Security’ First Interest.
SettlementDate – Date of Security’s Settlement. It is the date after issue date when the security is traded to the buyer.
Rate – Rate of security’s annual coupan.
ParValue – The security’s par value.
Frequency – The number of coupon payment par year.
Basis – It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
CalcMethod – It is optional. If omitted then it is TRUE.
ACCRINTM
Returns the accrued interest for a security that pays interest at maturity.
Syntax – ACCRINTM (IssueDate, MaturityDate, Rate, ParValue [, Basis])
IssueDate – Issue date of security.
MaturityDate–Maturity date of security
Rate –Annual coupon rate of security.
ParValue -The security's par value.
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
DISC
Returns the discount rate of a security.
Syntax – DISC (SettlementDate, MaturityDate, Pr, Redemption[, Basis])
SettlementDate–Settlement date of security.
MaturityDate – Maturity date of security or the expiry date of security.
Pr – Price per $100 face value of security.
Redemption –Redemption value per $100 face value of security.
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
DOLLARDE
Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for security prices.
Syntax –DOLLARDE (Fractional_dollar, Fraction)
Fractional_dollar - A number expressed as an integer part and a fraction part, separated by a decimal symbol.
Fraction - The integer to use in the denominator of the fraction.
DOLLARFR
Converts a dollar price expressed as a decimal number into a dollar price expressed as an integer part and a fraction part, such as 1.02. Fractional dollar numbers are sometimes used for security prices.
Syntax – DOLLARDE (Decimal_dollar, Fraction)
Decimal_dollar - A decimal number.
Fraction - The integer to use in the denominator of the fraction.
FV
Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, and/or a single lump sum payment.
Syntax –FV (Rate, Nper, Pmt, [Pv], [Type])
Rate - The interest rate per period.
Nper – The total number of payment periods in an annuity.
Pmt –The payment made each period;
Pv – The present value, or the lump-sum amount that a series of future payments is worth right now. It is Optional.
Type– It is optional. Default value is 0 if omitted.
PRICE
Returns the price per $100 face value of a security that pays periodic interest.
Syntax –PRICE (SettlementDate, MaturityDate, Rate, Yid, Redemption, Frequency, [Basis])
SettlementDate – Settlement date of security.
MaturityDate – Maturity date of security or the expiry date of security.
Rate – Annual coupon rate of security.
Yid – Annual yield of security.
Redemption – Redemption value per $100 face value of security.
Frequency –The number of coupon payments per year
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
RATE
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive result rate does not converge to within 0.0000001 after 20 iteration, RATE returns an error.
Syntax –RATE(Nper, Pmt, Pv, [Fv], [Type], [Guess])
Nper – The total number of payment periods in an annuity.
Pmt – The payment made each period and cannot change over the life of the annuity. Typically, Pmt includes principal and interest but no other fees or taxes.
Pv – The present value — the total amount that a series of future payments is worth now.
Fv – It is optional. Default value is 0 if omitted.
Type – It is optional. Default value is 0 if omitted.
Guess - It is optional. It assumed10% if omitted.
YIELD
Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield.
Syntax – YIELD (SettlementDate, MaturityDate, Rate, Pr, Redemption, Frequency, [Basis])
SettlementDate – Settlement date of security.
MaturityDate – Maturity date of security or the expiry date of security.
Rate – Annual coupon rate of security.
Pr – Price per $100 face value of security
Redemption – Redemption value per $100 face value of security.
Frequency –The number of coupon payments per year
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
YIELDDISC
Returns the annual yield for a discount security.
Syntax – YIELDDISC (SettlementDate, MaturityDate, Pr, Redemption, [Basis])
SettlementDate – Settlement date of security.
MaturityDate – Maturity date of security or the expiry date of security.
Pr – Price per $100 face value of security
Redemption – Redemption value per $100 face value of security.
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.
YIELDMAT
Returns the annual yield of a security that pays interest at maturity.
Syntax – YIELDMAT(SettlementDate, MaturityDate, Issue, Rate, Pr, [Basis])
SettlementDate – Settlement date of security.
MaturityDate – Maturity date of security or the expiry date of security.
Rate – Interest rate at date of issue of security.
Pr – Price per $100 face value of security
Basis - It is optional. Default value is 0, it means US(NASD) 30/360 day count basis. 1 means Actual/actual. 2 means Actual/360. 3 means Actual/365. 4 means European 30/360.