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."

Okay I’m still trying to wrap my mind around a few things. Help me with this example.

If I invested $100 at the beginning of this month and got paid $200 back at the end of the month to end the deal, making $100 profit.

And I could do that each month for one year but I could never invest more than the $100 each month what ROI rate would that be?

My mind works like this: If it was just simply $100 profit on $100 invested for 1 whole year it would be 100% ROI. Would it simply be 12 x 100% since I can do that 12 times in one year? So would each deal actually be at a yearly ROI rate of 1200%?

No, it wouldn’t be 1200% (if it were, people wouldn’t need specialized calculators :-). It would be higher.

For the moment, don’t think of the rate-of-return as a percent, think of it as an amount. In this case the "rate" of return is $100 earned on a $100 invested in one month. When doing an annualized calculation, unless there’s a withdrawal, the starting amount at the 2nd month is $200 and your rate is a $100 on a $100 (or $200 on $200) so after the 2nd month, the investment will be worth approx. $400.00 (I’m rounding).

Yes there would be a withdrawal each month. Actually the whole deal ends each month in that example and I can only put back in $100 for the next month. I would never have the opportunity to have more than $100 invested at any point with that example. So the most I could possibly earn per month would be $100. Would that be 1200% that way?

Another example also.

If a one year deal with a one time payment at the end = 100% ROI for $100 invested and $100 profit.

If the same deal is done but I’m paid in just 6 months instead of a year I’m thinking that is at a rate of 200% ROI for the year.

Since I would have time to do that same deal twice in a year basically. Does this seem right to you?

another example:

If I invested $100 at the beginning of the year and I collected $1200 profit at the end of the year plus my $100 back. Would the yearly ROI% be 1200%? For just a one time 1 year deal.

If the above is 1200% ROI then I need a way to calculate how much it goes up by receiving weekly payments instead of just one payment at the end of the year.

What I’m not getting across is the concept of annualization.

In the 6 month example, it’s not about how often you get to do a deal. As you stated, you invest $100 and in 6 months you have $200. That’s over a 300% "annualized rate of return". "If the same deal is done" it’s the same deal and it still has greater than a 300% ROR. That the usefulness or a ROR calculation. It allows you to compare deals that are different. The calculation allows you to normalize the rate of return to a year, regardless of the term of the investment.

But now, if your question is, I made two deals in a 12 month period, what’s my combined rate of return.

You should enter the amount invested in the first deal and enter a withdrawal amount including the initial investment (after all, the deals is finished) and then enter an investment amount on the date the second investment starts followed by the withdrawals including the return of the original investment made. The calculated IRR will be your annualized rate of return for the 2 deals.

Is there a way to calculate these without assuming all profits are being reinvested?

I think I want the annualized ROI without compounding, assuming reinvestment amt or rate, adding interest, or allowing for the calculator’s assumption of the value of money.

Sure. With this calculator, you can withdraw any amount on any date you want from the cash flow stream.

From your earlier example, you can make an initial investment of $100 and then withdrawal $100 each month. The return will be huge because you are doubling your money every month.