Financial functions - Anaplan Technical Documentation

Use financial functions to perform a range of financial calculations.

There are several general financial functions in Anaplan. These include CUMIPMT, CUMPRINC, FV, IPMT, IRR, NPER, NPV, PMT, PPMT, PV, and RATE.

Anaplan also offers investment management functions. These include COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, DURATION, MDURATION, PRICE, YEARFRAC, 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.

Title	Description
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=\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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The 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
Basis	Number	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

Related Anaplan functions

Examples

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

Formula Description Result

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.

Formula Description Result

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)

COUPDAYS

Syntax

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

Arguments

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The 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
Basis	Number	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

Related Anaplan functions

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.

Formula Description Result

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.

Formula Description Result

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)

COUPDAYSNC

Syntax

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

Arguments

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The 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
Basis	Number	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

Related Anaplan functions

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.

Formula Description Result

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)

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.

Formula Description Result

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)

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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date, when the bond is traded to the buyer.
Maturity (required)	Date	The 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

Related Anaplan functions

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.

Formula Description Result

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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date, when the bond is traded to the buyer.
Maturity (required)	Date	The 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

Related Anaplan functions

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.

Formula Description Result

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.

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.

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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date, when the bond is traded to the buyer.
Maturity (required)	Date	The 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)

Related Anaplan functions

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.

Formula Description Result

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

Argument	Data type	Description
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)	Number	The number of periods that the Principal is paid over.
Loan balance (required)	Number	The total amount to be paid between the Start period and End period.
Start period (required)	Number	The period to start calculating cumulative interest from.
End period (required)	Number	The period to stop calculating cumulative interest at.
Timing	Number	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

Related Anaplan functions

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 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5	Scenario 6
Interest rate	2.5%	2.5%	2.5%	2.5%	2.5%	2.5%
Number of periods	30	30	30	30	30	30
Loan Balance	300,000	300,000	300,000	300,000	300,000	300,000
Start period	1	1	30	1	15	1
End period	1	1	30	15	30	30
Timing	0	1	0	0	0	0
Cumulative interest paid `CUMIPMT(Interest rate, Number of periods, Loan balance, Start period, End period, Timing)`	-7,500	0	-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

Argument	Data type	Description
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)	Number	The number of periods that the Loan balance is paid over.
Loan balance (required)	Number	The total amount to be paid between the Start period and End period.
Start period (required)	Number	The period to start calculating the principal paid from.
End period (required)	Number	The period to stop calculating the principal paid at.
Timing	Number	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 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

Related Anaplan functions

CUMIPMT
IPMT

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 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5	Scenario 6
Interest rate	2.5%	2.5%	2.5%	2.5%	2.5%	2.5%
Number of periods	30	30	30	30	30	30
Loan balance	300,000	300,000	300,000	300,000	300,000	300,000
Start period	1	1	30	1	15	1
End period	1	1	30	15	30	30
Timing	0	1	0	0	0	0
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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The bond maturity date: The date when the bond expires.
Rate (required)	Number	The bond annual coupon date.
Yield (required)	Number	The bond annual yield.
Frequency (required)	Number	The number of coupon payments per year. Enter: 1 for annual 2 for semi-annual 4 for quarterly
Basis	Number	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 = \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

Related Anaplan functions

Examples

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

Formula Description Result

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.

Formula Description Result

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

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

Argument	Data type	Description
Interest rate (required)	Number	The interest rate per period.
Number of periods (required)	Number	The number of periods that the investment is held for, and that the interest rate applies to.
Payments (required)	Number	The amount paid into the investment each period.
Present value	Number	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 timing	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 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 rate, Number 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

Related Anaplan functions

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 1	Contract 2	Contract 3	Contract 4	Contract 5
Interest rate	0.4000%	0.7974%	0.7974%	0.4000%	0.4000%
Number of periods	24	12	12	12	12
Payments	300	0	300	300	300
Present value	-10,000	-10,000	-10,000	-10,000	-10,000
Payment timing	0	0	0	0	1
Future value `FV(Interest rate, Number of periods, Payments, Present value, Payment timing)`	3,464	11,000	7,238	6,810	6,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

Argument	Data type	Description
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)	Number	The period to return the amount of interest due for.
Number of periods (required)	Number	The number of periods that the Present value is paid over.
Present value (required)	Number	The total amount to be paid over the Number of periods.
Future value	Number	The amount of loan left after the final payment is made. If omitted, this assumed to be 0.
Payment timing	Number	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

