Tutorial File Budget Formulas For Multi-Currency Financial Planning

The possibility to enter formulas is available in Banana Accounting Plus only with the Advanced plan. Update now!

This example will explain how to use formulas specific to double-entry bookkeeping with multi-currency functionalities.

Documentation on use of formula in the Budget table.

• Documentation on use of formula in the Budget table.
• Examples:
  • Family with budget – Income & Expenses.
  • Financial Planning Start-up (Double-entry accounting).
  • Examples for basic use of formulas.

Amount calculation in base currency

The formula will calculate the value in the account's currency first and then the relative value in the base currency.

Exchange rates and historical rates

• Doc 10 and 12.
  If an exchange rate is indicated in the exchange rate column, the program will calculate the amount in the base currency (EUR in this case) using the exchange rate indicated in this row.
• Doc 11 and 13.

  If there is no exchange rate indicated, the program will apply the exchange rate in the Exchange rates table for this calculation as well as for the successive operation in the case a Repeat function is applied.

• If historical exchange rates are defined in the Exchange rates table, via adding a date, the program will apply the exchange relative to the date or the previous, most recent one.

Use of formulas in the base currency

All formulas available in the Budget table can be used in the Formula column.

The following examples show the value in the base currency (EUR) calculated according to a Formula.

• Doc 21.
  The sales value is multiplied according to the Formula EUR value. The exchange rate in the corresponding column will be ignored.
• Doc 22.
  As in the previous example, but for the value of "AmountCurrency" which is the result of the formula.
• Doc 23.
  In the “budget.js” document we have defined an exchange rate function calculating the value on the basis of the contents in the row.
  With these functions it is possible to create exchange rate simulations.
   

Example for Exchange rate function

The USD exchange rate is established on a monthly basis.

A constant exchange rate is used for GBP instead.

The function will result as the amount of AmountCurrency multiplied by the exchange rate.

  
  function cambio() {
    var importo = row.value("AmountCurrency");
    var cambio = 1;
    var moneta = row.value("ExchangeCurrency");
    var data = Banana.Converter.toDate(row.value("JDate"));
    var mese = data.getMonth() + 1;
    if (moneta == "USD") {
      cambio = 0.95;
      if (mese == 1)
      cambio = 0.96;
    else if (mese == 2)
      cambio = 0.97;
    }
    if (moneta == "GBP") {
    cambio = 1.30;
    }
    return importo * cambio;
  }
  

Formulas for calculation of exchange rate differences

For further information on calculation of exchange rate differences please refer to information about transactions in multi currency and to the Create exchange rate differences command.

• The currency is always the base currency.
• Amounts must not be entered in the account's currency.
• Exchange rate is left blank and will be ignored, even if a value is entered.
• The budgetExchangeDifference function calculates the exchange rate difference for an account. This value represents the non realized exchange rate profit or loss.
  .
  • The function will return a positive value if it is an exchange rate loss and a negative (credit) in case of exchange rate profit.

    Exchange rate profit and exchange rate loss need to be entered in two different rows.

  • The resulting amount represents the revaluation necessary in the base currency to ensure that the account balance in the base currency matches the balance of the account in the currency converted at the exchange rate used for the calculation of exchange rate differences.

• The first parameter "CUSD" of the budgetExchangeDifferences function is the account in foreign currency for which the exchange rate difference will be calculated.
• The second parameter is the exchange rate to be applied.
  • If no exchange rate is indicated, the historical exchange rate relative to the date of the entered transaction is applied.
  • When "current" is used, the exchange in the Exchange rate tables with no date will be used.
  • An exchange my also be indicated. A point "." must be used as a decimal separator.

  
  credit(budgetExchangeDifference("CUSD"))
  credit(budgetExchangeDifference("CUSD", "current"))
  credit(budgetExchangeDifference("CUSD", "0.95"))