IRR will work as long as you properly sign the cash flows. For example:

=irr(-3512000,150000,150000,150000,150000,3200000)

Yields a return of 1.73%.

The above flows would assume the balloon payment is made 15 months after the
initial lease. If the balloon payment is supposed to be 12 months after the
lease (ie, coincident with the last quarterly payment), you would use:

=irr(-3512000,150000,150000,150000,3350000)

Also, you might want to look into the XIRR function (part of the Analysis
Tookpak addin). I find it a lot easier to use, because you simply specify the
cash flow and the date on which it occurred.

--
Regards,
Fred


"littleps" wrote in
message ...

Financial lease calculations.
There is an asset of fair value @ 3,512,000. There are 4 payments -
150,000 quarterly and yet the last one (after year) is purchase of this
asset @ 3,200,000. Quaterly payments are made in advance. To record the
value I need to use min of either fair value or NPV of MLP.
First of all, I need to calculate IRR, then MLP based on IRR. A
colleague of mine told me that because of advance payments IRR should
be calculated as IRR(3,362,000.150,000.150,000.150,000.3,200,000)
saying that initial fair value should be decreased by 150,000. But as I
understand it should be like
IRR(3,512,000.150,000.150,000.150,000.150,000.3,20 0,000)

Appreciate if you can advise on these functions and correct lease
treatment since I need to calculate impact on P&L & BS of finance
charge and reduction in obligations. Unfortunetely never met with
financial lease. If you know any useful links on this issue .I'll be
obliged to you for them.

Thanks,
Littleps


--
littleps
------------------------------------------------------------------------
littleps's Profile:
http://www.excelforum.com/member.php...o&userid=25482
View this thread: http://www.excelforum.com/showthread...hreadid=526304