IRR calculation to not discount the first year of cashflow - any chance?

[Freigei5t]

Hi there,

i'm looking for a solution to calculate an IRR without discounting the first year (or more years) of the annual cashflows.

It seems there is no way to use the XIRR for that. Wondering if there is any manual way I could calculate it via a function?

In general, what i would need is a function same as (X)IRR not looking for ZERO, but a specified NPV (which would be the sum of all undiscounted years until discounting should be considered; used like an opening balance)

Do you see there any possibility?

The only way I see right now, is to use Excel's goal seek function, summing up all annual NPVs (with the first years unaffected by any discounting).

Thank you!

[Anonymous]

Hi @Freigei5t ,

One possible way is to use the modified internal rate of return (MIRR) formula. The MIRR is similar to the IRR, but it allows you to specify different discount rates for the initial investment and the future cash flows. You can set the discount rate for the initial investment to zero, and use your desired discount rate for the future cash flows. This way, the first year cash flow will not be discounted, and the MIRR will reflect the true profitability of the project. You can use the MIRR function in Excel or other spreadsheet programs to calculate the MIRR.

MIRR function - Microsoft Support

Power BI XIRR or IRR (Internal Rate of Return) function| Internal rate of return in pbi| xirr in pbi...

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Freigei5t]

Thank you for the reply.

I'm afraid the MIRR-solution will not work in my case, as it should not differenciate between cashflows being negative or positive. Usual cashflow profiles can even show 4 years of negative cashflows before generating revenue, and even then, there could be years of negative cashflows somewhere throughout the lifetime of the asset.

Discounting should start from a certain year onwards (set by the user via slicer).