amortization Please help :(

[Guest]

I need to produce a loan amortization with the ability to input actual
historic payments made or missed (need to add a late fee to missed or late
payments) and I also need to vary the interest rate part way through the
schedule. I have searched office templates and the only thing that comes
close is a template which allows for extra payments (but they have to be same
amount and date each month). I am a bit hopeless on the old excel as I have
no idea what formulas I would need to even produce such a template. PLEASE
HELP!! Can somebody direct me to a site that may offer free software that
performs the functions outlined above - anything would be good. Cheers!

 

[Bucky]

I need to produce a loan amortization with the ability to input actual
historic payments made or missed (need to add a late fee to missed or late
payments) and I also need to vary the interest rate part way through the
schedule.

You can still use the Excel Loan Amortization template. Just unprotect
the sheet by:
Tools > Protection > Unprotect Sheet

This will allow you to enter in varying amounts for extra payment. To
handle late fees, you can add them to the Beginning Balance amount. For
a change in interest rate, you can enter the rate in a cell at the top,
then change the interest calculation to reference the new rate for the
appropriate rows.

 

[Guest]

Hi Bucky,

Thanks so much. That all sounds so simple! Thanks for taking the time to
help me - I seriously needed it. Just a quick question if you have the time
to answer, the only thing that I won't be able to do going by your solution,
is be able to indicate within the schedule is actual amounts debited for
overdue fees. Meaning, I understand what you mean by adding it to the
beginning total, but apart from entering the value of the fee into the
beginning total, how else can I visually show and sum late payment fees??
Would I need to attach a new work sheet to the schedule and manually put it
into the beginning total for each month as well?

Anyway, again thanks for the solution, it covers 99% of what I needed to do.
I will give it a go. This is the first time I have ever used this
discussion group to get help with a microsoft problem office question and you
were the first to reply (seemingly only one so far too I think) and you were
very helpful.

Cheers!!

 

[Bucky]

the only thing that I won't be able to do going by your solution,
is be able to indicate within the schedule is actual amounts debited for
overdue fees. Meaning, I understand what you mean by adding it to the
beginning total, but apart from entering the value of the fee into the
beginning total, how else can I visually show and sum late payment fees??
Would I need to attach a new work sheet to the schedule and manually put it
into the beginning total for each month as well?

You can easily do that too. Once you unprotect the sheet, you can
customize it any you want. Just add another column at the end called
"Late Fees". Then alter the Beginning Balance formula to equal previous
month's Ending Balance + previous month's late fees.