Financial Functions in LibreOffice Calc

LibreOffice Calc includes a comprehensive suite of built-in financial functions designed for cash flow analysis, investment evaluation, loan amortization, and asset depreciation. This guide outlines the most commonly used financial formulas available in Calc—specifically focusing on metrics like Net Present Value (NPV) and Internal Rate of Return (IRR)—along with their syntax and standard use cases.

Investment Evaluation Functions

LibreOffice Calc provides several functions to analyze profitability, discounted cash flows, and returns on investment for both regular and irregular schedules.

• NPV (Net Present Value): Calculates the net present value of an investment using a discount rate and a series of future periodic payments (negative values) and income (positive values).
  • Syntax: =NPV(Rate; Value1; Value2; ...)
• IRR (Internal Rate of Return): Calculates the internal rate of return for a series of periodic cash flows generated by an investment.
  • Syntax: =IRR(Values; Guess)
• XNPV: Computes the net present value for an investment with cash flows that occur on specific, irregular dates rather than at regular intervals.
  • Syntax: =XNPV(Rate; Values; Dates)
• XIRR: Calculates the internal rate of return for a series of non-periodic cash flows occurring at specific dates.
  • Syntax: =XIRR(Values; Dates; Guess)
• MIRR (Modified Internal Rate of Return): Calculates the internal rate of return where positive and negative cash flows are financed at different interest rates (finance rate vs. reinvestment rate).
  • Syntax: =MIRR(Values; InvestmentRate; ReinvestRate)

Loan and Annuity Functions

These functions calculate interest, principal, and total payments across various compounding schedules.

• PMT: Returns the periodic payment amount for an annuity or loan based on constant payments and a fixed interest rate.
  • Syntax: =PMT(Rate; NPer; PV; [FV]; [Type])
• PPMT & IPMT:
  • PPMT calculates the principal portion of a specific loan payment.
  • IPMT calculates the interest portion of a specific loan payment.
• PV (Present Value): Calculates the current total value of a series of future periodic payments or a lump-sum amount.
  • Syntax: =PV(Rate; NPer; PMT; [FV]; [Type])
• FV (Future Value): Determines the future value of an investment based on periodic, constant payments and a constant interest rate.
  • Syntax: =FV(Rate; NPer; PMT; [PV]; [Type])
• NPER: Returns the total number of payment periods for an investment or loan.
  • Syntax: =NPER(Rate; PMT; PV; [FV]; [Type])
• RATE: Computes the interest rate per period for an annuity or loan.
  • Syntax: =RATE(NPer; PMT; PV; [FV]; [Type]; [Guess])

Asset Depreciation Functions

Calc supports several methods for calculating the periodic depreciation of capital assets.

• SLN (Straight-Line): Calculates linear depreciation for one period.
  • Syntax: =SLN(Cost; Salvage; Life)
• DB (Fixed-Declining Balance): Computes asset depreciation for a specified period using the fixed-declining balance method.
  • Syntax: =DB(Cost; Salvage; Life; Period; [Month])
• DDB (Double-Declining Balance): Computes depreciation using the double-declining balance method or another specified factor.
  • Syntax: =DDB(Cost; Salvage; Life; Period; [Factor])
• SYD (Sum-of-Years’ Digits): Calculates accelerated depreciation based on the sum-of-years’ digits method.
  • Syntax: =SYD(Cost; Salvage; Life; Period)
• VDB (Variable Declining Balance): Calculates depreciation for any specific period or partial period using a declining balance method.

Securities and Pricing Functions

For fixed-income analysis, LibreOffice Calc supports advanced securities calculations:

• PRICE: Returns the price per $100 face value of a security that pays periodic interest.
• YIELD: Computes the annual yield on a security paying periodic interest.
• DURATION & MDURATION: Calculates the Macaulay duration and modified duration for fixed-income securities.
• ACCRINT: Calculates accrued interest for a security that pays periodic interest.