Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to add Trendline to a chart in Excel
  • Data Series in Excel
  • Subtotal function in Excel
  • Error Bars in Excel
  • Working With Tables in Excel

References

  • Count rows with at least n matching values
  • How to use Excel ROW Function
  • Convert text string to valid reference in Excel using Indirect function
  • How to get last column number in range in Excel
  • How to get address of named range in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation specific characters only
  • Data validation must not exist in list
  • Excel Data validation must contain specific text

Category: Financial Functions

Excel Financial Functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and depreciation.

PRICE function: Description, Usage, Syntax, Examples and Explanation

by

What is PRICE function in Excel? PRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security that pays periodic interest. Syntax of PRICE function PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) The PRICE function syntax has the following arguments: Settlement: The security’s settlement date. …

Continue Reading

VDB function: Description, Usage, Syntax, Examples and Explanation

by

What is VDB function in Excel? VDB function is one of Financial functions in Microsoft Excel that returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance. Syntax of VDB function VDB(cost, salvage, life, start_period, …

Continue Reading

XIRR function: Description, Usage, Syntax, Examples and Explanation

by

What is XIRR function in Excel? XIRR function is one of Financial functions in Microsoft Excel that returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. Syntax of XIRR function …

Continue Reading

XNPV function: Description, Usage, Syntax, Examples and Explanation

by

What is XNPV function in Excel? XNPV function is one of Financial functions in Microsoft Excel that returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function. Syntax of XNPV function …

Continue Reading

YIELD function: Description, Usage, Syntax, Examples and Explanation

by

What is YIELD function in Excel? YIELD function is one of Financial functions in Microsoft Excel that returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield. Syntax of YIELD function YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) Important: Dates should be entered by using the DATE function, or as results …

Continue Reading

RATE function: Description, Usage, Syntax, Examples and Explanation

by

What is RATE function in Excel? RATE function is one of Financial functions in Microsoft Excel that returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns …

Continue Reading

RECEIVED function: Description, Usage, Syntax, Examples and Explanation

by

What is RECEIVED function in Excel? RECEIVED function is one of Financial functions in Microsoft Excel that returns the amount received at maturity for a fully invested security. Syntax of RECEIVED function RECEIVED(settlement, maturity, investment, discount, [basis]) The RECEIVED function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the …

Continue Reading

RRI function: Description, Usage, Syntax, Examples and Explanation

by

What is RRI function in Excel? RRI function is one of the Financial functions in Microsoft Excel that returns an equivalent interest rate for the growth of an investment. Syntax of RRI function RRI(nper, pv, fv) The RRI function syntax has the following arguments. Nper    Required. Nper is the number of periods for the investment. Pv    …

Continue Reading

SLN function: Description, Usage, Syntax, Examples and Explanation

by

What is SLN function in Excel? SLN function is one of Financial functions in Microsoft Excel that returns the straight-line depreciation of an asset for one period. Syntax of SLN function SLN(cost, salvage, life) The SLN function syntax has the following arguments: Cost: The initial cost of the asset. Salvage: The value at the end of …

Continue Reading

SYD function: Description, Usage, Syntax, Examples and Explanation

by

What is SYD function in Excel? SYD function is one of Financial functions in Microsoft Excel that returns the sum-of-years’ digits depreciation of an asset for a specified period. Syntax of SYD function SYD(cost, salvage, life, per) The SYD function syntax has the following arguments: Cost: The initial cost of the asset. Salvage:  The value at …

Continue Reading

TBILLEQ function: Description, Usage, Syntax, Examples and Explanation

by

What is TBILLEQ function in Excel? TBILLEQ function is one of Financial functions in Microsoft Excel that returns the bond-equivalent yield for a Treasury bill. Syntax of TBILLEQ function TBILLEQ(settlement, maturity, discount) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd …

Continue Reading

PMT function: Description, Usage, Syntax, Examples and Explanation

by

What is PMT function in Excel? PMT function is one of the Financial functions in Microsoft Excel that calculates the payment for a loan based on constant payments and a constant interest rate.Use the Excel Formula Coach to figure out a monthly loan payment. At the same time, you’ll learn how to use the PMT function in a …

Continue Reading

PPMT function: Description, Usage, Syntax, Examples and Explanation

by

What is PPMT function in Excel? PPMT function is one of the Financial functions in Microsoft Excel that returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. Syntax of PPMT function PPMT(rate, per, nper, pv, [fv], [type]) The PPMT function syntax has …

Continue Reading

TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation

by

