Amortization / Loan Payment Schedule

[Jeff Word]

I am trying to create a amortization schedule that will
keep track of interest/principal/Balance information for
the dates the payments are actually made rather than when
they were scheduled. Please let me know if you have one
or have any ideas on making one.

 

[Norman Harker]

Hi Jeff!

By all means post direct for some samples that show the basic formulas
and construction techniques that are used. You might also look at the
Microsoft template.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Thomas]

This should get you started,
You have 3 fields to fill first:
APR
Loan Amount
Start loan date

In your schedule you have 6 columns
payment date-payment- days of interest-interst-principal-balance

1 & 2>You fill in payment date and payment
3>Days of interest is payment date - loandate(after that it would be the
number of days between the payment dates)
4>Interest is APR/365*days of interest
5>principal is payment amount - interest
6>balance is loan amount - principal

 

[Thomas]

I think that used too be called the rule of 12 or something like that.
CC comapanies seem to do it the other way as it generates more interst.
Just checked a couple of my statements and its APR/12 on them to match
the interst they charged me.

 

[Thomas]

correction I should have written:
Just checked a couple of my statements and its APR/365

 

[Norman Harker]

Hi Thomas!

I've just checked a few home loan sites in the US and they are all
quoting the Annual Nominal Compounded Monthly. Also checked a few
mortgage calculators for US and they are all using Annual Nominal
Compounded Monthly. I think you'll find that it is a Truth In Lending
Act requirement.

Credit Card companies may quote a daily effective rate in which case
the Truth in Lending legislation requires they quote the Annual
Nominal Compounded daily which would be the daily rate multiplied by
365.

Credit card amortization details are different from home loan ones. I
do have a user defined function that calculates the term of a credit
card loan that requires inputs of the amount of the balance, the loan
rate as Annual Nominal compounded monthly, the minimum percentage
repayment and the minimum repayment amount.

This is a problem of using the very loose term APR. To my mind a
reference to an APR without reference to a frequency of compounding is
meaningless. The general assumption with mortgages though is that it
is monthly compounding and the monthly rate applied is the rate
divided by 12.

How I wish that the general comparator rate was prescribed as the
Annual Effective Rate.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.