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

Data Analysis

  • Excel Frequency Function Example
  • Remove Duplicates Example in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to Create Area Chart in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Find closest match in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Offset in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Extract all partial matches in Excel

Data Validations

  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain

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.

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

by

What is COUPNUM function in Excel? COUPNUM function is one of the Financial functions in Microsoft Excel that returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. Syntax of COUPNUM function COUPNUM(settlement, maturity, frequency, [basis]) The COUPNUM function syntax has the following arguments: Settlement: The …

Continue Reading

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

by

What is IRR function in Excel? IRR function is one of the Financial functions in Microsoft Excel that returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash …

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

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

by

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

Continue Reading

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

by

What is ISPMT function in Excel? ISPMT function is one of the Financial functions in Microsoft Excel that calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments. Syntax of ISPMT function ISPMT(rate, per, nper, pv) The ISPMT function syntax has the following arguments: Argument Description Rate …

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

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

by

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

Continue Reading

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

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

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

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 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

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

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

  • IF with wildcards in Excel
  • How to use Excel XOR Function
  • Excel If, Nested If, And/Or Criteria Examples
  • SWITCH function example in Excel
  • Return blank if in Excel

Date Time

  • Get fiscal year from date in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Assign points based on late time in Excel
  • Convert text timestamp into time in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel

General

  • Convert column number to letter in Excel
  • Convert column letter to number in Excel
  • How to calculate project complete percentage in Excel
  • Hide and Unhide Columns or Rows in Excel
  • Spell Check in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning