IRR Based Investment Comparison Model in Excel

This IRR template was designed for CFO's or anyone that is reviewing projects that have an initial investment cost and projected future cash flows. The goal was a super simple user input interface and easy analysis that is automated per the assumptions entered. You can project out for up to 20 years.


$45.00 USD

The template will be immediately available to download after purchase. Check out all templates here.


IRR comparison chart

IRR or internal rate of return is a highly used investment return metric that helps CFO's or project managers/owners/investors determine the financial viability of putting cash at X investment per project vs. putting it in some other cash returning investment.

Let's start with the last things first. That would be the dashboard. It lists up to 19 different projects, dynamically lists their IRR's and lets you input a required rate of return (this just means the return you know you could get with the cash somewhere else or the minimum compounding return you are willing to accept with the initial investment) that results in an approve/reject outcome.

There is also a comparison chart to show the IRR vs. required rate of return.

There are 3 primary inputs:
  • Investment amount of project (and the date of this)
  • Expected net cash flows from the project (and the date those cash flows are expected to begin)
  • Finally, the month those cash flows are expected to stop (and an exit value if applicable)
I also added in a consolidated IRR so you can see what the return looks like assuming the first month you have cash going out is the first month and the last month you have cash coming in is the last month for that calculation.

One of the more difficult parts of doing an IRR comparison with differing cash in and out periods is using the indirect formula in combination with the XIRR function. I have done this in a way where the formula knows the right range of cash flows to reference based on each projects start and end dates. This is the primary value you are getting beyond an IRR comparison dashboard.

And, if you need a quick refresher on what internal rate of return means, it is the discount rate you have to apply to the value of the future cash flows in order for those future cash flows to have a present value (value today) that is equal to the initial investment amount. So...the higher the better.