What is TBILLYIELD function in Excel? TBILLYIELD function is one of the Financial functions in Microsoft Excel that returns the yield for a Treasury bill. Syntax of TBILLYIELD function TBILLYIELD(settlement, maturity, pr) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd …

Continue Reading

PRICEDISC function: Description, Usage, Syntax, Examples and Explanation

by

What is PRICEDISC function in Excel? PRICEDISC function is one of Financial functions in Microsoft Excel that returns the price per $100 face value of a discounted security. Syntax of PRICEDISC function PRICEDISC(settlement, maturity, discount, redemption, [basis]) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, …

Continue Reading

PRICEMAT function: Description, Usage, Syntax, Examples and Explanation

by

What is PRICEMAT function in Excel? PRICEMAT function is one of Financial functions in Microsoft Excel that returns the price per $100 face value of a security that pays interest at maturity. Syntax of PRICEMAT function PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) Important: Dates should be entered by using the DATE function, or as results of other …

Continue Reading

PV function: Description, Usage, Syntax, Examples and Explanation

by

What is PV function in Excel? PV function is one of  the Financial functions in Microsoft Excel that calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that’s …

Continue Reading

PDURATION function: Description, Usage, Syntax, Examples and Explanation

by

What is PDURATION function in Excel? PDURATION function is one of the Financial functions in Microsoft Excel that returns the number of periods required by an investment to reach a specified value. Syntax of PDURATION function PDURATION(rate, pv, fv) The PDURATION function syntax has the following arguments. Rate: Rate is the interest rate per period. Pv:  …

Continue Reading

ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation

by

What is ODDLYIELD function in Excel? ODDLYIELD function is one of the Financial functions in Microsoft Excel that returns the yield of a security that has an odd (short or long) last period. Syntax of ODDLYIELD function ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) The ODDLYIELD function syntax has the following arguments: Settlement: The security’s …

Continue Reading

ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation

by

What is ODDLPRICE function in Excel? ODDLPRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security having an odd (short or long) last coupon period. Syntax of ODDLPRICE function ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) The ODDLPRICE function syntax has the following …

Continue Reading

ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation

by

What is ODDFYIELD function in Excel? ODDFYIELD function is one of the Financial functions in Microsoft Excel that returns the yield of a security that has an odd (short or long) first period. Syntax of ODDFYIELD function ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis]) The ODDFYIELD function syntax has the following arguments: Settlement: The …

Continue Reading

ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation

by

What is ODDFPRICE function in Excel? ODDFPRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security having an odd (short or long) first period. Syntax of ODDFPRICE function ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis]) The ODDFPRICE function syntax has the following …

Continue Reading

NPV function: Description, Usage, Syntax, Examples and Explanation

by

What is NPV function in Excel? NPV function is one of the Financial functions in Microsoft Excel that calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Syntax of NPV function NPV(rate,value1,[value2],…) The NPV function syntax has the following arguments: …

Continue Reading

NPER function: Description, Usage, Syntax, Examples and Explanation

by

What is NPER function in Excel? NPER function is one of the Financial functions in Microsoft Excel that returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax of NPER function NPER(rate,pmt,pv,[fv],[type]) The NPER function syntax has the following arguments: Rate: The interest rate per period. Pmt: …

Continue Reading

NOMINAL function: Description, Usage, Syntax, Examples and Explanation

by

What is NOMINAL function in Excel? NOMINAL function is one of the Financial functions in Microsoft Excel that returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. Syntax of NOMINAL function NOMINAL(effect_rate, npery) The NOMINAL function syntax has the following arguments: Effect_rate: The effective interest rate. Npery: …

Continue Reading

MIRR function: Description, Usage, Syntax, Examples and Explanation

by

What is MIRR function in Excel? MIRR function is one of the Financial functions in Microsoft Excel that returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash. Syntax of MIRR function MIRR(values, finance_rate, reinvest_rate) The …

Continue Reading

TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation

by

What is TBILLPRICE function in Excel? REPT function is one of Financial functions in Microsoft Excel that returns the price per $100 face value for a Treasury bill. Syntax of TBILLPRICE function TBILLPRICE(settlement, maturity, discount) The TBILLPRICE function syntax has the following arguments: Settlement: The Treasury bill’s settlement date. The security settlement date is the date after …

Continue Reading

How to calculate present value of annuity in excel

by

To get the present value of an annuity, you can use the PV function. Formula =PV(rate,periods,payment,0,0) In the example shown, the formula in C9 is: =PV(C5,C6,C4,0,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, an annuity pays 10,000 per year for the next 25 years, with …

Continue Reading

Calculate loan interest in given year in Excel

by

To calculate the total interest for a loan in a given year, you can use the CUMIPMT function. Formula =CUMIPMT(rate,nper,pv,start,end,type) Explanation In the example shown, the total interest paid in year 1 is calculated by using 1 for start period and 12 for end period. The The formula in F5 is: =CUMIPMT(5%/12,60,30000,1,12,0) Note: values hardcoded for readability only. …

Continue Reading

Calculate interest rate for loan in Excel

by

To calculate the periodic interest rate for a loan, given the loan amount, the number of payment periods, and the payment amount, you can use the RATE function. Formula =RATE(periods,payment,-amount)*12 Explanation In the example shown, the formula in C10 is: =RATE(C7,C6,-C5)*12 Loans have four primary components: the amount, the interest rate, the number of periodic …

Continue Reading

Calculate interest for given period in Excel

by

To calculate the interest portion of a loan payment in a given period, you can use the IPMT function. Formula =IPMT(rate,period,periods,-loan) Explanation In the example shown, the formula in C10 is: =IPMT(C6/12,1,C8,-C5) How this formula works For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 …

Continue Reading

Calculate cumulative loan principal payments in Excel

by

To calculate the cumulative principal paid between any two loan payments, you can use the CUMPRINC function. Formula =CUMPRINC(rate,nper,pv,start,end,type) Explanation In the example shown, we calculate the total principal paid over the full term of the loan by using the first and last period. The formula in C10 is: =CUMPRINC(C6/12,C8,C5,1,60,0) How this formula works For this example, …

Continue Reading

Calculate cumulative loan interest in Excel

by

To calculate the cumulative principal paid between any two loan payments, you can use the CUMIPMT function. In the example shown, we calculate the total principal paid over the full term of the loan by using the first and last period. Formula =CUMIPMT(rate,nper,pv,start,end,type) Explanation The formula in C10 is: =CUMIPMT(C6/12,C8,C5,1,60,0) How this formula works For this example, …

Continue Reading

How to calculate compound interest in Excel

by

To calculate compound interest in Excel, you can use the FV function. Case study: This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%, compounded monthly. Formula =FV(rate,nper,pmt,pv) Explanation In the example shown, the formula in C10 is: =FV(C6/C8,C7*C8,0,-C5) How this formula works The FV function can calculate compound …

Continue Reading

Compound Annual Growth Rate CAGR formula examples in Excel

by

To calculate Compound Annual Growth Rate (CAGR) in Excel, the average rate of return for an investment over a period of time, you can use several approaches. Formula =(end/start)^(1/periods)-1 Note that unlike most other financial functions in Excel, future value (FV) does not need to be entered as a negative number. Explanation In the example …

Continue Reading

Bond valuation example in Excel

by

This tutorial show how to calculate the value of a bond on the issue date using the PV function. Explanation In the example shown, the formula in C10 is: =-PV(C6/C8,C7*C8,C5/C8*C4,C4) Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the …

Continue Reading

How to calculate annuity for interest rate in excel

by

An annuity is a series of equal cash flows, spaced equally in time.  To solve for an annuity interest rate, you can use the RATE function. Case study:  The goal in this example is to have $100,000 at the end of 10 years, with an annual payment of $7,500 made at the end of each year. …

Continue Reading

How to calculate annual compound interest schedule in Excel

by

To calculate annual compound interest, you can use a formula based on the starting balance and annual interest rate. Formula =start+(start*rate) Explanation In the example shown, the formula in C6 is: =C5+(C5*rate) Note: “rate” is the named range F6. How this formula works If you have an annual interest rate, and a starting balance you …

Continue Reading

Example of payment for annuity in Excel

by

This tutorial shows how to solve for an annuity payment in Excel. An annuity is a series of equal cash flows, spaced equally in time. Case study:  Using the PMT function, the goal in this example is to have 100,000 at the end of 10 years, with an interest rate of 5%. Payments are made annually, …

Continue Reading

How to calculate Net Present Value (NPV) in Excel

by

NPV formula for net present value To calculate Net Present Value (NPV) you can use the NPV function. Formula =NPV(rate,cashflows)-initialcost Explanation In the example shown, the formula in F6 is: =NPV(F4,C6:C10)+C5 How this formula works Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Excel If, Nested If, And/Or Criteria Examples
  • How to use Excel NOT Function
  • Check multiple cells are equal in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel

Date Time

  • Get day from date in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Custom weekday abbreviation in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Get project end date in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Group times into 3 hour buckets in Excel
  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to get original number from percent change in Excel
  • 44 Practical Excel IF function Examples
  • Convert column number to letter in Excel
  • Currency vs Accounting Format in Excel
  • How to create dynamic named range with INDEX in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning