Amortization schedule with veriable payments

[Gervis]

I need an amortization schedule that allows changing the monthly payment
amount. I have a house that I have sold to someone with a contract for deed.
I have an amortization schedule, but due to them having some financial
probems they have lowered the monthly payment with my permission. That is
why I would like to change the payment amount after 2 years of calculated
payments. I would appreciate any ideas. Gervis

 

[Joe User]

I need an amortization schedule that allows
changing the monthly payment amount.

Off-hand, I don't know of a free template that will do this. But it is very
easy to roll your own. (Most MS templates do it wrong anyway.)

If you like, I could probably whip up a bare-bones spreadsheet to get you
started. If you are interested, send email with the same subject to joeu2004
"at" hotmail.com.

The following are the basic formulas, although they could be embellished to
handle details properly (e.g. the last payment, and early loan termination).

B1, loan amount
B2, annual interest rate
B3, number of payments (loan term)
B4, number of payments per year
B5, periodic interest rate: =B2/B4
B6, normal periodic payment: =ROUNDUP(PMT(B5, B3, -B1),2)

B9, loan origination date
F9, initial balance: =B1

A10, payment number: 1
B10, payment date: =EDATE($B$9,ROW()-ROW($B$9))
C10, payment due: =$B$6
D10, interest amount: =F9*$B$5
E10, principal amount: =C10-D10
F10, remaining balance: =F9-E10

A11: =A10+1
B11: =EDATE($B$9,ROW()-ROW($B$9))
C11: =C10
D11: =F10*$B$5
E11: =C11-D11
F11: =F10-E11

Copy A11:F11 down for the number of payments.

Whenever the payment changes, simply replace the formula in column C in the
appropriate row. It will be propagated down automatically.

The catch-up payment can be computed based on the remaining balance and
remaining number of payments. That's a detail (TBD).


----- original message -----

 

[ryguy7272]

Here's a free amortization schedule:
http://www.vertex42.com/ExcelTemplates/loan-amortization-schedule.html

Change the payment after two years; you can calculate it to be anything you
want it to be.