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

Data Analysis

  • Get column index in Excel Table
  • Conditional Formatting New Rule with Formulas in Excel
  • How to create dynamic reference table name in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to count table rows in Excel

References

  • Left Lookup in Excel
  • How to get last column number in range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in text data in Excel
  • How to get address of named range in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only

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.

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

by

What is MDURATION function in Excel? MDURATION function is one of the Financial functions in Microsoft Excel that returns the modified Macauley duration for a security with an assumed par value of $100. Syntax of MDURATION function MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) The MDURATION function syntax has the following arguments: Settlement:  The security’s settlement date. …

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

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

by

What is COUPDAYS function in Excel? COUPDAYS function is one of the Financial functions in Microsoft Excel that returns the number of days in the coupon period that contains the settlement date. Syntax of COUPDAYS function COUPDAYS(settlement, maturity, frequency, [basis]) The COUPDAYS function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement …

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

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

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

by

What is CUMPRINC function in Excel? CUMPRINC function is one of the Financial functions in Microsoft Excel that returns the cumulative principal paid on a loan between start_period and end_period. Syntax of CUMPRINC function CUMPRINC(rate, nper, pv, start_period, end_period, type) The CUMPRINC function syntax has the following arguments: Rate: The interest rate. Nper:  The total number …

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

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

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

by

What is DB function in Excel? DB function is one of the Financial functions in Microsoft Excel that returns the depreciation of an asset for a specified period using the fixed-declining balance method. Syntax of DB function DB(cost, salvage, life, period, [month]) The DB function syntax has the following arguments: Cost: The initial cost of the …

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

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

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

by

What is DDB function in Excel? DDB function is one of the Financial functions in Microsoft Excel that returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. Syntax of DDB function DDB(cost, salvage, life, period, [factor]) The DDB function syntax has the following arguments: …

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

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

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

by

What is DISC function in Excel? DISC function is one of the Financial functions in Microsoft Excel that returns the discount rate for a security. Syntax of DISC function DISC(settlement, maturity, pr, redemption, [basis]) The DISC function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after 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

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

How to set check register balance in Excel

by

This tutorial shows how to set a check register formula that calculates a running balance and also, blank values using formula based on simple addition and subtraction. See illustrations below: Formula =balance-debit+credit Explanation In the example shown, the formula in G6 is: =G5-E6+F6 How this formula works The value in G5 is hard-coded. The formula picks …

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

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

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

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

Future value vs. Present value examples in Excel

by

This tutorial shows how present value and future value are related using the example below:   Explanation In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this: F5=C9 F6=C6 F7=C7 F8=C8 The formula to calculate future value in C9 is: =FV(C8/C7,C6*C7,0,-C5,0) The formula to calculate present value in F9 …

Continue Reading

Example of Future value of annuity in Excel

by

To get the present value of an annuity, you can use the PV function. Formula =FV(rate,periods,payment) Explanation In the example shown, the formula in C7 is: =FV(C5,C6,-C4,0,0) Explanation An annuity is a series of equal cash flows, spaced equally in time.  In this example, a $5000 payment is made each year for 25 years, with …

Continue Reading

How to calculate simple interest in Excel

by

To calculate simple interest in Excel (i.e. interest that is not compounded), you can use a formula that multiples principal, rate, and term. Case Study: This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%. Simple interest means that interest payments are not compounded – the interest is …

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

How to calculate principal for given period in Excel

by

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

Continue Reading

Calculate periods for annuity in Excel

by

To calculate the number of periods needed for an annuity to reach a given future value, you can use the NPER function. Formula =NPER(rate,pmt,pv,fv,type) Explanation In the example shown C9 contains this formula: =NPER(C6,-C7,-C4,C5,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to …

Continue Reading

Calculate payment periods for loan in Excel

by

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

Continue Reading

Calculate payment for a loan in Excel

by

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

Continue Reading

Calculate original loan amount in Excel

by

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

Continue Reading

Tax rate calculation with fixed base in Excel

by

This tutorial shows how to work Tax rate calculation with fixed base in Excel using the example below; Formula =IF(A1<limit,A1*rate,(A1-limit)*rate+fixed) Explanation This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components. In the example shown, the formula in C5 is: =IF(B5<limit,B5*rate,(B5-limit)*rate+fixed) in a …

Continue Reading

Posts navigation

  • Previous
  • 1
  • 2

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

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • How to use Excel FALSE Function
  • How to use IFS function in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add days exclude certain days of week in Excel
  • How to calculate project start date based on end date in Excel
  • Convert Excel time to decimal minutes
  • Get first day of previous month in Excel
  • List holidays between two dates in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Group times into unequal buckets in Excel
  • Map inputs to arbitrary values in Excel

General

  • Spell Check in Excel
  • How to get random value from list or table in Excel
  • How to count total columns in range in Excel
  • Sum by group in Excel
  • How to calculate percent of goal in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning