Excel - Amortization w/ variable interest rate

Asked By Betty K

21-Nov-09 09:37 AM

Need to create a spreadsheet where the interest rate is variable and can be
entered each month.  Payments are being made in varying amounts also in order
to pay off earlier than the minimum payment would achieve.  Example:  $10,000
original loan amount dated 10/25/09; monthly payments of varying amounts
starting 11/25/09; current interest rate is 3.25%.  Would like to enter the
interest rate, payment amount and date of payment and have it calculate the
interest paid for that period, cumulative interest, and the declining
balance.  Are there too many variables to make this happen?

21-Nov-09 11:53 AM

No.  it is "no problem" at all, except that I am not aware of any existing
templates that will do this.  Then again, templates are not my thing.
Perhaps someone else can give you a pointer.

For each line of the amortization schedule (or payment record, if you
prefer), generally the interest due for the period is always
prevBal*rate/12, where "rate" is the current annual interest rate.

At least, that is the case for so-called "closed-end" loans like mortgages.
For "open-end" loans like a HELOC, the interest due for the period might be
based on a daily balance.  So the interest due for the period is
(nextDate-prevDate)*rate/365, where "nextDate" and "prevDate" refer to the
payment dates.

(Note:  I am writing from the perspective of the US.  I suspect the same
general principles apply to other countries.  But some details in the
calculations might differ.  Also, some computation details might vary
depending on the terms of the loan.)

In either case, the remaining balance is always prevBal - payment - intPaid.

This is true for any amortization schedule.  In your case, the difference
is:  you will want to determine the amount of "payment" and "rate" from
entries in the current line instead of from values calculated one time in
individual cells.



You might also want to have a column to calculate the minimum payment.  For
a "closed-end" loan, that would be based on the remaining number of months
for the loan.  For an "open-end" loan, you could chose an arbitrary term
(e.g. 5 years), unless the loan has a time limit.

If you would like some hands-on assistance with this, I would not mind
helping you construct a bare-bones spreadsheet (nothing fancy).  Since I
might need some particulars, it would be best if you sent me email.  If you
are interested, send email to joeu2004 "at" hotmail.com.


----- original message ----