Internal Rate of Return & Net Present Value

Original Size

Click on the calculator's "Help" button for usage details. __It is VERY important that you understand about dates and cash flows__.

Related - Some investors like to use the Modified Rate of Return Calculator (MIRR). It calculates a more conservative IRR since the user can enter a different reinvestment rate.

It is the discount rate (think of it as you would an interest rate) that results in a net present value of the cash inflows (investments) and cash outflows (returns or withdrawals) equal to zero. More weight is given to the earlier cash flows than to the later cash flows because of the time value of money.

For the investor, the IRR is important, but an often overlooked number.

It's an important number because it is the tool that gives the investor the ability to compare investments. That is, the IRR normalizes the results for different investments.

Take for example two rental properties that are for sale. The offer price for both buildings is about the same. Projected rents are about the same. However one will have a higher upfront renovation cost while the other has higher property taxes. How does an investor know which purchase represents the better investment?

They can use an IRR calculator to make this determination.

A note of caution. When comparing investments, never make the comparison using internal rates of return calculated with different calculators.

Why is that?

Because two different calculators may calculate the results slightly differently. Neither one of them will necessarily be wrong either. (Consider for a moment that Microsoft Excel has two IRR functions that may calculate different IRRs for the same cash flows.) You don't need to get hung up on this idea. But it is something to be aware of so that you understand how to use the results correctly.

For the record, this calculator calculates the IRR by counting days (some calculators count periods).

In finance jargon, the net present value is the combined present value of both the investment cash flow and the return or withdrawal cash flow. To calculate the net present value, the user must enter a "Discount Rate." The "Discount Rate" is simply your desired rate of return (ROR).

The NPV is the calculation investors use to learn if they are paying too much for an investment (or if they could pay more) relative to the rate of return they want to earn. If the net present value is negative, the initial investment is too high for the investor to meet their goal ROR. If the NPV is positive, the investor can pay that amount more for the investment, and they'll still earn what they want to earn.

Here's an example....

Jack invests in already issued mortgages. Jack can buy a mortgage for $190,000 that has 210 remaining monthly payments of $1,235.90 each. The next payment is due on June 1. Jack wants to earn 6% on his investments.

Is this a good deal for Jack?

Follow these steps.

- Enter -190,000.00 for the "Initial Investment"
- Set "Initial Investment Date". In this case, that's the date Jack plans to purchase the mortgage. Use May 22 to follow along.
- Click on "Add Series". Create 210 monthly entries of $1,235.90 starting on June 1.
- Enter Jack's personal "Discount Rate" i.e. 6% — the ROR he wants to earn on his investments.
- Click "Calc"
- IRR = 3.847%
- NPV = -$27,198.22

At 3.8%, Jack will not earn the 6% he desires.

What is Jack to do?

This is where the NPV calculation is useful. It tells Jack that he is paying $27,198.22 too much for the investment. See for yourself. Change the "Initial Investment" to $-162,801.78 ($190,000.00 - $27,198.22) and click "Calc" again. Now we have:

- IRR = 6.0%
- NPV = 0.0

Jack is now a happy man assuming he can negotiate the price he needs.

Note: When the NPV is positive, that is the amount the investor can increase the initial investment by and still receive the desired ROR.

I think users will find these enhancements useful:

- "Add Series" option. Create repeated cash flows easily. Work with hundreds of cash flows without manual entry.
- Creating entries with "Add Series" does not populate the existing dates with values or reset the existing values. It creates NEW entries. If a cash flow entry exists on July 1, and you then use the "Add Series" feature to add monthly cash flows starting on June 1, you'll have two entries for July 1.
- "Add Series" feature can be used to add additional "0" entries that you can manually edit. There is no longer a restriction to 96 inputs.
- Use the "Remove 0's" feature to be left with a nice clean look.

- Now prints all cash flows
- Optionally removes zero entries so as not to print.
- Net Present Value Calculation - NPV
- Dates created from "First Cash Flow Date" not "Initial Investment Date."

Calendar Tip: When using the calendar, click on the month at the top to list the months, then, if needed, click on the year at the top to list years. Click to select a year, select a month and select a day. Naturally, you can scroll through the months and days too. Or you can click on "Today" to quickly select the current date.

If you prefer not using a calendar, single click on a date or use the [Tab] key (or [Shift][Tab]) to select a date. Then, as mentioned, type 8 digits only - no need to type the date part separators. Also, because the date is selected, you do not need to clear the prior date before typing. If your selected date format equals mm/dd/yyyy, then for Dec. 1, 2016, type 12012016.

And one more time: you do **not** need to enter the cash flows in date order. You have a computer. It and this calculator are smart enough to sort the cash flows for you once you've clicked the "Calc" button. :-)

IRR is the annualized return on an investment expressed as a percentage.

The investment can be made up of a series of cash flows. That is, there can be more than one investment or one withdrawal. (However, there has to be at least one or each.) The cash flows may occur on any date and for any amount.

It is important to use the right sign (positive or negative) for each cash flow. How do you know what the correct sign is?

Think of it this way. When you first invest, you have to write a check or transfer funds. Writing a check decreases your account balance. Therefore, enter all investment cash flows, including the "Initial Investment" as negative values

When you earn money back on your investment, you can deposit it into your checking account. The return increases your account balance. Therefore, enter all investment returns, including the final liquidation value of your investment, as positive values.

The scheduled dates update every time you change the "Cash Flow Frequency." The new dates are calculated based on the "First Cash Flow Date." But the "Cash Flow Frequency" has no direct impact on the IRR result per se. The calculator only uses the "Cash Flow Frequency" setting to create dates that most closely match your investment cash flows. If, in general, you only make additional investments (or withdrawals) twice a year, then set "Cash Flow Frequency" to "Semiannually" for example.

**Also, zero amount cash flows have no impact on the IRR result.** You may set the frequency to "Monthly," and if there are only four cash flows in a given year, you just leave eight set to 0. (This also applies to 0 cash flow amounts after you've entered the final liquidation value as well.

**You do not need to enter cash flows in date order.** The calculator will sort them before calculating the result. This feature is handy, of course, if you realize that you missed entering a cash flow. Enter the amount in any available cell. Then change the date associated with that cell. Click "Calc" to sort.

If you mistakenly duplicate a cash flow, simply set one of the duplicates to "0".

Changing the "First Cash Flow Date" will reset the dates without clearing the values you've entered.

Depending on the order you use "First Cash Flow Date," "Remove 0's" and "Add Series," the "First Cash Flow Date" will frequently not be the first date in the input area. This is not a bug. Changing "First Cash Flow Date" initializes a series starting on the date selected. However, the user can change the date, or it can be removed with "Remove 0's" if the value for the start date is 0. Finally, a user can insert a series with a date before "First Cash Flow Date."

This makes no sense, I put the beginning value in, I get a monthly amount from my retirement IRA but there is no place to put the year end value so that an IRR can be calculated. I have a formula that seems to work in excel and I don’t mean the function, but if the year end value is a loss, then it doesn’t work..

The last cell with a value (by date) is the ending value.

I do need to get some examples posted, but I believe this is also covered in Help. But here’s a simple example:

Investment: -10,000

Investment Date: 03/05/2016

withdrawal from investment (your income): 75.00

cash flow date: 06/08/2016

withdrawal: 75.00

cash flow date: 09/08/2016

new investment: -2,000.00

cash flow date: 10/01/2016

final value: 8,125.00value date (in the cash flow section) 12/31/2016The final value is the amount you could withdrawal from the investment to close it out. The above shows there has been a loss since the amount available to withdrawal on the IRR date (valuation date) is less than what had been invested

>>> but if the year end value is a loss, then it doesn’t work..The user is not entering the gain or loss. The calculator calculates the gain (or loss). The user enters investments and withdrawals.

I like to think of it this way – when you are making an investment, you are writing a check. The amount comes out of your checking account and is thus negative. When you get earning back from your investment, the amount goes into your checking account and are positive.

And with the cash flow entries, don’t worry if they are out of order, the calculator sorts them. If you miss one, put it at the end.

Hope this helps.

Thanks, I’ll try it again. I ended up doing it long hand but it wasn’t compounded the way I think it’s supposed to be. I did 5 years and went to bed at 7 a.m. this morning. I did it this way

10000 current value

-12000 original investment

= -2000

+1200 an exp of taking $100 each month & I’m adding this to the -2000

because withdrawals I make shouldn’t negatively impact

financial advisor

= -800

12000 divided by original investment

= -0.067%

We’ll see how close my example above matches when I try it again. I do remember buying some Pine Grove software a few years ago, it’s on my desktop. I’m on my laptop. I found it, it is an IRR calculator and I put in the data about, I didn’t write down the result but it was -6.xx% and that was putting the $100 each month as positive, if I put them in as negative because it is an outflow, the result changed to -22xx% and this was definitely wrong, it also added the 1200 to the 2000 but in the first example the amount was -800 which is what I believe it should be. So I don’t know is -6% or -0.06% correct? I’ll have to try a couple of the positive ones where the ROR was double digits. I could see the above being multiplied by 100 but not say 14.3%

In your example, the IRR is negative, if I understand you correctly.Think of it this way, there are only 3 important numbers.

-12,000 invested

1,200 returned to you

10,000 final value

You’ve taken out from the investment $1,200 and it’s current value is $10,000 = 11,200. There is a loss of $800.

Therefore, the internal rate of return has to be negative.

This really answered my problem, thank you!

https://www.youtube.com/watch?v=r_lQdwJgdfk

Respected sir I want to take loan amount of RS 300000 from Mahendra finance and I want to pay monthly installment of the RS 20000/month, what will be the IRR

Interest rates , thank you

Are you asking how you set up the calculation? If so, you can enter the loan amount as a positive value and each payment as a negative value.

hi karl can you help with this one- i contributed from 1 may 02 to 1 may 16

$680301 and my total invesment is now 160 697 how do i work out my return of the above calculator/?

I’ll try, but I may need some more details. When figuring the IRR, totals aren’t really what’s needed. Do you know all the investment amounts and withdrawals, and their dates? At a high level, what you are going to need to do is to enter each investment on the date it was made as a negative amount. And if you have withdrawals along the way, enter those as positive amounts, again on the dates they happened. And the final entry can be today’s date and the value of the investment account. That is, if you were to liquidate the account(s) today, how much would you get? That’s a positive value. Once these values are entered, click “calc” of course.

Is this enough to get you started? If not, please ask a more specific question as to what is not clear.

Hi Karl,

Thank you for the calculator…but when I want to print the detail it only gives me print screen. Am I missing a step?

No, you didn’t miss anything, I did. I need to update the calculator so that all the cash flows get printed. If you need to print a calculation in the mean time, you can buy the Windows version of this calculator for $4.95. Click on the “Store” menu choice at the top of any page and scroll down the list for IRR calculator. (Windows only.)

Very helpful. It would be great if one could directly enter dates, rather than use only calendar entries. I was calculating IRR for investments started in 2000, so required many back steps of calendar.

Overall, I appreciate intuitive nature of table. Results seem right on

Thanks

Can you tell me if you are using a desktop or a tablet or phone? There might be a difference in how the calculator work across devices.

If you are using a desktop computer or notebook, you should be able to very easily type in the date. When you type the date, just type numbers however. Don’t type any of the slashes between the date parts.

I did notice that the calendar seems to cover part of the date on smaller screens, but you should still be able to type the date. Also, you don’t have to worry about typing the dates in order – the computer is smart enough to sort them :-).

Still, it does need to be made smoother.

Hello, I’ve entered what I believe to be the correct negative and positive formatted values into the calculator. But I am continually receiving an “IRR cannot be calculated” error. Basically the numbers are as follows:

Initial Investment: -$5000.00 (04/08/2008)

Premium payment: -$225.41 (03/08/2013)

Premium payment: -$450.83 (08/08/2013)

Premium payment: -$450.83 (08/01/2014)

Premium payment: -$450.83 (08/26/2015)

Protected Return: $9,090.86 (12/31/2016)

I’m fairly sure that the Initial Investment should be a negative number as well as the premium payments. The Projected Return was entered as a positive number.

Would you have any ideas as to what I’m doing incorrectly that is creating the error?

Thanks

Hi, using your inputs, I get an irr of 4.463%. My guess is, that while you had all the amounts correctly entered, there was probably an error with the dates.

Note, you should be able to right click on this image and select view in another tab to see the image at its full size.

Hi Karl, I tried inputting the numbers and dates exactly as you did several times and still got no results. I was using a Safari browser and then tried re-entering the data in Google Chrome and suddenly got the same calculations as you did. No more Safari !! Google Chrome from now on !!

Thanks again

Hi. Glad to hear that you got it working. Thanks for letting me know. I’ll take a look at why there’s a problem with Safari.

So I switched from ROI to IRR for my investment in energy conservation.

I assumed (at this point), that I draw my ‘benefit’ (return) annually. I put in the start date of 1/1/17. It forces me to input the return (so it could be different every year, right (and most likely would be w/ the inflation of energy each year)). I assume at this point that the annual benefit draw is always the same. However, it lists the draws as bi monthly; forcing me to ‘hunt’ for each year and then input my draw/return.

And it doesn’t let me input the values for 15 years … only about 6. I hope I don’t have to input BOTH the date and the amount for each of my years.

Am I doing something wrong?

It’s interesting that the analysis tool we were told to use generates an ROI for this type of scenario, not an IRR.

Oh boy, I see there is a problem and the calculator is not working as intended. When the page first loads, the cash flow frequency is monthly and the dates should reflect that and they do. If a user changes the frequency, then the dates should update. So, in your case, you would set an initial investment date of Jan. 1, 2017 and the cash flow to annually. Doing this should update the dates to Jan. 1, 2018, Jan. 1, 2019, Jan. 1, 2020….

I’ll try to have a fix in for this by early next week.

To your questions. IRR, ROR and ROI are all annualized return on investment calculations.

Mark, I just released an update to this calculator per the notes above. Initializing the dates for your particular cash flow needs now work much better (I hope!). Please see notes above. Thanks for taking the time to mention you were having a problem with this.

Is a ROR (rate of return) the same as ROI?

Yes.

This is so useful, and it took me so long to find. While Quicken is essential for keeping track of investments (IMHO), it often does not correctly calculate the IRR.

THANK YOU SO MUCH!!!

You’re so welcome. Please come back again and please tell your friends about this site.

Hi Karl –

Thanks for providing this model. Question: You had mentioned in your Dec 1st update “The selection of frequency of cash flow itself has no impact on the calculated result.” Can you please explain this?

Running this in your model, actually shows that the frequency DOES have an impact on the calculation.

For example, if you were to receive “monthly” payments vs. “quarterly” payments, for the same initial Investment, the IRR should go up due to time value of money.

HERE ARE THE INPUTS USING YOUR FINANCIAL MODEL:

Initial Investment: -$5,500

Investment Date: 5/1/2017

Cash Flow Frequency: MONTHLY

No Date Amount No Date Amount

1 06/01/2017 1,000.00 2 07/01/2017 1,000.00

3 08/01/2017 500.00 4 09/01/2017 500.00

5 10/01/2017 1,000.00 6 11/01/2017 1,000.00

7 12/01/2017 1,000.00

IRR = 29.305%

As Compared to: QUARTERLY PAYMENTS

Initial Investment: -$5,500

Investment Date: 5/1/2017

Cash Flow Frequency: QUARTERLY

No Date Amount No Date Amount

1 08/01/2017 1,000.00 2 11/01/2017 1,000.00

3 02/01/2018 500.00 4 05/01/2018 500.00

5 08/01/2018 1,000.00 6 11/01/2018 1,000.00

7 02/01/2019 1,000.00

IRR = 8.98%

You’re right. But that’s not what I meant.

“The selection of frequency of cash flow itself has no impact on the calculated result.” Can you please explain this?

The frequency selection is only a tool for creating dates. A user should select a frequency that most closely matches the actual cash flow for convenience. But if a user has an initial date of Jan 1 and selects “Monthly” and enters cash flows on Jan 1, Apr 1, Jul 1 and Oct 1 they will get the same calculated IRR as if they selected quarterly cash flows and enter the cash flows on the same dates.

So, the

selectionof frequency has no impact, but the actual frequency certainly does. The calculator is looking at the dates themselves.Does this clarify?

Thank you Karl. Yes, that answers my question.

Only 8 years data can be input.

No, that’s not correct. The limit is 96 inputs but the number of years is a function of how "Cash Flow Frequency" is set. If you set it to semiannual for example, then you can calculate the IRR for an investment that goes on for 48 years.

How many years are you looking for? Would bi-monthly cash flows work for you? That will give you 16 years.

invest $1.6 million in a new MRI machine. The MRI will

be depreciated its 5-year economic life to a $200,000 salvage value. Additional

revenues attributed to the new MRI will be in the amount of $1.5 million per year

for 5 years. Additional operating expenses, excluding depreciation expense, will

amount to $1 million per year for 5 years. Over the life of the machine, net

working capital will increase by $30,000 over the life of the project.

© 2015 Laureate Education, Inc. Page 2 of 2

a. Assuming that the hospital is a non-profit entity, what is the project’s net

present value (NPV) at a discount rate of 8%, and what is the project’s

IRR?

b. Assuming that the hospital is a for-profit entity and the tax rate is 30%,

what is the project’s NPV at a cost of capital of 8%, and what is the

project’s IRR?

I am having trouble, can someone please help? thank you.

Anjali

Hi Anjali, I can help to the extent you have a question about how the calculator works. How the before and/or after tax flows should be interpreted is probably a question you should ask the hospital’s comptroller.

The calculator does not calculate NPV. SolveIT! has a NVP calculator. I’ll probably release an online one sometime this month, I hope.

Also, the best use of an IRR calculator is not for calculating an IRR on a single investment but rather for calculating an IRR on two or more investments where the same cash flow rules were applied.