One way to calculate the amortization schedule is:Īlthough I176 is exactly zero, note that K176 is not.
The monthly and daily interest rates are derived from the annual rate using simple interest calculations, as you did in the worksheet "XNPV":Ģ. This demonstrates the way a US lease would be calculated based on only the daily rate. This is due to binary arithmetic anomalies. Note that I176 and K176 are not exactly zero. One way to calculate the amortization schedule consistently with XNPV is: The compounded monthly and daily rate are derived from the annual rate as follows:Ģ. Refer to the worksheet "cmpnd daily" for corrections to the XNPV calculations. Refer to the worksheet "arrears v advance".įor the advance calculation, the interest calculation is based on only the previous balance (B1), where B3 is the periodic interest rate:Ĭompare with the interest payment calculated based on the ending balance (B7) after the first payment (B6):įor the arrears calculation, the interest calculation is based on the previous balance (B1) less the payment (B12):Ĭompare with the interest payment calculated based on the ending balance (B13) after the first payment: To demonstrate, consider a loan of $100,000 (B1) with 24 payments (B2) at a periodic interest rate of 1%. Your amortization schedule calculations are "in arrears".
But for US leases and loans, a simple daily or monthly rate is typically used.Īlso, as you say, leases are paid "in advance". That said, I might note that for US leases, XNPV is not the correct way to calculate interest rate and amortization schedule calculations. 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. I made that change to the original worksheet "XNPV" DAYS(C8,B8)+1 could be written just as easily as C8-B8+1, which works in all versions of Excel. If you want your workbook to be usable with versions before Excel 2013, do not use the DAYS function. Just download the file.įirst, a word to the wise. Refer to the the Excel file "Lease Schedule v2.xlsx" ( click here).
0 kommentar(er)
