B
BJ
I am trying to define a payment formula that can account for irregular
payment periods.
I think I should be able to do this with the following XNPV formula,
where the VALUES contains the original loan amount (negative) and the
subsequent payments (set amount) and the DATE contains the original
loan start and subsequent payment dates.
=0-XNPV((1+0.08/12)^(366/30)-1, VALUES, DATES)
My problem is that to determine the set payment amount, I use the PMT
function, but when I plug in regular periodic dates, the loan contains
a balance at the end of the term (should be 0).
I understand that the fixed payment formulas do not really work
against monthly payments since these periods may range from 28-31
days in length. But even when I used equal periods of 30 or 31 days
apart, I get a balance at the end of the term. Consequently, I am not
certain if the above formula is correct and don't know how to verify
it, so I wanted to check with someone that really knows accounting to
see if I am doing this correctly.
Thanks.
-b
payment periods.
I think I should be able to do this with the following XNPV formula,
where the VALUES contains the original loan amount (negative) and the
subsequent payments (set amount) and the DATE contains the original
loan start and subsequent payment dates.
=0-XNPV((1+0.08/12)^(366/30)-1, VALUES, DATES)
My problem is that to determine the set payment amount, I use the PMT
function, but when I plug in regular periodic dates, the loan contains
a balance at the end of the term (should be 0).
I understand that the fixed payment formulas do not really work
against monthly payments since these periods may range from 28-31
days in length. But even when I used equal periods of 30 or 31 days
apart, I get a balance at the end of the term. Consequently, I am not
certain if the above formula is correct and don't know how to verify
it, so I wanted to check with someone that really knows accounting to
see if I am doing this correctly.
Thanks.
-b