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