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

COUPDAYBS

## 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) 75

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.

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.

 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) 90

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.

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.

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.

 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) 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.

 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) 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

 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.

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.

 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.

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.

 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. 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

 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)

## 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.

### 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.

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 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.

### 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

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 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.

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).

DURATION

## 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
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

 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.

### 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.

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 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.

### 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.

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 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.

### 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
• 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.

## 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.

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.

MDURATION

## 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.

### 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.

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 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.

### 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
• 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.

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.

 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

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 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.

### 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))

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 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.

### 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.

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 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.

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.

PRICE

## 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
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

 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.

### 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 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.

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 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).

YEARFRAC

## 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.

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)

## 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%

Disclaimer

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