When creating an amortization schedule in Excel from scratch (i.e., not using
the templates) can Excel differintiate between actual/360 amortization and
30/360 amort? If so, how?
Well, Excel itself does not make the differentiation (except for some
bond functions). But you can make the differentiation in your
formulas.
(Note: The following applies to US loans and for countries that are
similar. In particular, it does not apply to Canadian loans. If you
are asking about Canadian loans, please post back.)
For both 30/360 and actual/360, the daily interest rate is the
annualRate/360.
For 30/360, each month is considered to be 30 days. Thus, the monthly
interest rate for on-time payments is annualRate*30/360, which is the
same as annualRate/12. The regular monthly payment can be computed
by:
roundup(pmt(annualRate/12, termInMonths, -loanAmount), 2)
(Note: Because of rounding, the last payment will probably be less
than the regular payments. Be sure to allow for that in your formula
design.)
Late payments would accrue interest at the daily rate of annualRate/
360 per day.
For actual/360, the monthly interest rate varies depending on the
actual number of days in the month. The monthly rate is
days*annualRate/360, where "days" is the actual number of days between
payment due dates or actual payment dates, the latter applying to late
payments. That is, "days" is D2 - D1, where: D1 is the previous
payment due date if it was on time or early, otherwise D1 is the
actual late payment date; and D2 is the current payment due date if it
is on time or early, otherwise D2 is the actual late payment date.
I must say that I do not know how any lending institution that uses
actual/360 (none that I know of) computes the regular monthly
payment. The best I can do with a formula is:
roundup(pmt((365/12)*annualRate/360, termInMonths, -loanAmount), 2)
In one trial amortization schedule, that results in a large final
payment -- more than 2.5 times the regular payment. Not surprisingly,
replacing "365/12" with 31 (the highest monthly rate), the resulting
payment is much too high. In my trial amortization, the loan was paid
off nearly 4 years early.
I use the payment above (based on "365/12" times the daily rate) as a
starting point and, using a binary search, increase that amount until
the last payment is a little less than the regular amount, but
otherwise the loan amortizes in the stated number of months. In my
trial amortization, that was less $1 more per month. But of course,
that difference will depend on the terms of the loan.
(Perhaps Solver could be used here. But the way that I set up my
amortization schedules confuses Solver. However, I have not played
with the Solver options to see they work around the problem. It
really is "too easy" for me to do this manually rather than fit the
Solver to the problem or fit the problem to Solver.)
HTH.
