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!

No need to post to more than one group; it can waste the time of people
trying to help and produce disconnected series of postings. Here's the reply
given in microsoft.public.excel.

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