Master the 9 Basic Excel Financial Functions and Formulas

Excel provides a comprehensive set of financial functions that allow you to perform various calculations related to loans, investments, depreciation, and more. In this article, we’ll cover key financial functions—PMT, NPV, IRR, SYD, FV, PV, DDB, PRICE, SLN, CUMIPMT, YIELD, DURATION, and ACCRINT. These functions are essential for anyone involved in finance, accounting, or investing.

1. PMT: Calculating Loan Payments

The PMT function calculates the payment amount for a loan based on constant payments and a constant interest rate.

Syntax:

=PMT(rate,nper,pv,[fv],[type])

• rate: The interest rate per period.
• nper: The total number of payment periods.
• pv: The present value (loan amount).
• fv: (Optional) The future value, or balance after the last payment (default is 0).
• type: (Optional) When payments are due: 0 (end of period) or 1 (beginning of period).

Example:

• Monthly payment for a $10,000 loan with a 5% annual interest rate over 5 years:

=PMT(5%/12,60,-10000)

• Returns -188.71, meaning a $188.71 monthly payment.

Use Case: Loan repayment calculations for mortgages, car loans, etc.

2. NPV: Calculating Net Present Value

The NPV function calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.

Syntax:

=NPV(rate,value1,[value2],...)

• rate: Discount rate per period.
• value1, value2, ...: Cash flows occurring at regular intervals.

Example:

• NPV of an investment with a 10% discount rate and cash flows of $1,000, $2,000, and $3,000 over three years:

=NPV(10%,1000,2000,3000)

• Returns 4,815.16.

Use Case: Investment analysis to assess profitability.

3. IRR: Calculating Internal Rate of Return

The IRR function calculates the internal rate of return for a series of cash flows.

Syntax:

=IRR(values,[guess])

• values: Range of cells containing the cash flows (include the initial investment as a negative value).
• guess: (Optional) Initial guess for IRR (default is 0.1 or 10%).

Example:

• IRR for an investment with an initial outflow of $5,000 and inflows of $1,500, $2,000, $2,500, and $3,000:

=IRR(A1:A5)

• Returns 18.86%.

Use Case: Evaluating the attractiveness of an investment.

4. SYD: Calculating Depreciation Using the Sum-of-Years' Digits Method

The SYD function calculates depreciation using the sum-of-years' digits method, which accelerates depreciation in the earlier years of an asset's life.

Syntax:

=SYD(cost,salvage,life,per)

• cost: Initial cost of the asset.
• salvage: Salvage value at the end of the asset's life.
• life: Number of periods for depreciation.
• per: Period for which depreciation is calculated.

Example:

• Depreciation for the first year of a $10,000 asset with a $1,000 salvage value and 5-year life:

=SYD(10000,1000,5,1)

• Returns 3,000.

Use Case: Accelerated depreciation for accounting or tax purposes.

5. FV: Calculating Future Value

The FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax:

=FV(rate,nper,pmt,[pv],[type])

• rate: Interest rate per period.
• nper: Total number of payment periods.
• pmt: Payment made each period (negative if it’s an outflow).
• pv: (Optional) Present value of the investment (default is 0).
• type: (Optional) When payments are due: 0 (end of period) or 1 (beginning of period).

Example:

• Future value of saving $200 per month for 10 years with a 6% annual interest rate:

=FV(6%/12,10*12,-200)

• Returns 33,232.82.

Use Case: Savings and retirement planning.

6. PV: Calculating Present Value

The PV function calculates the present value of an investment or loan based on future payments, a constant interest rate, and the total number of periods.

Syntax:

=PV(rate,nper,pmt,[fv],[type])

• rate: Interest rate per period.
• nper: Total number of payment periods.
• pmt: Payment made each period.
• fv: (Optional) Future value or balance after the last payment.
• type: (Optional) When payments are due: 0 (end of period) or 1 (beginning of period).

Example:

• Present value of receiving $500 per month for 5 years with a 5% annual interest rate:

=PV(5%/12,5*12,500)

• Returns 26,209.35.

Use Case: Assessing the value of future cash flows.

7. DDB: Calculating Depreciation Using the Double-Declining Balance Method

The DDB function calculates depreciation using the double-declining balance method, which is an accelerated depreciation method.

Syntax:

=DDB(cost,salvage,life,period,[factor])

• cost: Initial cost of the asset.
• salvage: Salvage value at the end of the asset's life.
• life: Number of periods over which the asset is depreciated.
• period: The period for which you want to calculate depreciation.
• factor: (Optional) Rate at which the balance declines (default is 2 for double-declining).

Example:

• Depreciation for the first year of a $10,000 asset with a $1,000 salvage value and a 5-year life:

=DDB(10000,1000,5,1)

• Returns 4,000.

Use Case: Accelerated depreciation for tax benefits.

8. PRICE: Calculating the Price of a Bond

The PRICE function calculates the price per $100 face value of a bond based on an expected yield.

Syntax:

=PRICE(settlement,maturity,rate,yld,redemption,frequency,[basis])

• settlement: Bond's settlement date (when the bond is traded to the buyer).
• maturity: Bond's maturity date (when the bond expires).
• rate: Bond's annual coupon rate.
• yld: Bond's annual yield.
• redemption: Bond's redemption value per $100 face value.
• frequency: Number of coupon payments per year (1, 2, or 4).
• basis: (Optional) Day count basis to use (0-4, with 0 being the default).

Example:

• Price of a bond with a 5% coupon rate, 6% yield, and 10 years to maturity:

=PRICE("2024-09-01","2034-09-01",5%,6%,100,2)

• Returns a value like 92.64, indicating the bond's price.

Use Case: Bond pricing and valuation for investors and financial analysts.

9. SLN: Calculating Straight-Line Depreciation

The SLN function calculates the depreciation of an asset for one period using the straight-line method.

Syntax:

=SLN(cost,salvage,life)

• cost: Initial cost of the asset.
• salvage: Salvage value at the end of the asset's life.
• life: Number of periods over which the asset is depreciated.

Example:

• Straight-line depreciation for a $10,000 asset with a $1,000 salvage value and a 5-year life:

=SLN(10000,1000,5)

Returns 1,800, indicating yearly depreciation.

Use Case: Fixed annual depreciation for accounting purposes.