Use financial functions to perform a range of financial calculations.

There are several general financial functions in Anaplan. These include CUMIPMTCUMPRINCFVIPMTIRRNPERNPVPMTPPMTPV, and RATE.

Anaplan also offers investment management functions. These include COUPDAYBSCOUPDAYSCOUPDAYSNCCOUPNCDCOUPNUMCOUPPCDDURATIONMDURATIONPRICEYEARFRAC, and YIELD.

Investment management functions enable you to perform common calculations relating to the price and yield of bonds and the coupon periods that define when the bondholder receives interest as the bond matures.

Many investment management functions rely on day count conventions to determine the number of days between two dates. Anaplan defaults to a modified version of the US 30/360 day count convention, but you can choose to use other day count conventions.

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

TitleDescription
COUPDAYBS

Use the COUPDAYBS (coupon days before settlement) function to calculate the number of days from the beginning of the coupon period until its settlement date. The number returned includes both the first day of the period and the settlement date.

COUPDAYS

Use the COUPDAYS function to return the number of coupon days in the coupon period that contains the settlement date.

COUPDAYSNC

Use the COUPDAYSNC function to calculate the number of coupon days from the settlement date until the next coupon date. The number returned excludes the settlement date and includes the last day of the next coupon period.

COUPNCD

The COUPNCD function calculates the next coupon date after a settlement date.

COUPNUM

The COUPNUM function returns the number of coupons payable between a settlement and maturity date.

COUPPCD

The COUPPCD function calculates the previous coupon date before a settlement date.

CUMIPMT

The CUMIPMT function calculates the cumulative interest paid on a loan over a period given equal payments made to the balance.

CUMPRINC

The CUMPRINC function calculates the amount of principal paid on a loan over a period, given consistent, equal payments.

DURATION

You can use the DURATION function to calculate the Macauley duration for an assumed parity value of 100 monetary units.

The Macauley duration is the weighted average maturity of cash flows. That is, the weighted average distance to payment. It's used to measure a bond price's response to changes in yield. A higher Macauley duration value indicates a riskier investment.

FV

The FV function calculates the future value of an investment. The future value is the lump sum or closing balance received at the end of an investment.

IPMT

The IPMT function calculates the amount of interest to be paid on a loan in a given payment period. The function assumes a consistent interest rate and payment timings in each period.

IRR

The IRR function calculates the internal rate of return for a series of positive and negative transactions. It can be used either with all transactions over a timescale, or with specified transactions on certain dates.

MDURATION

You can use the MDURATION function to calculate the modified Macauley duration for an assumed parity value of 100 monetary units.

The modified Macauley duration expresses the measurable change in the value of a bond in response to a change in interest rates. The result represents the effect that a 1% change in interest rates will have on the price of a bond.

NPER

The NPER function calculates the required number of periods to achieve a certain value for a loan or investment. This is based on a given interest rate, consistent payments, and opening and closing balance.

NPV

The NPV function calculates the net present value for a series of positive and negative transactions with a constant interest rate.

PMT

The PMT function calculates the payments due for a loan or annuity over a specified number of periods, given a consistent interest rate and payment amount.

PPMT

The PPMT function calculates how much of a payment is allocated to its principal part rather than interest. The function assumes a consistent interest rate and payment timings in each period.

PRICE

The PRICE function calculates the price per 100 monetary units invested for a bond that pays periodic interest.

PV

The PV function calculates the present value of an investment or the principal value of a loan.

RATE

The RATE function calculates the interest rate for a loan or investment based on length, payments, and present and future value.

YEARFRAC

Use the YEARFRAC function to calculate the fraction of a year between two dates (inclusive of the start date, exclusive of the end date).

The function uses a basis (day-count convention) to count the number of days between these dates, and then divide that number by the basis.

YIELD

Use this function to calculate the yield to maturity (YTM) of a bond.

Day count conventions

Anaplan defaults to the US 30/360 convention for day count, with a few differences. However, you can also choose to use other day count conventions.

US 30/360 day count conventions

The US 30/360 day count convention assumes 30 days for every month, and 360 days for the year. This convention was originally defined by the Financial Industry Regulatory Authority (FINRA).

US 30/360 uses the DayCountFactor formula to determine day count:

DayCountFactor=360×(Y2Y1)+30×(M2M1)+(D2D1)360DayCountFactor=\frac{360\times(Y_2-Y_1)+30\times(M_2-M_1)+(D_2-D_1)}{360}


Where:

  • Y is year,
  • M is month, and
  • D is day.

There are then various conventions by which you can adjust D1 and D2 to determine the end of the month, as some months are not 30 days long.

The US 30/360 conventions are:

  • If the investment is End of Month (EOM), the start date is the last day of February, and the end date is the last day of February, then change D2 to 30.
  • If the investment is EOM and the start date is the last day of February, then change D1 to 30.
  • If D2 is 31 and D1 is 30 or 31, then change D2 to 30.
  • If D1 is 31, then change D1 to 30.

Differences in Anaplan

Anaplan conventions differ from these in that the full set of rules is only applied when calculating COUPDAYSNC. For other calculations, the start date check is not performed for the first and third conventions outlined above. Instead, these modified conventions apply:

  • If the investment is EOM and the end date is the last day of February, then change D2 to 30.
  • If the investment is EOM and the start date is the last day of February, then change D1 to 30.
  • If D2 is 31, then change D2 to 30.
  • If D1 is 31, then change D1 to 30.

This allows the date adjustments for D2 to be independent of D1.

Other day count conventions

US 30/360 is the convention used by default in Anaplan. However, these conventions are also accommodated in the basis argument of the management functions:

  • Actual/360 and EUR 30/360, for which a year has 360 days.
  • Actual/365, for which a year has 365 days.
  • Actual/Actual, for which a year may have 365 or 366 days.

Note: Anaplan uses the International Swaps and Derivatives Association (ISDA) convention for Actual/Actual. In this convention, the number of days in leap and non-leap years are calculated separately.

COUPDAYBS

Syntax

