Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.

Excel provides standard functions, IRR and NPV, for calculating the internal rate of return and net present value of a set of cash flows. While most often you’ll want to use these two functions, they may sometimes present a practical problem: Both the IRR and NPV functions assume you’ve first constructed a worksheet that arranges the cash flows into equal periods. In other words, to use the IRR or NPV function, you must first construct a worksheet that shows the investment’s monthly cash flows, or its annual cash flows, or the cash flows from some other consistent time period.

Some Background on the XIRR and XNPV Functions

Unlike the IRR and the NPV functions, the XIRR and XNPV functions don’t require you to first construct a worksheet schedule that arranges the investment cash flows into equal periods. With the XIRR and XNPV functions, you supply the date values that correspond to the cash flow values to the function as arguments.

NOTE: You might want to review the Microsoft Help file's discussion of the IRR and NPV functions if you have questions about how the XIRR and XNPV tools work.

The somewhat unique feature of both the XIRR and the XNPV function is that if you supply the actual date values or cash flow values inside the formula as arguments, they expect you to supply the values argument and the dates argument as arrays. (An array is just a set of numbers.)

For example, suppose that you want to calculate the internal rate of return and net present value for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

If you include the actual array in the argument, you can designate the array by enclosing the values and dates arguments inside braces. To show the preceding date values in an array, for example, you would type the following:

{"1/1/2000","12/31/2000","4/15/2001","12/31/2001"}

To show the preceding cash flow values as an array, you would type the following:

{-1000,-1000,2000,1000}

If you enter the date values and cash flow values in worksheet ranges, you don’t need to worry about identifying the date values and cash flow values as arrays. For example, if you enter the preceding set of date values in the worksheet range A1:A4 and the preceding set of cash flow values in the worksheet range B1:B4, you can use these worksheet ranges as the function arguments.

The XIRR and XNPV functions, predictably, require you to use date values that are valid. You must also use the same number of date values as you use cash flow values. If you supply an invalid argument or set of arguments, Excel returns the #NUM error value.

NOTE: Excel considers the first date value to be the starting date of the investment. Accordingly, the first date value in your array or worksheet range must be the earliest. Subsequent date values don't have to be in chronological order, however.

Using the XIRR Function

The XIRR function calculates the internal rate of return for an investment given its cash flows, the dates of those cash flows, and, optionally, an initial guess as to the internal rate of return. The function uses the following syntax:

XIRR (values, dates, guess)

For example, suppose that you want to calculate the internal rate of return for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

To calculate the internal rate of return for this set of cash flows using the XIRR function and using a starting guess of 20%, you would use the following formula:

=XIRR({-1000,-1000,2000,1000},{"1/1/2000","12/31/2000","4/15/2001","12/ 31/2001"},.2)

The formula returns the value .470251, which is equivalent to 47.0251% annually.If the date values were stored in the worksheet range A1:A4 and the cash flow values were stored in the worksheet range B1:B4, you could instead use the following formula:

=XIRR (B1:B4,A1:A4,.2)

Using the XNPV Function

The XNPV function calculates the net present value for an investment given its cash flows, the dates of those cash flows, and the annual discount rate. The function uses the following syntax:

XNPV (rate, values, dates)

For example, suppose that you want to calculate the net present value for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

If the date values were stored in the worksheet range A1:A4, the cash flow values were stored in the worksheet range B1:B4, and you wanted to use a discount rate of 15%, you would use the following formula:

=XNPV (.15,B1:B4,A1:A4)

The formula returns the value 557.17.

Curiously, the XNPV function doesn’t accept date values supplied as text strings. For example, although the following formula is equivalent to the preceding one, it returns the

#VALUE error value:

=XNPV (.15,{-1000,-1000,2000,1000},{"1/1/2000","12/31/2000","4/15/

2001","12/31/2001"})

You could, however, rewrite this formula using equivalent serial date values (the serial date 36526 for 1/1/2000, the serial date value 36891 for 12/31/2000, and so on), and then Excel returns the correct net present value:

=XNPV (0.15,{-1000,-1000,2000,1000},{36526,36891,36996,37256})

- What are your values?: Personal Values Examples
- What Your Cash Flow Stinks? Making Money Online is about Cash Flow
- Is Solitaire Cash Legit? Uncovering the Truth About Earning Real Money
- The Benefits of Choosing Cash for Cars Adelaide Services
- Understanding the Role of pKa in Indole Synthesis: A Comprehensive Review
- Corporate Values Provide Strong Foundations for Organisational Effectiveness
- How Cash-on-Cash Return Is Used for Investment Comparisons
- Management of Renewable Energies and Environmental Protection, Part II
- Using Core Values as a Catalyst for Winning as a Coach

- The Power of Vector Graphics in Modern Design
- Master the Game: Proven Matka Tips for Consistent Wins
- Why You Should Consider Offshoring to India
- QNET: Pioneering Wellness with InShape Products
- Press Release Script- Writing An Effective Press Release Takes A Little More Time And Effort
- The Critical Role of Order Accuracy in Hospitality
- Austin Apartments | Texas Living with Urban Influence
- Unveiling the Superiority of Extra Virgin Olive Oil
- Essential Insights for Prospective Ant Farm and Yellow Spotted Lizard Owners
- Communication Theory
- How to Start a Waste Removal Business
- The Critical Role of Dietary Fiber in Health
- Requirements for oxygen pipe
- Harnessing the Power of InShape Meal Shake for Body Fat Management
- Online solution by vashikaran specialist of love problem solution

- Crafting a Comprehensive Investment Strategy: A Guide to Building Your Financial Future
- How To Do Cunnilingus
- Choosing the Right Engine Oil: 0w20 vs 5w30 Detailed Comparison
- Accessing Your Old SBCGlobal Email Account: A Step-by-Step Guide
- Why Did Walter Kill Walter JR in Breaking Bad?
- 5 Main Characteristics of American Literature
- Round Bruise with White Center: Types, Causes
- Top 10 Stream2Watch Alternatives to Watch Live Sports in 2024
- Basic Components of Cellular Systems
- Seamless Integration: Connecting Your Blink Camera to Wi-Fi

Calculating Interest Rates With Microsoft Excel

Making interest rate calculations? Microsoft Excel can help. Excel's RATE, or interest rate, function lets you precisely calculate the implicit interest rate for a loan given the payment amount, loan amount, and number of payments.Mastering Accrued Interest Calculations with Microsoft Excel

Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.Using the Bond Duration Add-in Functions

Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.