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

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 A3:D3 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


"Lizzie" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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 A3:D3 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


"Lizzie" wrote in message
...
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