software

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

quot;Bernie Deitrickquot; wrote:

gt; Lizzie,
gt;
gt; Open a new sheet, and in cells A1:F1, enter the following headers:
gt;
gt; A1: Date
gt; B1: Loan Amount
gt; C1: Interest Rate
gt; D1: Interest
gt; E1: Payment
gt; F1: Penalty
gt;
gt; Enter in:
gt; A2: starting date of loan
gt; B2: starting amount of loan
gt; C2: Annual percentage rate, entered as a percent
gt; D2: the formula =IPMT(C2/365,1,1,-B2)
gt; E2: leave blank
gt; F2: leave blank
gt;
gt; Enter in:
gt;
gt; A3: =A2 1
gt; B3: =B2 D2-E2 F2
gt; C3: =C2
gt; D3: =IPMT(C3/365,1,1,-B3)
gt; E3: leave blank
gt; F3: leave blank
gt;
gt; Copy A33 down as far as you need: 365 rows for one year.
gt;
gt; Enter any payments into column E on the date that they are made, and any penalty in column F on the
gt; day that they are assessed.
gt;
gt; If your interest rate changes, simply type the new rate as a percent into column C on the date row
gt; that the change takes affect.
gt;
gt; That should give you a good start on your problem.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;Lizziequot; gt; wrote in message
gt; ...
gt; gt;I have downloaded a loan amortization template which allows for extra
gt; gt; payments, but only regular extra payments and the same payment amount each
gt; gt; time for the life of the loan.
gt; gt; I need a template (or god forbid a formula) that allows me to put in
gt; gt; payments as they are made, put in skipped payments, calculates interest
gt; gt; daily, and one which I can vary the interest rate. I know I would be pushing
gt; gt; my luck, but any chance of adding a function which calculates all extra
gt; gt; interest payments (for example if I needed to outline overdue payments
gt; gt; seperate to overdue interest charges). I am not familiar with formulas (not
gt; gt; stupid just inexperienced with excel) so please don't use any excel jargon to
gt; gt; explain your answer if it is at all possible. Many thanks
gt;
gt;
gt;