Excel - actual/360 and 30/360 amortization?

Asked By Terr on 10-Jan-08 06:47 PM
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?




Tyro replied on 10-Jan-08 10:26 PM
Amortization is based on 2 schedules. 360 days or 365.

Tyro
joeu2004 replied on 11-Jan-08 07:57 AM
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.
joeu2004 replied on 13-Jan-08 03:10 AM
Errata....


Please forgive the incessant posts.  But a google search stumbled
across http://www.askarcs.com/loan_programs/definitions.asp , which
states:  the "monthly loan payments are the same for both methods".
Thus, PNC ARCS, at least, "one of America's leading commercial
lenders", computes the regular payment for an actual/360 loan using
the same 30/360 PMT() formula that I posted previously.

ARCS explains:  "This leaves the loan balance 1-2% higher than a
30/360 10-year loan with the same payment".  I don't know how they
figured that.  For a $100,000 loan at 6%, the balance and last payment
for an actual/360 loan is about 64.5%(!)higher than for a 30/360 loan
with the same monthly payments.  I suspect that instead of "loan
balance", they meant either the total payments or total interest,
which 0.5% and 2.2% higher respectively.

I had presumed that the regular payments were different for 30/360 and
actual/360 loans of the same terms (otherwise), and my presumption
seemed to be confirmed by the calculator at http://www.cmdatabase.com/calcac=
t360.html
=2E

However, I cannot find anything out about CMDataBase.com, other than
it is a "Commercial Real Estate Finance reference website".

Since PNC ARCS is a lender and CMDataBase.com does not appear to be, I
would trust ARCS.  However, perhaps there is simply no standard in the
(US) industry for computing the regular payment for actual/360 loans.

HTH.