lease amortization schedule with residual value excel

The 1st producing the residual value immediately after the final payment (so at the beginning of month 24, which is effectively the end of month 23 considering either a second before or after midnight). The Beginning worksheet shows 2 amortization tables. I've also made a fair number of changes in your Example worksheet. I've added simple amortization tables with fixed interest rates in 2 additional worksheets. This should not come as a shock as it's not the only error, though MSFT's apparent indifference to correcting them is impressive. One implication of this is that Excel's documentation is wrong for PMT and IPMT. That incurs interest between the beginning and end of month 24, and balance at the beginning of month 24 plus interest incurred during month 24 produce 15K residual value at the END of month 24. Thus the balance after the 24th payment at the BEGINNING of the 24th month should be 14,936.52. For positive residual value AND payments at the Beginning of each period, you have to make adjustments to produce exact amortization table results.Īlso, for Beginning repayment type, the residual value is realized at the END of the loan period, so in your example at the END of 24 months.

Perfectly reasonable for that, but that scenario invariably calls for a residual value of 0. The amortization table you're using is meant for variable interest rates, which is why it recalculates the loan payment in each row in E13:E372 for the months remaining. In other words, your amortization table is wrong in month 1 for Beginning. Getting back to the semantic point above, while the balance upon which interest would be incurred in month 1 would be D13-E13, that interest would be paid with the 2nd loan payment at the beginning of month 2, not at the beginning of month 1. IPMT doesn't allow interest payments after the end of the loan term. That is, if the loan amount if 200,000.00, you'd receive it on, AND you make an 8,153.59 loan payment on, you're immediately paying down the loan and have incurred NO INTEREST.Īnyway, the cause of the #NUM! error in M36 is =IPMT($D$5/12, C36+1, $D$6,-$D$4,$D$10,$E$9). Semantics: when loan payments are made at the BEGINNING of each period, the first such loan payment is an immediate reduction of the loan amount.

Lease amortization schedule with residual value excel