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?

Yes, Excel can differentiate between actual/360 and 30/360 amortization when creating an amortization schedule from scratch. Here's how:

1. Open a new Excel spreadsheet and create a table with the following columns: Payment Date, Payment Amount, Interest, Principal, and Balance.
2. In the Payment Date column, enter the first payment date.
3. In the Payment Amount column, enter the total payment amount for each period.
4. In the Balance column, enter the initial loan amount.
5. In the Interest column, enter the formula to calculate the interest for each period. For actual/360, the formula is:
   Code:

   (Balance * Rate * Days)/360

   . For 30/360, the formula is:
   Code:

   (Balance * Rate * Days)/360

   , where Days is calculated as follows: if the payment date falls on the last day of the month, use 30; if the payment date falls on the 31st day of the month, use 30; otherwise, use the actual number of days in the month.
6. In the Principal column, enter the formula to calculate the principal for each period. The formula is: Payment Amount - Interest.
7. In the Balance column, enter the formula to calculate the remaining balance for each period. The formula is: Balance - Principal.
8. Copy the formulas down to the rest of the rows in the table.
9. Format the table as desired.

By following these steps, you can create an amortization schedule in Excel that differentiates between actual/360 and 30/360 amortization.

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

Tyro
"Terry" wrote in message
...
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?

On Jan 10, 3:47 pm, Terry wrote:
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, whe 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.

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.