Excel NPV function

See also

  1. IRR - internal rate of return
  2. NIV - net intermediate value
  3. MIRR - modified IRR

NPV may be best calculated using Excel spreadsheet program but the Excel NPV function has severe limitations as it only permits finding net present value of an ordinary annuity that makes end of period payments. That is all the native Excel financial function is capable of but as you may know finding net present value is such a complex task when you define the properties of the cash flows. Here the tadNPV function addresses all such limitations of Excel NPV function and offers a more robust and feature rich functionality that makes possible net present value for different scenarios. For example, you will be able to find NPV of a perpetuity, of a project, of a business or company valuation. These properties include but are not limited to use a schedule of discount rates where each of the cash flows is discounted at a different rate. This would be required for example in finding current value or market price of a coupon bearing bond that has a term structure of interest rates. A schedule of compounding frequencies of interest, a schedule of payment periods, growth and tax rates, possible hair cuts on income and permission to rig the discount rates.

Rates5%4%3%2%1%2%3%4%
Growth0%0%0%0%0%0%0%0%
Tax_Rates30%31%32%33%34%35%36%37%
Cash_Flows $(100) $250 $350 $(450) $550 $650 $750 $850
Adjust_for_inflation - - - - - - - -
Frequencies 4 365 24 365 INF 260 INF 5,200
Types 1 - 1 - - 1 - -
Compoundings0.250.0027397260.0833333330.00273972610.03846153810.019230769
Periods0.250.0027397260.0833333330.00273972610.038461538 1 0.019230769
Concentrations10.521010.510.75
Hair_Cuts0%20%20%20%0%20%20%20%
Rate_Rigged_By0%15%15%15%0%15%15%15%
=tadNPV(B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8, B9:I9, B10:I10, B11:I11, B12:I12 )

NPV = $33,659.11

Using tadXL function such as tadNPV, and tadXNPV finding net present value in Excel 2007, 2010 and 2013 becomes really easy. Let us now briefly look at each of these three financial functions in tadXL to find NPV using Excel:

=tadNPV( rates, inflation, tax_rates, cashflows, adjust_for_inflation, frequencies, types, compoundings, periods, concentrations, hair_cuts, rate_rigged_by )

=tadNPV( cashflows, dates, guess, compounding, iterations, precision )