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

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

"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,20 0,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.

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

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" wrote in
message ...

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

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


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