loan amortization

[Guest]

I have downloaded a loan amortization template which allows for extra
payments, but only regular extra payments and the same payment amount each
time for the life of the loan.
I need a template (or god forbid a formula) that allows me to put in
payments as they are made, put in skipped payments, calculates interest
daily, and one which I can vary the interest rate. I know I would be pushing
my luck, but any chance of adding a function which calculates all extra
interest payments (for example if I needed to outline overdue payments
seperate to overdue interest charges). I am not familiar with formulas (not
stupid just inexperienced with excel) so please don't use any excel jargon to
explain your answer if it is at all possible. Many thanks

 

[Bernie Deitrick]

Lizzie,

Open a new sheet, and in cells A1:F1, enter the following headers:

A1: Date
B1: Loan Amount
C1: Interest Rate
D1: Interest
E1: Payment
F1: Penalty

Enter in:
A2: starting date of loan
B2: starting amount of loan
C2: Annual percentage rate, entered as a percent
D2: the formula =IPMT(C2/365,1,1,-B2)
E2: leave blank
F2: leave blank

Enter in:

A3: =A2+1
B3: =B2+D2-E2+F2
C3: =C2
D3: =IPMT(C3/365,1,1,-B3)
E3: leave blank
F3: leave blank

Copy A33 down as far as you need: 365 rows for one year.

Enter any payments into column E on the date that they are made, and any penalty in column F on the
day that they are assessed.

If your interest rate changes, simply type the new rate as a percent into column C on the date row
that the change takes affect.

That should give you a good start on your problem.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Many thanks Bernie - I will give it all a go. Thank you