The lease payments are in advance for each period. What am I missing to make the amortization zero at in the final period?
Refer to the the Excel file "Lease Schedule v2.xlsx" (
click here) [1]. Ignore any preview errors. Just download the file.
[1] https://app.box.com/s/nk8cgzi0kivi6kv924nxhct2q24spo6f
-----
First, a word to the wise.... If you want your workbook to be usable with versions before Excel 2013, do not use the DAYS function. DAYS(C8,B8)+1 could be written just as easily as C8-B8+1, which works in all versions of Excel. I made that change to the original worksheet "XNPV"
-----
If you want to use XNPV to calculate the initial value of the lease, your interest rate and amortization schedule calculations must mimic the way that XNPV is calculated.
That said, I might note that for US leases, XNPV is not the correct way to calculate interest rate and amortization schedule calculations. XNPV assumes a compounded daily rate. But for US leases and loans, a simple daily or monthly rate is typically used.
Also, as you say, leases are paid "in advance". Your amortization schedule calculations are "in arrears".
To demonstrate, consider a loan of $100,000 (B1) with 24 payments (B2) at a periodic interest rate of 1%. Refer to the worksheet "arrears v advance".
For the advance calculation, the interest calculation is based on only the previous balance (B1), where B3 is the periodic interest rate:
B9: =B1*B3
Compare with the interest payment calculated based on the ending balance (B7) after the first payment (B6):
B8: =B6 - (B1-B7)
For the arrears calculation, the interest calculation is based on the previous balance (B1) less the payment (B12):
B15: =(B1-B12)*B3
Compare with the interest payment calculated based on the ending balance (B13) after the first payment:
B14: =B12 - (B1-B13)
-----
Refer to the worksheet "cmpnd daily" for corrections to the XNPV calculations. In summary:
1. The compounded monthly and daily rate are derived from the annual rate as follows:
C2: =(1+C1)^(1/12) - 1
C3: =(1+C1)^(1/365) - 1
2. One way to calculate the amortization schedule consistently with XNPV is:
I8: =E8-J8
J8: =H8-K8
K8: =(H8-E8) * (1+$C$3)^D8
Copy I8:K8 into I9:K176
Note that I176 and K176 are not exactly zero. This is due to binary arithmetic anomalies. They can be avoided, if necessary.
-----
Refer to the worksheet "smpl daily". This demonstrates the way a US lease would be calculated based on only the daily rate. In summary:
1. The monthly and daily interest rates are derived from the annual rate using simple interest calculations, as you did in the worksheet "XNPV":
C2: =C1/12
C3: =C1/365
2. One way to calculate the amortization schedule is:
I8: =E8-J8
J8: =H8-K8
K8: =(H8-E8) * (1 + $C$3*D8)
Although I176 is exactly zero, note that K176 is not. Again, this is due to binary arithmetic anomalies, and the fact that I176 is exactly zero is coincidental.
Note: A lendor that uses only daily interest rates might use C1/366 in leap years.
3. The initial loan in H8 is derived by using Solver to set K176 to zero by changing H8. In order to get a small enough non-zero remainder in K176, I set the Constraint Precision option to 0.000000000001.
4. In order to calculate the initial loan by using XNPV, the annual rate would need to be the value in G1. That was also derived by using Solver to set H1 to zero by changing G1. In order to avoid a Solver error, G1 must have a non-zero initial value. I used 5% (B1); but any positive value should work.
Off-hand, I do not see any relationship between the annual rate derived in G1 and the annual rate in B1. In particular, G1 is not the "effective" rate for the "nominal" rate in B1.
-----
However, I do not believe that the amortization of monthly US leases and loans are based on only the daily rate. Instead, I believe they follow the APR rules in Truth in Lending regulations, to wit: use the monthly rate for monthly payments ("regular periods"); and use the daily rate for sub-monthly payments ("odd periods").
Refer to the worksheet "smpl monthly". This demonstrates the way a US lease would be calculated based on monthly rates for regular periods and daily rates for odd periods. In summary:
1. The monthly and daily interest rates are derived from the annual rate using simple interest calculations, as you did in the worksheet "XNPV":
C2: =C1/12
C3: =C1/365
2. One way to calculate the amortization schedule is:
I8: =E8-J8
J8: =H8-K8
K8: =(H8-E8) * (1 + C3*D8)
I9: =E9-J9
J9: =H9-K9
K9: =(H9-E9) * (1+$C$2)
Copy I9:K9 into I10:K176.
3. As above, the initial loan in H8 is derived by using Solver to set K176 to zero by changing H8.
4. And as above, in order to calculate the initial loan by using XNPV, the annual rate would need to be the value in G1, which is also derived by using Solver to set H1 to zero by changing G1.
