Dashpivot supports Excel-style financial functions inside Default Tables and Prefilled Tables, allowing you to streamline budgeting, forecasting, and project cost tracking.
Note: Financial formulas must be used within Default or Prefilled Tables.
DOLLARDE()
Purpose: Converts a price entered in fractional notation into a decimal value.
Formula to use:
=DOLLARDE(A1,B1)
Example:
If A1 = 10.375
and B1 = 8
=DOLLARDE(A1,B1) returns 10.46875
Note:
B1 represents the number of fractional parts in a whole dollar (e.g. 16 = sixteenths).
DOLLARFR()
Purpose: Converts a decimal price into fractional notation.
Formula to use:
=DOLLARFR(A1,B1)
Example:
If A1 = 10.46875
and B1 = 8
=DOLLARFR(A1,B1) returns 10.375
ISPMT()
Purpose: Returns the interest paid during a specific period for loans with equal principal payments.
Formula to use:
=ISPMT(A1,B1,C1,D1)
Example:
If:
A1 = 0.05/12 (Monthly interest rate)
B1 = 1 (Period number)
C1 = 60 (Total periods)
D1 = 20000 (Loan value)
=ISPMT(A1,B1,C1,D1) returns the interest amount for period 1.
NOMINAL()
Purpose: Returns the nominal annual interest rate from an effective annual rate.
Formula to use:
=NOMINAL(A1,B1)
Example:
If A1 = 0.0512 (Effective annual rate 5.12%)
and B1 = 12 (Monthly compounding)
=NOMINAL(A1,B1) returns 0.05 (5%)
If the rate is entered as a percentage value:
=NOMINAL(A1/100,B1)
NPER()
Purpose: Returns the number of periods required for a loan or investment.
Formula to use:
=NPER(A1,B1,C1,D1,0)
Example:
If:
A1 = 0.05/12
B1 = -300 (Monthly payment)
C1 = 10000 (Present value)
D1 = 0
=NPER(A1,B1,C1,D1,0) returns the number of months required.
Notes:
Divide annual rate by 12 for monthly periods
Type = 0 (end of period, default)
Type = 1 (beginning of period)
NPV()
Purpose: Returns the net present value of a series of cash flows.
Formula to use:
=NPV(A1,A2:A6)
Example:
If:
A1 = 0.10 (Discount rate)
A2 = 3000
A3 = 4000
A4 = 3500
A5 = 4500
A6 = 5000
=NPV(A1,A2:A6) returns the present value of future cash flows.
To include the initial investment separately:
=NPV(A1,A3:A6) + A2
(Where A2 is the initial negative investment.)
PDURATION()
Purpose: Returns the number of periods required for an investment to reach a specified future value.
Formula to use:
=PDURATION(A1,B1,C1)
Example:
If:
A1 = 0.08 (8% growth rate)
B1 = 10000 (Present value)
C1 = 20000 (Future value)
=PDURATION(A1,B1,C1) returns the number of periods required to double the investment.
SLN()
Purpose: Calculates straight-line depreciation of an asset.
Formula to use:
=SLN(A1,B1,C1)
Example:
If:
A1 = 50000 (Cost)
B1 = 5000 (Salvage value)
C1 = 10 (Useful life in years)
=SLN(A1,B1,C1) returns 4500
(Annual depreciation amount.)
XNPV()
Purpose: Returns the net present value for irregular cash flow dates.
Formula to use:
=XNPV(A1,B1:B5,C1:C5)
Example:
If:
A1 = 0.10 (Discount rate)
B1 = -10000
B2 = 3000
B3 = 4000
B4 = 3500
B5 = 4500
C1 = 01/01/2024
C2 = 01/06/2024
C3 = 01/01/2025
C4 = 01/06/2025
C5 = 01/01/2026
=XNPV(A1,B1:B5,C1:C5) returns the discounted present value of irregular cash flows.
Notes:
The first payment is typically the initial investment (negative value)
Each date must correspond to each payment
Using financial formulas in Dashpivot helps automate loan calculations, investment analysis, depreciation tracking, and project cost forecasting.