Help with Lease Amortization Schedule

texmln

New Member
Joined
Sep 21, 2017
Messages
2
I am trying to amortize a lease liability schedule to zero using an assumed rate of 5%. I realize the implicit rate is different but I've discounted the cash flows at 5% and would like the amortization to zero out starting with the current lease liability derived using XNPV. I get close to zero but not exactly zero. Part of my problem is a stub period payment up front and I have payment increases at two points in the schedule that are irregular in that they do not occur annually and are not a fixed percentage. The lease payments are in advance for each period. What am I missing to make the amortization zero at in the final period? Here's a link to my schedule:

https://app.box.com/s/tc1rxmmgnz3259ny8894ypfmnvewikvx

I appreciate any advice you can offer.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Last edited:
Upvote 0
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
[....]
For the arrears calculation, the interest calculation is based on the previous balance (B1) less the payment (B12):
B15: =(B1-B12)*B3

Aarrgghh!! I got the terms "advance" and "arrears" mixed up -- just a typo, of sorts.

The first calculation is for arrears, which is common for regular loans.

The second calculation is for advance, which is common for leases.

Sorry for any confusion.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top