NPV and XIRR calculator is a ready-to-use excel template that helps you to calculate Net Present Value and Rate of Return for a given investment.
This template helps you to compare two scenarios of investment having different discount rates. It also displays comparative charts of actual vs expected return as well as return rates.
NPV is the value is the difference between the present value of cash inflows outflows over a period of time.
Generally, businesses use NPV for investment planning. It helps to check the profitability of a project or investment. A positive Net Present Value indicates profitability and a negative NPV indicates a loss.
XIRR is the extended internal return rate for multiple investments in the same project at distinct times.
When cash inflows and outflows vary, you cannot rely on a simple interest rate. Hence, XIRR is used for that purpose to get the actual rate of return based on cash inflows and outflows.
NPV and XIRR Calculator Excel Template
We have created a simple and easy NPV and XIRR Calculator Excel Template with predefined formulas. You can compare two different projects/investments based on a given time and provided projected cash flows.
Enter a few details and the template automatically calculates the NPV and XIRR for you. In addition to that, the template also compares the data and provides a feasibility report with charts.
Click here to download NPV and XIRR Calculator Excel Template.
Additionally, you can also download other financial analysis templates like CAGR Calculator, ROCE Calculator ROE Calculator Portfolio Analysis With BSE Bhav Copy, Income Tax Calculator FY 2018-19 and Loan Amortization Template from our website.
Let us understand the contents of the template in detail and how to use it.
Contents of NPV And XIRR Calculator Excel Template
This template consists of 2 sheets: Net Present Value and XIRR Calculator and Comparison Charts.
Net Present Value and XIRR Calculator
In this sheet, we compare 2 investment scenarios with different discount rates and similar cash flows. Each section consists of similar rows and columns.
For Scenario-1, insert the following details:
Investment Value: Investment amount in dollars.
Period: No. of years for which you want to invest.
Expected Return Rate: Expected annual return rate or discount rate.
Cash Flow Dates: Insert cash flow dates.
Expected Cash Flow: Expected Cash flow is auto-calculated based on the expected return rate or discount rate.
Projected Cash Flow: Business cash flow based on projections.
Terminal Cash Flow: The terminal cash flow is the value of the final value recovered by selling off or completion of the project.
Providing the above data, the template automatically calculates the Net Present Value and XIRR for the given data.
Similar to this there is another section below this to find Net present value for the second scenario.
Comparison Charts
This sheet compares the results of both the scenarios of an investment calculated in the previous sheet. At the top, it displays the comparative data.
The template generates the following comparative graphs:
NPV Comparison Chart
Actual Vs Expected Cash Flow Chart
XIRR Vs Expected Return Rate Chart
Example
As you can see in the template, we have compared 2 different scenarios with similar investment amounts, similar discount rates but different terminal cash flows. In both the scenarios, the template displays the result as a feasible investment as both the values are positive.
Seeing the graph above, one gives 8 % XIRR and the other gives 9 % XIRR. Thus, a more feasible investment is the one that gives us a 9% return.
If the NPV is negative, then it means that the investment is not feasible and the template will display “Not Feasible” and turn the cell red as shown below:
This template can be helpful to investors looking to buy or invest in businesses, stock traders, and also individual investors.
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. We will be more than happy to assist you.
