Help with amortization template

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

meyer47@gmail.com - 28 Sep 2006 22:09 GMT

I have used the Amortization template but would like to adjust it so
that it automatically fills in the missing value. If I have 3 of the 4
values (note amount, term, payment and interest rate) it would fill in
the missing value and create an amortization table.

How do I do this? Thanks.

Reply to this Message

Bernie Deitrick - 29 Sep 2006 16:31 GMT

In cells A1:F1, enter

Pmt #
Note Amount
Term
Payment
Interest Rate
Principal

In Cells A2:F2, enter the formulas:
(Note that these formulas will return errors initially - the errors will resolve when you have
entered three of the four items)
=ROW()-1
=PV(E2/12,C2,D2)
=NPER(E2/12,D2,B2)
=PMT(E2/12,C2,B2)
=RATE(C2,D2,B2)*12
=D2+B2*E2/12

In cells A3:F3, enter the formulas:
=ROW()-1
=B2+F2
=C2
=D2
=E2
=D3+B3*E3/12

Then in cells B2:E2, enter the three items that you know - just type over the formula with the
value. Note that the term is assumed to be in months, the Amount and Pmt need to be of opposite
signs (one needs to be positive, the other negative) and the rate is the annual rate.

Then copy Cells A3:F3 down for as many rows as you have months in the term.

HTH,
Bernie
MS Excel MVP

>I have used the Amortization template but would like to adjust it so
> that it automatically fills in the missing value. If I have 3 of the 4
> values (note amount, term, payment and interest rate) it would fill in
> the missing value and create an amortization table.
>
> How do I do this? Thanks.

Reply to this Message

meyer47@gmail.com - 04 Oct 2006 19:40 GMT

Bernie,
Thanks for the response. This is somewhat helpful, but I was really
looking for a template that made this easier. I'm surprised the Excel
template that MS provides does not do this.

-Rich

Reply to this Message