I have a question regarding present values of uneven payments. We signed a lease that orignally was approx 30 months long and had three payments even payments. The next month, it was amended and required three upfront payments monthly and then an annual payment on January 15th of each year through 2014.

I am using the XNPV function, but you will see on the attached that I am not zeroing out on my amortization table which makes me think I am doing this all wrong.

Can someone take a look at the attached spreadsheet (had to zip it because its the only way they accept excel) and let me know if they agree with my calc. The original lease is on the first tab and the amended lease is on the second.

Thanks!

I didn't look at your file but here is something you can try.

Function npv_5(d As Range, i As Range, e As Range)
Static values(4) As Double
values(0) = d * i
values(1) = values(0) * (1 + e)
values(2) = values(1) * (1 + e)
values(3) = values(2) * (1 + e)
values(4) = values(3) * (1 + e)
npv_5 = NPV(Cells(4, "m"), values()) - 0
End Function

=npv_5($D9,$I9,$E9)

On Jan 17, 5:00*pm, jjones42 wrote:
I have a question regarding present values of uneven payments. *We
signed a lease that orignally was approx 30 months long and had three
payments even payments. *The next month, it was amended and required
three upfront payments monthly and then an annual payment on January
15th of each year through 2014.

I am using the XNPV function, but you will see on the attached that I am
not zeroing out on my amortization table which makes me think I am doing
this all wrong.

Can someone take a look at the attached spreadsheet (had to zip it
because its the only way they accept excel) and let me know if they
agree with my calc. *The original lease is on the first tab and the
amended lease is on the second.

Thanks!

+-------------------------------------------------------------------+
|Filename: Capital Lease Amortization Table.zip * * * * * * * * * * |
|Download:http://www.excelbanter.com/attachment.php?attachmentid=278|
+-------------------------------------------------------------------+

--
jjones42

I am sorry, I don't follow what you are saying.

"jjones42" wrote:
I am using the XNPV function, but you will see on the
attached that I am not zeroing out on my amortization
table which makes me think I am doing this all wrong.
[....]
+--------------------------------------------------------------------+
|Filename: Capital Lease Amortization Table.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=278|
+--------------------------------------------------------------------+

We cannot use XNPV for calculating the PV of the capital lease payments.
Instead, I have developed a formula that I believe you can use.
Alternatively, you can use Goal Seek or Solver to determine the PV.

(Note: I will rely on Goal Seek for now. I am still tweaking my formula.)

We cannot use XNPV for several reasons.


1. Typically (as I believe you do, too), capital leases assume payments and
interest in advance, whereas XNPV assumes interest accrues in arrears.

For example, if we have a 2-month capital lease [1] starting on 10/1/2010
with a single payment, the payment would be due on 11/1/2010, and we would
accrue two months in interest. But XNPV effectively accrues one month of
interest, namely (1+r)^(("11/1/2010"-"10/1/2010")/365), where "r" is the
annual rate.


2. XNPV compounds daily effective interest -- i.e. (1+r)^(1/365) -- whereas
you accrue simple interest based on a nominal interest rate, e.g. r/12 or
r/365. I don't know which is correct according GAAP for capital leases per
se. But your method "feels" right since it conforms to (US) practices for
most loans. In any case, it makes a significant difference.

For example, if we have a 6-month capital lease at 10% annual interest
starting on 10/1/2010 with a single payment of 316,996.25 on 3/1/2011, we
might compute a PV of 301,901.19 with one-half year interest of 15,545.06 --
301901.19*10%/2. But XNPV computes a PV of 302,283.53 with interest of
14,712.72, even if we fudge the lease end-date to compensate for XNPV's
computing interest in arrears --
302283.53*(1+10%/365)^(("4/1/2010"-"10/1/2010")/365)-302283.53.


3. You assume equal months in the "Original Lease" worksheet. XNPV computes
exact days from the PV date to the payment date, even adding 1 for leap
years even though the daily interest divisor is 365. (You compute exact
days in the "Amended" worksheet. So you are inconsistent.)


-----

Before we can proceed, we should address some mistakes (IMHO) that you made
in the "Original Loan" worksheet.

First, you cannot use the Excel PV function because your payments occur
irregularly.

Second, for first payments after 6 months, the monthly interest is
B19*B16/12, not B19*B16/6.

But note that the sum of the displayed amounts does not agree with the
actual sum. They differ by 2 cents. This is a common problem. IMHO, it
would be better to compute simple interest only on the payment line, e.g.
B19*B16/2.

Arguably, B19*B16*(A25-A19)/365, where A25-A19 computes the exact number of
days between payments. Again, I don't know which is correct according to
GAAP. But the equal-month assumption "feel" more correct insofar as it
conforms to my understanding of typical (US) practices for loans with
payments on monthly anniversary dates.


-----

