Help with amortization template
 

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.

Help with amortization template
 

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


wrote in message ups.com...
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.

Help with amortization template
 

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