Related Anaplan functions

PMT
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 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 1	Loan 2	Loan 3	Loan 4	Loan 5
Present value	70,000	330,000	500,000	500,000	500,000
Interest rate	5.5%	3.5%	1.44%	1.44%	1.44%
Period to examine	1	1	10	10	10
Number of periods	6	25	25	25	25
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 value	0	30,000	0	10,000	10,000
Timing	0	0	0	0	1
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

Argument

Data type

Description

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.

Estimate

Number

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

Argument	Data type	Description
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)	Date	The date associated with each value of the Cash flow argument.
Transactions (required)	List	A list of transactions, which must be a common dimension of the Cash flow and Dates argument.
Estimate of rate	Number	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 = \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
P_i is the payment in the i^th period
d_i 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

IRR
XIRR

Related Anaplan functions

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.

	FY20	FY21	FY22
Cash flow	-100,000	0	130,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.

	Description	Cash flow	Date
Transaction 01	Land purchase	-100,000	1/1/2011
Transaction 02	Turbine purchase	-850,000	1/1/2012
Transaction 03	Energy generation revenue	200,000	1/3/2013
Transaction 04	Energy generation revenue	200,000	1/3/2014
Transaction 05	Energy generation revenue	200,000	1/3/2015
Transaction 06	Energy generation revenue	200,000	1/3/2016
Transaction 07	Energy generation revenue	200,000	1/3/2017
Transaction 08	Energy generation revenue	200,000	1/3/2018
Transaction 09	Energy generation revenue	200,000	1/3/2019
Transaction 10	Energy generation revenue	200,000	1/3/2020
Transaction 11	Energy generation revenue	200,000	1/3/2021
Transaction 12	Energy generation revenue	200,000	1/3/2022
Transaction 13	Energy generation revenue	200,000	1/3/2023
Transaction 14	Energy generation revenue	200,000	1/3/2024
Transaction 15	Energy generation revenue	200,000	1/3/2025
Transaction 16	Final energy generation revenue	200,000	1/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 1 Plant 2 Plant 3 Plant 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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The bond maturity date: The date when the bond expires.
Rate (required)	Number	The bond annual coupon date.
Yield (required)	Number	The bond annual yield.
Frequency (required)	Number	The number of coupon payments per year. Enter: 1 for annual 2 for semi-annual 4 for quarterly
Basis	Number	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 = \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

Related Anaplan functions

Examples

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

Formula Description Result

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.

Formula Description Result

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

Argument	Data type	Description
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)	Number	The present value of the instrument, or initial investment.
Residual value	Number	The amount of loan left after the final payment is made. If omitted, this is assumed to be 0.
Timing	Number	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

Related Anaplan functions

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 1	Contract 2	Contract 3	Contract 4	Contract 5
Interest rate	4%	4%	8%	1%	15%
Payments	300	300	300	1,500	-5,000
Present value	10,000	10,000	10,000	-50,000	-50,000
Residual value	0	0	0	25,000	0
Timing	0	1	0	0	0
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

Argument	Data type	Description
Discount rate (required)	Number	The discount rate used to calculate net present value. This argument uses percentage format, so 0.1 is equivalent to 10%.
Cash flow (required)	Number	A series of positive and negative values that represent cash inflow and outflow.
Dates	Date	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.
Transactions	List	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 = \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
P_i is the payment in the i^th period
d_i 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

Related Anaplan functions

Examples

Example of NPV with timescale

	FY20	FY21	FY22
Cash flow	-100,000	0	130,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 rate 10%

NPV of annual cash flow

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

7,438.02

Example of NPV with dates

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

	Description	Cash flow	Date
Transaction 01	Land purchase	-100,000	1/1/2011
Transaction 02	Turbine purchase	-850,000	1/1/2012
Transaction 03	Energy generation revenue	200,000	1/3/2013
Transaction 04	Energy generation revenue	200,000	1/3/2014
Transaction 05	Energy generation revenue	200,000	1/3/2015
Transaction 06	Energy generation revenue	200,000	1/3/2016
Transaction 07	Energy generation revenue	200,000	1/3/2017
Transaction 08	Energy generation revenue	200,000	1/3/2018
Transaction 09	Energy generation revenue	200,000	1/3/2019
Transaction 10	Energy generation revenue	200,000	1/3/2020
Transaction 11	Energy generation revenue	200,000	1/3/2021
Transaction 12	Energy generation revenue	200,000	1/3/2022
Transaction 13	Energy generation revenue	200,000	1/3/2023
Transaction 14	Energy generation revenue	200,000	1/3/2024
Transaction 15	Energy generation revenue	200,000	1/3/2025
Transaction 16	Final energy generation revenue	200,000	1/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 1 Plant 2 Plant 3 Plant 4

Net Present Value for Plant

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

445,464

325,849

194,035

86,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

Argument	Data type	Description
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)	Number	The number of periods that the loan is paid over.
Present value (required)	Number	The present value of the instrument, or initial investment.
Future value	Number	The amount of loan left after the final payment is made. If omitted, this assumed to be 0.
Timing	Number	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

Related Anaplan functions

IPMT
PPMT

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 1	Contract 2	Contract 3	Contract 4	Contract 5
Interest rate	8.00%	8.00%	5.00%	3.50%	0.10%
Number of periods	10	10	25	4	8
Present value	-10,000	-10,000	-250,000	-5,000	-1,000
Future value	0	0	0	0	4,000
Timing	0	1	0	0	1
Payment due each period `PMT(Interest rate, Number of periods, Present value, Future value, Timing)`	1,490.29	1,379.90	17,738.11	1,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

Argument	Data type	Description
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)	Number	The period to return the amount paid towards the principal for.
Number of periods (required)	Number	The number of periods that the loan is paid over.
Present value (required)	Number	The principal amount of the loan.
Future value	Number	The amount of loan left after the final payment is made. If omitted, this is assumed to be 0.
Timing	Number	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.

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

Related Anaplan functions

IPMT
PMT

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 1	Loan 2	Loan 3	Loan 4	Loan 5
Present value	70,000	330,000	500,000	500,000	500,000
Interest rate	5.5%	3.5%	1.44%	1.44%	1.44%
Period to examine	1	1	10	10	10
Number of periods	6	25	25	25	25
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 value	0	30,000	0	10,000	10,000
Timing	0	0	0	0	1
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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The bond maturity date: The date when the bond expires.
Rate (required)	Number	The bond annual coupon date.
Yield (required)	Number	The bond annual yield.
Redemption (required)	Number	The 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
Basis	Number	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 = \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 = E – A

Price= \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 = \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

Related Anaplan functions

YIELD

Examples

This example shows a PRICE calculation that specifies a basis.

Formula Description Result

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.

Formula Description Result

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

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

Argument	Data type	Description
Interest rate (required)	Number	The interest rate per period.
Number of periods (required)	Number	The total number of periods.
Payments (required)	Number	The 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)

Related Anaplan functions

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 1	Mortgage 2
Interest rate	5%	5%
Number of periods	360	300
Payments	1000	550
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 1	Customer 2
Interest rate	5%	7.5%
Number of periods	120	36
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

Argument	Data type	Description
Number of periods (required)	Number	The number of periods that the interest rate is applied to.
Payments (required)	Number	The amount paid into the investment each period.
Present value (required)	Number	The present value of the investment.
Future value	Number	The future value of the investment.
Payment timing	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
Rate estimate	Number	The 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)

Related Anaplan functions

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 1	Contract 2
Loan amount	$5,000	$7,500
Monthly payment	-$95.00	-$135.00
Number of periods	60	60
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 1	Contract 2
Loan amount	$5,000	$7,500
Monthly payment	-$95.00	-$135.00
Number of periods	60	60
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 1	Contract 2
Number of periods	12	36
Payments	0	0
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

Argument	Data type	Description
Start (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
End (required)	Date	The bond maturity date: The date when the bond expires.
Basis	Number	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

Related Anaplan functions

Examples

This example shows a YEARFRAC calculation that includes a basis.

Formula Description Result

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.

Formula

Description

Result

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 = \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

Argument	Data type	Description
Settlement (required)	Date	The bond settlement date: The date the bond is traded to the buyer.
Maturity (required)	Date	The bond maturity date: The date when the bond expires.
Rate (required)	Number	The bond annual coupon date.
Price (required)	Number	The bond price per 100 monetary units, face value.
Redemption (required)	Number	The 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
Basis	Number	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.

Excel equivalent

YIELD(opens external page)

Related Anaplan functions

Examples

This example shows a YIELD calculation that specifies a basis.

Formula Description Result

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.

Formula Description Result

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%