IRR&NPV Financial lease

L

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
 
H

Harlan Grove

littleps wrote...
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)
Click to expand...

If you obtain *full* ownership rights (e.g., the right to sell it) to
the asset at time 0, then maybe you record the asset value as cash in
at time 0. However, if you don't get full ownership rights until the
time of the final payment, then you shouldn't record the asset value as
cash in until the time of the final payment. Also, IRR will return
errors unless there's at least one positive and one negative cashflow.
Payments are cash out, and usually shown as negative values.

So if you get full ownership rights at the time of the first payment,
your IRR formula should look like

=(1+IRR({3362000;-150000;-150000;-3200000}))^4-1

which gives an *annualized* IRR since IRR passed quarterly cashflows
returns a quarterly rather than annual IRR. On the other hand, if you
don't get full ownership rights until the time of the final payment,
your IRR formula should look like

=(1+IRR({-150000;-150000;-150000;312000}))^4-1

The first formula gives a modest positive annualized IRR. The second
formula gives a rather large negative annualized IRR. If you get some
use (economic benefit) but not full ownership rights from the time of
the initial payment, then you need to include the estimated economic
benefit (cash in, so positive values) in the quarterly cashflows.
 
J

joeu2004

Please note that my comments about competent advice in
cyberspace were not directed toward any particular individual
response. I wrote those comments before I had seen any
other responses to the OP.
 
F

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.
 
G

Guest

littleps said:
Financial lease calculations.
Click to expand...

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)
Click to expand...

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.
 
L

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.
 
H

Harlan Grove

littleps wrote...
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.
Click to expand...

The difficulty is the phrase 'IN ADVANCE', which seems to be before the
purchase is completed on 1-Jan-2007 but on and after the presumably one
year lease period which seems to begin on 1-Jan-2006.

Presumably you receive some economic use from the asset beginning on
1-Jan-2006, but you don't have ownership until 1-Jan-2007.
Unfortunately, in economic if not accounting terms, that means you
should realize some cash inflow reflecting the value of your economic
use during the lease period, 1-Jan-2006 to 1-Jan-2007, which would
offset somewhat the lease payments, and the value of completing the
assumption of ownership on 1-Jan-2007 should be less than the asset's
$3,512,000 value on 1-Jan-2006 (unless the asset's value is expected to
increase even after factoring in use during 2006).

If *accounting* rather than economic rules apply, and the accounting
rules require you to treat this as an acquisition on 1-Jan-2006, then
the value of cash in on 1-Jan-2006 is the asset value less the first
quarterly payment, and IRR will give a positive result, which will be
an effective *quarterly* interest rate. You shouldn't need to ask a CPA
to find out that IRR fed quarterly cashflows returns quarterly interest
rates.
 
F

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?
 
L

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
 
Top