loan amortization

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" <Lizzie@discussions.microsoft.com> wrote in message
news:274232E2-F529-4567-9FCD-A2CEF7D57DEB@microsoft.com...
>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" <Lizzie@discussions.microsoft.com> wrote in message
> news:274232E2-F529-4567-9FCD-A2CEF7D57DEB@microsoft.com...
> >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
>
>
>