IRR&NPV Financial lease

[littleps]

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,200,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

[Fred Smith]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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,200,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
>

[[email protected]]

"littleps" wrote:
> Financial lease calculations.

First, given the obvious professional liability of the situation,
it behooves you to continue to seek professional assistance
from a known-competent source. You should not assume
that anyone in cyberspace is competent or is who or what
they say they are. You have obviously already tried that by
talking to a colleague. It might be worthwhile to pay for some
advice from a CPA or similar professional. You can probably
write off the expense of the consultation.

I can tell you that I have no experience with financial leases.

> 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. [....] 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 as I understand it should be like
> IRR(3,512,000…150,000…150,000…150,000…150,000…3,200,000)

Both answers are correct, depending on details that you fail
to provide. See my questions below.

Your problem seems similar to an example in the HP 12C
Owner's Handbook (p.177, Rev E, c. 1982). You might look
there for more guidance.

In either case, one important fix: the first term should be
negative: -3,512,000 or -3,362,000.

Some other details to take into consideration when formulating
the exact IRR expression:

1. You wrote "4 payments – 150,000 quarterly and yet the last
one (after year) is purchase of this asset @ 3,200,000". That
sounds like 3 quarterly payments of $150,000 and a 4th
quarterly(?) payment of $3,200,000.

That is __not__ what your IRR formuation does. Your IRR
formulation has 4 quarterly payments of $150,000 and a
5th (quarterly?) payment of $3,200,000. Your colleague's
formulation has 3 quarterly payments of $150,000 and a
4th quarterly(?) payment of $3,200,000.

2. It is unclear from your English description and from your
IRR "pseudocode" (i.e. not exactly written in Excel form)
whether the quarterly payments are at the end or beginning
of each quarter.

Your formulation suggests that the 1st $150,000 is paid at
the end of the 1st quarter. If, instead, the 1st $150,000 is
paid at the beginning of the lease period, the first cash flow
should be -3,512,000+150,000 = -3,362,000. In fact, that
might what your colleague had in mind.

3. It is unclear from your English description and from your
IRR pseudocode whether the $3,200,000 is paid in the
same period of time as each of the other payments. You
wrote "after year". That sounds like the beginning or end
of the 13th month after beginning of the lease.

If that is the case, use XIRR (for uneven payment periods),
or if you use IRR, be sure to use the same period of time
for each cash flow -- for example, monthly -- using zeros
to fill in periods between quarterly payments.

[littleps]

Thank you for your replies.
Actually as per agreemnt the folowing info is known:
Cost of the asset is 3,512,000 at the beginning of the lease. Inception of the lease is let's say 01.01.2006 for convenience. 4 payments are to be made IN ADVANCE. It means that e.g. 01.01.2006 - 150,000; 01.04.2006 - 150,000; 01.07.2006 - 150,000; 01.10.2006 - 150,000. Then it says that the assset will be purchased on annivesary @ 3,200,000. My understanding is on Jan'07. The problem is correct calculation of internal rate of return (IRR). Based on this percentage rate I'll be able to pick out finance charge out of each pmnt.

Thanks.

[Fred Smith]

Now that we have the data, calculating the result is easy. Your cash flow is as
follows:

01/01/06 -3,362,000
04/01/06 150,000
07/01/06 150,000
10/01/06 150,000
01/01/07 3,200,000

Put those numbers in a table. Feed them to XIRR, as in =xirr(b1:b5,a1:a5). It
will give you your answer (9.17%)

Alternatively, you can use IRR because your cash flows happen at regular
intervals. Remember, however, that the rate of return that IRR gives you is the
*periodic* return. As your payments are quarterly, IRR will calculate a
quarterly return. If you want an annual return, you will need to convert it.
When you compare the two, you'll find a difference in the third decimal because
your payments are not exactly the same number of days apart. That's why most
people like XIRR.

Have we solved your problem for you?

--
Regards,
Fred


"littleps" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you for your replies.
> Actually as per agreemnt the folowing info is known:
> Cost of the asset is 3,512,000 at the beginning of the lease. Inception
> of the lease is let's say 01.01.2006 for convenience. 4 payments are to
> be made IN ADVANCE. It means that e.g. 01.01.2006 - 150,000; 01.04.2006
> - 150,000; 01.07.2006 - 150,000; 01.10.2006 - 150,000. Then it says that
> the assset will be purchased on annivesary @ 3,200,000. My understanding
> is on Jan'07. The problem is correct calculation of internal rate of
> return (IRR). Based on this percentage rate I'll be able to pick out
> finance charge out of each pmnt.
>
> Thanks.
>
>
> --
> littleps
> ------------------------------------------------------------------------
> littleps's Profile:
> http://www.excelforum.com/member.php...o&userid=25482
> View this thread: http://www.excelforum.com/showthread...hreadid=526304
>

[littleps]

Fred,
Thank you for your time. It's more clear to me now. That background will be useful for me. Actually, at first I even didn't know about xirr, xnpv functions.

Regards,
littleps