NPV, IRR and MIRR help

[Julie]

Hi all,

I am working on a spreadsheet that will compute the NPV, IRR, and MIRR of different computer
systems, as a way to analyze the investment. But I am at a loss with regards to using Excel's
built-in functions (specifically the NPV, IRR, MIRR). The data I have is:

System Cash Flows by Year: Year 1 Year 2 Year 3 Year 4
Revenues (Inflows) 10,000.00 12,300.00 14,609.00 15,927.00
Expenses (Outflows) 15,376.00 8,165.00 3,520.00 570.00

....and the functions call for an intial expense followed by the cash inflows, there must be at least
one negative value...the way I have the data there is an initial expense at Year 1, but the expenses
comtinue through every year

I am hoping to end up with the following financial metrics:
Total Cost of Ownership (TCO)
Return on Investment (ROI)
Payback Period
Internal Rate of Return (IRR)
Modified Internal Rate of Return (MIRR)
Net Present Value (NPV)
Profitability Index (PI)

I know that TCO is rather vague, but any help in using Excel's built-in functions to produce these
metrics would be greatly appreciated. I have looked through Excel's help system and it still does
not help.

TIA,

Julie

 

[Norman Harker]

Hi Julie!

There's no requirement for changes in sign for NPV or XNPV.

You need to set up a column for net flow (Revenue - Expenses)

With XNPV and XIRR you'll need an additional column with dates with the
earliest date first.

The NPV function assumes that the flow in the first cell of the range
argument is received at the end of the first period. This is different from
accepted definitions of NPV and to adjust for an income at point 0 you need
to use:

=NPV(DiscountRate,OriginFlow:EndFlow)*(1+DiscountRate)
or
=NPV(DiscountRate,SecondFlow:EndFlow)+OriginFlow

That should be enough to get you started but I'm sure that you'll still have
some problems. Follow the above and then scream for more help. Keep to the
same posting thread.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi all,

I am working on a spreadsheet that will compute the NPV, IRR, and MIRR of different computer
systems, as a way to analyze the investment. But I am at a loss with regards to using Excel's
built-in functions (specifically the NPV, IRR, MIRR). The data I have is:

System Cash Flows by Year: Year 1 Year 2 Year 3 Year 4
Revenues (Inflows) 10,000.00 12,300.00 14,609.00 15,927.00
Expenses (Outflows) 15,376.00 8,165.00 3,520.00 570.00

...and the functions call for an intial expense followed by the cash

inflows, there must be at least

 

[Julie]

Hi Julie!

There's no requirement for changes in sign for NPV or XNPV.

You need to set up a column for net flow (Revenue - Expenses)

With XNPV and XIRR you'll need an additional column with dates with the
earliest date first.

The NPV function assumes that the flow in the first cell of the range
argument is received at the end of the first period. This is different from
accepted definitions of NPV and to adjust for an income at point 0 you need
to use:

=NPV(DiscountRate,OriginFlow:EndFlow)*(1+DiscountRate)
or
=NPV(DiscountRate,SecondFlow:EndFlow)+OriginFlow

That should be enough to get you started but I'm sure that you'll still have
some problems. Follow the above and then scream for more help. Keep to the
same posting thread.

Thank Norman,

But what do you mean by "income at point 0"...do you mean income at the beginning of the first
period? I already have the yaerly net flows calculated for other calcualtions,these, of course are
based on year-end numbers.

What about values for the MIRR and IRR functions, about which the Excel Help says,

"Values must contain at least one positive value and one negative value to calculate the modified
internal rate of return. Otherwise, MIRR returns the #DIV/0! error value"

Julie

 

[Norman Harker]

Hi Julie!

Re: But what do you mean by "income at point 0"...do you mean income at the
beginning of the first period?

Yes!

Re IRR and MIRR and XIRR

Yes! There must be more than one sign in the cash flows. Otherwise, assuming
all positive flows, the rate of return is infinity because you have a zero
outlay. If all flows were negative, the return would be negative infinity
for the same reason.

If you set up your net flow as recommended, you will meet this requirement.

Watch it with MIRR!!!

MIRR is a special case IRR that was intended to cover cases where there is a
multiple internal rate of return. This can arise where there is more than
one sign change in the cash flow. It resolves the problem by applying a
finance rate to the negative flows. In my view this method is distinctly
questionable as in reality finance rates would be applied to balances; not
flows. However, this is only an extension of criticisms of the blind use of
the IRR concept where there are non-simple investments involving a single
outflow followed by a series of positive flows.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thank Norman,

But what do you mean by "income at point 0"...do you mean income at the beginning of the first
period? I already have the yaerly net flows calculated for other

calcualtions,these, of course are

 

[Tony Kenck]

Just a comment on MIRR.

It does handle the types of cases that you mention, but it is also
quiite useful for general purposes.

IRR is internal rate of return. It is an ok metric, but it has a
shortcoming--it can lead to inaccurate comparisons when projects have
different timing, cash flow patterns or durations. This is mainly
because IRR does not consider what will happen to cash that a project
spins off. MIRR takes into account what you will actuially do with the
cash. So from a comparison standpoint you now have a basis for comparing
a 3 year 28% IRR project with that 15 year 15% IRR project. They might
have very similar MIRRs.

MIRR really is a better measure of investment efficiency.

Tony Kenck

 

[Norman Harker]

Hi Tony!

But doesn't MIRR merely handle negative and positive *flows*
differently.

In the real world we handle negative and positive *balances*
differently.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Tony Kenck]

I'm not positive what you mean, but I believe that in the calculation,
the flows become balances that would compound at the rate specified in
the MIRR formula.

Tony

 

[Alan]

Hi all,

I am working on a spreadsheet that will compute the NPV, IRR, and
MIRR of different computer systems, as a way to analyze the
investment. But I am at a loss with regards to using Excel's
built-in functions (specifically the NPV, IRR, MIRR). The data I
have is:

System Cash Flows by Year: Year 1 Year 2 Year 3 Year 4
Revenues (Inflows) 10,000.00 12,300.00 14,609.00 15,927.00
Expenses (Outflows) 15,376.00 8,165.00 3,520.00 570.00

...and the functions call for an intial expense followed by the cash
inflows, there must be at least one negative value...the way I have
the data there is an initial expense at Year 1, but the expenses
comtinue through every year

I am hoping to end up with the following financial metrics:
Total Cost of Ownership (TCO)
Return on Investment (ROI)
Payback Period
Internal Rate of Return (IRR)
Modified Internal Rate of Return (MIRR)
Net Present Value (NPV)
Profitability Index (PI)

I know that TCO is rather vague, but any help in using Excel's
built-in functions to produce these metrics would be greatly
appreciated. I have looked through Excel's help system and it still
does not help.

TIA,

Julie

Hi Julie,

As has been mentioned by Norman already
(NPV (and derivatives)
do not require both cash inflows and outflows in order to calculate.

When you consider what NPV is, that make perfect sense, since you are
simply quantifying the present value of future cash flows, which can
certainly be all outflows, all inflows, or a mixture.


IRR (and derivates) is different in that it attempts to quantify the
rate of return on an investment. In order to have a rate of return,
you would have to have at least one outflow and at least one inflow
(more is fine).

Norman also hinted at this in his comment that if you only have flows
in one direction, you will have an infinite rate of return.


MIRR (Modified Internal Rate of Return) is often considered superior
to IRR due to the implicit assumption in IRR that cash flows from a
project can be re-invested in the (or a) project with exactly the same
returns. This is very rare in pratice, and hence the Modified
Internal Rate of Return was developed to take into account that
surplus cash (for example) would be re-invested at a given finance
rate (perhaps the WACC of the investing business or a Deposite rate).

You may see mentions of MIRR being related to a Multiple IRR
( I am not sure where
this originally came from, but in the real world I have never heard
anyone use that terminology when referring to the MIRR function in
Excel. This is not intended as a criticism of the poster, just a
comment that things can get confused easily. It is possible that the
inclusion of discussions of Multiples IRRs and Modified IRRs in single
discussion documents has led to the confusion (see the reference below
for an example of that).

It is true that a single project can have more than one calculated
IRR, but that is a mathematical issue, and is not related to the
rationale for developing MIRR.

You may wish to read this article on MIRR which gives a more through
backing to the concept:

http://garnet.acns.fsu.edu/~ppeters/fin3403/readings/capbud/capbud2.htm


Hopefully I have not muddied the waters too much!

Alan.

 

[Norman Harker]

Hi Tony!

The MIRR algorithm:

Accumulates positive flows at the re-investment rate to the end of the
term.
Discounts the negative flows at the finance rate to the beginning of
the term.
Calculates the IRR of the cash flow comprising these two flows.

A more rational / real world approach:

Tracks balances and applies a finance rate to negative balances and an
investment rate to positive balances.
The rate of return received would be the investment rate that produces
a final balance of 0 and is found using Solver.

You'll find demonstrations of these different approaches in Chapter
12, Examples 13-15 in John Walkenbach's Excel 2003 Formulas. I can
send you a workbook with these examples if you want. You'll find that
the rate of return is demonstrably not the MIRR.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/