• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
Finance Train

Finance Train

High Quality tutorials for finance, risk, data science

  • Home
  • Data Science
  • CFA® Exam
  • PRM Exam
  • Tutorials
  • Careers
  • Products
  • Login

Calculating Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel

CFA® Exam Level 1, Excel Modelling

This lesson is part 5 of 9 in the course Capital Budgeting

Net Present Value (NPV) Function

The NPV function calculates the present value of a series of cash flows at equal time intervals. The function is represented as follows:

= NPV(rate,value1,value2,…)

Here, rate is the discount rate for one period, and values are the cash flows. Any payments are entered with a negative sign, and income is entered as positive.

Note that even though the function is named Net Present Value (NPV), it doesn’t really calculate the net present value. This is because it does not take into consideration the initial investment at time 0.

To calculate the net present value, you will need to subtract the initial investment from the result you get from the NPV function.

Lets take an example to demonstrate this function. Assume that you started a business with an initial investment of $10,000 and received the following income for the next five years.

To calculate the net present value, we will apply the NPV function as follows:

This is the present value of all the future cash flows.

The net present value will be:

Net Present Value = 11,338.77 – 10,000

= $1,338.77

Internal Rate of Return (IRR) Function

IRR is based on NPV. It as a special case of NPV, where the rate of return calculated is the interest rate corresponding to a 0 (zero) net present value.

IRR function is represented as follows:

= IRR(values,guess)

This function accounts for the inflows and the outflows, including the initial investment at time 0.

Using the same example above, the IRR calculation is shown below:

The IRR of 14.974% means that at this rate the net present value will be zero.

Other Related Functions

MIRR: MIRR calculates the modified internal rate of return for a series of periodic cash flows, considering both cost of investment and interest on reinvestment of cash.

Syntax: MIRR(values,finance_rate,investment_rate)

XNPV: This function calculates the net present value for a series of cash flows that are not equally spaced in time.

Syntax: XNPV(rate,values,dates)

XIRR: This function calculates the internal rate of return for a series of cash flows that are not equally spaced in time.

Syntax: XIRR(values,dates,guess)

Download the NPV IRR.xls to view these example calculations.

Previous Lesson

‹ Net Present Value of a Project

Next Lesson

How to Calculate Payback Period ›

Join Our Facebook Group - Finance, Risk and Data Science

Posts You May Like

How to Improve your Financial Health

CFA® Exam Overview and Guidelines (Updated for 2021)

Changing Themes (Look and Feel) in ggplot2 in R

Coordinates in ggplot2 in R

Facets for ggplot2 Charts in R (Faceting Layer)

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

In this Course

  • The Capital Budgeting Process
  • Principles of Capital Budgeting
  • Mutually Exclusive Projects, Project Sequencing, and Capital Rationing
  • Net Present Value of a Project
  • Calculating Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel
  • How to Calculate Payback Period
  • How to Calculate Discounted Payback Period
  • Calculating Profitability Index of a Project
  • Conflict Between NPV and IRR

Latest Tutorials

    • Data Visualization with R
    • Derivatives with R
    • Machine Learning in Finance Using Python
    • Credit Risk Modelling in R
    • Quantitative Trading Strategies in R
    • Financial Time Series Analysis in R
    • VaR Mapping
    • Option Valuation
    • Financial Reporting Standards
    • Fraud
Facebook Group

Membership

Unlock full access to Finance Train and see the entire library of member-only content and resources.

Subscribe

Footer

Recent Posts

  • How to Improve your Financial Health
  • CFA® Exam Overview and Guidelines (Updated for 2021)
  • Changing Themes (Look and Feel) in ggplot2 in R
  • Coordinates in ggplot2 in R
  • Facets for ggplot2 Charts in R (Faceting Layer)

Products

  • Level I Authority for CFA® Exam
  • CFA Level I Practice Questions
  • CFA Level I Mock Exam
  • Level II Question Bank for CFA® Exam
  • PRM Exam 1 Practice Question Bank
  • All Products

Quick Links

  • Privacy Policy
  • Contact Us

CFA Institute does not endorse, promote or warrant the accuracy or quality of Finance Train. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.

Copyright © 2021 Finance Train. All rights reserved.

  • About Us
  • Privacy Policy
  • Contact Us