Function - Finance

Perform Financial Calculations on Records like determining Present Value of investments, Future Value, Interest Payments, Payments, Principal Payments, Rate, Net Present Value, and Internal Rate of Return

🔴 On2Air Actions has been shutdown to focus on our On2Air Backups Airtable app Learn more about automated Airtable backups - on2air.com

Function Type:

Record Processing Functions

Finance Functions Available

These functions emulate the same formulas as those found in Excel and should result in similar answers.

Present Value - Returns the present value of an investment, or the total amount that a series of future payments is worth now.
Future Value - Returns the future value of an investment based on periodic, equal, payments and a constant interest rate.
Payment - Calculates the payment for a loan based on a constant stream of equal payments and a constant interest rate.
Interest Payment - Returns the calculated interest portion of a payment for a specific period based on a constant stream of equal payments and a constant interest rate.
Principal Payment - Returns the calculated principal portion of a payment for a specific period based on a constant stream of equal payments and a constant interest rate.
Rate - Returns the interest rate per period for a loan or investment.
Net Present Value - Returns the net present value of an investment based on a constant rate of return and a series of future payments/investments (as negative values) and income/return (as positive values).
Internal Rate of Return - Returns the internal rate of return for a series of cash flows. NOTE: There must be at least one negative and one positive value as part of the cash flow. Cash flows are assumed to be due in the same order they are arranged in the cash flow values array.

In this article

Finance Functions Available
Finance Functions Configuration Video
General Function Configuration
Function Configuration for each option
Present Value
Future Value
Payment
Payment
Interest Payment
Principal Payment
Rate
Net Present Value
Internal Rate of Return
Test Your Function
Install Your Function

Finance Functions Configuration Video

General Function Configuration

Click Create New Function ✔
Choose your Function
Click Add Configuration
Set up your
Common Function Configurations
- Source Connection, Source Configuration, and Trigger Mode
Set up your Function Configuration

Set up your Function Configuration for your desired Finance Function Option

(Using the Internal Rate of Return option in this example)

Function Configuration for each option

Present Value

Returns the present value of an investment, or the total amount that a series of future payments is worth now.

Select Rate Field - Choose the field containing the rate for the period
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Payment Field **- Choose the field containing the payment amount
Negate Value - Auto convert payment input to negative value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Manage Format Styles - You can configure your format styles to be used on each field. See full formatting documentation IMPORTANT - These are configured across ALL functions. Be careful when configuring or deleting a format style if it's used in another function.

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Future Value

Returns the future value of an investment based on periodic, equal, payments and a constant interest rate.

Select Rate Field - Choose the field containing the rate for the period
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Payment Field **- Choose the field containing the payment amount
Negate Value - Auto convert payment input to negative value
*Select Present Value Field **(optional)- Choose the field containing the present value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Payment

Calculates the payment for a loan based on a constant stream of equal payments and a constant interest rate.

Select Rate Field - Choose the field containing the rate for the period
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Present Value Field **(optional)- Choose the field containing the present value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Payment

Calculates the payment for a loan based on a constant stream of equal payments and a constant interest rate.

Select Rate Field - Choose the field containing the rate for the period
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Present Value Field **(optional)- Choose the field containing the present value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Interest Payment

Returns the calculated interest portion of a payment for a specific period based on a constant stream of equal payments and a constant interest rate.

Select Rate Field - Choose the field containing the rate for the period
Select PER Field - Choose a specific numeric period
Default Value - Enter a value here instead of using a field value
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Present Value Field **(optional)- Choose the field containing the present value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Principal Payment

Returns the calculated principal portion of a payment for a specific period based on a constant stream of equal payments and a constant interest rate.

Select Rate Field - Choose the field containing the rate for the period
Select PER Field - Choose a specific numeric period
Default Value - Enter a value here instead of using a field value
Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
*Select Present Value Field **(optional)- Choose the field containing the present value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Rate

Returns the interest rate per period for a loan or investment.

Select NPER Field - Choose the field containing the total number of periods See NPER function documentation
Default Value - Enter a value here instead of using a field value
Select Payment Field - Choose the field containing the payment amount
Negate Value - Auto convert payment input to negative value
*Select Present Value Field **(optional)- Choose the field containing the present value
*Select Future Value Field **(optional)- Choose the field containing the future value
Select Type - Choose the Payment Due Type Options: End of period (Arrears) or Beginning of Period (Advance)
Select Guess Field (optional) - Choose the field containing an optional guess at the rate
Default Value - Enter a value here instead of using a field value
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Net Present Value

Returns the net present value of an investment based on a constant rate of return and a series of future payments/investments (as negative values) and income/return (as positive values).

Select Rate Field - Choose the field containing the rate for the period
Cash Flow Values- Choose the field(s) to be used as cash flow inputs
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Internal Rate of Return

Returns the internal rate of return for a series of cash flows. NOTE: There must be at least one negative and one positive value as part of the cash flow. Cash flows are assumed to be due in the same order they are arranged in the cash flow values array.

Cash Flow Values- Choose the field(s) to be used as cash flow inputs
Select Guess Field (optional) - Choose the field containing an optional guess at the rate
Default Value - Enter a value here instead of using a field value
Select Output Raw Field - Select the field where you want to output the raw result
*Select Output Formatted Field **- Select the field where you want to output the formatted result
Output Format Style - Choose your format style

Options:

Standard
Currency
Decimal
Ft Units
Percent
Or your own custom format

Manage Format Styles - You can configure your format styles to be used on each field. See full formatting documentation

IMPORTANT - These are configured across ALL functions. Be careful when configuring or deleting a format style if it's used in another function.

Choose the green icon to edit the format style. Choose the yellow icon to delete the style. Click Save Styles.
Next, choose any Advanced Settings you need

Input Separator - Add an input separator between text elements. This will add a separation value to any field data.

You can use any character, such as a comma (,) or dash (-). If you use \n, you can add a new line in your text field. You can also use HTML.

Last Updated - Choose a date field to keep track of the last time the field was updated

Click Save Action Function Details
Then, Test and Install Your Function

Test Your Function

Next,
Test Your Function
✔

Before using your Function, you must test it.

Install Your Function

Then,
Install Your Function in Your Base
✔ via a Schedule (recommended), a Zapier zap, an Airtable Automation, a Script in the Scripting app, or directly via REST API.

All Action Functions

Go to

On2Air Help Center