Instead of trying to detail every change, I suggest that you download my
modified version of your file, titled "Capital Lease Amortization Table
modified.xlsx" from http://www.box.com/s/0ae1t9m7jlgcbdzk9qfv.

Each worksheet shows how I used Goal Seek to compute the PV of the lease
payments.

I also made changes in the amortization schedules.

In "Original Loan", I corrected the mistakes noted above, computing simple
interest based on months.

Similarly in "Amended", except that for the first payment, less than a month
from the start of the lease. In that case, simple interest is computed
based on actual days.

Note that interest is computed only when a payment is made. As noted above,
that ameliorates the problem of displayed values not matching the actual
values. The problem is still there; it is just less noticeable.

By the way, it is important to note that none of the computation are
explicit rounded to the cent. I think it is important not to do that in
amortization tables. It usually avoids infinitesimal calculation "errors"
(anomalies) that arise because of the way that Excel stores numbers.

Hope this helps. Questions?


-----
[1] 2 months probably does not meet the GAAP criteria for a capital lease
since 75% of the life of the asset is probably more than 2 months. I am
choosing smaller numbers simply to make the numerical calculations
tractable, if not obvious.

Errata....

I wrote:
We cannot use XNPV for calculating the PV of the capital lease payments.

I meant to add: unless the payments are on annual anniversary dates.

But Goal Seek or Solver can always be used.


I wrote:
Instead, I have developed a formula that I believe you can use.
[....]
(Note: I will rely on Goal Seek for now. I am still tweaking my
formula.)

I won't bother posting unless you are very interested.

As it is currently designed, it only handles payments on monthly anniversary
dates. It would not handle the payment 14 days after the start date for the
capital lease.

I could tweak it to handle that. But then it would not match __exactly__
with the monthly interest charges in the amortization schedule.

Of course, I could always special-case it. But that's exactly why I don't
think it is worthwhile, since Goal Seek and Solver work just fine for this.

Errata #2....

I wrote:
Instead of trying to detail every change, I suggest that you download my
modified version of your file, titled "Capital Lease Amortization Table
modified.xlsx" from http://www.box.com/s/0ae1t9m7jlgcbdzk9qfv.

If you already downloaded that file, please download the updated version;
same link.

I corrected two critical errors in the "Amended" worksheet.

1. The formula for the interest for the period ending with the first payment
referenced column A (date!) instead of column B (beginning balance).
(Klunk!)

2. The same formula mistakenly calculated interest in arrears instead of in
advance.

As a bonus, starting H1, I include the method for computing the PV directly
without having to resort to using Goal Seek or Solver.

It is really quite "obvious" once I stepped back from the algebraic
manipulations and thought about what I was doing. (Doh!)

Mathematically, the PV of the capital lease is the sum of the PVs of the
payments. No surprise there. The mathematical formula is:

PV = PMT1/(1+r1) + PMT2/((1+r1)*(1+r2)) + ... +
PMTn/((1+r1)*(1+r2)*...*(1+rN))

The Excel formula is simply (using "Amended" for example):

=SUMPRODUCT(I3:I7/K3:K7)

where I3:I7 are the payments and K3:K7 are the respective denominators, the
products of the applicable (uneven) periodic interest factors (rate plus 1)
1+r1, 1+r2, etc.

The set-up for the table in H2:K7 is easier to see in the uploaded Excel
file. But in a nutshell....

H I J K
2 12/01/2011 PMTn (1+rN) denomN
3 12/15/2011 105,452.40 1.0122 1.0122
4 1/15/2012 105,452.40 1.0083 1.0206
5 2/15/2012 105,452.40 1.0083 1.0291
6 1/15/2013 314,813.48 1.0917 1.1234
7 1/15/2014 314,813.48 1.1000 1.2358

The formulas in column are =J3 for K3, =K3*J4 in K4, etc.

For the payments on monthly anniversaries in rows 4 through 7, the interest
factors in J4 et al are straight-forward, namely:

=1+DATEDIF(H3,H4,"m")*$B$16/12

where B16 is the annual interest rate (10%).

For the first payment on 12/15, the interest factor in J3 is based on the
daily interest rate for the fractional month (12/1 to 12/15) plus the
monthly interest rate for the one month in advance starting 12/15. To wit:

=1+((H3-H2)*B16/365 + B16/12)

The key point is: since all of the interest rate calculations are done in
the table, including special handling of odd payment periods, the Excel
formula is quite simple and "obvious", much more straight-forward than I had
imagined.

PS.... I wrote:
I wrote:
Instead of trying to detail every change, I suggest that you download my
modified version of your file, titled "Capital Lease Amortization Table
modified.xlsx" from http://www.box.com/s/0ae1t9m7jlgcbdzk9qfv.

If you already downloaded that file, please download the updated version;
same link.

I forgot to mention.... Ignore any errors in the box.com preview. It is
buggy. Simply download the file.