Internal Rate of Return (IRR) Calculator
Money Weighted Rate of Return (MWRR)
Insert the date and amount of each cash flow associated with your investment
How to Calculate IRR
Enter the date of each cash flow in the date column and the amount in the cash flow column. WOWA’s IRR calculator performs IRR calculation and provides the IRR for your project/investment.
Each capital infusion is a negative cash flow when considering an investment project. In contrast, dividends, proceeds of disposition and any money you receive concerning this investment is a positive cash flow.
Net Present Value (NPV)
Net present value is the sum of cash flows discounted to the present time. The following formula defines net present value (NPV):
NPV = CF0 + CF1/(1+r) + CF2/(1+r)2 + …+CFn/(1+r)n
Where r is the discount rate, the discount rate is often considered to equal the cost of capital or the opportunity cost of capital. CF represents cash flow, and CFi represents cash flow during the time interval i. n is the number of intervals.
IRR description
Internal rate of return (IRR) is defined as the discount rate, which equates net present value (NPV) to zero, and money-weighted rate of return (MWRR) is the discount rate which sets the present value of cash outflows equal to the present value of the cash inflows. The numeric value of IRR equals the numeric value of MWRR.
Investment can be defined as using present resources with the expectation of gaining more resources in the future. In a financial context, investment is spending money expecting to receive more money in the future. The value of a dollar today is different from the value of a dollar next year, and interest rates are effectively the exchange rate of dollars at different times.
IRR is a financial metric used to evaluate the profitability of an investment or project. The internal rate of return represents the discount rate at which the net present value (NPV) of the project's cash flows becomes zero.
In simpler terms, IRR is the rate of return that an investment is expected to generate over its lifespan, considering the time value of money. It helps investors and businesses assess the attractiveness of an asset by comparing the expected return to the required rate of return or cost of capital.
To calculate the IRR, determine the cash inflows and outflows associated with the investment or project and find the discount rate that makes the NPV equal to zero. This is typically done using iterative methods by financial software.
IRR allows one to compare the profitability of a project with savings account rates, GIC rates or bond yields.
Here's how IRR is interpreted:
If the IRR is greater than the required rate of return or cost of capital, the investment is considered attractive because it generates a higher return than what is expected or required.
If the IRR is less than the required rate of return, the investment may be seen as less attractive or unprofitable. If the IRR is equal to the required rate of return, the project is expected to break even with no positive or negative net present value.
IRR is a widely used tool for investment analysis and capital budgeting decisions. It helps compare different investment opportunities and select the ones with the highest potential return. However, it has some limitations, such as assumptions about cash flow reinvestment and the presence of multiple IRRs in complex projects. Therefore, it's essential to consider other financial metrics and qualitative factors when making investment decisions.
How to Calculate IRR in Excel?
You can use any spreadsheet application to calculate IRR. Microsoft Excel is the most famous spreadsheet software. Google Sheets is a very convenient spreadsheet application freely available on the web. LibreOffice Calc is a free alternative to Microsoft Excel, developed by the Linux community. Each of these spreadsheets can be used for calculating the internal rate of return.
One can use the IRR function to calculate the internal rate of return. IRR takes the sequence of all cash flows as input and gives the IRR value as its output. IRR assumes that the cash flows occur at regular intervals. Since cash flows occur at irregular intervals in many situations, we need to become familiar with the more general formula XIRR. The use of this formula is explained in the example below.
IRR Calculation, Example 1:
Alex purchased an annuity which cost him $1000. In exchange, he will receive 12 payments of $100 each. The first payment will be one month after the purchase and each succeeding month for 12 months. To calculate the IRR, we insert all cash flows in an Excel spreadsheet, as shown below.
As we see, IRR is calculated as 3%. It is important to note that as cash flows are one month apart, the calculated IRR is a monthly rate. In cell B16, the equivalent annual rate is 41.3%. In cell B17, NPV is calculated using the calculated IRR as the discount rate. The result of 0 confirms that IRR is calculated correctly.
IRR Calculation, Example 2:
Fred bought a mirror image duplex in August 2022 for $270k with a 20% down payment and 4.1% interest. He spends $5,000 on closing costs. He pays $1,500 monthly for the mortgage, property tax and insurance. Fred keeps the tenants of unit 2 and receives $900 monthly while spending $8,000 each month improving unit 1 for five months. He is counting $5,000 a month as his wage for making the improvements.
After five months, tenets leave Unit 2, and Fred lists Unit 1 for sale. At this time, Fred starts working on improving Unit 2. 7 Months after his purchase, he sells the first house for $230k and pays back his mortgage. He incurs a 5% closing cost, including his mortgage prepayment penalty. 10 Months after his purchase, he completed improvements to unit 2 and listed it for sale. It sells for $230k one year after his initial investment, with 5% closing costs.
What is the IRR in this project?
We enter all the cash flows into the freely available online version of Excel. Column A contains the description of each cash flow, while column B contains the date of each cash flow. Finally, column C has the signed amount of each cash flow. The data and calculations are presented in the figure below.
In cell B36, we enter the formula “=XIRR(C2:C35,B2:B35,0.3)”. XIRR is the most general Excel formula for calculating the IRR. In this formula, the first argument (C2:C35) is the range of cells containing cash flows, and the second argument (B2:B35) is the range of cells containing dates where each cash flow occurs. The third (last) argument is optional. It contains your guess of what IRR might be. In this example, Excel has calculated IRR to be 74%.
Unfortunately, there is no formula for calculating IRR. Excel uses a numeric method which involves testing many potential values for IRR. As a result, it is best to check your calculations' results and ensure they are correct.
Checking the Calculated IRR
When the correct value of IRR is used as the discount rate, NPV is zero. We enter the formula =XNPV(B36,C2:C35,B2:B35) to check the calculated IRR. XNPV is the most general Excel formula for calculating net present value. In this formula, the first argument (B36) is the discount rate, the second argument (C2:C35) is the list of cash flows, and the third argument (B2:B35) is the list of dates when each cash has flowed. In this example, we see that using the calculated IRR, NPV is almost zero. Thus our calculated IRR is correct.
What is the Internal Rate of Return?
The Internal Rate of Return (IRR) is a financial metric used to estimate the profitability of an investment or project. IRR is the discount rate that makes the net present value (NPV) of future cash flows from the investment equal to zero.
In other words, the IRR is the rate at which the sum of the present value of future cash inflows equals the initial investment or cost of the project. Investors often use IRR as a measure of investment attractiveness or to compare the relative profitability of different investment opportunities.
To calculate the IRR, one needs to solve for the discount rate that makes the NPV of future cash flows equal to zero. IRR is calculated using trial and error, financial software, or calculators.
For example, if an investment requires an initial outlay of $100,000 and generates cash inflows of $30,000 in year 1, $50,000 in year 2, and $70,000 in year 3, the IRR can be calculated as follows:
NPV = -100,000 + 30,000 / (1 + R)1 + 50,000 / (1 + R)2 + 70,000 / (1 + R)3
To find the IRR, one needs to solve for IRR such that NPV = 0.
NPV = 0 = -100,000 + 30,000 / (1 + IRR)1 + 50,000 / (1 + IRR)2 + 70,000 / (1 + IRR)3
This can be done using trial and error, financial software, or calculators.
The IRR is a commonly used metric in capital budgeting and investment analysis, and it is often used in conjunction with other metrics such as NPV and payback period to evaluate the attractiveness of an investment opportunity.
What is the Money-Weighted Rate of Return?
The Money Weighted Rate of Return (MWRR), which is equivalent to the Internal Rate of Return (IRR), is a measure of an investment portfolio's performance that considers the timing and amount of cash flows into and out of the portfolio.
The MWRR considers the actual timing of cash flows and weights them by the amount invested. The MWRR is calculated by finding the discount rate that makes the present value of all cash inflows equal to the current value of all cash outflows. The formula for MWRR is similar to that of IRR.
Present value of cash inflows = Present value of cash outflows ->
CI1/(1+MWRR)ti1 + CI2/(1+MWRR)ti2 + … = CO1/(1+MWRR)to1 + CO2/(1+MWRR)to2 + …
Where CI stands for cash inflow, and CO stands for cash outflow. ti stands for time of inflow, and to stands for time of outflow.
The MWRR is useful for evaluating the performance of investments with irregular cash flows, such as private equity or real estate investments. However, it can be sensitive to the timing and size of cash flows. It may not be a suitable measure for comparing the performance of portfolios with different cash flows or investment strategies.