COUPDAYBS(Settlement, Maturity, Frequency[, Basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The COUPDAYBS function returns a number.

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

COUPDAYBS

Examples

This example shows how the number of days before the settlement date can be calculated when a basis is specified.

FormulaDescriptionResult
COUPDAYBS(DATE(2015, 1, 15), DATE(2018, 1, 31), 1, 1)

This formula uses:

  • a settlement date of 01/15/2015
  • a maturity date of 01/31/2018
  • a frequency of 1 (annual)
  • a basis of 1 (Actual/Actual)
349

In this example, the number of days in the coupon period that contains the settlement date is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
COUPDAYBS(DATE(2015, 1, 15), DATE(2018, 1, 31), 4)

This formula uses:

  • a settlement date of 01/15/2015
  • a maturity date of 01/31/2018
  • a frequency of 4 (quarterly)
75


COUPDAYS

Syntax

COUPDAYS(Settlement, Maturity, Frequency[, basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The COUPDAYS function returns a number.

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

COUPDAYS

Examples

This example shows how the number of days in the coupon period that contains the settlement date can be calculated when a basis is specified.

FormulaDescriptionResult
COUPDAYS(DATE(2018, 1, 15), DATE(2021, 1, 15), 1, 1)

This formula uses:

  • a settlement date of 01/15/2018
  • a maturity date of 01/15/2021
  • a frequency of 1 (annual)
  • a basis of 1 (Actual/Actual)
365

In this example, the number of days in the coupon period that contains the settlement date is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
COUPDAYS(DATE(2018, 1, 15), DATE(2021, 1, 15), 4)

This formula uses:

  • the settlement date is 01/15/2018
  • the maturity date is 01/15/2021
  • the frequency is 4 (quarterly)
90


COUPDAYSNC

Syntax

COUPDAYBS(Settlement, Maturity, Frequency[, basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The COUPDAYSNC function returns a number.

Additional information

When the US 30/360 basis is used, the conventions used to calculate the days after settlement will vary depending on whether the start date and end date are independent. Where the end date is dependent on the start date, the full set of NASD day count conventions apply.

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

COUPDAYSNC

Examples

This example shows how the number of days from the settlement date until the next coupon date can be calculated when a basis is specified.

FormulaDescriptionResult
COUPDAYSNC(DATE(2018, 1, 15), DATE(2021, 1, 31), 1, 1)

This formula uses:

  • a settlement date of 01/15/2018
  • a maturity date of 01/31/2021
  • a frequency of 1 (annual)
  • a basis of 1 (Actual/Actual)
16

In this example, the number of days from the settlement date until the next coupon date is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
COUPDAYSNC(DATE(2018, 1, 15), DATE(2021, 1, 31), 4)

This formula uses:

  • a settlement date of 01/15/2018
  • a maturity date of 01/31/2021
  • a frequency of 4 (quarterly)
15
COUPNCD

For example, you can use the COUPNCD function to identify the next coupon date after you purchase a bond.

Syntax

COUPNCD(Settlement, Maturity, Frequency)

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date, when the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date, when the bond expires.
Frequency (required)Number

The number of coupon payments per year. 

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly

If you use any value other than 1, 2, or 4, the function returns a blank result.

The COUPNCD function returns a date.

Constraints

  • The settlement and maturity dates must be between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

COUPNCD

Examples

In this example, two formulas calculate the next coupon date. The first formula uses 1 for the Frequency argument, so the coupon is paid annually. The second formula uses 4 for the Frequency argument, so the coupon is paid quarterly.

FormulaDescriptionResult
COUPNCD(DATE(2021, 1, 15), DATE(2024, 1, 15), 1)

This example calculates the next coupon date for a bond with a frequency of 1 (annual).

The settlement date is 01/15/2021 and the maturity date is 01/15/2024.

01/15/2022
COUPNCD(DATE(2021, 1, 15), DATE(2024, 1, 15), 4)

In this example, the next coupon date is calculated for a bond with a frequency of 4 (quarterly).

The example uses a settlement date of 01/15/2021 and a maturity date of 01/15/2024.

04/15/2021
COUPNUM

For example, you can use the COUPNUM function to calculate how many coupons remain to be paid for a bond.

Syntax

COUPNUM(Settlement, Maturity, Frequency)

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date, when the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date, when the bond expires.
Frequency (required)Number

The number of coupon payments per year. 

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly

If you use any value other than 1, 2, or 4 for this argument, the function returns a blank result.

The COUPNUM function returns a number.

Constraints

  • The settlement and maturity dates must be between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

COUPNUM

Examples

In this example, two formulas calculate how many coupons are payable between the settlement and maturity date. The first formula uses 1 for the Frequency argument, so coupons are paid annually. The second formula uses 4 for the Frequency argument, so coupons are paid quarterly.

FormulaDescriptionResult
COUPNUM(DATE(2021, 1, 15), DATE(2024, 1, 15), 1)

This example calculates how many coupons are payable given a coupon frequency of 1 per year.

The settlement date is 01/15/2021 and the maturity date is 01/15/2024.

3
COUPNUM(DATE(2021, 1, 15), DATE(2024, 1, 15), 4)

This example calculates how many coupons are payable given a coupon frequency of 4 per year.

The example uses a settlement date of 01/15/2021 and a maturity date of 01/15/2024.

12
COUPPCD

For example, you can use the COUPPCD function to identify the most recent coupon date for a bond before it was traded to a buyer.

Syntax

COUPPCD(Settlement, Maturity, Frequency)

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date, when the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date, when the bond expires.
Frequency (required)Number

The number of coupon payments per year. 

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly

If you use any value other than 1, 2, or 4, the function returns a blank result.

The COUPPCD returns a date.

Constraints

  • The settlement and maturity dates must be between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent function

COUPPCD(opens external page)

Examples

For example, the formula below calculates the previous coupon date before the settlement date for a bond. In this example, as the coupon is paid annually, the previous coupon date was 01/31/2014.

FormulaDescriptionResult
COUPPCD(DATE(2015, 1, 15), DATE(2018, 1, 31), 1)

In this example, the previous coupon date is calculated for a bond with a frequency of 1 (annual). 

The example uses a settlement date of 01/15/2015 and a maturity date of 01/31/2018.

01/31/2014
CUMIPMT

For example, you can use the CUMIPMT function to see how much interest is paid during different periods for a loan.

Syntax

CUMIPMT(Interest rate, Number of periods, Principal, Start period, End period [, Timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percent format, as 0.1 equals 10%, and so on.

Number of periods (required)NumberThe number of periods that the Principal is paid over.
Loan balance (required)NumberThe total amount to be paid between the Start period and End period.
Start period (required)NumberThe period to start calculating cumulative interest from. 
End period (required)NumberThe period to stop calculating cumulative interest at.
TimingNumber

Whether interest payments are made at the start or end of each period.

If a payment is made at the start of the period, that period's interest applies to it. You can enter a value of 0 or 1 for this argument. If you enter:

  • 0, payments are made at the end of each period (the default if this argument is omitted)
  • 1, payments are made at the start of each period

The CUMIPMT function returns a number, which is the cumulative interest payable between the Start period and End period.

Additional information

Periods used with CUMIPMT function

The Number of periods argument enables you to specify the number of periods the loan is paid over. The Start period and End period arguments enable you to specify what range of periods to calculate the cumulative interest for.

The value you provide for the Interest rate argument must match the Number of periods arguments. For example, if the periods are years, you should provide the annual interest rate.

Positive and negative values

For any values you give the IPMT function via an argument, or that the function returns:

  • a positive value represents money you receive, such as a dividend or loan
  • a negative value represents money paid, such as a deposit or interest payment

Constraints

The Start period and End period arguments must be less than the Number of periods argument.

Excel equivalent

CUMIPMT

Examples

In this example, there's a Scenarios list on columns, and line items on rows. There's one line item for each argument of the CUMIPMT function.

Each item in the Scenario list uses the same value for the Interest rate, Number of periods, and Loan Balance arguments. However, they use different values for the Start period and End period arguments. This highlights how the cumulative interest payable changes in different periods over the duration of a loan.


Scenario 1Scenario 2Scenario 3Scenario 4Scenario 5Scenario 6
Interest rate2.5%2.5%2.5%2.5%2.5%2.5%
Number of periods303030303030
Loan Balance300,000300,000300,000300,000300,000300,000
Start period11301151
End period1130153030
Timing010000

Cumulative interest paid

CUMIPMT(Interest rate, Number of periods, Loan balance, Start period, End period, Timing)

-7,5000-349.59-92,465.29-42,211.51-129,998.77
CUMPRINC

For example, you can use the CUMPRINC function to calculate the remaining balance on a mortgage.

Syntax

CUMPRINC(Interest rate, Number of periods, Loan balance, Start period, End period [, Timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percent format, as 0.1 equals 10%, and so on.

Number of periods (required)NumberThe number of periods that the Loan balance is paid over.
Loan balance (required)NumberThe total amount to be paid between the Start period and End period.
Start period (required)NumberThe period to start calculating the principal paid from.
End period (required)NumberThe period to stop calculating the principal paid at.
TimingNumber

Whether payments are made at the start or end of each period.

If a payment is made at the start of the period, that period's interest applies to it.

You can enter a value of 0 or 1 for this argument. If you enter:

  • 0, payments are made at the end of each period (the default if this argument is omitted)
  • 1, payments are made at the start of each period

The CUMPRINC function returns a number, which is the amount of principal paid for the loan.

Additional information

Periods used with the CUMPRINC function

The Number of periods argument enables you to specify the number of periods the loan is paid over. The Start period and End period arguments enable you to specify what range of periods to calculate the cumulative interest for.

The value you provide for the Interest rate argument must match the Number of periods arguments. For example, if the periods are years, you should provide the annual interest rate.

Positive and negative values

For any values you give the CUMPRINC function via an argument, or that the function returns:

  • a positive value represents money you receive, such as a dividend or loan
  • a negative value represents money paid, such as a deposit or interest payment

Excel equivalent

CUMPRINC

Examples

In this example, there's a Scenarios list on columns, and line items on rows. There's one line item for each argument of the CUMPRINC function.

Each item in the Scenario list uses the same value for the Interest rate, Number of periods, and Loan Balance arguments. However, they use different values for the Start period and End period arguments. This highlights how the cumulative principal paid changes in different periods over the duration of a loan.


Scenario 1Scenario 2Scenario 3Scenario 4Scenario 5Scenario 6
Interest rate2.5%2.5%2.5%2.5%2.5%2.5%
Number of periods303030303030
Loan balance300,000300,000300,000300,000300,000300,000
Start period11301151
End period1130153030
Timing010000

Cumulative principal paid

CUMPRINC(Interest rate, Number of periods, Loan balance, Start period, End period, Timing)

-6,833.29-13,983.70-13,983.70-122,534.09-187,121.17-300,000
DURATION

Syntax

DURATION(Settlement, Maturity, Rate, Yield, Frequency [, Basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Rate (required)NumberThe bond annual coupon date.
Yield (required)NumberThe bond annual yield.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The DURATION function returns a number.

Additional information

The Macauley duration is calculated with the following formula:

Duration=t=1TtC(1+y)t+TF(1+t)TP Duration = \frac { \sum_{t=1}^{T} \frac {tC} {(1+y)^{t}} + \frac{TF}{(1+t)^{T}} } { P }


Where:

  • C is coupon
  • y is yield
  • F is face value
  • P is price, inclusive of accrued interest
  • T is number of periods

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The rate and yield must be positive or zero.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Excel equivalent

DURATION

Examples

This example shows a Macauley duration calculation that specifies a basis.

FormulaDescriptionResult
DURATION(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 0.1, 1, 4)

The example has a:

  • settlement date of 01/15/2015
  • maturity date of 01/15/2018
  • rate of 0.12 (12%)
  • yield of 0.1 (10%)
  • frequency of 1 (annual)
  • basis of 4 (European 30/360)
2.6976811

This example shows a Macauley duration calculation that does not specify a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
DURATION(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 0.1, 4)

The example has a:

  • settlement date of 01/15/2018
  • maturity date of 01/15/2021
  • rate of 0.12 (12%)
  • yield of 0.1 (10%)
  • frequency of 4 (quarterly)
2.5760086
FV

For example, you could use this function to calculate the return of an investment given a certain interest rate, principal, and regular payments into the investment.

Syntax

FV(Interest rate, Number of periods, Payments [, Present value] [, Payment timing])

Arguments

ArgumentData typeDescription
Interest rate (required)NumberThe interest rate per period.
Number of periods (required)NumberThe number of periods that the investment is held for, and that the interest rate applies to.
Payments (required)NumberThe amount paid into the investment each period.
Present valueNumber

The present value of the investment at the start of the investment period.

If you omit this argument, the present value is equivalent to 0.

Payment timingNumber

Determines whether each payment is made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it.

You can enter a zero or non-zero number for this argument. If you enter:

  • zero, payments are made at the start of each period
  • a non-zero number, payments are made at the end of each period

The FV function returns a number, which is the future value of the investment.

Additional information

How FV is calculated

You can create a formula equivalent to the FV function with the structure below:

IF Rate = 0 THEN - Present value - Payments * Number of periods ELSE - Present value * POWER(1 + Interest rate, Number of periods) - Payments * (1 + Interest rate * Payment timing) * (POWER(1 + Rate, Number of periods) - 1) /Interest rate

In this formula, Payment timing must either be zero for payments to be made at the start of each period, or non-zero for the end.

Positive and negative values

For any values you give the FV function via an argument, or that the function returns:

  • a positive value represents money you receive, such as a dividend or loan
  • a negative value represents money paid, such as a deposit or interest payment

Consistent time periods

You must use the same time period granularity for the Interest rateNumber of periods, and Payments arguments. This means that they should all consistently be monthly, quarterly, annual, or over another interval.

For example, an investment might over 3 years. In this case, there are 36 monthly periods, and you should divide the annual interest rate by 12. Additionally, any payment amounts should also be monthly.

Excel equivalent

FV

Examples

In this example, the five items within the Contracts list are on columns. On rows, there are six line items. One for each of the arguments of the FV function, and one that uses the FV function to calculate the future value of each contract.


Contract 1Contract 2Contract 3Contract 4Contract 5
Interest rate0.4000%0.7974%0.7974%0.4000%0.4000%
Number of periods2412121212
Payments3000300300300
Present value-10,000-10,000-10,000-10,000-10,000
Payment timing00001

Future value

FV(Interest rate, Number of periods, Payments, Present value, Payment timing)

3,46411,0007,2386,8106,796
IPMT

For example, you can use the IPMT function to see how the interest due changes over the duration of loan.

Syntax

IPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Payment timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate to apply to the Present value each period.

This argument is best used with the Percent format, as 0.1 equals 10%, and so on.

Period to examine (required)NumberThe period to return the amount of interest due for.
Number of periods (required)NumberThe number of periods that the Present value is paid over.
Present value (required)NumberThe total amount to be paid over the Number of periods.
Future valueNumberThe amount of loan left after the final payment is made. If omitted, this assumed to be 0.
Payment timingNumber

Whether interest payments are made at the start or end of each period.

0 means payments are made at the end of each period. This is the default behavior if you omit the Payment timing argument.

A non-zero value means payments are made at the start end.

The IPMT function returns a number, which is the interest due in the Period to examine.

Additional information

Periods used with IPMT function

The Period to examine and Number of periods arguments enable you to specify the number of periods interest accrues over, and the period for which to view the interest.

The values you provide for the Interest rate, Period to examine, and Number of periods arguments should all use the same time scale. For example, if the periods are years, you should provide the annual interest rate.

Positive and negative values

For any values you give for the Present value or Future value arguments, or that the IPMT function returns:

  • A positive value represents money you receive, such as a dividend or loan.
  • A negative value represents money paid, such as a deposit or interest payment.

Excel equivalent

IPMT

Examples

In this example, a module has eight line items on rows, and a Loans list on columns. The first four line items contain data for each of the required arguments for the IPMT function. The fifth line item uses the IPMT function to calculate interest payments.

The sixth and seventh line items contain data for the optional arguments for the IPMT function. The eighth line item uses the IPMT function to calculate interest payments with both optional arguments applied.


Loan 1Loan 2Loan 3Loan 4Loan 5
Present value70,000330,000500,000500,000500,000
Interest rate5.5%3.5%1.44%1.44%1.44%
Period to examine11101010
Number of periods625252525

Interest due for period

IPMT(Interest rate, Period to examine, Number of periods, Present value)

-3,850-8,475.38-4,898.82-4,898.82-4,898.82
Future value030,000010,00010,000
Timing00001

Amended interest due for period

IPMT(Interest rate, Period to examine, Number of periods, Present value, Future value, Timing)

-3,850-8,195.87-4,898.82-4,852.79-4,783.90
IRR

The internal rate of return is the annual rate of return an investment is expected to generate. You could use the IRR function to assess a number of investments and find the most effective investment.

Syntax

The IRR function has two different syntaxes. The syntax that applies depends on whether you use more or less than two arguments with the function.

IRR with time scale

IRR(Cash flow [, Estimate])

IRR with dates

IRR(Cash flow, Dates, Transactions [, Estimate])

Arguments

IRR with time scale

ArgumentData typeDescription
Cash flow (required)Number

A line item that contains a series of positive and negative values that represent cash inflow and outflow.

The line item used for this argument must have a time scale.

EstimateNumber

An estimate of the IRR. This argument uses percentage format, so 0.1 is equal to 10%.

This argument is optional and helps the IRR function to calculate a result more quickly.

IRR with dates

ArgumentData typeDescription
Cash flow (required)Number

A series of positive and negative values that represent cash inflow and outflow.

Must have the list used for the Transactions argument as a dimension.

Dates (required)DateThe date associated with each value of the Cash flow argument.
Transactions (required)ListA list of transactions, which must be a common dimension of the Cash flow and Dates argument.
Estimate of rateNumber

An estimate of the IRR. This argument uses percentage format, so 0.1 is equal to 10%.

This argument is optional and helps the IRR function to calculate a result more quickly.

The IRR function returns a number.

Additional information

How IRR is calculated

IRR is the iterative solution to this equation:

0=Σi=1npi(1+IRR)di/3650 = \Sigma_{i = 1}^{n} \dfrac{p_i}{(1+IRR) ^{d}i/365}

In this equation:

  • N is the number of payments in and out from the start of the first period
  • Pi is the payment in the ith period
  • di is the number of days from the start of the first period

Use IRR with the Users list

You can reference the Users list with the IRR function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints 

The Cashflow argument must contain at least one positive and one negative value.

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

Examples

Example of IRR with time scale

This example uses two modules. The first module, Annual cash flow, contains Cash flow line item with a Time Scale of Year. The Cash flow line item contains both a positive and negative value.


FY20FY21FY22
Cash flow-100,0000130,000

The second module contains a single Percent format line item, which contains a formula that uses IRR with the Cash flow line item from the first module. As the IRR function returns a single value, the result does not need a time dimension.



IRR of annual cash flow

IRR('Annual cash flow'.Cash flow)

14.0%

Example of IRR with dates

In this example, two modules are used. One source module, Plant Transaction Data, is dimensioned by the Transactions and Plants lists. Transactions is on rows, and Plants is on pages, with Plant 1 selected.

The Plant Transaction Data module contains the values of transactions, dates of transactions, and a description for some transactions.


DescriptionCash flowDate
Transaction 01Land purchase-100,0001/1/2011
Transaction 02Turbine purchase-850,0001/1/2012
Transaction 03Energy generation revenue200,0001/3/2013
Transaction 04Energy generation revenue200,0001/3/2014
Transaction 05Energy generation revenue200,0001/3/2015
Transaction 06Energy generation revenue200,0001/3/2016
Transaction 07Energy generation revenue200,0001/3/2017
Transaction 08Energy generation revenue200,0001/3/2018
Transaction 09Energy generation revenue200,0001/3/2019
Transaction 10Energy generation revenue200,0001/3/2020
Transaction 11Energy generation revenue200,0001/3/2021
Transaction 12Energy generation revenue200,0001/3/2022
Transaction 13Energy generation revenue200,0001/3/2023
Transaction 14Energy generation revenue200,0001/3/2024
Transaction 15Energy generation revenue200,0001/3/2025
Transaction 16Final energy generation revenue200,0001/3/2026
Total
1,850,000

The second module uses the data from the Plant Transaction Data module with the IRR function to calculate the internal rate of return for each plant. The column for Plant 1 contains the internal rate of return for data displayed in the Plant Transaction Data module.


Plant 1Plant 2Plant 3Plant 4

Internal Return Rate for Plant

IRR('Plant Transaction Data'.Cash flow, 'Plant Transactions Data'.Date, Transactions, 0.1)

18.1%12.2%13.8%9.2%
MDURATION

Syntax

MDURATION(Settlement, Maturity, Rate, Yield, frequency [, basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Rate (required)NumberThe bond annual coupon date.
Yield (required)NumberThe bond annual yield.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The DURATION function returns a number.

Additional information

The modified Macauley duration is calculated with the following formula:

MDuration=Duration(1+yf)MDuration = \frac { Duration } { \left( 1 + \frac{y}{f} \right ) }

Where:

  • y is yield
  • f is frequency

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The rate and yield must be positive or zero.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

MDURATION

Examples

This example shows a modified Macauley duration calculation that specifies a basis.

FormulaDescriptionResult
MDURATION(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 0.1, 1, 4)

The example has a:

  • settlement date of 01/15/2018
  • maturity date of 01/15/2021
  • rate of 0.12 (12%)
  • yield of 0.1 (10%)
  • frequency of 1 (annual)
  • basis of 4 (European 30/360)
2.4524373

This example shows a modified Macauley duration calculation that does not specify a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
MDURATION(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 0.1, 4)

The example has a:

  • settlement date of 01/15/2018
  • maturity date of 01/15/2021
  • rate of 0.12 (12%)
  • yield of 0.1 (10%)
  • frequency of 4 (quarterly)
2.5131792
NPER

For example, you can use the NPER function to calculate how long it will take to pay off a loan.

Syntax

NPER(Interest rate, Payments, Present value [, Residual value] [, Timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percentage format, as 0.1 equals 10%, and so on.

Payments (required)Number

The amount paid each period.

You can omit this argument by entering 0, but in this case, you must provide a value for the Present value argument.

Present value (required)NumberThe present value of the instrument, or initial investment.
Residual valueNumberThe amount of loan left after the final payment is made. If omitted, this is assumed to be 0.
TimingNumber

Whether interest payments are made at the start or end of each period.

0 means payments are made at the start of each period. A non-zero value means payments are made at the end.

If omitted, payments are made at the start of each period.

The NPER function returns a number.

Additional information

Positive and negative values

For any values you give the NPER function via an argument, or that the function returns:

  • A positive value represents money you receive, such as a dividend or loan.
  • A negative value represents money paid, such as a deposit or interest payment.

How the NPER function is calculated

You can recreate the behavior of the NPER function. To do this, you can use the formula below with the values you would otherwise use as arguments for the NPER function.

IF Interest rate = 0 THEN (-Present value - Residual value) / Payments ELSE LOG((-Interest rate * Residual value + Payments * (1 + Interest rate * Timing))/(Interest rate * Present value + Payments * (1 + Interest rate * Timing)))/LOG(1 + Interest rate)

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

NPER

Examples

In this example, there is a Contracts list on columns, and line items on rows. There are five line items, one for each argument of the NPER function. The sixth line item uses the NPER function to calculate the required payments.


Contract 1Contract 2Contract 3Contract 4Contract 5
Interest rate4%4%8%1%15%
Payments3003003001,500-5,000
Present value10,00010,00010,000-50,000-50,000
Residual value00025,0000
Timing01000

Number of periods

NPER(Interest rate, Payments, Present value, Residual value, Timing)

-21.6-21.04-16.88-13.14-6.556
NPV

The net present value of an investment enables you to know how much a future investment is worth today. You could use the NPV function to assess whether an investment is worthwhile while accounting for depreciation.

Syntax

NPV(Discount rate, Cash flow, Dates, Transactions)

Arguments

ArgumentData typeDescription
Discount rate (required)NumberThe discount rate used to calculate net present value. This argument uses percentage format, so 0.1 is equivalent to 10%.
Cash flow (required)NumberA series of positive and negative values that represent cash inflow and outflow.
DatesDate

The date associated with each value of the Cash flow argument.

This argument is optional, but if used, you must provide the Transactions argument also.

TransactionsList

A list of transactions.

This argument is optional, but if used, the list must be a common dimension of the Cash flow and Dates argument.

The NPV function returns a number.

Additional information

How NPV is calculated

NPV is the solution to this equation:

NPV=Σi=1N(1+Rate)di/365PNPV = \Sigma_{i = 1}^{N} \quad_{(1 + Rate) ^{d}i/365} ^ {\quad \quad P}

In this equation:

  • N is the number of payments in and out over the time scale
  • Pi is the payment in the ith period
  • di is the number of days from the start of the first period
  • Rate is the discount rate

Use NPV with the Users list

You can reference the Users list with the NPV function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Excel equivalent

NPV

Examples

Example of NPV with timescale

This example uses two modules. The first module, Annual cash flow, contains Cash flow line item with a Time Scale of Year. The Cash flow line item contains both a positive and negative value.


FY20FY21FY22
Cash flow-100,0000130,000

The second module contains two line items and no other dimensions. One line item contains the Discount rate, and another line item contains a formula that uses NPV with the Discount rate and the Cash flow line item from the previous module. As the IRR function returns a single value, the result does not need a time dimension.



Discount rate10%

NPV of annual cash flow

NPV(Discount rate, 'Annual cash flow'.Cash flow)

7,438.02

Example of NPV with dates

In this example, two modules are used. One source module, Plant Transaction Data, is dimensioned by the Transactions and Plants lists. Transactions is on rows, and Plants is on pages, with Plant 1 selected.

The Plant Transaction Data module contains the values of transactions, dates of transactions, and a description for some transactions.


DescriptionCash flowDate
Transaction 01Land purchase-100,0001/1/2011
Transaction 02Turbine purchase-850,0001/1/2012
Transaction 03Energy generation revenue200,0001/3/2013
Transaction 04Energy generation revenue200,0001/3/2014
Transaction 05Energy generation revenue200,0001/3/2015
Transaction 06Energy generation revenue200,0001/3/2016
Transaction 07Energy generation revenue200,0001/3/2017
Transaction 08Energy generation revenue200,0001/3/2018
Transaction 09Energy generation revenue200,0001/3/2019
Transaction 10Energy generation revenue200,0001/3/2020
Transaction 11Energy generation revenue200,0001/3/2021
Transaction 12Energy generation revenue200,0001/3/2022
Transaction 13Energy generation revenue200,0001/3/2023
Transaction 14Energy generation revenue200,0001/3/2024
Transaction 15Energy generation revenue200,0001/3/2025
Transaction 16Final energy generation revenue200,0001/3/2026
Total
1,850,000

The second module uses the data from the Plant Transaction Data module with the NPV function to calculate the net present value for each plant. The column for Plant 1 contains the internal rate of return for data displayed in the Plant Transaction Data module.


Plant 1Plant 2Plant 3Plant 4

Net Present Value for Plant

NPV(0.1, 'Plant Transaction Data'. Cash flow, 'Plant Transaction Data'. Date, Transactions)

445,464325,849194,03586,659
PMT

For example, you can use the PMT function to calculate the monthly amount required to pay back a loan given each payment is equal.

Syntax

PMT(Interest rate, Number of periods, Present value [, Future value] [, Timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percent format, as 0.1 equals 10%, and so on.

Number of periods (required)NumberThe number of periods that the loan is paid over.
Present value (required)NumberThe present value of the instrument, or initial investment.
Future valueNumberThe amount of loan left after the final payment is made. If omitted, this assumed to be 0.
TimingNumber

Whether interest payments are made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it.

A value of 0 means payments are made at the end of each period. This is the default behavior if you omit the Payment timing argument.

A non-zero value means payments are made at the start end.

The PMT function returns a number.

Additional information

Periods used with PMT function

The Number of periods argument enables you to specify the number of periods interest accrues over.

The values you provide for the Interest rate and Number of periods arguments should use the same time scale. For example, if the periods are years, you should provide the annual interest rate.

Positive and negative values

For any values you give for the Present value or Future value arguments, or that the PMT function returns:

  • A positive value represents money you receive, such as a dividend or loan.
  • A negative value represents money paid, such as a deposit or interest payment.

How the PMT function is calculated

You can recreate the behavior of the PMT function. To do this, you can use the formula below with the values you would otherwise use as arguments for the PMT function.

IF Interest rate = 0 THEN (-Present value -Future value) / Number of periods ELSE Interest Rate / ((1 - POWER(1 + Interest rate, Number of periods)) * (1 + Interest Rate * Timing)) * (Future value + Present value * POWER(1 + Interest rate, Number of periods))

Excel equivalent

PMT

Examples

In this example, there is a Contracts list on columns, and line items on rows. There are five line items, one for each argument of the PMT function. The sixth line item uses the PMT function to calculate the required payments.


Contract 1Contract 2Contract 3Contract 4Contract 5
Interest rate8.00%8.00%5.00%3.50%0.10%
Number of periods10102548
Present value-10,000-10,000-250,000-5,000-1,000
Future value00004,000
Timing01001

Payment due each period

PMT(Interest rate, Number of periods, Present value, Future value, Timing)

1,490.291,379.9017,738.111,361.26-348.59
PPMT

For example, you could use the PPMT function to calculate equity built during a loan.

Syntax

PPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Timing])

Arguments

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percentage format, as 0.1 equals 10%, and so on.

Period to examine (required)NumberThe period to return the amount paid towards the principal for.
Number of periods (required)NumberThe number of periods that the loan is paid over.
Present value (required)NumberThe principal amount of the loan.
Future valueNumberThe amount of loan left after the final payment is made. If omitted, this is assumed to be 0.
TimingNumber

Whether interest payments are made at the start or end of each period.

0 means payments are made at the start of each period. A non-zero value means payments are made at the end.

If omitted, payments are made at the start of each period.

The PPMT function returns a number.

Additional information

Periods used with PPMT function

The Period to examine and Number of periods arguments enable you to specify the number of periods interest accrues over, and which period to view the interest for.

The values you provide for the Interest rate, Period to examine, and Number of periods arguments should all use the same time scale. For example, if the periods are years, you should provide the annual interest rate.

Positive and negative values

For any values you give for the Present value or Future value arguments, or that the PPMT function returns:

  • A positive value represents money you receive, such as a dividend or loan.
  • A negative value represents money paid, such as a deposit or interest payment.

Excel equivalent

PPMT

Examples

In this example, a module has eight line items on rows, and a Loans list on columns. The first four line items contain data for each of the required arguments for the PPMT function. The fifth line item uses the PPMT function to calculate the principal paid.

The sixth and seventh line items contain data for the optional arguments for the PPMT function. The eighth line item uses the PPMT function to calculate the principal paid with both optional arguments applied.


Loan 1Loan 2Loan 3Loan 4Loan 5
Present value70,000330,000500,000500,000500,000
Interest rate5.5%3.5%1.44%1.44%1.44%
Period to examine11101010
Number of periods625252525

Principal paid

PPMT(Interest rate, Period to examine, Number of periods, Present value)

-10,162.53-11,547.06-19,058.85-19,058.85-19,058.85
Future value030,000010,00010,000
Timing00001

Amended principal paid

PPMT(Interest rate, Period to examine, Number of periods, Present value, Future value, Timing)

-10,162.53-12,596.79-19,058.85-19,440.02-19,164.06
PRICE

You can use PRICE to calculate how much you pay against the bond's final value, and therefore the return on your investment.

Syntax

PRICE(Settlement, Maturity, Rate, Yield, Redemption, Frequency[, Basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Rate (required)NumberThe bond annual coupon date.
Yield (required)NumberThe bond annual yield.
Redemption (required)NumberThe payment received when the bond reaches maturity.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The PRICE function returns a number.

Additional information

For bonds that pay two or more coupons between settlement and maturity, the price is calculated with this formula:

Price=[redemption(1+yldfrequency)N1+DSCE]+[k=1N100ratefrequency(1+yldfrequency)k1+DSCE]100ratefrequencyAEPrice = \left [ \frac {redemption} {\left ( 1+\frac{yld}{frequency} \right )^{N-1+\frac{DSC}{E}}} \right ] + \left [ \sum_{k=1}^{N} \frac {100 * \frac{rate}{frequency}} {\left ( 1+\frac{yld}{frequency} \right )^{k-1+\frac{DSC}{E}}} \right ] - 100 * \frac{rate}{frequency} * \frac{A}{E}


For a bond that pays one coupon between settlement and maturity, the price is calculated with this formula:

DSR=EADSR = E – A


Price=100ratefrequency+redemptionyldfrequencyDSRE+1100ratefrequencyAEPrice= \frac {100 * \frac{rate}{frequency} + redemption} {\frac{yld}{frequency} * \frac{DSR}{E}+1} 100 * \frac{rate}{frequency} * \frac{A}{E}


Where bonds have a zero rate and do not pay a coupon, the price is calculated with this formula:

Price=redemption(1+yld)nPrice = \frac{redemption}{(1 + yld)^{n}}


In these formulas:

  • E is the number of coupon days in the coupon period containing the settlement
  • A is the number of coupon days before settlement
  • DSC is the number of coupon days between the settlement and the next coupon
  • DSR is the number of coupon days between the settlement and the maturity
  • yld is the yield
  • N is the total number of coupon periods between settlement and maturity
  • n is the number of years to maturity as a fraction (calculated using a basis)

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The rate must be greater than zero.
  • The yield must be greater than negative one.
  • The redemption must be greater than zero.
  • The frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • The basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

PRICE

Examples

This example shows a PRICE calculation that specifies a basis.

FormulaDescriptionResult
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 1, 4)

The example has a:

  • settlement date of 01/15/2015
  • maturity date of 01/15/2018
  • rate of 0.12 (12%)
  • a yield of 0.10 (10%)
  • redemption of 100 monetary units
  • frequency of 1 (annual)
  • basis of 4 (European 30/360)
104.97

This example shows a PRICE calculation that does not specify a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 4)

The example has a:

  • settlement date of 01/15/2015
  • maturity date of 01/15/2018
  • rate of 0.12 (12%)
  • a yield of 0.10 (10%)
  • redemption of 100 monetary units
  • frequency of 1 (annual)
105.13
PV

For example, you can use the PV function to calculate the amount you can borrow for a loan, or the amount you need to invest to achieve a financial goal.

Syntax

PV(Interest rate, Number of periods, Payments, Future value, Payment timing)

Arguments

ArgumentData typeDescription
Interest rate (required)NumberThe interest rate per period.
Number of periods (required)NumberThe total number of periods.
Payments (required)NumberThe amount paid each period.
Future value (required)Number

The future value of the investment or loan.

For a loan, the future value is 0.

Payment timing (required)Number

Determines whether each payment is made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it.

You can enter a value of 0 or 1 for this argument. If you enter:

  • 0, payments are made at the start of each period
  • 1, payments are made at the end of each period

The PV function returns a number.

Additional information

Positive and negative values

For any values you give the PV function via an argument, or that the function returns:

  • a positive value represents money you receive, such as a dividend or loan.
  • a negative value represents money paid, such as a deposit or interest payment.

Consistent time periods

You must use the same time periods for the Interest rate, Number of periods, and Payments arguments. For example, an investment might over 3 years. In this case, there are 36 monthly periods, and you should divide the annual interest rate by 12. Additionally, any payment amounts should also be monthly.

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent function

PV(opens external page)

Examples

Loan example

In this example, a module has the Mortgages list on columns, and line items on rows. The Interest rate line item uses the Percentage format.

The formula uses the PV function to calculate the amount a customer can borrow for each mortgage. The interest rate is divided by 12 to reflect monthly payments at the end of each period.


Mortgage 1Mortgage 2
Interest rate5%5%
Number of periods360300
Payments1000550

Principal loan value

PV(Interest rate / 12, Number of periods, Payments, 0, 1)

-$187,057.79-$94,475.04

Investment example

In this example, a module has the Customers list on columns, and line items on rows. The Interest rate line item uses the Percentage format.

The formula uses the PV function to calculate the required investment amount for each customer. For example, if a customer wants to save $50,000 over 10 years at an interest rate of 5%, they need to initially invest $30,358. 


Customer 1Customer 2
Interest rate5%7.5%
Number of periods12036
Future value$50,000$10,000

Present investment value

PV(Interest rate / 12, Number of periods, 0, Future value, 0)

-$30,358-$7,991
RATE

For example, you can use the RATE function to calculate the monthly interest rate for a loan.

Syntax

RATE(Number of periods, Payments, Present value[, Future value] [, Payment timing] [ ,Rate estimate])

Arguments

ArgumentData typeDescription
Number of periods (required)NumberThe number of periods that the interest rate is applied to.
Payments (required)NumberThe amount paid into the investment each period.
Present value (required)NumberThe present value of the investment.
Future valueNumberThe future value of the investment.
Payment timingNumber

Determines whether each payment is made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it.

You can enter a value of 0 or 1 for this argument. If you enter:

  • 0, payments are made at the start of each period
  • 1, payments are made at the end of each period
Rate estimateNumberThe estimated interest rate.

The RATE function returns a number.

Additional information

For any values you give the RATE function via an argument, or that the function returns:

  • a positive value represents money you receive, such as a dividend or loan.
  • a negative value represents money paid, such as a deposit or interest payment.

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Excel equivalent function

RATE(opens external page)

Examples

For example, an Interest rates module has the Contracts list on columns and line items on rows. The module shows different loan amounts and monthly payments for two customer contracts.

The formula in the Monthly interest rate line item calculates the periodic interest rate for each contract. 


Contract 1Contract 2
Loan amount$5,000$7,500
Monthly payment-$95.00-$135.00
Number of periods6060

Monthly interest rate

RATE(Number of periods, -Monthly payment, Loan amount)

0.440039%0.255868%

In this example, the formula in the Annual interest rate line item calculates the yearly interest rate for each contract. 


Contract 1Contract 2
Loan amount$5,000$7,500
Monthly payment-$95.00-$135.00
Number of periods6060

Annual interest rate

RATE(Number of periods, -Monthly payment, Loan amount) * 12

5.28047%3.07042%

In this example, the formula calculates the required interest rates for two investments to return a future value.


Contract 1Contract 2
Number of periods1236
Payments00
Current value-$5,000-$7,500
Future value$15,000$25,000

Interest rate

RATE(Number of periods, Payments, Current value, Future value)

9.58727%3.40092%
YEARFRAC

Syntax

YEARFRAC(Start, End[, Basis])

Arguments

ArgumentData typeDescription
Start (required)DateThe bond settlement date: The date the bond is traded to the buyer.
End (required)DateThe bond maturity date: The date when the bond expires.
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The YEARFRAC function returns a number.

Constraints

  • The start and end dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Excel equivalent

YEARFRAC

Examples

This example shows a YEARFRAC calculation that includes a basis.

FormulaDescriptionResult
YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30), 1)

This formula uses:

  • a start date of as 01/15/2015
  • an end date of 04/30/2018
  • a basis of 1 (Actual/Actual)
3.28767123

This example shows a YEARFRAC calculation does not use a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30))

This formula uses:

  • a start date of 01/15/2015
  • an end date of 04/30/2018
3.29166666
YIELD

The YTM is the overall rate of interest that, when used to discount the bond's future cashflows, produces the given price.

When interest rates rise, bond prices fall, so it is important to know the yield when calculating the price of a bond.

As yield is used in the formula for calculating price, you can determine the value of the yield using an iterative solution.

This is the formula for price:

Price=tT1CashFlowst(1+Yield)t Price = \sum_{t}^{T-1} \frac {CashFlows_{t}} {(1+Yield)^t}


Where T is the total number of coupon periods.

Syntax

YIELD(Settlement, Maturity, Rate, Price, Redemption, Frequency [, Basis])

Arguments

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Rate (required)NumberThe bond annual coupon date.
Price (required)NumberThe bond price per 100 monetary units, face value.
Redemption (required)NumberThe payment received when the bond reaches maturity.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The YIELD function returns a number.

Additional information

If the price and redemption parameters are very far apart, the iterative method of calculation might never converge on a result. In this case, NaN (not a number) is returned.

Constraints

  • The settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399.
  • The maturity date must be later than the settlement date.
  • The price must be greater than zero.
  • The redemption must be greater than zero.
  • The basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Calculation engine functionality differences

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Examples

This example shows a YIELD calculation that specifies a basis.

FormulaDescriptionResult
YIELD(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 90, 100, 1, 4)

The example has a:

  • settlement date of 01/15/2018
  • maturity date of 01/15/2021
  • rate of 0.12 (12%)
  • price of 90 monetary units
  • redemption of 100 monetary units
  • frequency of 1 (annual)
  • basis of 4 (European 30/360)
0.1648 or 16.5%.

In this example the yield is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
YIELD(DATE(2018, 1, 15), DATE(2021, 1, 15), 0.12, 90, 100, 4)

Here:

  • the settlement date is 01/15/2018
  • the maturity date is 01/15/2021
  • the rate is 0.12 (12%)
  • the price is 90 monetary units
  • the redemption is 100 monetary units
  • the frequency is 4 (quarterly)
0.1627 or 16.3%

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.