1

Most of the times, most of the searches are returning the IRR calculation just based on a single initial investment.

Imagine you have a plan that asks investors to supply money not only once, and you dividend the payments into 2 or more sections within the years.

I have looked at the MIRR concept as well, I think it is telling about re-investing from your cash-flows, if I be right, this is a bit different from the scenario which I defined here, but not totally sure maybe it can work.

I pasted a screenshot of my Excel worksheet which I made, please let me know if it's correct or I need to make some refinements.

IRR Calculation

** Loss means the timely payments.

** To clarify what I did let me explain the year 1, the project is gaining 500, in the same year the investor should pay another 1300. so the CF and PV of the year as calculated are "-800"

Improve this question
6
  • Look at the XIRR function. It’s what I use for cash flows that are irregular in time and in and out.
    – Peter K.
    Jul 1, 2018 at 17:12
  • I looked at XIRR as well, but not sure, here son't think our timetable be strange, these are all based on years, just what I wanted in this plan is that I wanted to divide the initial payment into two, one at the beginning and the other in the beginning of the 2nd year. What is your idea about what I implemented here in excel, is it right or wrong? How can I fix it? thanks.
    – Kasrak
    Jul 2, 2018 at 9:55
  • Your calculation of NPV is wrong (I suspect you're not using the discount rate for the future cash flows) but the IRR is correct.
    – D Stanley
    Jul 2, 2018 at 16:30
  • Just a note, IRR and MIRR will have as many roots as there are changes in direction of cash flow. In this case, there is just one change in direction, but be cautious. The IRR,MIRR in Excel isn't always the correct root. You need to find all the roots and figure out which one is the valid root.
    – Dave Harris
    Jul 2, 2018 at 19:52
  • @Dave Harris thanks for the note, could you please provide an example of what you need?
    – Kasrak
    Jul 5, 2018 at 14:41

1 Answer 1

Reset to default
2

IRR should work fine since you use the same time period for the cash flows - just use the total amount for each "year" as your cash flows.

MIRR takes into account the interest rate at which you can borrow the outflows and the rate at which you can invest the inflows, which doesn't seem to be of importance to you.

If you want to validate the IRR, calculate the NPV of the future cash flows using the IRR as the discount rate, and you should get the amount of the "initial" investment (1,300). In other words, if you add the initial investment as an outflow to the PV of the future cash flows (including the "second" investment as an outflow), you should get an NPV of zero.

Improve this answer
6
  • In the top part comments also you mentioned my NPV is wrong, also as the guide you provided looks a bit hard to understand and follow at the first sight, could you please add an updated section below your current answer with some simple steps? Your help is appreciated.
    – Kasrak
    Jul 5, 2018 at 14:44
  • @Sypress Do some research on "present value" since there are many ways to calculate it, but essentially, you divide each cash flow by (1+r) ^ n where r is the discount rate and n is the number of years`. THen add them all up to find the total PV. In your case, the NPV is the sum of all cash flows with no discounting, so that's why I think your NPV formula does not have the discount factor applied.
    – D Stanley
    Jul 5, 2018 at 19:20
  • I used the NPV function of the Excel. So should calculate it manually, or Excel does have an option to add that in place.
    – Kasrak
    Jul 6, 2018 at 10:19
  • 1
    @Sypress NPV should work fine. The syntax is similar to IRR - the discount rate is the first parameter, followed by the range of values. Note that for NPV the values must follow the same period as the discount rate (e.g. if you have monthly values you divide the annual discount rate by 12). With annual cash flows you can use the annual rate as-is.
    – D Stanley
    Jul 6, 2018 at 13:30
  • I made the v2.0 of my previous question, would be appreciated if you can help me on it as well.
    – Kasrak
    Jul 31, 2018 at 11:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .