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.
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:
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.
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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
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:
| 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:
| 75 |
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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
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:
| 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:
| 90 |
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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
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:
| 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:
| 15 |
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:
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
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 |
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:
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
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 |
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:
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
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 |
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:
|
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
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
| -7,500 | 0 | -349.59 | -92,465.29 | -42,211.51 | -129,998.77 |
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:
|
The CUMPRINC function returns a number, which is the amount of principal paid for the loan.
Additional information
Periods used with the CUMPRINC function
The Number of periods argument enables you to specify the number of periods the loan is paid over. The Start period and End period arguments enable you to specify what range of periods to calculate the cumulative interest for.
The value you provide for the Interest rate argument must match the Number of periods arguments. For example, if the periods are years, you should provide the annual interest rate.
Positive and negative values
For any values you give the CUMPRINC function via an argument, or that the function returns:
- a positive value represents money you receive, such as a dividend or loan
- a negative value represents money paid, such as a deposit or interest payment
Excel equivalent
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
| -6,833.29 | -13,983.70 | -13,983.70 | -122,534.09 | -187,121.17 | -300,000 |
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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:
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
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:
| 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:
| 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:
|
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
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
| 3,464 | 11,000 | 7,238 | 6,810 | 6,796 |
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
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
| -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
| -3,850 | -8,195.87 | -4,898.82 | -4,852.79 | -4,783.90 |
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:
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
| 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
| 18.1% | 12.2% | 13.8% | 9.2% |
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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:
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
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:
| 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:
| 2.5131792 |
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
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
| -21.6 | -21.04 | -16.88 | -13.14 | -6.556 |
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:
In this equation:
- N is the number of payments in and out over the time scale
- Pi is the payment in the ith period
- di is the number of days from the start of the first period
- Rate is the discount rate
Use NPV with the Users list
You can reference the Users list with the NPV function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.
Excel equivalent
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
| 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
| 445,464 | 325,849 | 194,035 | 86,659 |
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
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
| 1,490.29 | 1,379.90 | 17,738.11 | 1,361.26 | -348.59 |
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.
The values you provide for the Interest rate, Period to examine, and Number of periods arguments should all use the same time scale. For example, if the periods are years, you should provide the annual interest rate.
Positive and negative values
For any values you give for the Present value or Future value arguments, or that the PPMT function returns:
- A positive value represents money you receive, such as a dividend or loan.
- A negative value represents money paid, such as a deposit or interest payment.
Excel equivalent
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
| -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
| -10,162.53 | -12,596.79 | -19,058.85 | -19,440.02 | -19,164.06 |
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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:
For a bond that pays one coupon between settlement and maturity, the price is calculated with this formula:
Where bonds have a zero rate and do not pay a coupon, the price is calculated with this formula:
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
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:
| 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:
| 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:
|
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
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
| -$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
| -$30,358 | -$7,991 |
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:
|
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
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
| 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
| 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
| 9.58727% | 3.40092% |
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:
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:
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
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:
| 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:
| 3.29166666 |
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:
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:
|
Basis | Number | The basis determines how many days exist in a year. A full year has:
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:
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
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:
| 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:
| 0.1627 or 16.3% |