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.