Interest rate calculation with payment increases and early access

[tbarnett]

Hello folks!

I'm working on a lease accounting template, particularly the interest calculation for the capital lease liability, and am having trouble with circular references. There is an early access period during which interest expense increases the liability. Once payments commence, they are split between liability reduction and interest expense. The interest rate is unknown and is calculated such that the liability is zero at the end of the lease term. There are also several inputs that drive the calculation, pls see attached for more detail:

https://linkedin.box.com/s/v6gqppsmwy6anfsv4317

I'm looking forward to any suggestions you may have. Thanks so much for your time!
Also can't wait for the day when I can become an excel guru and help others on this site .

Best,
Tyler

 

[joeu2004]

I'm working on a lease accounting template, particularly the interest calculation for the capital lease liability, and am having trouble with circular references.

I don't see any circular references, and Excel does not indicate or find any. For my edification, can you identify one formula with a circ ref in the uploaded file at
https://linkedin.box.com/s/v6gqppsmwy6anfsv4317?

Also, are you sure the model in AJ:AL is correct; in particular, the interest calculation in AK?

My understanding is: (most/all?) capital leases are based on "payment in advance"; i.e, at the beginning of the period. Thus, I believe interest is calculated on previous balance minus the current payment.

In contrast, your model is no different than "payment in arrears" (end of period), to wit: interest is calculated on the previous balance.

If you're sure your model is correct, so be it. I'm not used to "payment in advance" models.

On second thought, this might be just a matter of semantics. In your model, the first payment seems to be made 1 month after the lease begins. If that is correct, it is effectively the same as "payment in arrears".

 

[joeu2004]

Also, are you sure the model in AJ:AL is correct; in particular, the interest calculation in AK?

My understanding is: (most/all?) capital leases are based on "payment in advance"; i.e, at the beginning of the period. Thus, I believe interest is calculated on previous balance minus the current payment.

Oh, you might have based your amortization schedule on the example that I provided in a previous thread about this problem (click here) [1]. (I thought all this sounded familiar.)

But I based that on your requirements; in particular, you wrote: "I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month".

I probably did not give any thought to the fact that you said this is a capital lease. (Klunk!)


-----
[1] http://www.mrexcel.com/forum/excel-questions/761879-interest-rate-calculation-payment-increases.html#post3736724

 

[tbarnett]

Oh, you might have based your amortization schedule on the example that I provided in a previous thread about this problem (click here) [1]. (I thought all this sounded familiar.)

But I based that on your requirements; in particular, you wrote: "I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month".

I probably did not give any thought to the fact that you said this is a capital lease. (Klunk!)


-----
[1] http://www.mrexcel.com/forum/excel-questions/761879-interest-rate-calculation-payment-increases.html#post3736724

Hi there joeu2004!

Yes, I'm incorporating your previous calculation (modified slightly), however I can't figure out how to calculate the first few months interest when there are no payments being made. This file has the circular reference intact:
https://linkedin.box.com/s/tgo1x47auip6k68v62vg

Thanks for the comment regarding the interest calculation in column AL. I think it's correct: previous month's balance + interest expense [previous months balance * interest rate] - payment. Can you suggest an alternate calculation?

Again, your guidance is SUPER helpful!

Best,
Tyler

 

[joeu2004]

This file has the circular reference intact:
https://linkedin.box.com/s/tgo1x47auip6k68v62vg

Thanks for the comment regarding the interest calculation in column AL. I think it's correct: previous month's balance + interest expense [previous months balance * interest rate] - payment. Can you suggest an alternate calculation?

Download "NEW Lease Review Template 3.14 Scrubbed.xlsm" (click here) [1]. That incorporates my corrections to avoid the circular reference. I also made some other corrections and implemented some suggestions. See the comment callouts.

The file contains two worksheets with models: "Pmt in Arrears", which corrects your original model with payments at the end of each period; and "Pmt in Advance", which demonstrates a model for payments at the beginning of each period.

I am not an expert on capital leases. But several non-authoritative sources indicate leases differ from loans insofar as payments are in advance (beginning), not in arrears (end).

For long-term leases, the difference is probably small. For your example, the annual interest rate is about 3.22% for payments in arrears and 3.26% for payments in advance. But it is the principle of the matter.


-----
[1] https://www.dropbox.com/s/g8vr40fzklocl2h/NEW%20Lease%20Review%20Template%203.14%20Scrubbed.xlsm

 

[tbarnett]

Re: Interest rate calculation with payment increases and early access - SOLVED

OUTSTANDING!

Thanks so much joeu2004!

Tyler