3236 views

Skip to first unread message

Jan 10, 2008, 6:47:01 PM1/10/08

to

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?

the templates) can Excel differintiate between actual/360 amortization and

30/360 amort? If so, how?

Jan 10, 2008, 10:26:41 PM1/10/08

to

Amortization is based on 2 schedules. 360 days or 365.

Tyro

"Terry" <Te...@discussions.microsoft.com> wrote in message

news:6A25D082-5A48-4EB7...@microsoft.com...

Jan 11, 2008, 4:19:29 AM1/11/08

to

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.

Jan 11, 2008, 8:21:54 PM1/11/08

to

Errata....

On Jan 11, 1:19 am, I wrote:

> 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.

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/calcact360.html

.

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.

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu